All INDEX details query - gives duplicate entires

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » All INDEX details query - gives duplicate...

All INDEX details query - gives duplicate entires

Rate Topic

Display Mode

Topic Options

Author

Message

sqlwars

Posted Friday, May 14, 2010 1:11 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, August 04, 2010 3:59 AM
Points: 16, Visits: 67

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

Post #921792

Dave Ballantyne

Posted Friday, May 14, 2010 1:32 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943, Visits: 8,227

Possibly the simplest way is to use DISTINCT.

Clear Sky SQL
My Blog
Kent user group

Post #921798

sqlwars

Posted Friday, May 14, 2010 5:16 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, August 04, 2010 3:59 AM
Points: 16, Visits: 67

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?

Post #921913

Bhuvnesh

Posted Friday, May 14, 2010 8:25 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #922073

jeffem

Posted Friday, June 22, 2012 9:11 AM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:54 AM
Points: 401, Visits: 259

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.

Post #1320023

« Prev Topic | Next Topic »

Permissions