February 27, 2014 at 9:15 am
I had a customer today that asked me:
If we have a SQL Server 2012, where we monitor IO related Waits for 20 seconds every half hour, and we do not have ANY OTHER WAITS. Every time we monitor we will get the same: 20 seconds IO related waits for every half hour.
Can I then conclude that by optimizing the IO system, then the maximum performance improvement i will get will be 20 seconds every half hour?
I would like to get all feedback that you find interesting to share.
Have a nice day.
Søren,
MCITP SQL 2008 (administration / programming), MCITP SQL 2005 (BI), MCT
February 28, 2014 at 4:08 am
It's pretty unusual to only ever see IO related waits. But yeah, assuming the data you're providing is correct and you only ever see about 20 seconds of waits within 1/2 an hour, then the most you can expect tuning to improve is around 20 seconds. That's such a very tiny value that it's probably unlikely you'll even get that much out tuning the queries, the structures, or the IO system.
However, that just sounds wrong. I'd suspect that there's something off in the data collection method.
"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
February 28, 2014 at 4:31 am
Hi. Thank you for your reply.
I agree it sounds not right, and it wast just a theoretical discussion with a financial manager at my customer.
I told him how I use the Waits and queues, and I needed to narrow the explanation down to our topic: Would a new IO System improve performance (as an average, we agreed not to look at peak times... Just to make the world simple).
I told him about ressource and signal waits and so on, and then he said: "Cut the crap... What you are saying is that if the figures is correct, then I can at a maximum improve IO Performance with xx seconds pr half hour. Is that correct"?
I said yes, and then, at the way home, I aksed my self: Is it really that simple?
So once again thanks for your answer.
Søren,
MCITP SQL 2008 (administration / programming), MCITP SQL 2005 (BI), MCT
February 28, 2014 at 5:49 am
Is there a reason for only checking for 20 seconds every half hour? I had to followup on a junior DBA who used a similar approach and ended up tuning a system for the workload that he trace every Monday morning from 9:00 to 9:30. If those we the hours you were on the site, it was great. Most of the week was crap.
February 28, 2014 at 6:27 am
Hi Eric,
Thanks for the answer.
No. It is still just a theoretical discussion. The 20 seconds and half hour was just to simplify a question. It could have been said in another way:
If the monitoring of IO related wait-types is accurate and gives a precise measuring of the IO waits in the system, can we then conclude that the sum of io related waits (in miliseconds or seconds) are the MAXIMUM improvement in systemperformance for the given period (that could be a week or a year or half an hour) if we spend money on a better SAN.
/Søren
Søren,
MCITP SQL 2008 (administration / programming), MCITP SQL 2005 (BI), MCT
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply