May 14, 2010 at 5:58 am
http://msdn.microsoft.com/en-us/library/ms979207.aspx
Go through with this to practice on profiler.
May 14, 2010 at 6:14 am
Thanx chowdary.
May 14, 2010 at 6:20 am
I myself was strugling to remove the existing unwanted indexes on the system and may be adda few more, if needed.
I ran the DB engine tuning wizard with sugesstion to. In process I joined few queries to give all details of indexes in a DB 9the onnly issue with it is it returns duplicate rows of indexes because of few columns). yet it gives all details you need.
-- 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
May 14, 2010 at 6:37 am
hello sqlwar,
can i run this scrit to get all the indexes details in the DB,could you plese tell is there any change i should do before executing this script,i already got the backup the DB so i aim trying this in test server.
thanx
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply