“ I have been asked several times for advice on how to effectively maintain a production database. Sometimes the questions come from none-DBAs who are implementing new solutions and want help fine-tuning maintenance practices to fit their new databases’ characteristics. As with the majority of tasks and procedures in the IT world, there isn’t an easy one-size-fits-all solution for effective database maintenance, but there are some key areas that nearly always need to be addressed. My top five areas of concern are (in no particular order of importance):
- Data and log file management
- Index fragmentation
- Statistics
- Corruption detection
- Backups “
I was looking into constructive guidance and description and this article provides very good information about this subject.
Now, I also stumble into this T-SQL script few weeks ago and give a brief overview of the Index fragmentation volume, which can cause major performance issues in SQL servers.
SELECT dbschemas.[name] as 'Schema', dbtables.[name] as 'Table', dbindexes.[name] as 'Index', indexstats.avg_fragmentation_in_percent --SUM(page_count * 8) AS 'IndexSizeKB' --indexstats.page_count FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL)AS indexstats INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id] INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id] INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id WHERE indexstats.database_id = DB_ID() ORDER BY indexstats.avg_fragmentation_in_percent desc --select * from sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)
Sample Results (image):
NOTE: If the fragmentation percentage is over 40-50% it will be extremely bad.
