Friday 2 May 2014

why MAXDOP table hint is important?Is it required to know?

           When SQL Server runs on a computer with more than one processor or CPU, it detects the best degree of parallelism, that is the number of processors employed to run a single statement, for each query that has a parallel execution plan. You can use the max degree of parallelism option to limit the number of processors to use for parallel plan execution and to prevent run-away queries from impacting SQL Server performance by using all available CPUs.  
             The MAXDOP hint is used to implicitly specify the maximum degree of parallelism to use for a specific statement.  The degree of parallelism is roughly defined as how many separate processors to utilize for a single query.  

SELECT *
FROM dbo.test_demo 
OPTION (MAXDOP 1)
This will force only one instance of the SPID to be spawned. To allow more instances of a SPID to be spawned replace the 1 with the maximum number.
This needs to be specified after each statement. It is not specific to the statement.
 what is default value for maxdop?
The default value for MAXDOP is 0 (zero) and can be set or viewed using (sp_configure). A value of 0 means that SQL Server will use all processors if a query runs in parallel.
We can change this value based on our requirment.
sp_configure 'max degree of parallelism' 5.
 This means if a query uses a parallel execution plan it will only use four of the available processors.


First Database In Sql Server