All INDEX details query - gives duplicate entires

Question

Post reply

All INDEX details query - gives duplicate entires

sqlwars

SSC-Addicted

Points: 416
More actions
May 14, 2010 at 1:11 am

#140275

HI,
I mixed and matched many queries tto give all details of index needed for analysis. Howeever, the query returns duplicate values. how do i remove the duplicate entries. PLEASE HELP. Here is the query:
-- ALL Indexes Details
SELECT
[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (s.user_seeks + s.user_scans),0)
,avg_user_impact , TableName = statement ,[name] ,i.type_desc
,[index_size_in_MB] = (sum(a.total_pages) * 8) / 1024.00 -- Pages are 8 Bytes in size
,[records_in_index]= sum(CASE WHEN a.type = 1 THEN p.rows ELSE 0 END) -- Only count the rows once
,u.user_seeks,u.user_scans,u.user_lookups,u.user_updates
,[EqualityUsage] = equality_columns, [InequalityUsage] = inequality_columns
,[Include Cloumns] = included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
INNER JOIN sys.indexes i ON d.[object_id] = i.[object_id]
INNER JOIN sys.dm_db_index_usage_stats u ON d.[object_id] = u.[object_id]
INNER JOIN sys.partitions p ON i.[object_id] = p.[object_id]
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
GROUP BY
g.index_group_handle, g.index_handle
,s.avg_total_user_cost,s.avg_user_impact,s.user_seeks,s.user_scans
,u.user_lookups,u.user_updates,u.user_seeks,u.user_scans
,i.type_desc,i.name,d.statement,d.equality_columns,d.inequality_columns,d.included_columns;
-- ALL Indexes Details

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

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

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 1

Possibly the simplest way is to use DISTINCT.

Clear Sky SQL
My Blog[/url]

sqlwars SSC-Addicted Points: 416 More actions · Answer 2

distinct didnt work, same number of results. Each Index is being repeated 6 times.

The only columns where the value changes are:

--sys.partitions (partition id,hobt_id)

--sys.allocation_units (allocation_unit_id,container_id,total_pages,used_pages,data_pages)

--computed column - index size (which uses the above columes to compute data)

Any help please to avoid the duplicates?

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

Are you sure that u are getting duplicate data ? in my system i am getting same records with or without DISTINCT

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

jeffem Ten Centuries Points: 1232 More actions · Answer 4

There are columns with different data, in my results:

user_seeks, user_scans, user_lookups, and user_updates

If you don't want the values you listed to repeat, you need to either exclude the above columns or group by the columns you don't want to repeat and use an aggregate function (MAX(), MIN(), etc.) of the others.