Can't remove duplicates

  • Hi all, I have written below query to find indexes that have page compression.  I have added the sys.dm_db_index_usage_stats so that I can find out how many reads and writes each index has.  The problem is that I am getting dupes, and I can't figure out why.  I have attached an example of duplicates.  Can you please help me to remove the dupes?  Thank you.
    SELECT SCH.name AS SchemaName,
       OBJ.name AS ObjName,
       INDX.name AS IndexName,
       OBJ.create_date AS DateCreated,
       INDX.type_desc AS IndexType,
       IndexId = indx.index_id,
       STAT.row_count AS [RowCount],
       ((STAT.used_page_count * 8) / 1024) AS UsedSizeMB,
       user_updates AS [Total Writes],
       user_seeks + user_scans + user_lookups AS [Total Reads],
       (user_seeks + user_scans + user_lookups) - user_updates AS Difference,
       PART.data_compression_desc
    FROM sys.partitions AS PART
      INNER JOIN sys.dm_db_partition_stats AS STAT WITH(NOLOCK) ON PART.partition_id = STAT.partition_id
                          AND PART.partition_number = STAT.partition_number
      INNER JOIN sys.objects AS OBJ WITH(NOLOCK) ON STAT.object_id = OBJ.object_id
      INNER JOIN sys.schemas AS SCH WITH(NOLOCK) ON OBJ.schema_id = SCH.schema_id
      INNER JOIN sys.indexes AS INDX WITH(NOLOCK) ON STAT.object_id = INDX.object_id
                      AND STAT.index_id = INDX.index_id
      LEFT JOIN sys.dm_db_index_usage_stats AS s WITH(NOLOCK) ON s.[object_id] = INDX.[object_id]
                          AND INDX.index_id = s.index_id
    WHERE PART.data_compression_desc = 'PAGE'
      AND OBJ.type = 'U'
    ORDER BY SCH.name,
       OBJ.name;

  • Two things come to my mind, but seem to be weird cases:
     1. Your tables are partitioned, but it's weird that the partitions are exactly the same size.
     2. You have objects with the same id in multiple databases in that instance. sys.dm_db_index_usage_stats is an instance-scope object and you should limit it to your current database.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis, thank you so much, the second point was the reason why I saw dupes.  I added "s.database_id = DB_ID()" and that solved the issue.  Thank you very much!!!

Viewing 3 posts - 1 through 2 (of 2 total)

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