Unused indexes

  • Hi all,

    I used such script for finding unused indexes in my DB:

    Hi all,
    I used such script for finding unused indexes in my DB:

    SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name
    ,IX.name AS Index_Name
    --,IX.type_desc Index_Type
    ,SUM(PS.[used_page_count]) * 8 IndexSizeKB
    ,IXUS.user_seeks AS NumOfSeeks
    ,IXUS.user_scans AS NumOfScans
    ,IXUS.user_lookups AS NumOfLookups
    ,IXUS.user_updates AS NumOfUpdates
    ,IXUS.last_system_update
    ,IXUS.last_user_update AS LastUpdate
    ,IXUS.last_user_seek AS LastSeek
    ,IXUS.last_user_scan AS LastScan
    ,IXUS.last_user_lookup AS LastLookup
    FROM sys.indexes IX
    INNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID
    INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id
    WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1
    AND IXUS.user_lookups = 0
    AND IXUS.user_seeks = 0
    AND IXUS.user_scans = 0
    GROUP BY OBJECT_NAME(IX.OBJECT_ID) ,IX.name ,IX.type_desc ,IXUS.user_seeks ,IXUS.user_scans ,IXUS.user_lookups,IXUS.user_updates ,IXUS.last_system_update ,IXUS.last_user_update, IXUS.last_user_seek
    ,IXUS.last_user_scan
    ,IXUS.last_user_lookup
    ORDER BY IXUS.user_updates DESC

    It was interesting for which period I received such information.

    I read that this information updated each time when SQL Server is restarting.

    I used such a script for identifying when my SQL Server was last time restart:

    SELECT sqlserver_start_time 
    FROM sys.dm_os_sys_info

    I saw that my SQL Server was restarted in November 2020 (last time).

    But when I execute the first script each day I received different indexes. Who can explain to me why?

    For what period is the information in the view sys.dm_db_index_usage_stats relevant?

  • Quick google brought me to Microsoft's website on that view and to quote them:

    Returns counts of different types of index operations and the time each type of operation was last performed.

    (source - https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql?view=sql-server-ver15)

    So, dm_db_index_usage_stats gives you statistics about the last time that index was used.

  • Each restart, yes, but also failovers, detach/attach, all sorts of variation on that data. It is the one place to try to determine if indexes are in use, but, it's also possibly wrong. So if you do remove indexes based on the information there, be sure you have them backed up in source control so you can reapply a given index as needed, if needed.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grant Fritchey wrote:

    Each restart, yes, but also failovers, detach/attach, all sorts of variation on that data. It is the one place to try to determine if indexes are in use, but, it's also possibly wrong. So if you do remove indexes based on the information there, be sure you have them backed up in source control so you can reapply a given index as needed, if needed.

    e

    I'll add to that great idea... don't initially delete and index.  Instead, simply disable it.  That, way, a quick rebuild will enable it if the proverbial poo hits the fan.  Keep a record of when indexes were disabled (a DDL trigger would automate that process) and only drop them after some comfortable fixed amount of time has passed.  Even that can still be an error so I very much agree with Grant... the index should be in easily findable source control.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • @Mr. Brian Gale, @Grant Fritchey, @jeff Moden

    thanks a lot.

    1. Can I conclude about the appropriateness of the index based on scans, seeks and lookups in this table?

    2. Do I always need to have a clustered index in the table? For example, I have a table with very frequent inserts and there is high fragmentation. Is it advisable to create an index on such a table?

    1. Generally, yes. If the index is in active use, it's probably needed.
    2. The rule I follow is pretty simple. Unless I can absolutely prove otherwise, through very thorough testing, every table ought to have an appropriately chosen clustered index. Tons of work has been put into ensuring the performance of clustered indexes. It is the preferred storage mechanism within SQL Server (except, of course, for Clustered Columnstore, but different discussion).

    One other point, it is possible to have a unique constraint (which will be a unique index, it's just how that works), that is used by the optimizer, but, never used by your queries and therefore not visible in the DMV. This is because if the optimizer knows a given table has unique values, even though it may not choose that index, that index affects other choices, such as join method, etc.. This is rare, and unique to unique indexes. Still, I'd always be more cautious when it comes to those.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • christi1711 wrote:

    Hi all, I used such script for finding unused indexes in my DB:

    It was interesting for which period I received such information. I read that this information updated each time when SQL Server is restarting.

    I used such a script for identifying when my SQL Server was last time restart:

    I saw that my SQL Server was restarted in November 2020 (last time). But when I execute the first script each day I received different indexes. Who can explain to me why?

    For what period is the information in the view sys.dm_db_index_usage_stats relevant?

     

    You could run it regularly and save the results into a table, with an additional column containing GETDATE() if you want a history to refer to later

  • christi1711 wrote:

    For example, I have a table with very frequent inserts and there is high fragmentation. Is it advisable to create an index on such a table?

    Can you post the CREATE TABLE statement along with the CREATE INDEX statements for any indexes  created on the that table?

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Sure.

    I have separate scripts for creating table and for creating indexes.

    SET ANSI_PADDING ON
    GO


    CREATE NONCLUSTERED INDEX [IX_User_LastName] ON [dbo].[User]
    (
    [LastName] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
  • Does "index disabling" blocks a table?

    Do we have any constraints regarding it?

  • christi1711 wrote:

    Sure. I have separate scripts for creating table and for creating indexes.

    SET ANSI_PADDING ON
    GO


    CREATE NONCLUSTERED INDEX [IX_User_LastName] ON [dbo].[User]
    (
    [LastName] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

     

    The CREATE TABLE code appears to be missing from that.  If what you say is true, the PK and the Clustered Index (not necessarily the same) appears to be missing from the mix.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • christi1711 wrote:

    Does "index disabling" blocks a table? Do we have any constraints regarding it?

    No... disabling the index doesn't substantially block the table except for the time it takes to disable the index, which is quite short for most places where I've seen it done.

    Yes, there are some possibly serious constraints when it comes to either dropping or disabling an index.

    First, you should never drop a Clustered Index.  It will cause a table to revert to a heap and any Non-Clustered Indexes will automatically be rebuilt whether you want them to be or not.  Rebuilding the Clustered Index from a heap will cause the same issue.  The Clustered Index is frequently the target of any FKs, as well.

    Speaking of FKs, you probably shouldn't drop or disable any Non-Clustered index that is unique unless you've checked to see if any FKs are pointing to it and have made arrangements to handle that interaction.  It may mean rebuilding FKs if you go that route.

    When you REBUILD any index, it will paralyze the underlying table during the REBUILD unless you use the WITH (ONLINE = ON) option, which has it's own set of caveats including but not limited to some seriously massive log file entries and not doing the rebuild quite as tightly as doing an OFFLINE rebuild, which is also quite a bit faster (~350ms for a half-Gig index on my laptop for OFFLINE, almost 9 seconds for ONLINE).  It may be worth it, though.  "It Depends".  One good tradeoff is to do OFFLINE rebuilds for such a small index, wait a couple of seconds, and then do the next one if in a 24/7 environment but you do need to check the effect to make sure it's not going to kill your online customers especially if the table has a high hit ratio.

    For me, REORGANIZE simply isn't an option because, although it follows the Fill Factor, it's not capable of creating new pages to clear the area above the Fill Factor, which can be essential to preventing page-splits that cause blocking and the fragmentation caused by the page splits.

    A vivid example for the problems associated with REORGANIZE can be found in Random GUID keyed indexes.  Almost everyone regards them as a major fragmentation problem when, in reality, they're they the epitome of how most people envision how an index should actually behave.  The use of REORGANIZE on such indexes is the actual cause of the fragmentation because it packs partially full pages to the Fill Factor and does nothing to clear the area above the Fill Factor.  If you only use REBUILDs (whether online of offline) you can go literally for weeks and even months with <1% fragmentation because page-splits are seriously minimized.

    Of course, all bets are off for fragmented indexes that you rebuild that have the default Fill Factor of "0" or indexes that have a super high Fill Factor because they will also not have any place (or very little of it) to absorb mid-index inserts and "ExpAnsive Updates".  It's actually better to not bother doing index maintenance on such indexes than it is to do index maintenance wrong.  Of course, that can be the wrong thing to do, as well.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

Viewing 12 posts - 1 through 12 (of 12 total)

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