Help needed to identify the source of a problem

fas@pdv

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:



(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

Brian Donahue

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.

EdCarden

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.

wicky

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.