SQL Server Performance

All INDEX details query - gives duplicate entires

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by sqlwars, May 14, 2010.

  1. sqlwars New Member

    HI,

    I mixed and matched many queries to give all details of index needed for analysis. However, 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

  2. Adriaan New Member

    Well, they can't be completely duplicate - at least one column will have a different value between the "duplicate" rows.
    Order by the index name. In my case, it's the user_* columns that vary.
  3. sqlwars New Member

    yes, that confuses me even more.
    i need the details to check which indexes can be elimnated, but it has become even more confusing now [:(]
  4. sqlwars New Member

    these are the columns due to which the duplicates are occuring.
    --sys.partitions (partition id,hobt_id)
    --sys.allocation_units (allocation_unit_id,container_id, total_pages ,used_pages,data_pages)
    --computed column - index size
    I tried with distict, but it didnt work. each index name is being repeated 6 times. I tried grouping it with indexname, but it shows 6 results, for indexsize because of total_pages column.
    I guess it will work, if i put these values in temp table, then the duplicates wont come. the problem is: this is the biggest every query i have modified. I am not a query person at all. [:(] lets see.
  5. Adriaan New Member

    Remove the offending columns from the GROUP BY clause. You then either apply an aggregate on those offending columns where you want to have some sort of information (SUM, AVG, MIN, MAX) or you remove them from the select list.
  6. satya Moderator

Share This Page