This is the SQL statement I use to find what % of index are fragmented. SELECT A.object_id , A.index_id , [name] , Avg_Fragmentation_In_Percent FROM sys.dm_db_index_physical_stats (db_id(), NULL,NULL, NULL, NULL) AS A JOIN sys.indexes B WITH(NOLOCK) ON A.Object_id = B.Object_id AND A.Index_id = B.Index_id WHERE [name] IS NOT NULL If Avg_Fragmentation_In_Percent is > 5% and 30%; [...]
Archive for February, 2008
Check File Growth
Check the file growth option for your databases, it also tells you the logical and physical file name, file location, etc …
Rebuilding indexes
Script to rebuild indexes. it splits the tasks out into an online and offline reindexing. I made some modifications to it. You have to create this store procedure in the database you are looking to indexed.
usp_isJobRunning
Store Procedure to check whether a job is running or not.