What are the primary measures you look at, when performance tuning?

Question

What are the primary measures you look at, when performance tuning?

rwitt 95744

Mr or Mrs. 500

Points: 523
More actions
September 28, 2015 at 10:06 pm

#300990

Greetings, all.
I'm curious what are the most important/relevant stats, query plan aspects, etc., that you look at, when trying to determine if one T-SQL coding approach is more performant or efficient than another.
Thanks,
Randy

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

Above all, how long it takes for the query to complete. Reads (something you can get using extended events or SET STATISTICS IO ON). I look at the query plan (I always develop in SSMS with "Include Actual Query Plan" turned on.) Things like sorts, implicit conversions are bad. Those are a few random things I look at.. It's a science - the more tuning you do, the better you get.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 2

rwitt 95744 (9/28/2015)
Greetings, all.
I'm curious what are the most important/relevant stats, query plan aspects, etc., that you look at, when trying to determine if one T-SQL coding approach is more performant or efficient than another.
Thanks,
Randy

Further on Alan's reply, looking at execution plans in isolation can be as misleading as looking at interior designs when trying to plan a city, you may occasionally get a good view but most of the time you don't.

😎

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Eirikur Eiriksson (9/28/2015)
rwitt 95744 (9/28/2015)
Greetings, all.
I'm curious what are the most important/relevant stats, query plan aspects, etc., that you look at, when trying to determine if one T-SQL coding approach is more performant or efficient than another.
Thanks,
Randy
Further on Alan's reply, looking at execution plans in isolation can be as misleading as looking at interior designs when trying to plan a city, you may occasionally get a good view but most of the time you don't.
😎

+1000

Rule #1 - know your data.

This opens up a whole bunch of doors. If you know your data you can anticipate data access paths and compare what you expect to see with what the optimiser spits out.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 4

For queries, execution time and the number of reads. I do use extended events for this all the time now. I've found that STATISTICS IO tends to mask changes in execution time for faster running queries, so rather than try to guess when it's interfering, I just skip it.

The number one concern for queries is that they run fast and do as little I/O as possible, so that's where I focus. When I can't see obvious improvements in the code, then I go to the execution plan and look for what's going wrong.

There's a whole lot more to it. Someone should write a book, or two

|

V

"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

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 5

Grant Fritchey (9/29/2015)
For queries, execution time and the number of reads. I do use extended events for this all the time now. I've found that STATISTICS IO tends to mask changes in execution time for faster running queries, so rather than try to guess when it's interfering, I just skip it.
The number one concern for queries is that they run fast and do as little I/O as possible, so that's where I focus. When I can't see obvious improvements in the code, then I go to the execution plan and look for what's going wrong.
There's a whole lot more to it. Someone should write a book, or two
|
|
|
|
V

About time someone did:-D

😎

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

When I'm tuning a system, I look at the aggregate IO, CPU and duration of the queries over a fixed period of time, usually an hour or two, in peak business time. For individual queries, I'm going trying to minimise reads, duration and CPU. I'll use the execution plan to he in doing so, but the plan is not going to tell me which of multiple queries is most efficient.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass