November 18, 2009 at 3:44 pm
Thanks for this series, Gail. I have really enjoyed reading it and found it very informative.
Cheers!
Nicole Bowman
Nothing is forever.
November 18, 2009 at 4:44 pm
November 18, 2009 at 7:09 pm
j_braclin (11/18/2009)
i dunno. This kind of confused me.
Well then ask some questions. What are you confused about? I'm sure Gail, the author, will try to clarify, and I'll help where I can as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 19, 2009 at 3:03 am
sjsubscribe (11/18/2009)
Say I have a non-unique, non-clustered index with some included columns. It covers a bunch of frequently used queries. I could make it an unique index if I add another column to the index, assuming unique indexes are better than non-unique ones.
If it's just so that you can make the index unique, I probably wouldn't bother.
And, what if that column also happens to be part of the clustered index for that the underlying table? Would that alter your recommendation?
No, not really. If the index unique you should mark it as unique, don't go adding columns just to make it unique unless it also lets more queries use the index or otherwise enhances performance of something.
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
November 19, 2009 at 3:10 am
Cliff Jones (11/18/2009)
The Tuning Adviser often recommends that a column which is also a part of the Clustered index be added as an included column. I do not do this since I assume that it is included in the index anyway. Would including such a column make the index larger for no apparent gain?
Depends where it's suggesting that the column be added. Sure, the clustering key will be there (though whether or not it can be used for seeks depends on a property of the index, specifically unique) but, as far as I know it's the last column. If the recommendation has the clustering key earlier in the index key, then it may be necessary. If it's just specified as an include it may be unnecessary.
That said, the clustering key isn't going to be there twice. If you explicitly define it as part of the index key, SQL's not going to add it again.
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
November 19, 2009 at 3:14 am
Jack Corbett (11/18/2009)
j_braclin (11/18/2009)
i dunno. This kind of confused me.Well then ask some questions. What are you confused about? I'm sure Gail, the author, will try to clarify, and I'll help where I can as well.
I suspect that post is spam. Look at the link in the 'signature'
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
November 19, 2009 at 4:55 am
GilaMonster (11/19/2009)
Sure, the clustering key will be there (though whether or not it can be used for seeks depends on a property of the index, specifically unique) but, as far as I know it's the last column..
Would it be alright if we swapped brains for, oh, say, the next 20 years?
That'd help bunches. Thanks.
November 19, 2009 at 6:08 am
GilaMonster (11/19/2009)
Jack Corbett (11/18/2009)
j_braclin (11/18/2009)
i dunno. This kind of confused me.Well then ask some questions. What are you confused about? I'm sure Gail, the author, will try to clarify, and I'll help where I can as well.
I suspect that post is spam. Look at the link in the 'signature'
Ah, I didn't even notice that.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 19, 2009 at 6:53 am
INCREDIBLEmouse (11/19/2009)
GilaMonster (11/19/2009)
Sure, the clustering key will be there (though whether or not it can be used for seeks depends on a property of the index, specifically unique) but, as far as I know it's the last column..
Would it be alright if we swapped brains for, oh, say, the next 20 years?
That'd help bunches. Thanks.
Don't sweat it, Overlord Shaw is currently working on her coup to conquer the galaxy, at which time these matters will become sacred writ. All hail the conquering hero(ine)!
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
November 19, 2009 at 7:41 am
Really liked the explanations, great articles so far 😀
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
November 19, 2009 at 7:59 am
GilaMonster (11/19/2009)
Cliff Jones (11/18/2009)
The Tuning Adviser often recommends that a column which is also a part of the Clustered index be added as an included column. I do not do this since I assume that it is included in the index anyway. Would including such a column make the index larger for no apparent gain?Depends where it's suggesting that the column be added. Sure, the clustering key will be there (though whether or not it can be used for seeks depends on a property of the index, specifically unique) but, as far as I know it's the last column. If the recommendation has the clustering key earlier in the index key, then it may be necessary. If it's just specified as an include it may be unnecessary.
That said, the clustering key isn't going to be there twice. If you explicitly define it as part of the index key, SQL's not going to add it again.
Ok, that makes sense. So it might make sense to have a clustered index column in the index key. And apparently SQL Server is smart enough not to make it a redundant column if you should add the clustered index column as an INCLUDED column in the index, since that column would already exist in the leaf level of the index.
Thanks for the explaination.
November 19, 2009 at 7:10 pm
Great final article, and I saw that you did include the relatively new INCLUDE clause for indexes (2005) as you had said you were going to do in the Part II discussion. However, the one benefit about using INCLUDE columns you did not state in your article is that by using the INCLUDE on non-covered columns in the select clause it eliminates those expensive Bookmark Lookups from showing up in the execution plan and that does bear mentioning IMHO. 🙂 Now that said, I do realize it is not prudent to do this on every column in a select clause, so one does have to use some discretion here. However, as the illustrious Grant Fritchey (execution plan expert for (http://www.SQLShare.com) often explains, avoiding Bookmark Lookups whenever you can in an query execution plan is always something to lookout for, and can possibly reap very nice dividends in faster query execution times. HTH. Travis.
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
November 19, 2009 at 8:24 pm
Outstanding series! Thanks for writing it.
November 20, 2009 at 9:11 am
November 23, 2009 at 11:27 pm
Thanks, this has been a great help.
Michael
Viewing 15 posts - 16 through 30 (of 92 total)
You must be logged in to reply to this topic. Login to reply