Can't remove duplicates

Question

Can't remove duplicates

shahgols

SSCrazy Eights

Points: 9874
More actions
March 21, 2019 at 10:33 am

#416893

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;

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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

shahgols SSCrazy Eights Points: 9874 More actions · Answer 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!!!