INDEX DEFRAGMENTATION SCRIPT SQL 2000 Index Defragmentation is one of the most important DBA tasks. This will significantly improve query performance. When you perform any DML operation (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. If you use predefined maintenance plan it will take much server resource and time. Here is a custom stored procedure. Use the DBCC SHOWCONTIG statement. It will display the fragmentation information. Here I used 3 conditions: Fragmentation >=30 AND PAGES>1000 then rebuild Fragmentation between 15 to 29 AND PAGES>1000 then reorganize & update statistics If the above conditions are false then update the statistics Before you going to run the procedure…
-
-
INDEX DEFRAGMENTATION SCRIPT
INDEX DEFRAGMENTATION SCRIPT for 2008 / 2005 I dedicate this article to my senior DBA “Roshan Joe Joseph” who has helped me to write a script. I have posted the updated INDEX DEFRAGMENTATION script here, which is already published in sqlservercentral (SSC). http://www.sqlservercentral.com/scripts/Automated+index+defragmentation+script/68802/ For VLDB – Please use Ola Hallengren’s script https://ola.hallengren.com/, Which has many parameters. Script changes: · I used while loop instead of a cursor. · I used to rebuild the index if the page level locking is disabled. Msg 2552, Level 16, State 2, Line 1 The index “index name” (partition 1) on table “table name” cannot be reorganized because page level locking is disabled Index…