Help needed to identify the source of a problem
fas@pdv
Posts: 2
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
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
Comments
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.