SQL Server Disable Indexes and Rebuild Indexes dynamically

To generate the ALTER INDEX..DISABLE  and  the ALTER INDEX..REBUILD statements for all nonclustered indexes for a single table use the following sql statements. 

Copy and Paste the statements , execute on the database. I’ve included the URL column  for easy click through to some notes on the code

1) Generate the ALTER INDEX..DISABLE statements

SELECT  
'ALTER INDEX [' +  sys.indexes.name + '] ON [' + sys.objects.name + '] DISABLE;' +CHAR(13)+CHAR(10) as EXECUTE_STATEMENT,
'http://www.sqlserver-dba.com/2012/01/sql-server-disable-index.html' as URL

FROM    sys.indexes         JOIN sys.objects ON sys.indexes.object_id = sys.objects.object_id 
WHERE   sys.indexes.type_desc = 'NONCLUSTERED'   
AND sys.objects.type_desc = 'USER_TABLE' 
AND sys.objects.name = 'MyTable'

 

2) Generate the ALTER INDEX..REBUILD statements

 

SELECT  
'ALTER INDEX [' +  sys.indexes.name + '] ON ' + sys.objects.name + ' REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
;' +CHAR(13)+CHAR(10) as EXECUTE_STATEMENT,
'http://www.sqlserver-dba.com/2012/01/sql-server-rebuild-index.html'> as URL

FROM    sys.indexes         JOIN sys.objects ON sys.indexes.object_id = sys.objects.object_id 
WHERE   sys.indexes.type_desc = 'NONCLUSTERED'   
AND sys.objects.type_desc = 'USER_TABLE' 
AND sys.objects.name = 'MyTable'

 Related Posts

SQL SERVER REBUILD INDEX

SQL SERVER DISABLE INDEX

SQL SERVER REBUILD INDEX

Use the script below to rebuild an index on a table

ALTER INDEX   MY_INDEX_NAME on MY_TABLE_NAME  REBUILD  ; 

 Check SQL Server BOL for a the full list of options

 Notes

1)  Issuing an Index Rebuild request , drops and recreates the index

2) If  rebuilding a  CLUSTERED INDEX ,  nonclustered indexes won’t be rebuilt unless ALL keyword is used.

3) If no options are added , the statement inherits the options in sys.indexes

Why rebuild an index?

1)       Before rebuilding an index – ask the question “is the gain worth the cost of rebuilding the index?”  Rebuilding an index  can be a costly exercise , particularly on very large indexes.

2)       Strategy for large imports of data .  A typical strategy is disable NONCLUSTERED INDEXES , import the data and  rebuild the NONCLUSTERED INDEXES

3)       Index troubleshooting

4)     Index disable is issued  and the index needs to be enabled

Related Posts:

Disable Index and Rebuild Index for Updates on very large tables

SQL SERVER DISABLE INDEX

 

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL SERVER DISABLE INDEX

Use the script below to disable an index on a SQL Server table

ALTER INDEX MY_INDEX_NAME on MY_TABLE_NAME  DISABLE; 

 

Executing the code will  prevent access to the index. 

If it’s a CLUSTERED INDEX , the data remains intact on the table , but no DML can access the data. Drop the index or rebuild to enable  DML .  Disabling the CLUSTERED INDEX will also disable all NONCLSTERED INDEXES referenced to the table.

If it’s a NONCLUSTERED INDEX, the data is physically deleted.  The index definition  details are retained in the meta data.

 Why disable an index?

1)       Strategy for large imports of data .  A typical strategy is disable NONCLUSTERED INDEXES , import the data and  rebuild the NONCLUSTERED INDEXES

2)       Index troubleshooting

3)       Disk constraints in  Index Rebuild. Less disk space is required to commit an Index Rebuild

Related Posts:

Disable Index and Rebuild Index for Updates on very large tables

ALTER INDEX permissions

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Index Fragmentation and sys.dm_db_index_physical_stats

Fragmentation of an index can severely affect performance. When logical ordering of the key within a page does not match the physical ordering within the data file, fragmentation exists.

I execute index maintenance scripts  for databases on a regular basis.  If I’m executing a  custom job, such as a large UPDATE , I may need to analyse just one index on a single table.

Use the SQL views: sys.sysdatabases,  sys.sysobjects,  sys.sysindexes  to find  , all the relevant ids.  Push the ids as values on the sys.dm_db_index_physical_stats view.

sys.dm_db_index_physical_stats present fragmentation information for data and indexes.

 

USE MyDB
GO
DECLARE @ixId INT,@dbase_id INT,@table_id INT
SELECT @dbase_id=dbid FROM sys.sysdatabases  WHERE name = 'MyDB'
SELECT @table_id=id FROM sys.sysobjects WHERE name = 'MyTable' AND xtype = 'U'
SELECT @ixId=indid FROM sys.sysindexes WHERE id=OBJECT_ID('dbo.MyTable') and [name] = 'MyIndex'

SELECT * FROM sys.dm_db_index_physical_stats(@dbase_id,@table_id,@ixId,NULL,'LIMITED')

 Related Posts

Top 5 SQL Server DMV for Index Analysis

SQL Server non-clustered index checklist

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Top 10 SQL Server Posts for 2011

2011 is nearly over. It’s time to list the Top 10 viewed SQLServer-DBA.com posts for the year , based on Views.

 

1. System Reserved Partition and monitoring
2. Data Loading Performance Guide
3. Mobile Commerce and SQL performance Tuning
4. Index Disabled and Index Rebuild
5. How to report a SQL Server performance problem
6. Attach database without log file and rename database
7. Define IOPs for all database Servers
8. RAID server configuration and disk amounts
9. Calculate disk IO throughput and MB per second
10. Window Perfmon scripting, SQL Server perfmon and how to perfmon

Related Posts

Top 10 DB2 posts for 2011

Author: Jack Vamvas (http://www.sqlserver-dba.com)