Reading an Execution Plan for Index Suggestions
-
I have not done much tuning in the past, so I'm looking for pointers here.
I'm looking at This .sqlplan Execution plan to determine what might be "slow"
I'm looking at the items with the highest cost; two items i saw, for example, have a cost of 22% and 20%, respectively.
the first one I'm looking at had this detail:
when i read that, I'm inferring that because the "output list" of this Clustered Index Scan has 6 column,would it be correct to assume that adding an index on those 6 columns would help?
the second item, an Index_Spool had this detail:
looking at that output list, 4 columns are in the "output list", so i again infer that an index, or changing the clustered index to be those columns, might help.
am I reading plans right?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
There's no predicate (filter) on that scan, so alone there's no way to see what index would be useful there. The cluster is being scanned and the entire thing returned. If rows were filtered out during the scan (they would appear as a predicate in the properties of the scan), those would determine a good index.
Creating an index from those 6 columns might get you a nonclustered index scan rather than an index scan, but, unless there's a filter somewhere else in the plan, it will not get you an index seek.
Can't get the full plan. Link returns:
The page you are looking for might have been removed, had its name changed, or is temporarily unavailable.
Please zip it and attach the zip file to your post.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks Gail; This query is a rollup of a bunch of financial data based on it's attributes, so there's no WHERE statement in it....just a bunch of GROUP BY stuff in it;
my web server didn't let me serve .sqlplans, so i changed it to a .xml extention, and added the zip file as you suggested.
since there's no WHERE condition, I'm guessing that only a modification on the Clustered index is left?
Will changing my clustered index to include those columns, instead of the exisitng identity() column, assist the query performance wise? I'm going to do exactly that in my Sandbox, but I'd love to hear your input.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (5/26/2009)
TWill changing my clustered index to include those columns, instead of the exisitng identity() column, assist the query performance wise?A clustered index has the data page at it's leaf level, hence it already 'includes' every single column in the table.
Don't modify the cluster unless you've got a really good reason. Remember that it defines the logical order of data pages. There's a lot more to a good cluster than usable in a single query.
If it's aggregations, try an NC index with the group by columns in the index key and any columns that are aggregated as include columns. You still won't see an index seek, but it may allow SQL to do a nice, fast stream aggregate.
If you could post the definition of the view that you're querying, the table defs and the indexes on then, it may help.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply