Databases Reference
In-Depth Information
How it works...
SQL Server has a smart algorithm to decide whether to generate parallelism (the use of
more than one processor to execute the query) for a query or not. Overriding SQLServer's
decisionabout the number of processors to use needs expertise as well as experience.
There is no predefined number available for this setting, as deciding on a number depends
completely on the type of server you are using, the type of use you are having, the workload on
the server, and many other factors. There is only one sure way of deciding upon the number of
CPUs—by experimenting on the server.
In Step 1, we had executed the
SP_Configure
stored procedure for "max degree of
parallelism" with value
0
(zero), which is the default value. The zero indicates that SQL Server
has the power to decide whether to generate parallelism or not, and if yes, how many CPUs
should be used. If you set the value as
4
rather than
0
, SQL Server will use up to four cores to
process the query. If you set the value as
1
, it means that parallelism will not occur and the
query will be processed by one processor only.
In Step 2,
OPTION
(MAXDOP
numeric
value
)
sets the parallelism value for that particular
query as against the instance-level settings given in Step 1.
There are two
SELECT
queries executed in Step 2, out of which the first query uses (
MAXDOP
1
). It means that no parallelism will occur and the process will be processed by one processor
only. In the second
SELECT
query, (
MAXDOP
0
) is used, which means that SQL Server decides
whether to use parallelism or not.
Because of the
SET
STATISTICS
TIME
option with a
SELECT
query, we can see how much
CPU time is consumed by each query, in the Messages tab, beside the Results panel. You
might get a different CPU time in your instance, as it depends on the number of servers,
available memory, and many more things. You might also get a different CPU time and
elapsed time each time you execute the query.
There's more...
Change the default settings for "max degree of parallelism", as they can be dangerous on
a live server. So, keep the current value handy, in case you are making a change, and also
consult a senior or colleague before doing this on a live server. Based on my experience, I'm
not comfortable assigning all the available processors to process a single query in SQL Server.
Apart from that, I wouldn't touch this setting on an OLTP database, as changing this setting in
a big OLTP database creates uncertainty in performance that users won't like. But, it is good
to assign as many processors as possible in the database warehouse system.
Search WWH ::
Custom Search