List out and script all indexes in a database

Its quite common that when there is some upgrade, change or release in application, there are chances that our database objects get modified. It may drop some indexes too hampering performance. I had similar situation in my profession. I had creating multiple indexes to find tune database performance, but then there was an application patch release. All those indexes we manually created were dropped by the application. Soon, there was blocking, deadlocks, long running queries, high cpu and performance became pathetic. Later it was understood that all those custom index we DBAs manually created were mercilessly removed by the application. ūüė•

Continue reading “List out and script all indexes in a database”

Auto Index Maintenance for All Databases in a SQL Server Instance

Problem

A few months back, I had shared a script to automate index rebuild/reorg in a database with fragmentation analysis inplace at http://www.sherbaz.com/2013/06/resumable-auto-index-rebuild-and-reorganize-script/. But that script would perform the maintenance only on a particular database at a time.

Solution

The script is modified and it now performs maintenance on all indexes across all databases in an instance. This again has two scripts.

1. analyze2.sql – Checks fragmentation level on indexes in all databases of the SQL instance and stores the results in tempdb.

2. defrag2.sql – Based on the data stored in tempdb by analyze2.sql, the script will process the maintenance, either Index rebuild or an index reorganize based on the fragmentation and the @minfrag, @maxfrag settings in defrag.sql script. Indexes fragmented below @minfrag in percentage will be excluded. Indexes fragmented between @minfrag % and @maxfrag % will be reorganized. All remaining indexes will be rebuilt.

Scripts are pasted below for your reference. Please let me know if you find some errors, bugs or issues with the code or the method used.

Continue reading “Auto Index Maintenance for All Databases in a SQL Server Instance”

Resumable Auto-Index Rebuild and Reorganize Script

As part of SQL server database maintenance, the indexes have to be defragmented on regular basis by rebuild or reorganize for consistant performance. Most of the time, the index maintenance jobs fail in the middle for large databases due to insufficient storage in log files and the entire rebuild process has to be restarted. This consumes a lot of time and also causes prolonged performance issues.

To tackle this, I have scripts which could resume from where it stopped. Even if the defragmentation(Rebuild/Reorg) fails in the middle due to some reason, it will continue from the table it stopped.

Continue reading “Resumable Auto-Index Rebuild and Reorganize Script”

Code: Find missing indexes

Below query will be helpful in finding all missing indexes in a database. I got this query from one of my friend. I am not sure from where it was actually taken. Since I found this useful, I am just making note on the same.

Automatically Rebuild or Reorg index based on fragmentation

The Attached SQL Query can be safely used for rebuilding or reorganizing all indexes in a particular database based on the fragmentation level.

Note: The script is currently set to perform offline index rebuild since online index rebuild is supported only in Enterprise edition SQL Server. Inorder to change it into online mode, add , ONLINE = ON statement along with

in line 100 in the attached script.

3203_AutoIndexRebuild2005andAbove (Click to download the script)

Continue reading “Automatically Rebuild or Reorg index based on fragmentation”