April 22, 2014 at 1:51 pm
I recently collected the wait times on one of our servers. We know we have a disk issue and it is being addressed soon but I still wanted to see if there was anything else the server was waiting on.
We are using SQL Sentry to monitor and report on SQL performance. I ran a report for one day to show me the wait stats. Right at the top of the list for waits is ASYNC_IO_COMPLETION which has a total ms wait time of 2.6 billion ms or 30 days.
At first glance I wonder how the server spent 30 days waiting in only a day. My understanding is the total time spent waiting is counted across all connections. So if I have 100 users and they all had to wait 1ms, sql server would report a total wait time of 100ms. Is this the correct way to interpret this number?
If that is how it works can I then use that number to help justify purchasing faster disks? I see it as we spent the equivalent of 30 days waiting so if we reduce the waits by 50% we then give back 15 days worth of time to the business users where they can then process more thus increasing our profit. Correct? I understand it might be more complex than that since giving back 15 days worth of waits spread across all users might only result in a user having to wait say 10 ms less each transaction they make, but at a basic level it seems to make sense...at least to me 🙂
Thanks in advance for your help.
April 23, 2014 at 2:45 am
tfendt (4/22/2014)
My understanding is the total time spent waiting is counted across all connections. So if I have 100 users and they all had to wait 1ms, sql server would report a total wait time of 100ms.
Yes.
If that is how it works can I then use that number to help justify purchasing faster disks?
No. It's not that simple.
First, check that the tool really is showing you a day of waits. Because the wait stats from SQL are cumulative since SQL started.
Second, you need to do a lot more analysis before coming to a conclusion and plan of action. You need the wait stats more granular. When I do performance analysis, I look at the wait times in half hour periods across a couple of days.
You need to see if that wait time is normal, or did something unusual happen that day. You need to see when the waits are accrued, is it steadily across the day or is there a batch process overnight that's causing most of the waits. You need to look at the other waits as well, context is everything. You need to look at the latencies on the drives, you need to consider whether the problem is the IO subsystem or whether the problem is that the workload is driving the IO subsystem too hard (inefficient queries)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2014 at 3:44 am
The problems are much more often located in the T-SQL code and the database structures than they are on the hardware. SQL Sentry does a good job of identifying the longest running or most frequently called queries and of showing how those queries use resources. Some time spent tuning queries is frequently less expensive and more effective than trying to throw hardware at an ill-defined problem.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 23, 2014 at 8:09 am
Thanks for the verification. Yes, I agree that there is much more that goes into determining what is wrong. Yes, I am sure the wait stat is a daily count. In this case the new SAN is already approved and I didn't even have to ask :). It was already in the pipeline when I got hired. We see average latency times around 30ms across the SAN with spikes over 1000ms!
I also agree with Grant about the query performance part. Problem is the queries are apart of purchased software and they don't take too kindly to me telling them their query is poorly written. I had one vendor recently tell me that I couldn't even add indexes because it breaches their contract which said all source code is owned by them and no modifications are allowed. Not sure I would consider new indexes apart of source code but if we want continued support we have to follow the rules.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply