patternMinor
SQL Server statements intermittently slow on SQL Server 2008 R2
Viewed 0 times
2008sqlstatementsslowserverintermittently
Problem
On one of our customers, we've been having some performance issues on our application. It's a .NET 3.5 web app that is consuming and updating data on a SQL Server database. Currently our production environment consists of a Windows 2008 R2 machine as the front end, and a SQL Server 2008 R2 cluster on the back end. Our app uses COM+ and MSDTC to connect to the database.
Here's what's happening: our end users sometimes complain of slowness in the application. Some pages take some more time to load than would be expected. While trying to figure out what's happening, I managed to find out some strange behaviour on the database side that may be the cause for the performance degradation. I noticed that sometimes there are some SQL statements that take a lot more time to run that what would be expected. I managed to identify some of these statements (mainly it's invocations of some of our application's stored procedures) using a profiler trace (with TSQL_Duration template) to identify the long running queries.
The problem is that when I run these stored procedures directly on the database on SQL Management Studio sometimes they do take long (about 7/8 seconds), other times they are fast (under 1 sec.). I don't know why this happens and it's driving me nuts, because the SQL machine (4 core, 32 GB) is not being used by any other applications, and these queries should not take this long to run.
Not being a DBA or a SQL Server guru, I've been trying to look at some stuff that may help me understand the problem. Here's the steps I've taken to try and sort out the problem and what I found out so far:
Here's what's happening: our end users sometimes complain of slowness in the application. Some pages take some more time to load than would be expected. While trying to figure out what's happening, I managed to find out some strange behaviour on the database side that may be the cause for the performance degradation. I noticed that sometimes there are some SQL statements that take a lot more time to run that what would be expected. I managed to identify some of these statements (mainly it's invocations of some of our application's stored procedures) using a profiler trace (with TSQL_Duration template) to identify the long running queries.
The problem is that when I run these stored procedures directly on the database on SQL Management Studio sometimes they do take long (about 7/8 seconds), other times they are fast (under 1 sec.). I don't know why this happens and it's driving me nuts, because the SQL machine (4 core, 32 GB) is not being used by any other applications, and these queries should not take this long to run.
Not being a DBA or a SQL Server guru, I've been trying to look at some stuff that may help me understand the problem. Here's the steps I've taken to try and sort out the problem and what I found out so far:
- All of the TSQL code called by the application is written in stored procedures.
- I identified some of the long running queries on SQL Server profiler, however when I run these on Management Studio they either take long to run (from 4 to 10 secs.), or run quickly (under 1 sec.). I am running the exact same queries with the same data passed in the parameters. These queries a
Solution
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.
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.
Context
StackExchange Database Administrators Q#3892, answer score: 4
Revisions (0)
No revisions yet.