Help needed to identify the source of a problem

SQL Server performance monitoring and alerting

Moderators: Colin Millerchip, priyasinha, Adam, chriskelly

Help needed to identify the source of a problem

Postby fas@pdv » Wed Jan 15, 2014 10:03 am

Hi everybody,

our problem is not with SQL Monitor itself but I'd guess I'll maybe find the answer here anyways.

SQL Monitor shows our problem pretty good:

Image

(left the normal behavior, right one of our peaks)

We experience, irregular, extremly high peaks in disk average read time in one of our (virtuel) SQL Servers and are having a hard time identifiying the cause of the problem. The MSSQL Server is the only software that is running on this particular server, so another prgramm blocking the disk is.. unlikly, though not impossible.Due to its irregularity something like the scheduled intraday backups or users are also unlikly.

What strkining us odd is that the rest of the servers metrics are well within the normal scopes, the cpu usage, for example, doesn't excess it's normal 10-30% while these peaks occur.

The database performce itself plunges, naturally, down to nealry zero with hailing timeouts out in the Userfront ends.

We'd be greatful for any tip/advice where to look, what kind of other tool to use to identify the problems cause.

thx in advance and with kind reagards

F.Aßmann
fas@pdv
 
Posts: 1
Joined: Wed Jan 15, 2014 8:16 am

Postby Brian Donahue » Thu Jan 16, 2014 5:08 pm

Hi,
Have you had a look at the new Performance Diagnostics on the SQL Instance overview? This shows the top 10 waits. Presumably if queries are timing out it will manifest as some sort of wait condition. When you click on one of the waits, you will see a query (if available). This way you can tie a long-running IO operation with a SQL query.

It may also be worth having a look at your SQL Server error log: you may have maintenance plans running that are rebuilding indexes or other intensive IO operations.

I hope this helps.
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Postby EdCarden » Mon Feb 03, 2014 6:13 pm

By chance have you checked to see if VMware (or whatever you have managing your virtual servers) is doing anything at that time , something on a schedule that would effect the disk(s)? I remember reading somewhere that someone had something similar happened and it turned out that VMware (I think it was VMware) was running some kind of disk defrag or the like and that was killing their drive access. please keep in mind I am not a Virtualization guru so the above may sound like a crazy idea.
EdCarden
 
Posts: 90
Joined: Tue Nov 25, 2008 6:26 pm


Return to SQL Monitor 4

Who is online

Users browsing this forum: No registered users and 0 guests