Analysis of waitstats is the first step that I would take with any performance tuning activities on a system. Thats important because it is an easy and efficient way to understand the issues with a system at a very high level. There are many different wait types assocaited in SQL server which gives different meanings of issues. Here I would like to start a series on WAITSTATS analysis.

I am going to take CXPACKET as my first wait type.CXPACKET wait type is occuring where a query is parallelized and the parallel threads are not given equal amounts of work to do. Ther may be many threads depends on the core of CPUs and one thread may have a lot more to do than the others, and so the whole query is blocked while the long-running thread completes. This is a major kind of wait type seeing in many of the systems causing a delay in processing. If this is combined with a high number of PAGEIOLATCH_XX waits, it could be large parallel table scans going on because of incorrect non-clustered indexes, or out-of-date statistics causing a bad query plan.

A typical representation of Parallel execution as follows:

The below shows the effect of parallelism on multi processor environment .(The below is not related with the above query.I wanted to take a better from different execution to show the difference)

MAXDOP can be set at two places:

1. At server side:
EXEC sys.sp_configure N'max degree of parallelism', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

2. At Query level:
Using Option (MAXDOP 2) at the end of the query.

Important point to be noted here is that the server level configuration can be overwritten at query level.

Interpretation of parallelism is very important as it can cause a delay , ofcourse even speed up as well. Very often parallelsim cause issues on OLTP systems.There are several area where parallelism may give you a better results like rebuilding indexes, update statistics ect. So it is always better to evaluate the situation and disable the parallelism at server side. Mainly parallelism is occuring because of one of the following:

1. Query is not a pure OLTP query.
2. No proper index.
3. No proper filter conditions in query.

Useful Queries

--**********************Finding out the currently running parallel queries in the server Starts **********

SELECT 
	r.session_id
	,r.request_id
	,MAX(ISNULL(exec_context_id, 0)) as number_of_workers 
	,r.sql_handle ,r.statement_start_offset 
	,r.statement_end_offset 
	,r.plan_handle 
FROM sys.dm_exec_requests r 
	JOIN sys.dm_os_tasks t 
	ON r.session_id = t.session_id 
	JOIN sys.dm_exec_sessions s 
	ON r.session_id = s.session_id 
WHERE s.is_user_process = 0x1 
GROUP BY 
	r.session_id
	,r.request_id
	,r.sql_handle
	,r.plan_handle
	,r.statement_start_offset
	,r.statement_end_offset 
HAVING MAX(ISNULL(exec_context_id, 0)) > 0

--**********************Finding out all parallel queries in server(using cached plans) *******************

SELECT 
	 p.*
	,q.*
	,cp.plan_handle 
FROM sys.dm_exec_cached_plans cp 
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p 
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS q 
WHERE cp.cacheobjtype = 'Compiled Plan' 
AND p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; max(//p:RelOp/@Parallel)', 'float') > 0