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

What to look for in Execution plans when a query is slow ? Expand / Collapse
Author
Message
Posted Friday, December 20, 2013 5:39 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 03, 2014 2:14 PM
Points: 746, Visits: 1,702
Hi Pals,

Assuming the query execution is slow , I have collected the actual execution plan. What should I look for in the actual execution plan . What are the top 10 things I need to watch out for?

I know this is a broad and generic question but I am looking for anyone who is experienced in query tuning to answer this question.

Thanks in Advance.
Post #1524942
Posted Friday, December 20, 2013 5:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:20 PM
Points: 41,529, Visits: 34,445
This may help. http://www.sqlskills.com/blogs/paul/query-plan-analysis-first-steps/
Also, grab a copy of Grant's Exec plans book, should be lots in there, more than you'll get as forum replies



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1524947
Posted Friday, December 20, 2013 6:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 12,744, Visits: 31,071
Oracle_91 (12/20/2013)
Hi Pals,

Assuming the query execution is slow , I have collected the actual execution plan. What should I look for in the actual execution plan . What are the top 10 things I need to watch out for?

I know this is a broad and generic question but I am looking for anyone who is experienced in query tuning to answer this question.

Thanks in Advance.


actually a great question, and it makes me wish i had saved some images to backup some examples. seeing is understanding in things like this.

I'll start adding things, and i know my peers will pitch in with more.
in no particular order, here's some i can think of
» in an actual execution plan, for any node, if the actual number of rows is orders of magnitude higher than the estimated number of rows, the statistics on the table being used in that node are out of date.

»Table Scan : if it exists,the table is a heap, and could benefit from adding a clustered index. clustered is always better.

»key lookup: an index was used to find a reference to get an additional column value: adding or modifying an existing index to INCLUDE that column could help.

» the obvious, in your face missing index statement needs to be reviewed; it might be correct, or it might be that an existing index could be tweaked to resolve the same query.

»if a scalar function is being used at all int he query, since they scale poorly on large numbers of rows.

»if a cursor is being used at all, it's most likely doing RBAR when a set based solution could do the same work orders of magnitude faster.

»Index Scan: if an index scan was used, theres probably not an index that helps the query well enough to do an index seek

»the output list for a node: if it's using an index, maybe adding an index with to match the WHERE, and which has the INCLUDE columns found in the list might help


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1524949
Posted Friday, December 20, 2013 6:08 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:20 PM
Points: 41,529, Visits: 34,445
Lowell (12/20/2013)
» in an actual execution plan, for any node, if the actual number of rows is orders of magnitude higher than the estimated number of rows, the statistics on the table being used in that node are out of date.


Not necessarily. There's a number of other reasons for cardinality estimates being wrong



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1524950
Posted Friday, December 20, 2013 6:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 12,744, Visits: 31,071
GilaMonster (12/20/2013)
Lowell (12/20/2013)
» in an actual execution plan, for any node, if the actual number of rows is orders of magnitude higher than the estimated number of rows, the statistics on the table being used in that node are out of date.


Not necessarily. There's a number of other reasons for cardinality estimates being wrong


oh yes, i agree; but it's certainly something to watch for.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1524957
Posted Friday, December 20, 2013 6:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 14,802, Visits: 27,275
For just getting started, these are the things that I immediately look at for a plan. Yeah, there are tons more details, but this is the starting point. I did a presentation on this at the PASS Summit and it's available here.

----------------------------------------------------
"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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1524970
Posted Friday, December 20, 2013 10:30 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 03, 2014 2:14 PM
Points: 746, Visits: 1,702
Hi Grant,

Thanks for that cool stuff. It would be a great help for any starter and want to dig deep dive.

I also thank Gail, Lowell for putting up all the nice information. Thanks very much.
Post #1525099
Posted Friday, December 20, 2013 11:54 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:37 PM
Points: 3,305, Visits: 2,351
I have read Grant's book SQL Server Execution Plans book and I would certainly recommend it.


Tally Tables - Performance Personified
Best practices on how to ask questions
Post #1525138
Posted Sunday, December 22, 2013 5:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:30 PM
Points: 35,959, Visits: 30,251
Grant Fritchey (12/20/2013)
For just getting started, these are the things that I immediately look at for a plan. Yeah, there are tons more details, but this is the starting point. I did a presentation on this at the PASS Summit and it's available here.


Freakin' awesome, Grant. You're such a good speaker.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1525396
Posted Monday, December 23, 2013 7:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 14,802, Visits: 27,275
Jeff Moden (12/22/2013)
Grant Fritchey (12/20/2013)
For just getting started, these are the things that I immediately look at for a plan. Yeah, there are tons more details, but this is the starting point. I did a presentation on this at the PASS Summit and it's available here.


Freakin' awesome, Grant. You're such a good speaker.


Ha! Thanks Jeff.


----------------------------------------------------
"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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

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

Add to briefcase

Permissions Expand / Collapse