Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Statistics Information For Performance Tuning Expand / Collapse
Author
Message
Posted Friday, January 14, 2011 2:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:13 AM
Points: 154, Visits: 955
Hi,

I was wondering what information is important for you in regards to tuning a query. Is my ranking in terms of importance accurate?

1) Elapsed Time - SQL Server Execution Time
2) CPU Time - SQL Server Execution Time
3) Physical Reads (Potential I/O Contention)
4) Logical Reads (Data from Cache)
5) Read-Ahead Reads (I'm not sure...)

Thank you!



My Blog!
Post #1048200
Posted Friday, January 14, 2011 2:47 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 10:20 AM
Points: 2,049, Visits: 3,589
dajonx (1/14/2011)
Hi,

I was wondering what information is important for you in regards to tuning a query. Is my ranking in terms of importance accurate?

1) Elapsed Time - SQL Server Execution Time
2) CPU Time - SQL Server Execution Time
3) Physical Reads (Potential I/O Contention)
4) Logical Reads (Data from Cache)
5) Read-Ahead Reads (I'm not sure...)

Thank you!



Pretty hard to give an exact on that. ;) First though I would add that parse and compile time are important as well. An overly complex plan can be problematic to the optimizer, especially if that plan is being removed from cache.

I personally go through the list when I look at the output of statistics time and IO. I start with the parse and compile, look at the reads and then CPU and Elapsed time.

Ultimately they are all important and all are necessary for proper tuning. You want to make sure you have your query as low as it can go in all areas if this is a frequently run query. Example, you could have a query that is generating a bunch of logical reads due to a loop join. You force a hash join and see the reads go down but now your CPU time has doubled. Guess what, you probably don't want that and more than likely you can get the reads down further and keep the CPU down by reworking the query a bit.

So, use them all. Take you time and get the query to perform in all categories.

Just my thoughts.


David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #1048227
Posted Friday, January 14, 2011 2:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:13 AM
Points: 154, Visits: 955
Thank you, David!

I understand that all of the categories are important, but is there some sort of "ranking" that I could use as a guideline? In your example, you said that I probably wouldn't want CPU time to go up which I agree. So, I guess in a way, you could rank CPU time as #1 in terms of importance?

I completely understand that there will have to be some sort of give and take. I just want to be certain that my order of importance is correct. By the way, I know I have a LOT to read to improve myself in this area of query tuning.


My Blog!
Post #1048232
Posted Friday, January 14, 2011 3:12 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 10:20 AM
Points: 2,049, Visits: 3,589
Sincerely I would be hard pressed to rank them. In my present environment we place a high emphasis on reads due to the volume of activity that we have on disk and we can have some exceptions in the area of CPU since we have "room to spare" so to speak.

Me personally, I would rank reads and cpu highest, in that order. I'm sure you could probably get a bunch of different answers though.


David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #1048242
Posted Friday, January 14, 2011 3:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:08 PM
Points: 5,401, Visits: 7,514
dajonx (1/14/2011)
Thank you, David!

I understand that all of the categories are important, but is there some sort of "ranking" that I could use as a guideline? In your example, you said that I probably wouldn't want CPU time to go up which I agree. So, I guess in a way, you could rank CPU time as #1 in terms of importance?

I completely understand that there will have to be some sort of give and take. I just want to be certain that my order of importance is correct. By the way, I know I have a LOT to read to improve myself in this area of query tuning.


Personally, execution time (from call to results) is almost always my priority. That can encapsulate a lot, so the rest of them are on a per-issue basis. They will get ranked according to your system's usage more than any 'set' list, as David mentioned above. If you've got a heavy transactional system, disk I/O will become important, where as if you're doing a ton of sorting CPU and memory may be more important.

It's a matter of isolating your primary bottlenecks and working to make sure they don't get overwhelmed.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1048243
Posted Friday, January 14, 2011 3:25 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 10:20 AM
Points: 2,049, Visits: 3,589
Craig Farrell (1/14/2011)

It's a matter of isolating your primary bottlenecks and working to make sure they don't get overwhelmed.


We need a "Like" button on here. :)


David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #1048245
Posted Monday, January 17, 2011 11:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 13,890, Visits: 28,285
I agree. Execution time is one of the most important. Not on your list is one of my other favorites, frequency of call. Your longest running proc may be called once a year. You may have a very fast proc that is called a hundred times a second. If you can cut either proc's execution time in half, which would be the better target?

But, that said, you have to take it all into account. Just tuning for reads may not solve slow performing queries. Just tuning for speed might not tune queries that are accessing too much data. While I do put execution time & frequency at the top of the list, the entire list has to be taken into account.



----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1048897
Posted Monday, January 17, 2011 1:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:13 AM
Points: 154, Visits: 955
Ohh, ok. I understand. Thank you very much for shedding some light on this subject for me. If, by chance, you happen to have any literature (links, books, etc) that I can read, I'd greatly appreciate it!

Thanks again!


My Blog!
Post #1048940
Posted Monday, January 17, 2011 2:01 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 7:56 AM
Points: 598, Visits: 1,628
Hi I am in a similar situation to you in that I am just getting into performance tuning. I have found this a great article:

http://sqlcat.com/whitepapers/archive/2007/11/19/sql-server-2005-waits-and-queues.aspx
Post #1048944
Posted Monday, January 17, 2011 4:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 13,890, Visits: 28,285
dajonx (1/17/2011)
Ohh, ok. I understand. Thank you very much for shedding some light on this subject for me. If, by chance, you happen to have any literature (links, books, etc) that I can read, I'd greatly appreciate it!

Thanks again!


Yes, I can recommend a book on query performance tuning. Look down in my signature.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1048977
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse