August 16, 2007 at 2:51 am
Does any one have any experience with Performance Dashboard's missing index report?
I've recently been given a set of indexes to apply that seem very poor choices. For example the report include indexes on bit fields with includes on account number fields.
Has anyone had any positive experiences or is my understanding of indexes wrong?
August 18, 2007 at 5:15 am
I'm not quite sure what you're asking - the missing index report should be used as a guide to performance tuning, but it should not be followed blindly.
In certain cirmstances indexing bit fields can indeed be a good idea, as part of an index of course. Includes should be checked carefully, as in all such you should test, make the change, test again. If you're adding indexes without any idea what queries they are against then that is basically risky. I'm in the process of blogging about index analysis, there's two parts so far, not got to missing indexes yet!
http://sqlblogcasts.com/blogs/grumpyolddba/archive/2007/07/12/analysing-indexes-part-1.aspx
http://sqlblogcasts.com/blogs/grumpyolddba/archive/2007/07/22/working-with-indexes-part-2.aspx
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 22, 2007 at 5:51 am
I think that the main reason that the report was alerting me to poor results was the SQLservers uptime was only a few days old (dmvs were reset).
However I am interested in why you think bit fields can be a good idea? Did you mean only when as part of composite indexes?
August 23, 2007 at 7:28 am
yes when part of another index - it really depends upon the actual queries, for example working with data where there is a column to indicate active/disabled so queries include part of the where as " where current ='1' " In certain circumstances this can cause a scan - I found adding the bit field ( to anothe rindex ) speeded up the query and lowered io - it depends on the query and table - but it can be valid to index a bit column.
Yes the missing index results are culmulative from last uptime, as long as you take that into account - I take readings from these columns every so often and then compare the data. I'm not dismissing the use of this table - but it should be used as part of a process and not relied upon blindly.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 23, 2007 at 7:30 am
Thanks for the advice.
Very impressed by your blog enteries btw.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply