February 20, 2014 at 12:35 pm
This topic is based on reading this short article: How much is that query costing[/url].
In the scenario where you just saved your company that much money by just shaving off a bit of time from a single query, brings me to ask:
Do you bother capturing a before and after to show what you accomplished?
What type of information did you capture (perfmon counters, DMV output, wait stats, etc.)?
Now the financial side of the business may not be known by every DBA in the industry, but I am sure there are ways to associate some cost savings to it. Either way things like this I think would be good resume builders to have available when needed. I always read a job description on a resume should point out the "big" things you did at the job more than what you did on a daily basis. Having a statement and then proof of it that you had experience taking a 5 minute complex query down to seconds or even 1 minute can go a long with these days.
An example I came across sometime back was use of a simple picture[/url]. Not having access to monitoring software there may be performance counters or DMV data that could be captured to also render this type of information in a graph with Excel.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
February 20, 2014 at 12:48 pm
When I am tuning a "critical" piece of code for a client I just capture profiler trace of the code for a period of time. I will use that to do analytics such as min/max duration and average duration (and perhaps some ancillary stuff such as CPU/IO too - but DURATION is where it is at for making a key sproc (order entry, product search, etc) save a client money. The key here is AGGREGATE analytics. You simply must capture a sufficient time period to show overall performance wins. Tuning a single execution, or too short a period, doesn't really show you made a difference on the whole. I really don't bother much with other types of metrics when doing this type of tuning, although if the code is a HARD HITTER I will attempt to show wait stats and IO stall improvement too.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 20, 2014 at 1:05 pm
Shawn Melton (2/20/2014)
Do you bother capturing a before and after to show what you accomplished?What type of information did you capture (perfmon counters, DMV output, wait stats, etc.)?
Always.
I'll have performance stats from beforehand, because I never tune without knowing exactly what execution characteristics the query has in production first. Too easy to waste time tuning that which isn't a problem if you don't.
I get the query and sample data set up on a dev box, take statistics IO and TIME and execution plan before and after. That's how I tell whether or not what I did was successful. Without that it's easy to fiddle a bit, achieve nothing and claim progress.
After the changes are in production, I take the same stats which I took in step 1, so that I have like to compare to like and some stats to allow me to identify the next problem to be tackled.
All of that is part of my report to my client.
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
February 20, 2014 at 1:27 pm
Agree about having the performance stats before doing any change/tuning/indexing/stats revision, etc...
I usually take a production copy of a database where I can benchmark to what an actual user will experience (or as close to as possible) then look at logical reads, the amount of rows being retrieved, how much data in the throughput, etc...
Make changes, step by step and compare it each step of the way to see if I'm bettering what was there before, which usually is the case
February 20, 2014 at 1:31 pm
stormsentinelcammy (2/20/2014)
Agree about having the performance stats before doing any change/tuning/indexing/stats revision, etc...I usually take a production copy of a database where I can benchmark to what an actual user will experience (or as close to as possible) then look at logical reads, the amount of rows being retrieved, how much data in the throughput, etc...
Make changes, step by step and compare it each step of the way to see if I'm bettering what was there before, which usually is the case
Servers I generally deal with I don't have the option of moving them to a test server, and they are generally to large for me to try and pull down through a VPN connection. I sure wish I could on some of them though.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
February 20, 2014 at 1:32 pm
GilaMonster (2/20/2014)
Shawn Melton (2/20/2014)
Do you bother capturing a before and after to show what you accomplished?What type of information did you capture (perfmon counters, DMV output, wait stats, etc.)?
Always.
I'll have performance stats from beforehand, because I never tune without knowing exactly what execution characteristics the query has in production first. Too easy to waste time tuning that which isn't a problem if you don't.
I get the query and sample data set up on a dev box, take statistics IO and TIME and execution plan before and after. That's how I tell whether or not what I did was successful. Without that it's easy to fiddle a bit, achieve nothing and claim progress.
After the changes are in production, I take the same stats which I took in step 1, so that I have like to compare to like and some stats to allow me to identify the next problem to be tackled.
All of that is part of my report to my client.
Are there ways to get that SET STATS IO and TIME output when you can't run the query in SSMS yourself? It seems I had read something is there through DMVs or Profiler maybe that can provide similar information...
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
February 20, 2014 at 1:35 pm
I should have prefaced my answer with (If the database is not huge :))
If its not a huge DB then I take a copy/backup and load it into Dev to play with it whenever I need to test as if I were a user, if I'm stuck with a dev environment then what I mentioned before will still work regardless of the DB you use...
February 20, 2014 at 1:40 pm
Shawn Melton (2/20/2014)
GilaMonster (2/20/2014)
Shawn Melton (2/20/2014)
Do you bother capturing a before and after to show what you accomplished?What type of information did you capture (perfmon counters, DMV output, wait stats, etc.)?
Always.
I'll have performance stats from beforehand, because I never tune without knowing exactly what execution characteristics the query has in production first. Too easy to waste time tuning that which isn't a problem if you don't.
I get the query and sample data set up on a dev box, take statistics IO and TIME and execution plan before and after. That's how I tell whether or not what I did was successful. Without that it's easy to fiddle a bit, achieve nothing and claim progress.
After the changes are in production, I take the same stats which I took in step 1, so that I have like to compare to like and some stats to allow me to identify the next problem to be tackled.
All of that is part of my report to my client.
Are there ways to get that SET STATS IO and TIME output when you can't run the query in SSMS yourself? It seems I had read something is there through DMVs or Profiler maybe that can provide similar information...
Like Gail, I capture the before and after information. Of course, if I can't run the query it makes it harder to tune.
You can capture execution statistics for the query. Keep in mind that those stats are across all executions of the query. So having the before snapshot is very important.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 20, 2014 at 2:01 pm
I'm not sure you can get that the way you're asking, but I know that there are DMV's that will give you stats but not the same that you'd get ad-hoc from setting statistics io on
February 20, 2014 at 3:48 pm
If I don't have the before and after I don't know if I tuned it, let alone telling my boss that I just shaved X amount of time or resources off a query. You absolutely should be saving those things, but...
Just to deviate a little from what others have said, if you're early in development and you're just fixing bad code smells and other low-hanging fruit to get a clean and consistent system prior to putting it through testing, I don't bother as much. It's not worth it then.
But as soon as we're into testing, especially performance testing, or we're dealing with production issues, heck yes, I record it.
"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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply