User Rating: 0 / 5

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
Get index usage - Returns the indexes in the current database along
                  with metrics showing how often the index has been used
                  and how it was used in a query plan. Also shows how
                  often the index has been changed (incurring overhead).
Output :
   Database         - Database in which the indices are evaluated. 
                      sys.dm_db_index_usage_stats obtains information from
                      all databases and is limited to the current database
                      in the query
   Schema           - Schema of the table or view
   Object Name      - Name of the table or view
   Index Name       - Name of the index
   Type             - Specifies if the index contains the data or if this is
                      a secondary index on the table.
   Index Type       - Description of the index type.
   User Seeks       - Number of times the index was used in a 'seek'
   Last User Seek   - When the last seek happened.
   User Scans       - Number of times the leaf pages where scanned for a value.
   Last User Scan   - When the last scan happened.
   User Lookups     - Number of times the index was used for a bookmark looup.
                      For clustered indexes only
   Last User Lookup - When the last bookmark lookup happened.
   User Updates     - The number of insert, update, or delete operations on the
                      table that had to be maintained on the index. Does not
                      count the number of rows, but the number of statements.
   Last User Update - When the last update happened. 
Notes : sys.dm_db_index_usage_stats does not return information about memory-optimized indexes.
        See sys.dm_db_xtp_index_stats for memory-optimized indexes. \
        sys.dm_db_index_usage_statsThe counters are initialized to empty whenever
        the SQL Server service is started. In addition, whenever a database is
        detached or is shut down , all rows associated with the database are removed.
   -- Get the startup time for the instance and print.
   DECLARE @InstanceStartup DATETIME;
   SELECT @InstanceStartup = sqlserver_start_time FROM sys.dm_os_sys_info
   PRINT @@SERVERNAME + ' : Started on ' + CONVERT(VARCHAR(20), @InstanceStartup)
   SELECT DB_NAME()                                          AS [Database],
          SCHEMA_NAME(so.schema_id)                          AS [Schema],
                                            AS [Object Name],
                                            AS [Index Name],
          CASE us.index_id
             WHEN 0 THEN 'Data'
             WHEN 1 THEN 'Data'
             ELSE        'Secondary'
          END                                                AS [Type],
          si.type_desc                                       AS [Index Type],
          ISNULL(us.user_seeks, 0)                           AS [User Seeks],
          ISNULL(CONVERT(CHAR(20), us.last_user_seek), '')   AS [Last User Seek],
          ISNULL(us.user_scans, 0)                           AS [User Scans],
          ISNULL(CONVERT(CHAR(20), us.last_user_scan), '')   AS [Last User Scan],
          ISNULL(us.user_lookups, 0)                         AS [User Lookups],
          ISNULL(CONVERT(CHAR(20), us.last_user_lookup), '') AS [Last User Lookup],
          ISNULL(us.user_updates, 0)                         AS [User Updates],
          ISNULL(CONVERT(CHAR(20), us.last_user_update), '') AS [Last User Update]
   FROM sys.indexes       si
   INNER JOIN sys.objects so ON so.object_id = si.object_id
                            AND so.type IN ('U', 'V')
   LEFT JOIN sys.dm_db_index_usage_stats us ON us.object_id   = si.object_id
                                           AND us.index_id    = si.index_id
                                           AND us.database_id = DB_ID()
   ORDER BY [Schema],
            [Object Name],
            [Index Name];

Add comment

Security code