March 9, 2015 at 5:48 am
We have this long standing issue with SQL Server (Enterprise 2012)
Every now and then, a running query timeout raising a "wait timeout" error on a .NET application.
Lately it's happening more often. Anything is possible, so where to start troubleshooting this ?
If I run SQL Profiler, looking for long running queries, it can be silent for hours, but all of the sudden, for like two minutes, it will capture hundreds of queries with huge running times, say 15, 30 and up to 90 seconds.
Most of the queries captured are optimized, bear with me for a minute.
I suspect that something is happening, that makes all the queries issued or already running, to wait, or drag, which makes these queries to stand up in the SQL Profiler.
So, the queries I'm seeing on Profiler are not the real culprits of the slowness, or do not seem to be, it is something else that is not getting logged ?
If the SQL server is running a process such as a database backup, or an index defrag, or an index rebuild, is it likely to slow down some or all queries, which would make the queries to stand out on SQL Profiler, so these would be false positives.
But what do I have to do to get to identify the real culprit ? What else could / should I look for on SQL Profiler ?
How can I get a stack of absolutely all running queries when the DB starts dragging ?
NOTE: I'm not saying all the queries are perfect, au contraire, there clearly is a problem here, I'm checking the exec plan of (most) of captured queries and all I see are index seeks and clustered seeks.
Thanks, any advise in this regard will be appreciated 🙂
Cordially, Agustin.
March 9, 2015 at 6:53 am
It sounds like a blocking or resource contention issue. Let's assume that your queries are tuned, but they suddenly start timing out. It's likely that there's one resource of query that is causing the issues. So, I'd suggest starting by setting up blocking monitoring using extended events. Here's some documentation on that. I would also suggesting focusing on the wait statistics to understand specifically what is causing your system to run slow. sys.dm_os_wait_stats will help there. You can query it daily or before and after one of your blocking events. This won't tell you specifically what is causing things to run slow, but it will show what resource everything is waiting on, CPU, I/O, Memory. You can also use extended events to capture long running queries without seriously negatively impacting your system.
That's where I'd start.
Also, stop using Profiler/Trace. You're on 2012, so you have a GUI and everything you need to set up and consume extended events. They put less of a load on the system. They filter better. There are more events to capture. You can chain related events together. It's just a better way to do your monitoring in general.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply