SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Flexible Index reorganize and rebuild

By Ugur Yorulmaz,

This procedure will find most fragmented and most accessed indexes server wide. and then rebuild or reorganize depending on your parameters.

So it will alter/list only the indexes which you will get the most benefit of fixing defragmentation.

Important Note : This procedure can create extensive IO operations. Please check first in DEV environment and beware that altering indexes can also create locks. That's why please use it with caution in PRD environments. Please try to exclude every unnecessary database name in WHERE clause and try to reduce the load.

  1. Please create the stored procedure
  2. Execute the procedure in debug mode first: EXEC sp_FlexibleReindex @Debug=1  this will only list the indexes sorted by most benefit
  3. Please execute the procedure with the parameters that satisfy your needs

Total article views: 382 | Views in the last 30 days: 42
 
Related Articles
FORUM

create Procedure within Procedure

create Procedure within Procedure

FORUM

Dynamically Creating Indexes

Dynamically Creating Indexes

FORUM

Creating Index on the subcription database tables

Creating Index on the subcription database tables

FORUM

Tips on creating indexes

Tips on creating indexes

FORUM

Create index progress.

How can i know create index progress?

Tags
dynamic sql    
index rebuild    
index reorganize    
indexing    
metadata    
reindex    
 
Contribute