Sql-server – Wait Stats, Server Up Time and CXPACKET

sql serversql-server-2012wait-types

I have been looking at the following post and using the query to look at my top wait stats:

http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

My top wait stat is CXPACKET and according to the total wait time I have a number which converts to about 700 days. I know that the server has only been up 58 days so how can this be accurate?

Does CXPACKET total for each thread? In which case how do I know if this is a problem?

Best Answer

It's strange how many "problems" CXPACKET is causing :)

You can find my answer to similar question at this link: https://dba.stackexchange.com/a/141399/28611

But I will re-post my answer here as well

The CXPACKET was always the confusing wait type for younger DBAs and some predictably wrong reactions are generally expected. There are multiple aspects of the CXPACKET wait type and I've tried in Troubleshooting the CXPACKET wait type in SQL Server article to bring on the table most of the reasons for high CXPACKET but also to explain the background of the CXPACKET, as the proper understanding of parallelism in SQL Server is the key for understanding

So for those who don't want to get in details, I will post the summary of the article here (but I would definitely suggest reading of the article for complete info about CXPACKET wait type):

Do not set MAXDOP to 1, as this is never the solution

Investigate the query and CXPACKET history to understand and determine whether it is something that occurred just once or twice,
as it could be just the exception in the system that is normally
working correctly

Check the indexes and statistics on tables used by the query and make sure they are up to date

Check the Cost Threshold for Parallelism (CTFP) and make sure that the value used is appropriate for your system

Check whether the CXPACKET is accompanied with a LATCH_XX (possibly with PAGEIOLATCH_XX or SOS_SCHEDULER_YIELD as well). If this is the
case than the MAXDOP value should be lowered to fit your hardware

Check whether the CXPACKET is accompanied with a LCK_M_XX (usually accompanied with IO_COMPLETION and ASYNC_IO_COMPLETION). If this is
the case, then parallelism is not the bottleneck. Troubleshoot those
wait stats to find the root cause of the problem and solution

Related Solutions

Tuning SharePoint Site Using SQL Server Waits and Queues

As this is SharePoint your hands are tied. You can't create indexes or statistics without loosing support from Microsoft.

CXPACKET just means that one thread of a parallized query is waiting for another thread to finish waiting for something else. SOS_SCHEDULER_YIELD can mean a few things, typically it means that you need to fix your indexes are you have queries that aren't tuned correctly so the SQL Server is pausing those queries (yielding them) so that it can work on other stuff (as this is SharePoint you can't really do anything about this). OLEDB means that the SQL Server is waiting on the OLEDB driver so probably there are linked server queries or something along those lines that are calling out to another database server. There's nothing you can do about that either.

From what it sounds like there's about nothing you can do without throwing more hardware at the problem. Use Profiler, Server Side Tracing or Extended Events and find the queries which are taking to long to run and check their execution plans and see how bad that are. Odds are those search queries are doing large scan operations.

How big are the content databases? Microsoft recommends keeping the content databases under 100 Gigs so that scanning is kept to a minimum.

Sql-server – SQL Server 2012 CPU spike due to LinkedServer – how to find the offending query

To answer the question: How to find the offending query:

Since the spikes in the graph you posted last for several minutes you have plenty of time to use the following method:

Download sysinternals process explorer

start process explorer and find the SQL Server process.
right click and select properties
look at the thread tab.
Sort on the CPU column and note the thread id (TID) that is consuming the most CPU.

Use this query and lookup the query that is currently being executed by that thread:

SELECT r.session_id, st.text, qp.query_plan
FROM sys.dm_os_threads AS ot
JOIN sys.dm_os_tasks AS t
ON t.worker_address = ot.worker_address
JOIN sys.dm_exec_requests AS r
ON t.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE os_thread_id = <thread id>

Best Answer

Related Solutions

Tuning SharePoint Site Using SQL Server Waits and Queues

Sql-server – SQL Server 2012 CPU spike due to LinkedServer – how to find the offending query

Related Question