“Wait” is one of the very familiar word in human’s life. Nobody likes to wait though it is not always bad to wait for a while for something good, at the same time long unnecessary waiting period resulted into frustration.
When I am about to finish my day in office, I always gets eager to meet my 2 year old son “Teerth”. I enjoy this wait period, if I would be with him for day, I wouldn’t get pleasure of meeting him at the end of day, though I enjoy his company whole day.
I don’t like to wait for upload/download something due to slow internet speed, it is unproductive and unnecessary wait time. Who likes to wait for 1 minute video on YouTube.com gets 5 minutes to load (streaming)?
This is how I evaluate wait period and SQL Server apply almost same kind of rule. It is up-to you to know wait stat, identify whether it is good (necessary) or bad and resolve it if possible.
In simple words, you would like to see result of your request immediately. You expect, you execute query in SQL Server and it finishes operation immediately. In normal scenario SQL Server even do it but there are many cases and real time scenario which wouldn’t let operation go smooth.
There are so many reasons which makes your query waiting, whether it is memory issue, CPU pressure, blocking, locking, dead locking to name a few among so many.
There is one DMV which you can use to monitor wait stats. “sys.dm_os_wait_stats” is your weapon to deal with wait stats.
You can execute simple SELECT query on “sys.dm_os_wait_stats” to see all wait stats along with the value in your system.
Yesterday I got an email from one of the reader this blog, he was asking that he is facing big values in CXPACKET wait stats. Is it good or bad? If bad, how can I can resolve it? This email from the reader insist me to write this blog post.
As per MSDN, CXPACKET occurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem. To find detail about different wait stats, click here.
Generally parallelism is being used to execute your process so for example, if you have executed SELECT query and four processor is executing your SELECT query now three processor has finished their part of job but those processors can’t show you the result until last one processor return the result because request result of SELECT query can only be completed if all four processors return result and clubbed together. Since your last processor was still working, three processors had to wait which can raise CXPACKET wait stat.
To overcome this problem, you can decrease value of MAXDOP or Max degree of parallelism. I have written article on this topic earlier which you can refer by following link:
Note: This is just a generic advice to handle CXPACKET wait type. For more accurate implementation, need to study the environment in detail. Generally in Online Transactional Pr0cessing (OLTP) should have low number of value (near to zero) for MAXDOP as it used to have quick and short transaction and Online Analytical Processing (OLAP) should have higher number of MAXDOP.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.