Skip to main content

Smart Re-index

Smart Re-index

Def: Smart re-index keeps the database available unlike normal re-index and the type of re-index(rebuild or re-organization) will be decided based on the fragmentation percentage. 

To set up smart reindex, we need to check the fragmentation level of the database and decide whether it should be index rebuild or reorganizing.

If fragmentation percentage <30 then reorganize else rebuild

After the reindex is performed, we update statistics for that database. 

Here's the git link for smart re-index


Above mentioned is one of the ways to configure smart re-index. A job can be scheduled to run the above code and reports can also be configured to check the fragmentation percentage.
Labels:

Comments

Post a Comment

Popular posts from this blog

Tail Log Backups

Tail log backups capture the tail of the log even if the database is offline, damaged, or missing data files. This might cause incomplete metadata from the restore information commands and msdb. However, only the metadata is incomplete; the captured log is complete and usable. If a tail-log backup has incomplete metadata, in the backupset table, has_incomplete_metadata is set to 1. Also, in the output of RESTORE HEADERONLY, HasIncompleteMetadata is set to 1. To create a tail log backup, use below script BACKUP LOG [DB Name]    TO [TLog_Device_Location]      WITH NO_TRUNCATE;   GO Scenarios That Require a Tail-Log Backup If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. To avoid an error for an online database, you must use the ... WITH NORECOVERY option of the BACKUP Transact-SQL statement. If a database is offline and fails to start and you need to res...
Post a Comment
Read more

SQL Server

                                                                     SQL Server  Its been a while that I have updated my blog. Though Databases and SQL was something that I used to stay away as much as possible because for some reasons, I got to work on these all the day for 8-9 hours and sometimes haunting in the nights and weekends as well. However, it has been a good journey so far and I found some interesting stuff in SQL Server. So, this triggered an idea a couple of weeks back to share some cool stuff that I am learning in SQL Server over numerous sources. Hope I can make this a good series. As there are not much visitors to my blog, le...
4 comments
Read more