Post reply

about system table of sys.dm_db_missing_index_details and sys.dm_db_index_usage_

  • December 6, 2019 at 3:45 am

    #3705219

    sys.dm_db_missing_index_details shows we need to create an index based on the columns under equality_columns or inequality_columns plus included_columns information,

    now I have some questions about this one,

    1. do you often refer to (or use) the information of this table to create or alter index ?

    2. if we seldom create/alter index based on this inforamtion, can we say it not big practical value to us?

    sys.dm_db_index_usage_stats shows the numbers of usage of index about user_seeks/ user_scans / user_lookups and user_updates , about these informations, I have questions below, thanks!

    3. Can we use the the numbers of usage of index about user_seeks/ user_scans / user_lookups to change the index on the table ? if ok, how can we change this present indexs using these information ?

    if there is a certain number to show need to change present indexs while user_scans is much higher than the user_seeks ?

    4. what does it mean about the field of user_updates ?

    5. if user_updates is much higher than the user_seeks, can we say this index unhelpful ?

  • December 6, 2019 at 8:28 am

    #3705255

    1. No
    2. It's there in case you need it.  You just need to bear in mind that each recommendation is based on the execution of a single query - it doesn't take into account the whole workload
    3. Yes, if the write activity in sys.dm_db_index_usage_stats is high and the read activity is low, the index may not be useful.  That may be because it's not on quite the right columns, or it may not be needed at all.  There is no magic threshold for judging this - index tuning is as much trial and error as it is science
    4. This is defined in the documentation.  Please post back if there's anything in particular you don't understand
    5. Possibly.  See (3) above.  You'll also want to take user_scans and user_lookups into account

    John

  • December 6, 2019 at 3:08 pm

    #3705424

    1. NO!!!!!!!! These are suggestions only.
    2. There is no way, in the world, to correlate the information within the missing index DMVs with the queries on your system. This fact alone makes this information useless. Is the index being suggested because of a single query, run one time, that will never be run ever again? You don't know. Therefore, this information is utterly useless.
    3. Eh, maybe. I'd focus a lot more on query metrics personally.
    4. What John said
    5. One giant caveat about the whole index usage stats. Specifically in the case of unique indexes, you may not see user scans, lookups, updates, or seeks for an index. Yet, that index is used by the optimizer to know that unique values exist in the table which can affect the choices in an execution plan. It's a weird spot, but you can't just rely on the fact that the index is "used" to know that the index is helpful.

    Now, if you really want to use the missing index information, I'd suggest querying the execution plans, either through the Query Store or the plan cache. This way, you can correlate the missing index suggestions (and they are VERY much suggestions, not hard and fast good indexes) with an actual query and query performance metrics. Just remember to evaluate the suggestions and test them thoroughly before implementing them. I've seen the missing index suggestions be extremely poor and/or repetitive with existing indexes. I don't trust them even as I use them regularly when tuning.

    "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

  • December 6, 2019 at 6:28 pm

    #3705500

    You also have to remember that, on sys.dm_db_index_usage_stats, things may not be as they seem.  For example, you might have 1 "UserRead" (UserSeek, UserScan, UserLookup) per day and tens of thousands of UserUpdates.  That doesn't mean that the index isn't perfect.  1 "UserRead", in this case, is due to one query that used the index.  It could have processed a million rows but will still show up as 1 "User Read".  The same goes for the UserUpdates.  It may very well be that each update was just 1 row at a time and so the number seems inordinately high compared to the "UserReads" (whatever form they take).

    As both John and Grant have indicated, the contents of that "DMV" provide information to make decisions based on a lot of additional information.  About the only thing that you can derive directly from the table is that if the SQL Service hasn't been rebooted for more than a month, the index has existed for at least that period of time, and there are no UserUpdates, there's a really good chance that the table is static.  Even then,  you could end up with a table that's only updated once per quarter or even once per year.

    As Sergiy once said to me, "A Developer must not guess... a Developer must know" and all either of the two items in the title of this post will actually do for you is to provide additional information to help you guess less.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 7, 2019 at 1:26 am

    #3705608

    Thanks John ?Grant and Jeff for you kind help and benefit much from your suggestions! thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply