December 3, 2012 at 10:46 am
Hello - "set showplan_xml on" can be used to output the query plan of the current query. I know that table scans are a key thing to look for in the output but I haven't actually used the showplan functionality for years.
How are table scans represented in the showplan output? For example, I was just googling around and I found some ShowPlanXml output where "IndexScan" was listed as a child node.
How is a table scan represented in the output? Also, I know that table scans are a key thing to look for in showplan xml output but is there anything else I should be looking for?
December 3, 2012 at 11:12 am
You should be looking for all the things you'd look for in any review of an execution plan:
Out of date/Missing stats
Table/Index scans (not always a bad thing, but worth noting)
Join types
Key Lookups
Start with those, go from there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 3, 2012 at 11:58 am
I have a blog post on the basic things to look at first[/url] when you're looking at an execution plan. That's where I would start.
As to the XML plans. Generally, I either read the graphical plans directly, or I query against the XML plans. Trying to read the XML directly as it sounds like you're doing is very difficult. If you're interested in querying the XML, I also have a blog post up on that[/url] (if you search the blog, I have several other examples).
Of course, if you really want to get detailed about execution plans, you can get a copy of my book. The ebook is free to download, or you can follow the link below to the paper book.
"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
December 3, 2012 at 12:54 pm
Don't try to read the XML plan raw. It's possible if you like pain.
SAve the xml as a .sqlplan file and open that file in management studio.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply