June 1, 2016 at 11:47 am
Evening All,
I am using some of the DMV's to identify missing indices on tables and their potential impact.
It spits out 3 columns of interest:
inequality columns
equality columns
included columns
The latter I know what to do with, simply adding that as an included column..
But it dawned on me, I don't really know how to react differently to the first two?
I was going to create a nonclustered index covering both the fields in equality and inequality and then INCLUDE the including columns.
But I am not sure if that's right? Are they split out into separate fields for a reason?
These fields are sourced from dm_db_missing_index_details
So my ultimate question is how does one react between those 2-3 types of field mentioned above?
Cheers
Alex
June 1, 2016 at 12:21 pm
alex.sqldba (6/1/2016)
Evening All,I am using some of the DMV's to identify missing indices on tables and their potential impact.
It spits out 3 columns of interest:
inequality columns
equality columns
included columns
The latter I know what to do with, simply adding that as an included column..
But it dawned on me, I don't really know how to react differently to the first two?
I was going to create a nonclustered index covering both the fields in equality and inequality and then INCLUDE the including columns.
But I am not sure if that's right? Are they split out into separate fields for a reason?
These fields are sourced from dm_db_missing_index_details
So my ultimate question is how does one react between those 2-3 types of field mentioned above?
Cheers
Alex
The missing indexes system is a morass, sadly. The KEY THINGS to know are:
a) It will NOT check what you have and will duplicate/overlap in a heartbeat.
b) It LOVES covering a query, leading to a tremendous amount of index/included columns.
I have seen clients DESTROY their system with rampant use of this and/or Database Tuning Advisor.
I would use Glenn Berry's or others scripts to see missing index benefit, columns, etc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 1, 2016 at 12:34 pm
To answer your question, equality columns are columns that generally appear in JOIN/WHERE clauses with an equal sign; inequality columns generally appear with some inequality, e.g., <=. Equality columns allow a seek, but inequality columns require at least a partial scan. Because of that, it is usually best to place equality columns before inequality columns when creating an index.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 1, 2016 at 12:41 pm
And if you're going to apply them as is (and please read Kevin's warnings, they're very accurate and if anything not dire enough), the keys consist of the equality and any inequality columns.
Here's my question though. Which query does that missing index fix? Is it a query called hundreds of times a day, so that index will be very helpful? Or, is it a query that was called one time, three weeks ago, and is never going to be called again? Using the missing index DMVs, you can't answer that question. Ignoring all of Kevin's, absolutely accurate, warnings, this is the biggest problem with relying on the missing index DMVs.
"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
June 1, 2016 at 12:58 pm
I have also seen the Missing Index DMVs and DTA recommend a nonclustered index that if implemented duplicated the entire table in the index between indexed and included columns.
Take the advice of these tools with a tom of salt, and a lot of testing.
June 1, 2016 at 1:23 pm
Lynn Pettis (6/1/2016)
I have also seen the Missing Index DMVs and DTA recommend a nonclustered index that if implemented duplicated the entire table in the index between indexed and included columns.Take the advice of these tools with a tom of salt, and a lot of testing.
I've seen THREE of those on a single very wide table before. :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 1, 2016 at 3:16 pm
Ah right, thanks all, appreciate the details and the warnings.
I wasn't going to apply all the suggestions (there are over 300) but there were two that made sense. One of which is called every couple of minutes. But then the equality/inequality portion threw me. Which has been answered too, above.
The main problem is a bit of a pig actually - might be contender for a new post. But the overview is we have a table and process (the process cannot change) that has hints that put a holdlock and update lock on a table. Which causes massive blocking problems.
Cheers
Alex
June 1, 2016 at 3:22 pm
alex.sqldba (6/1/2016)
But the overview is we have a table and process (the process cannot change) that has hints that put a holdlock and update lock on a table.
Be careful about removing them as they may be needed for concurrency control (if they're doing an 'if exists then update else insert' pattern)
To answer in more detail about the equality/inequality
http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/
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
June 2, 2016 at 12:51 am
Cheers Gail. Yeah I wasn't going to remove them as I presume they were added to fix something.
But it appears to be either bad design, or the product has grown in such an extent it's not longer used the same. The client application has two screens, one is a modal window within another, and the parent window with its hold and update locks manages to block the child window.
Which is annoying.
As a test, on the staging instance I left the process running to see if it would even unblock and resume. For 3 days it sat there, I'd left it for the long weekend.
So the only thing left was to file a bug with the mfr - who aren't particularly interested because they've already been paid.
/rant
Alex
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply