Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

All INDEX details query - gives duplicate entires Expand / Collapse
Author
Message
Posted Friday, May 14, 2010 1:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Friday, May 14, 2010 1:32 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:26 AM
Points: 1,949, Visits: 8,291
Possibly the simplest way is to use DISTINCT.



Clear Sky SQL
My Blog
Kent user group
Post #921798
Posted Friday, May 14, 2010 5:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Friday, May 14, 2010 8:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
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
Post #922073
Posted Friday, June 22, 2012 9:11 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 12:34 PM
Points: 401, Visits: 279
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 »

Add to briefcase

Permissions Expand / Collapse