Feb201005

Alex

0

How to find missing indexes in SQL Server 2005 and 2008

SELECT
[Impact] = (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans),
[Table] = [statement],
[CreateIndexStatement] = ‘CREATE NONCLUSTERED INDEX ix_’
+ sys.objects.name COLLATE DATABASE_DEFAULT
+ ‘_’
+ REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,”)+ISNULL(mid.inequality_columns,”), ‘[', ''), ']‘,”), ‘, ‘,’_’)
+ ‘ ON ‘
+ [statement]
+ ‘ ( ‘ + IsNull(mid.equality_columns, ”)
+ CASE WHEN mid.inequality_columns IS NULL THEN ” ELSE
CASE WHEN mid.equality_columns IS NULL THEN ” ELSE ‘,’ END
+ mid.inequality_columns END [...]

May200901

Alex

0

sys.dm_db_index_physical_stats

Returns size and fragmentation information for the data and indexes of the specified table or view. For an index, one row is returned for each level of the B-tree in each partition. For a heap, one row is returned for the IN_ROW_DATA allocation unit of each partition. For large object (LOB) data, one row is [...]