1

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?

migrated from stackoverflow.com Apr 18 at 11:12

This question came from our site for professional and enthusiast programmers.

  • This question looks more appropriate for Database Administrators rather than here. – Ken White Apr 18 at 2:25
  • 1
    If your question is about SQL Server, why is it tagged for PostgreSQL? – Bob Jarvis Apr 18 at 2:32
2

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.