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 post 1 (of 2 total)
You must be logged in to reply to this topic. Login to reply