September 6, 2011 at 8:52 am
Hi All,
I have fine tuned a stored procedure recently. The problematic area is I/O cost (not the execution time).
Earlier, the procedure took around 600000 I/O cost and ran within 2 or 3 seconds. This procedure is being executed by the user atleast for 700 times per day.
The I/O cost is reduced to 20000 now. I would like to send a status report saying "this will save this much of $$$...." and so on...to my management team. how should i calculate it? is it possible to find out the amount saved from this tuning work? i am curoius to know the anser for my questions.
Inputs are welcome!
karthik
September 6, 2011 at 8:59 am
The only good metric I've seen work on a CTO was total processing time.
I was able to say I've saved a total of 45 X 24 hours non stop processing for that server. Which was 80%+ of the available processing time for that particular server.
Now factor in the licenses, network load, hardware, SAN, IT time, etc he can see the benefit from that even if it's no X $.
September 6, 2011 at 9:32 am
The cost saving in terms of hardware most probably is not significant enough to be measured in $$$. Total processing time is about 30min/day before and after.
Is there any other (parallel) process running faster now due to the reduced IO? Have you been able to reduce some network/IO related waits?
It might be the only benefit is: "We've utilized our IO subsystem by X% before and now we're utilizing it only by Y%." Not enough to add another DB or application to that system to use the resources now available but maybe good enough for a "Thank You"-clap-on-your-shoulder.
I'm not a big fan of management notes for this amount of saving. I'd consider it my daily business. It would be different if my work would allow us to completely eliminate a server or any other major improvement.
You should also put it into the right perspective: is this sproc one of the top 10 DB bottlenecks the system is struggling with? If not, you might consider shifting priorities... 😉
September 6, 2011 at 9:37 am
LutzM (9/6/2011)
The cost saving in terms of hardware most probably is not significant enough to be measured in $$$. Total processing time is about 30min/day before and after.Is there any other (parallel) process running faster now due to the reduced IO? Have you been able to reduce some network/IO related waits?
It might be the only benefit is: "We've utilized our IO subsystem by X% before and now we're utilizing it only by Y%." Not enough to add another DB or application to that system to use the resources now available but maybe good enough for a "Thank You"-clap-on-your-shoulder.
I'm not a big fan of management notes for this amount of saving. I'd consider it my daily business. It would be different if my work would allow us to completely eliminate a server or any other major improvement.
You should also put it into the right perspective: is this sproc one of the top 10 DB bottlenecks the system is struggling with? If not, you might consider shifting priorities... 😉
My best run was close to 80% improvement. Definitely enough to add another 500% of users compared to what was previously trashing the server at 80+%. Not to mention the need to buy a new 5K server gone after only 5 days (linear time). About 10 hours invested.
September 6, 2011 at 11:56 am
@Remi:
That's what I call quite an achievement!! Awesome!
My personal "records":
1) a search function frequently called from an application from 30+ seconds to less than half a second (moved the queries from the app into a sproc and tuned it "a little")
2) migration of an ACCESS DB into SQL Server (including most the business logic). Front end still unchanged. Definitely got some "Wow" respnses from the end users... But migrating from ACCESS was more like just picking the low hanging fruits. 😉
Edit: As a side note: the first one has not even been raised to management. The second one was just a short "migration succesfully completed" notice. (Mainly due to the fact that this project was part of a list of ACCESS DB's to be migrated)
September 6, 2011 at 2:25 pm
Yes it was quite amazing. There was 100 low hanging fruits and I picked them all in not time flat (using Gail's article).
My favorite was SELECT MAX(INT COL) + 1 As NextID from dbo.BaseTbl. With Int col NOT indexed. Needless to say tha ton 18+ M rows it gets quite expansive to insert the next row :-D... That was the worst but there were quite a few more like that.
I reckon I had already achieved 75% of the boost on day 1. The next 4 days were just tweaks a little boosts and making sure I hadn't screwed up something. I could have continued but there was no approved budget and I couldn't go on. I don't think it would have mattered much tho.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply