Sql-server – ny way to find information of index fragmentation, Statistics information with the help of system tables in SQL Server

fragmentationindexindex-maintenancesql serversql-server-2012

I am working on a report which will give information for different segments like

  1. blocking process
  2. slow running queries
  3. disk space etc.

I also want to add one more segment where I can get information related to SQL Server index fragmentation.

Is there anyway to find information for Index Fragmentation and statistics in SQL Server with the help of system tables?

Best Answer

Is there anyway to find information for Index Fragmentation and statistics in SQL Server with the help of system tables?

The more appropriate way of finding fragmentation details for SQL Server is by using DMV sys.dm_db_index_physical_stats. Please note since DMV's came from SQL Server 2005 onward below script will work for 2005 onward only

select
                    o.name AS objectName,     
                    i.name AS indexName, 
     s.name as Schemaname
                ,p.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID (), NULL, NULL , NULL, null) AS p 
                    INNER JOIN sys.objects as o 
                        ON p.object_id = o.object_id 
                    INNER JOIN sys.schemas as s 
                        ON s.schema_id = o.schema_id 
                    INNER JOIN sys.indexes i
                        ON p.object_id = i.object_id 
                        AND i.index_id = p.index_id 
                WHERE p.page_count > 2000 and p.avg_fragmentation_in_percent >5
                AND p.index_id > 0 and s.name <> 'sys'

For statistics you also have sys.dm_db_stats_properties IF you are using SQL Server 2008 R2 Sp2 and above you can use below script. Source

-- Script - Find Details for Statistics of Whole Database
-- (c) Pinal Dave
-- Download Script from - https://blog.sqlauthority.com/contact-me/sign-up/
SELECT DISTINCT
OBJECT_NAME(s.[object_id]) AS TableName,
c.name AS ColumnName,
s.name AS StatName,
STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated,
DATEDIFF(d,STATS_DATE(s.[object_id], s.stats_id),getdate()) DaysOld,
dsp.modification_counter,
s.auto_created,
s.user_created,
s.no_recompute,
s.[object_id],
s.stats_id,
sc.stats_column_id,
sc.column_id
FROM sys.stats s
JOIN sys.stats_columns sc
ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
JOIN sys.partitions par ON par.[object_id] = s.[object_id]
JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
CROSS APPLY sys.dm_db_stats_properties(sc.[object_id], s.stats_id) AS dsp
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND (s.auto_created = 1 OR s.user_created = 1)
ORDER BY DaysOld;
Related Question