Recently, I came across a fantastic T-SQL script that offers an additional feature for changing the recovery mode while enabling and disabling indexes. What impressed me most about this script is its ability to change the SQL Server recovery mode to Simple during index operations, significantly speeding up the process. This stored procedure is self-documented, so I won’t elaborate much on its details. Let us learn how to disable and enable indexes efficiently. Let us learn about disabling indexes.
Stored Procedure USP_DisableEnableNonClusteredIndexes can be downloaded from here.
Execute SP using the following script.
-- sample to Execute EXEC USP_DisableEnableNonClusteredIndexes 'ADVENTUREWORKS', 'N',1 -- DISABLE EXEC USP_DisableEnableNonClusteredIndexes 'ADVENTUREWORKS', 'N',2 -- REBUILD/ENABLE with out Changing Recovery model. EXEC USP_DisableEnableNonClusteredIndexes 'ADVENTUREWORKS', 'Y',2 -- REBUILD/ENABLE with Change Recovery model. -- or EXEC USP_DisableEnableNonClusteredIndexes 'ADVENTUREWORKS',NULL, 1 -- DISABLE EXEC USP_DisableEnableNonClusteredIndexes 'ADVENTUREWORKS', NULL,2 -- REBUILD/ENABLE with out Changing Recovery model.
Here are few additional blog posts related to index, which you should read about:
Introduction to Force Index Query Hints – Index Hint
For this article, I will start with a disclaimer instead of having it at the end of the article.
“SQL Server query optimizer selects the best execution plan for a query, it is recommended to use query hints by experienced developers and database administrators in case of special circumstances.”
SQL SERVER – Introduction to Force Index Query Hints – Index Hint – Part2
I just received an email from one of my regular readers that, are there any other methods for the same as it will be difficult to read the syntax of the joint.Yes, there is an alternate way to do the same using OPTION clause, however, as an OPTION clause is specified at the end of the query we have to specify which table the index hint is put on.
Reference: Pinal Dave (https://blog.sqlauthority.com)
13 Comments. Leave new
What would be the ramifications of changing the recovery model of your database?
You would have a service level agreement with the business on a recovery strategy and changing your recovery model during maintenance tasks probably is not part of this agreement. Plus, how do you recover if the database goes down after you have switched the recovery model back and forth from full or bulk logged to simple and then back again?
Are you taking a full backup once the database is in simple mode – or what exactly would be your suggested approach of keeping integrity within the database and backups of the database in sync with what you are doing as well as keeping to the contract of your SLA?
I see this as a fun thing to do on a database that has no value such as adventureworks, but on a production database – processing many transactions per minute – with a service level agreement in place on a recovery strategy this is not going to sit well with the business that is paying for your services.
Good idea, but what about breaking the backup log chain.
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms178052(v=sql.90)
@Simon Worth
I appreciate your response for the above article. I am not a genius. I love when experts like you comment on my work. I thank you for the same.
I will not defend myself, but would like to bring two points to your notice,
1. Script by default will not change recovery model, unless user specify an option to change it. It is just an added option.
2. This is only for Non-Clustered Index (excluding one created on Primary Keys and Unique Keys). Script was created to reduce execution time for huge data loads.
@Jason Crider
Thanks for comment, I completely agree with you.
I could have added Fill Factor as a parameter when rebuilding indexes, I could have used case function and removed many IF statements.
Regards,
IM.
good
it’s not working on my adventureworks database
when i try to use is says invalid object name sys.databases
Hello Emeka,
Please make sure that you have required permission to see the result of sys.databases catalog view. According to BOL:
“If the caller of sys.databases is not the owner of the database and the database is not master or tempdb, the minimum permissions required to see the corresponding row are ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database.”
Regards,
Pinal Dave
Its Awesome.
Hello,
great script. Just one question:
Inside script, why don’t check the value on “is_unique” field in sys.indexes too?
…WHERE I.[INDEX_ID] > 1
AND I.[TYPE] = 2
AND I.[IS_PRIMARY_KEY] 1
AND I.[IS_UNIQUE_CONSTRAINT] 1
AND I.[INDEX_ID] = SI.INDID’
Should be:
…WHERE I.[INDEX_ID] > 1
AND I.[TYPE] = 2
AND I.[IS_PRIMARY_KEY] 1
AND I.[IS_UNIQUE_CONSTRAINT] 1
AND I.[INDEX_ID] = SI.INDID’
AND I.[IS_UNIQUE] 1
TIA
David
Hi all
Please solve my prob anybody it is urgent
here i am giving some data. i want to delete duplicate records from table
these are all duplicate records with clnum,linenum,status from this data i have to consider only latest “date” records and “id” column is also unique and i have to delete old dated records.
clnum linenum status date id
1 3 11 2011-02-24 9092
1 3 11 2011-03-22 16235
2 4 11 2011-03-22 16290
2 4 11 2011-02-24 11572
3 2 11 2011-03-22 16222
2 4 11 2011-02-24 11572
for ex from the above data i have to consider only
clnum linenum status date id
1 3 11 2011-03-22 16235
2 4 11 2011-03-22 16290
3 2 11 2011-03-22 16222
and i have to delete
clnum linenum status date id
1 3 11 2011-02-24 9092
2 4 11 2011-02-24 11572
2 4 11 2011-02-24 11572
Please help me it’s urgent
This selects what you need
select clnum, linenum ,status ,max(date) as date from table
group by clnum, linenum ,status
Hi Pinal.
This is a great script, but is there a revised version that includes tablename so that only the non-clustered indexes on a specific table are disabled/rebuilt?
Thanks.
Thanks Pinal, helpful as always. I ran into 2 issues with your stored proc in my environment. First, to exclude indexes on system tables (for replication) I added another condition to the WHERE in the dynamic SQL – AND T.is_ms_shipped = 0. Second, my database has tables with the same name but in different schemas, so I had to add an additional condition to the JOIN to Information_Schema.Tables – AND SC.TABLE_SCHEMA = SCHEMA_NAME(T.Schema_ID). The final version works as intended. Here is the full updated version of the SELECT.
SET @SQL1 = ‘USE ‘+@DB_NAME+ ‘
SELECT IDENT = IDENTITY (INT,1,1)
,SC.[TABLE_SCHEMA]+”.”+SC.[TABLE_NAME] [FULLOBJECTNAME]
,SI.[NAME] [INDEXNAME]
INTO ##STOREINDEXINFORMATION
FROM SYS.INDEXES I JOIN SYS.TABLES T ON I.[OBJECT_ID] = T.[OBJECT_ID] JOIN SYSINDEXES SI ON SI.ID = T.[OBJECT_ID] JOIN INFORMATION_SCHEMA.TABLES SC ON SC.TABLE_NAME = OBJECT_NAME (T.[OBJECT_ID]) AND SC.TABLE_SCHEMA = SCHEMA_NAME(T.Schema_ID)
WHERE I.[INDEX_ID] > 1
AND I.[TYPE] = 2
AND I.[IS_PRIMARY_KEY] 1
AND I.[IS_UNIQUE_CONSTRAINT] 1
AND I.[INDEX_ID] = SI.INDID
AND T.is_ms_shipped = 0’
This is excellent. I will update the original blog post with due credit to you and also will create a new blog post where I link to the blog post.
Many thanks,