Good question…for production databases I try (on some kind of regular basis!) to keep tabs on the table structures and their “overall health”. This means I try to be proactive about table size, proper design, fragmentation, etc. Over the years I have collected a series of queries that help in this regard, and decided recently to put them all together and update them for SQL 2014. I got rid of references to DBCC SHOWCONTIG and to old system tables. At the bottom you will find my script, “table health.sql”. This script is mostly based on information that is found in the DMV sys.dm_db_index_physical_stats, so it is resource intensive. Please run it during off-hours on your production machine or run it against a copy of production. I use a temp table to store results, but it can be easily modified to store the results in a permanent table so that you can compare between time periods.
This post isn’t about documenting tables…I have other scripts that provide “data dictionary”-type information about my tables…that’s for another time…
How does this script help me? First, it provides me with technical documentation on every table in my database, and second it provides me with technical statistics that I can use to determine if structure changes are needed or if defragmentation needs to occur. Please note that this script was written from a table perspective, so it concentrates on information about the data pages and related table structure. I have other scripts used to examine index health; I often use them together to determine if changes are needed or if a defragmentation is required. I also like to use a scheduled task to check fragmentation of all indexes and rebuild/reorg only as necessary, so this information is mostly just for reference (and to verify that my scheduled task is working correctly!).
The queries found in the script below will provide the following information:
- Table inventory
- Table storage location (file group)
- Table sizes by row count and page count and used space (data pages)
- Tables without a clustered index
- Tables without a non-clustered index
- Tables and their average and max row sizes
- Tables and amount of space used on each page
- Tables and fragmentation amount
The queries found in the script help me answer the following questions:
- Do I have any tables that do not have a clustered index?
- Do I have any tables that do not have a non-clustered index and might need one or more?
- Do I have any tables that I can drop (tables used to store a copy of a prod table as a backup or for a data fix, for instance, and was left there for months and months…)?
- Which tables have fragmentation that may need to be corrected?
- I usually work on tables by table size (largest first to get the biggest bang for my buck!), so having the different size statistics helps me decide which tables might be more important to fix first.
Here is the script:
-- table health queries -- originally from: -- 10 things you can learn from DBCC SHOWCONTIG -- from best of SQL Server Central Vol 3 - article by Joe Sack -- updated for SQL 2014: -- MS says not to use DBCC SHOWCONTIG anymore, use queries to sys.dm_db_index_physical_stats instead! -- dm_db_index_physical_stats can show info for ALL indexes, but this script limits it to -- clustered indexes and heaps to concentrate on the data pages of each table. use yourUserDatabase go -- create a table to hold results - could be a permanent table create table #TableHealth ( DatabaseID int, ObjectID int, IndexID int, PartitionNumber int, IndexTypeDesc varchar(60), PageCount bigint, RecordCount bigint, AvgRecSizeBytes float, MaxRecSizeBytes int, AvgSpaceUsedPct float, AvgFragPct float, TableName varchar(100), TableOwner varchar(50) ); -- this statement takes some time to execute! -- it reads every page in the database, so it trashes the buffer pool! -- only run off-hours or on a copy of the database! insert into #TableHealth select ps.database_id, ps.object_id, ps.index_id, ps.Partition_number, ps.index_type_desc, ps.page_count, ps.record_count, ps.avg_record_size_in_bytes, ps.max_record_size_in_bytes, ps.avg_page_space_used_in_percent, ps.avg_fragmentation_in_percent, o.name, s.name from sys.dm_db_index_physical_stats(db_id(),null,null,null,'DETAILED') ps join sys.objects o on ps.[object_id] = o.[object_id] join sys.schemas s on o.[schema_id] = s.[schema_id] ; -- query 1- list all tables without a clustered index -- general rule; all tables should have a clustered index! select distinct TableOwner, TableName from #TableHealth where ObjectID not in (select ObjectID from #TableHealth where IndexTypeDesc = 'CLUSTERED INDEX'); -- query 2 - list all tables without a non-clustered index select distinct TableOwner, TableName, IndexTypeDesc from #TableHealth where ObjectID not in (select ObjectID from #TableHealth where IndexTypeDesc = 'NONCLUSTERED INDEX'); -- query 3 - tables, data pages and space used select TableOwner, TableName, IndexTypeDesc, format(PageCount, '#,###,###') as NumOfPages, format(PageCount * 8, '###,###,###') as SpaceUsedKB from #TableHealth where IndexTypeDesc in ('HEAP','CLUSTERED INDEX') order by PageCount desc; -- query 4 - top 50 tables by row count select top 50 TableOwner, TableName, format(RecordCount , '###,###,###') as RecCount from #TableHealth where IndexTypeDesc in ('HEAP','CLUSTERED INDEX') order by RecordCount desc; -- query 5 - top 50 tables with largest avg record size select top 50 TableOwner, TableName, format(AvgRecSizeBytes , '###,###') as AverageRowSizeBytes from #TableHealth where IndexTypeDesc in ('HEAP','CLUSTERED INDEX') order by AvgRecSizebytes desc; -- query 6 - top 50 tables with largest max record size select top 50 TableOwner, TableName, format(MaxRecSizeBytes , '###,###') as MaximumRowSizeBytes from #TableHealth where IndexTypeDesc in ('HEAP','CLUSTERED INDEX') order by MaxRecSizeBytes desc; -- query 7 - top 50 tables with largest free bytes per page -- filter out tables that are empty or only use one page select top 50 TableOwner, TableName, format(AvgSpaceUsedPct, '###.##') as AvgPageSpaceUsedPct from #TableHealth where IndexTypeDesc in ('HEAP','CLUSTERED INDEX') and PageCount > 1 and AvgSpaceUsedPct > 0 order by AvgSpaceUsedPct asc; -- query 8 - top 50 tables with most fragmentation -- for heaps it's the extent fragmentation (out of order extent) -- for indexes it's the logical fragmentation (out of order pages) select top 50 TableOwner, TableName, format(AvgFragPct , '###.##') as AvgFragmentationPct from #TableHealth where IndexTypeDesc in ('HEAP','CLUSTERED INDEX') order by AvgFragPct desc; -- query 9 - list tables / filegroups SELECT f.[name] as FileGroup, o.[name] as TableName, i.[name] as ObjName, i.[index_id] FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id] WHERE i.data_space_id = f.data_space_id AND o.type = 'U' -- User Created Tables and i.index_id in (0,1) -- only grab clustered indexes and heaps order by 1,2; -- when done, drop the table drop table #tableHealth; go --end of script