January 21, 2011 at 8:27 am
Hi ALL,
I have a server and it slows down around once every 15 days for about 15 minutes. There isn't a consistent pattern, for me to think if there is a job running in the background.How do I start trying to look for the cause.
I have thought about the following and numbered them in the order of their help
1) Activity Monitor
2) DMV
3) Network Issues (I check this by pinging----Any better ideas)
4) Replication Monitor (This is the publisher)
5) Create Traces and read them through.5
6) What else?
Is my approach correct? Is my order right. Please advice.
January 21, 2011 at 1:39 pm
This could most probably due to a scheduled bulk insert kind of jobs. Are you aware of any such kind of jobs running on the server ? Sometimes this could also be due the application , external to SQL Server .
I think you approach looks alright.
1. Check the activity monitor at that point of time for any high CPU nad Disk IO times by the SPIDS .
2. Also Check for any extended blocking happenning on the server
3. You can even run profiler and\or perfmon to do further investigation .
Thank You,
Best Regards,
SQLBuddy
January 21, 2011 at 2:39 pm
Agreed..
When a server is inexplicably slow, this is usually the order of things I check:
1) CPU
2) SAN Cache (may not be applicable)
3) Blocks
4) Spids that are Runnable (usually looking for ad-hoc queries with maxdop 0 or something)
5) Long running jobs that might be hung
It's pretty much always one of the above.
January 21, 2011 at 3:33 pm
I dont have any jobs running at that time. I looked into it. Also I think running profiler would slow down the slow machine even more.
This is a production box. So I am being cautious.
January 21, 2011 at 4:07 pm
If this is SAN attached, you might take a look at any latency reports you can get from the SAN that indicate increased latency during your slow periods. This might indicate a non-DB related job that slows down your SAN response.
January 28, 2011 at 3:14 am
I would run perfmon for an extended period (saving the .BLG file) and then use PAL to analyse the results. This will capture network/disk/memory/CPU etc (depending on your chosen counters) to see if hardware is the bottleneck or not...
January 28, 2011 at 9:04 am
Is everything slow during the 15 minute period or just specific procedures? Are there blocks happening during the slowdowns?
If you are lucky enough to be connected during a slow_down, running SP_WHO2 will at least show you the SQL procedures currently running and if there are blocks going on. That would give you an idea if any of the procedures is the culprit. I've seen behaviour like this due to a bad execution plan periodically being chosen.
You could also create a server side trace, which is very light weight, for long durations, high CPU or high reads. These are going to help determine if the problem is somewhere with the SQL being executed.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply