June 18, 2017 at 10:19 am
We have a server, it was build on VMware volumes attached over network.
When I run a query It has taking 10 minutes one day 30 minutes another day to execute.
It's a dataware house ,Dev deletes complete data and insert updated into database.
Daily I am collecting statistics of data and rebuilding indexes if require.
I am suspecting that query slow is due to volumes attached this VM.
How to prove query slow is due to Disk I/O?
Thanks in advance.
June 19, 2017 at 3:17 am
Hi Krishna,
Here are my suggestions:
1. Setup the following performance counters:
a. avg disk sec/read (on the drive that contains the data files)
b. avg disk sec/write (on the drive that contains the tlog files)
Establish your baseline. In other words, examine the values of these counters on normal days (no slowness) and compare the values against the slow day. The acceptable values of these counters are less than 20ms. But again, it will depend on your baseline to know what is normal and what is not.
2. Analyze the execution plan of the query that you are running. You may attach the execution plan here so that other DBAs/Developers can help you troubleshoot the issue. Additionally, attach the query/stored procedure and table structure as well.
You may find this link useful: http://www.red-gate.com/products/dba/sql-monitor/resources/articles/monitor-sql-server-io
Best Regards
June 19, 2017 at 4:01 am
krishna83 - Sunday, June 18, 2017 10:19 AMWe have a server, it was build on VMware volumes attached over network.When I run a query It has taking 10 minutes one day 30 minutes another day to execute.
It's a dataware house ,Dev deletes complete data and insert updated into database.
Daily I am collecting statistics of data and rebuilding indexes if require.
I am suspecting that query slow is due to volumes attached this VM.How to prove query slow is due to Disk I/O?
Thanks in advance.
Check the counters related to that. This link will help you
June 19, 2017 at 4:18 am
I'd go into this with a more open mind. If it's a data load from another server, it doesn't have to be disk IO - it could be network latency, blocking on the remote server, or all sorts of things. Capture wait stats immediately before the query runs, then again immediately it's finished. Compare the two to see what the biggest waits are. Is there anything else going on at the same time as this query runs?
John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply