Index Research

index_research.sql

SELECT TOP 100
    t.NAME AS [TableName],
    SCHEMA_NAME(t.schema_id) AS [SchemaName],
    MAX([data_compression_desc]) AS [Compression],
    MAX(p.rows) AS [RowCounts],
    SUM(a.total_pages) AS [TotalPages], 
    SUM(a.used_pages) AS [UsedPages], 
    SUM(a.data_pages) AS [DataPages],
    (SUM(a.total_pages) * 8) / 1024 AS [TotalSpaceMB], 
    (SUM(a.used_pages) * 8) / 1024 AS [UsedSpaceMB], 
    (SUM(a.data_pages) * 8) / 1024 AS [DataSpaceMB]
INTO #largest_tables
FROM
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%'
    AND i.OBJECT_ID > 255
GROUP BY 
    t.NAME, t.schema_id
--HAVING MAX([data_compression_desc]) NOT IN ('PAGE','ROW')
ORDER BY SUM(a.total_pages) DESC

SELECT top 100
    t.NAME AS TableName,
    SCHEMA_NAME(t.schema_id) as [SchemaName],
	MAX([data_compression_desc]) AS [Compression],
    i.name as IndexName,
    sum(p.rows) as RowCounts,
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
INTO #largest_indexes
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, t.schema_id, i.object_id, i.index_id, i.name 
ORDER BY SUM(a.total_pages) DESC

DECLARE @DatabaseID int
SET @DatabaseID = DB_ID()
SELECT DB_NAME(@DatabaseID) AS DatabaseName,
       schemas.[name] AS SchemaName,
       objects.[name] AS ObjectName,
       indexes.[name] AS IndexName,
       indexes.fill_factor,
       objects.type_desc AS ObjectType,
       indexes.type_desc AS IndexType,
       dm_db_index_physical_stats.partition_number AS PartitionNumber,
       dm_db_index_physical_stats.page_count AS [PageCount],
       dm_db_index_physical_stats.avg_fragmentation_in_percent AS AvgFragmentationInPercent
INTO #fragmentation
FROM sys.dm_db_index_physical_stats (@DatabaseID, NULL, NULL, NULL, 'LIMITED') dm_db_index_physical_stats
INNER JOIN sys.indexes indexes ON dm_db_index_physical_stats.[object_id] = indexes.[object_id] AND dm_db_index_physical_stats.index_id = indexes.index_id
INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id]
INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]
WHERE objects.[type] IN('U','V')
AND objects.is_ms_shipped = 0
AND indexes.[type] IN(1,2,3,4)
AND indexes.is_disabled = 0
AND indexes.is_hypothetical = 0
AND dm_db_index_physical_stats.alloc_unit_type_desc = 'IN_ROW_DATA'
AND dm_db_index_physical_stats.index_level = 0
AND dm_db_index_physical_stats.page_count >= 1000
AND dm_db_index_physical_stats.avg_fragmentation_in_percent >= 5
ORDER BY dm_db_index_physical_stats.avg_fragmentation_in_percent desc


SELECT
f.DatabaseName
,ISNULL(lt.SchemaName, f.SchemaName) AS SchemaName
,ISNULL(lt.TableName, f.ObjectName) AS TableName
,lt.RowCounts
,ISNULL(li.IndexName, f.IndexName) AS IndexName
,li.[Compression]
,f.fill_factor
,f.AvgFragmentationInPercent
,ISNULL(li.TotalPages, f.[PageCount]) AS [PageCount]
,li.UsedPages
,li.TotalSpaceMB
,f.PartitionNumber
FROM #largest_tables lt
FULL OUTER JOIN #largest_indexes li ON lt.TableName = li.TableName AND lt.SchemaName = li.SchemaName
FULL OUTER JOIN #fragmentation f ON lt.TableName = f.ObjectName AND lt.SchemaName = f.SchemaName AND li.IndexName = f.IndexName
ORDER BY ISNULL(lt.SchemaName,'z'), ISNULL(lt.TableName,'z'), ISNULL(li.IndexName,'z')