Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Speeding up Data Access Part 1 - Missing Indexes


Speeding up Data Access Part 1 - Missing Indexes

Author
Message
mperdeck
mperdeck
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 37
Comments posted to this topic are about the item Speeding up Data Access Part 1 - Missing Indexes
naweed.akhtar
naweed.akhtar
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 78
Hi

Very informative article, thanks for the info.

One question, you say in the article:

...If this is high in relation to the number of reads, consider dropping the index...

Which column is the 'number of reads' column? I am trying to see if I need to get rid of some indexes but am not sure which column shows the 'number of reads'

Thanks
Naz
mister boom
mister boom
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 254
Hi

Very nice article. I'm looking forward to the rest of the series.

I ran the query to show unused indexes, but it returned some strange results including linking databases to the wrong tables and indexes.

The reason appears to be that sys.dm_db_index_usage_stats is a server-wide DMV, while sys.tables and sys.indexes are database-specific, so an object_id in sys.dm_db_index_usage_stats can relate to multiple objects in different databases.

Adding "and ius.database_id=db_id()" to the query to only look at one database at a time fixed it for me.

Thanks
dave.clark
dave.clark
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 555
Another quick note - the dmv will only show an index if it has been used (read from or written to).
So you may have an index that is truly not used at all - and it would not appear in the results.


Although I guess if that were the case - the associated table wouldn't be being used :-P
k.lasuy
k.lasuy
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 13
I'm confused with the results i get from the dm_db_missing_index_details.
If i understand correct i should add the indexes in de included_columns column.
While it shows indexes that we already made.
So which indexes should i add?
Nadrek
Nadrek
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1554 Visits: 2714
I would suggest including more warnings about the missing index DMV's flaws; suggesting duplicate or overlapping indexes, suggesting indexes that won't actually help, and so on.

I may have missed it, but finding queries by aggregate cost is also critical; a 100 cost (read, CPU, etc.) query executed 50,000 times is likely more important than a 5,000 cost (read, CPU, etc.) query executed twice.

Suggesting the Database Tuning Advisor "identifies an optimal set of indexes that takes the requirements of all queries into account" is also... optimistic, to say the least. The DTA often makes bad suggestions and misses good suggestions.

The trace information was better, though I'd suggest that the ".Net SqlClient Data Provider" is only there if the developer fails to set "Application Name=My Apps Name" in the connection string.
bholt-1148951
bholt-1148951
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 38
I ran the dm_db_missing_index_details query and one result listed in the Included_Columns was the Primary Key for the table. Not sure why this is suggested as an Index to be added. ??

Bill
Chris Harshman
Chris Harshman
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3839 Visits: 3750
naweed.akhtar (7/19/2011)
Which column is the 'number of reads' column?


There's actually 3 columns to look at in the query, all are from dm_db_index_usage_stats:
USER_SEEKS, USER_SCANS, USER_LOOKUPS. When those 3 numbers are all 0 or very low, then consider if you really need this index.
alen teplitsky
alen teplitsky
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2248 Visits: 4654
Nadrek (7/19/2011)
I would suggest including more warnings about the missing index DMV's flaws; suggesting duplicate or overlapping indexes, suggesting indexes that won't actually help, and so on.

I may have missed it, but finding queries by aggregate cost is also critical; a 100 cost (read, CPU, etc.) query executed 50,000 times is likely more important than a 5,000 cost (read, CPU, etc.) query executed twice.

Suggesting the Database Tuning Advisor "identifies an optimal set of indexes that takes the requirements of all queries into account" is also... optimistic, to say the least. The DTA often makes bad suggestions and misses good suggestions.

The trace information was better, though I'd suggest that the ".Net SqlClient Data Provider" is only there if the developer fails to set "Application Name=My Apps Name" in the connection string.


depends on the system

i just changed the filtering i use for reporting missing indexes after running the DMV's in the morning. i used to report anything over a few hundred or 1000 seeks until one of our BI servers had the CPU spike for hours every day. turns out a few queries that only run once or twice a day caused it. so now i filter by cost

and my favorite is when it says to create an index on a bit column or one where the table will have a few million rows but there are only a few unique values in the column of data
Chris Harshman
Chris Harshman
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3839 Visits: 3750
k.lasuy (7/19/2011)
If i understand correct i should add the indexes in de included_columns column.
as Nadrek mentioned, you have to take the information that dm_db_missing_index_details gives you with a grain of salt, not everything it recommends is a good idea.

Reading your question though, maybe the DMV needs more explanation itself. The statement column is really the 3 part database.schema.table name for the index to be put on. The equality_columns lists the columns you will be indexing, included_columns lists the additional columns to put in the INCLUDE clause of the CREATE INDEX statement. This will create what's called a "covering index", since the query engine will be able to just read the index then instead of doing additional reads on the table itself. Sometimes it will recommend multiple indexes on the same table with the same equality_columns, but with no or different included_columns. Those can likely be created as 1 index with the included_columns. If it recommends an index that matches equality_columns with an index you already have, you may want to consider adding the included_columns to your existing index.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search