Sql-server – Slow queries on production Server

performancequery-performancesql server

I am a developer who has been asked to look at a database slowness issue.
I have had a look at the waits on the server and retrieved the following using a query from Red Gate's wait stats white paper.
The results are:

Wait type         wait_S            Resource_S     Signal_S     WaitCount      Percentage

PAGEIOLATCH_SH      393375.51       391289.91      2085.59      76733247       32.60

CXPACKET            367110.76       334340.19      32770.57     34663540       30.42

OLEDB               84136.77        84136.77       0.00         78106          6.97

SOS_SCHEDULER_YIELD 77896.38        15.97          77880.41     254840824      6.46

PAGEIOLATCH_EX      65944.98        65550.98       394.00       16298376       5.46

ASYNC_NETWORK_IO    45579.23        32512.01       13067.22     125659899      3.78

WRITELOG            37415.61        35457.02       1958.59      18559390       3.10

LCK_M_IX            36602.81        36587.52       15.29        67118          3.03

LCK_M_X             35503.99        35494.55       9.44         32114          2.94

LCK_M_U             30464.81        30452.64       12.17        28824          2.52

Does anyone have any advice on what to do next?

Does CXPacket suggest a maxdop change? The server is a virtual server showing 4 CPU's in windows task manager.

Best Answer

While by no means a dead cert, PAGEIOLATCH_SH and CXPACKET in equal weight can be indicative of parallel table scans. Without a baseline to compare with I'd suggest looking at the top CPU and IO consumers and looking for those which appear in both.

Related Solutions

Sql-server – SQL Server statements intermittently slow on SQL Server 2008 R2

Thank you for the detailed explanation of your problem (one of the best laid out questions actually).

WRITELOG is a very common type of wait, so don't worry about it. Looking at the SOS_SCHEDULER_YIELD indicate CPU pressure and also the CXPACKET, it is possible that there must be some missing indexes and you may be retrieving lot of data from the queries for an OLTP system. I suggest you to look at the Missing Indexes DMV and see if there are any indexes (almost sure there will be more than few) that are in the questionable procs.

http://sqlfool.com/2009/04/a-look-at-missing-indexes/

http://troubleshootingsql.com/2009/12/30/how-to-find-out-the-missing-indexes-on-a-sql-server-2008-or-2005-instance-along-with-the-create-index-commands/

Look for Jonathan Kehayias's post on sqlblog.com on this too.

Also, take a look at Parameter sniffing.

http://sommarskog.se/query-plan-mysteries.html

http://pratchev.blogspot.com/2007/08/parameter-sniffing.html

It's NOT a compete answer for your needs but a good starting point. Let us know if you need more details.

Sql-server – Slow SQL Server authentication, but fast SAN, fast querying

Here is your answer:

MSDB on C:
86% of 32GB
io_stall_write_ms : 192217

If MSDB is on c: master is on c: as well and I'm almost willing to bet that tempdb is still in the default location as the average I/O stall on c is 19 seconds!

First, check if tempdb is on c:\ or has any file on the c:\ partition and if so move it away

Check if the master database has some free space - Usually you dont need to tune it as very little is written to the master database, unless if someone has created objects in it

Then make space in the MSDB database is way to big which can cause a lot of troubles, please remove as much backup and job history from it database as you can.

You can do all these checks very simply by downloading and running sp_blitz

Best Answer

Related Solutions

Sql-server – SQL Server statements intermittently slow on SQL Server 2008 R2

Sql-server – Slow SQL Server authentication, but fast SAN, fast querying

Related Question