July 18, 2011 at 9:47 pm
Comments posted to this topic are about the item Speeding up Data Access Part 1 - Missing Indexes
July 19, 2011 at 2:20 am
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
July 19, 2011 at 4:04 am
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
July 19, 2011 at 6:09 am
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 😛
July 19, 2011 at 7:37 am
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?
July 19, 2011 at 8:13 am
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.
July 19, 2011 at 9:26 am
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
July 19, 2011 at 10:36 am
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.
July 19, 2011 at 11:02 am
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
July 19, 2011 at 11:08 am
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.
July 21, 2011 at 10:25 am
What did you mean by the statement "If you send stored procedure calls to the database, good on you." ?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply