I am not sure, how to name this topic. May be, start by following questions. How important and effective is clustered index for a table? How to solve the heap fragmentation? Why my query is running slow most of the time for this particular table? Are the primary key and clustered index are same? There are developers still they understand that, we must need a primary key to create a clustered index. No, that’s not. Both are different used for different purpose. The primary key is to enforce the business logic. I.e. enforce the uniqueness. By default it will create a clustered index, if we are not…
-
-
INDEX DEFRAGMENTATION SCRIPT for SQL server 2000
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…