Performance Tuning
How to Drop All Your Indexes – Fast
Sometimes I need to reset stuff during performance training classes. I know some of you teach classes, too, and some of you just like doing crazy stuff.
So here you go, a stored procedure to lose weight fast:
DropIndexes for SQL Server 2016 & Newer
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
CREATE OR ALTER PROCEDURE dbo.DropIndexes @SchemaName NVARCHAR(255) = 'dbo', @TableName NVARCHAR(255) = NULL, @WhatToDrop VARCHAR(10) = 'Everything', @ExceptIndexNames NVARCHAR(MAX) = NULL AS BEGIN SET NOCOUNT ON; SET STATISTICS XML OFF; CREATE TABLE #commands (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Command NVARCHAR(2000)); CREATE TABLE #ExceptIndexNames (IndexName NVARCHAR(1000)); INSERT INTO #ExceptIndexNames(IndexName) SELECT UPPER(LTRIM(RTRIM(value))) FROM string_split(@ExceptIndexNames,','); DECLARE @CurrentCommand NVARCHAR(2000); IF ( UPPER(@WhatToDrop) LIKE 'C%' OR UPPER(@WhatToDrop) LIKE 'E%' ) BEGIN INSERT INTO #commands (Command) SELECT N'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(o.parent_object_id)) + N' DROP CONSTRAINT ' + QUOTENAME(o.name) + N';' FROM sys.objects AS o WHERE o.type IN ('C', 'F', 'UQ') AND SCHEMA_NAME(o.schema_id) = COALESCE(@SchemaName, SCHEMA_NAME(o.schema_id)) COLLATE DATABASE_DEFAULT AND OBJECT_NAME(o.parent_object_id) = COALESCE(@TableName, OBJECT_NAME(o.parent_object_id)) COLLATE DATABASE_DEFAULT AND UPPER(o.name) NOT IN (SELECT IndexName COLLATE DATABASE_DEFAULT FROM #ExceptIndexNames); END; IF ( UPPER(@WhatToDrop) LIKE 'I%' OR UPPER(@WhatToDrop) LIKE 'E%' ) BEGIN INSERT INTO #commands (Command) SELECT 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + t.name + ';' FROM sys.tables t INNER JOIN sys.indexes i ON t.object_id = i.object_id WHERE i.type NOT IN (0, 1, 5) AND SCHEMA_NAME(t.schema_id) = COALESCE(@SchemaName, SCHEMA_NAME(t.schema_id)) COLLATE DATABASE_DEFAULT AND t.name = COALESCE(@TableName, t.name) COLLATE DATABASE_DEFAULT AND UPPER(i.name) NOT IN (SELECT IndexName COLLATE DATABASE_DEFAULT FROM #ExceptIndexNames); INSERT INTO #commands (Command) SELECT 'DROP STATISTICS ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) + '.' + QUOTENAME(s.name) + ';' FROM sys.stats AS s INNER JOIN sys.tables AS t ON s.object_id = t.object_id WHERE NOT EXISTS (SELECT * FROM sys.indexes AS i WHERE i.name = s.name) AND SCHEMA_NAME(t.schema_id) = COALESCE(@SchemaName, SCHEMA_NAME(t.schema_id)) AND t.name = COALESCE(@TableName, t.name) AND OBJECT_NAME(s.object_id) NOT LIKE 'sys%'; END; DECLARE result_cursor CURSOR FOR SELECT Command FROM #commands; OPEN result_cursor; FETCH NEXT FROM result_cursor INTO @CurrentCommand; WHILE @@FETCH_STATUS = 0 BEGIN PRINT @CurrentCommand; EXEC(@CurrentCommand); FETCH NEXT FROM result_cursor INTO @CurrentCommand; END; --end loop --clean up CLOSE result_cursor; DEALLOCATE result_cursor; END; GO |
DropIndexes for SQL Server 2008-2014
This one doesn’t include support for the @ExceptIndexNames parameter because I’m lazy:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
CREATE PROCEDURE [dbo].[DropIndexes] @SchemaName NVARCHAR(255) = 'dbo', @TableName NVARCHAR(255) = NULL, @WhatToDrop VARCHAR(10) = 'Everything' AS BEGIN SET NOCOUNT ON; CREATE TABLE #commands (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Command NVARCHAR(2000)); DECLARE @CurrentCommand NVARCHAR(2000); IF ( UPPER(@WhatToDrop) LIKE 'C%' OR UPPER(@WhatToDrop) LIKE 'E%' ) BEGIN INSERT INTO #commands (Command) SELECT N'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(o.parent_object_id)) + N' DROP CONSTRAINT ' + QUOTENAME(o.name) + N';' FROM sys.objects AS o WHERE o.type IN ('C', 'F', 'UQ') AND SCHEMA_NAME(o.schema_id) = COALESCE(@SchemaName, SCHEMA_NAME(o.schema_id)) AND OBJECT_NAME(o.parent_object_id) = COALESCE(@TableName, OBJECT_NAME(o.parent_object_id)) END; IF ( UPPER(@WhatToDrop) LIKE 'I%' OR UPPER(@WhatToDrop) LIKE 'E%' ) BEGIN INSERT INTO #commands (Command) SELECT 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + t.name + ';' FROM sys.tables t INNER JOIN sys.indexes i ON t.object_id = i.object_id WHERE i.type = 2 AND SCHEMA_NAME(t.schema_id) = COALESCE(@SchemaName, SCHEMA_NAME(t.schema_id)) AND t.name = COALESCE(@TableName, t.name) INSERT INTO #commands (Command) SELECT 'DROP STATISTICS ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) + '.' + QUOTENAME(s.name) + ';' FROM sys.stats AS s INNER JOIN sys.tables AS t ON s.object_id = t.object_id WHERE NOT EXISTS (SELECT * FROM sys.indexes AS i WHERE i.name = s.name) AND SCHEMA_NAME(t.schema_id) = COALESCE(@SchemaName, SCHEMA_NAME(t.schema_id)) AND t.name = COALESCE(@TableName, t.name) AND OBJECT_NAME(s.object_id) NOT LIKE 'sys%'; END; DECLARE result_cursor CURSOR FOR SELECT Command FROM #commands; OPEN result_cursor; FETCH NEXT FROM result_cursor INTO @CurrentCommand; WHILE @@FETCH_STATUS = 0 BEGIN PRINT @CurrentCommand; EXEC(@CurrentCommand); FETCH NEXT FROM result_cursor INTO @CurrentCommand; END; --end loop --clean up CLOSE result_cursor; DEALLOCATE result_cursor; END; GO |
Look, you’re the one who reads this blog, alright? Don’t blame me for the fact that you’re – STOP COPYING THAT, AND NO, DO NOT PASTE THAT INTO PRODUCTION.
No, this one’s not going into the First Responder Kit.
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
While I agree that this shouldn’t be in the First Responder Kit, I can see a project in the works to the tune of “Training Helper Kit” maybe.
If only losing weight this fast worked this well in real life…
A Refractor one,
CREATE PROCEDURE [dbo].[DropIndexes] AS
SET NOCOUNT ON
DECLARE @AllIndex varchar(max),@AllStatistics varchar(max)
SELECT @AllIndex=DBO.GROUP_CONCAT_D(‘DROP INDEX [‘ + i.name + ‘] ON [‘ + s.name + ‘].[‘ + t.name + ‘]’,’;’)
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE i.type = 2
SELECT @AllStatistics=DBO.GROUP_CONCAT_D(‘DROP STATISTICS ‘ + SCHEMA_NAME([t].[schema_id]) + ‘.’ + OBJECT_NAME([s].[object_id]) + ‘.’ + s.[name],’;’)
FROM sys.[stats] AS [s]
JOIN sys.[tables] AS [t]
ON s.[object_id] = t.[object_id]
WHERE [s].[name] LIKE ‘[_]WA[_]Sys[_]%’
AND OBJECT_NAME([s].[object_id]) NOT LIKE ‘sys%’
—-With out CLR
–SELECT @AllIndex=(select
— stuff((select ‘;’+’DROP INDEX [‘ + i.name + ‘] ON [‘ + s.name + ‘].[‘ + t.name + ‘]’
–FROM sys.tables t
–INNER JOIN sys.indexes i ON t.object_id = i.object_id
–INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
–WHERE i.type = 2
–for xml path(”), root(‘MyString’), type ).value(‘/MyString[1]’,’varchar(max)’)
— , 1, 2, ”))
–SELECT @AllStatistics=(select
— stuff((select ‘;’+’DROP STATISTICS ‘ + SCHEMA_NAME([t].[schema_id]) + ‘.’ + OBJECT_NAME([s].[object_id]) + ‘.’ + s.[name]
–FROM sys.[stats] AS [s]
–JOIN sys.[tables] AS [t]
–ON s.[object_id] = t.[object_id]
–WHERE [s].[name] LIKE ‘[_]WA[_]Sys[_]%’
–AND OBJECT_NAME([s].[object_id]) NOT LIKE ‘sys%’
–for xml path(”), root(‘MyString’), type ).value(‘/MyString[1]’,’varchar(max)’)
— , 1, 2, ”))
select @AllIndex,@AllStatistics
–EXEC (@AllIndex)
–EXEC (@AllStatistics)
GO
What’s “DBO.GROUP_CONCAT_D”?
https://groupconcat.codeplex.com/
Forgot to post the link, and unfortunately codeplex is going to shutdown
With lots of indexes in the database, the length of @AllIndex don’t enough to use in this case.
I was about to try that on a nice, busy production server … but I saw the cursor just in time… 🙂
We need a rocket telling Groot “Don’t press this button” DBA Reaction GIF for this.
“Here’s a drop all indexes button. Don’t press it.” “I am Mongo!” “NOOOOOOOOOO!!!!!!”
Yeah, you know, you really should have read the whole post because the warnings… The warnings come *after* the script. 😉
Nice reference to Dr. Strange!
I tried this on a database I was going to drop anyway (TEST SERVER!) and got this message: Msg 3723, Level 16, State 4, Line 8
An explicit DROP INDEX is not allowed on index ‘dbo.’. It is being used for PRIMARY KEY constraint enforcement. So it isn’t “set it and forget it” after all.
Craig – yeah, I only use clustered primary keys. It sounds like you’re using nonclustered primary keys.
No kidding, I built almost the exact same thing this week. If only this had come out Monday…
I still wouldn’t have used the cursor, though. It’s like 2017 already…
Don’t use a cursor. SELECT all the commands to the output, let the guy copy and paste into the executable window and take responsibility for himself.
Ross – I’m running it MYSELF to clear out my indexes during demos. There is no other guy.
Here’s a thought: if you want to “reset stuff” wouldn’t RESTORE DATABASE… have done the job?
Dave – yes, but students don’t generally want to sit around watching a progress bar for several minutes. 😉
would be nice to have a parameter where you can provide a table name and only the indexes of that table would be dropped
Marco – hahaha, funny you mention that! I actually updated it for our Mastering Query Tuning classes and I forgot to update this blog post. It’s now updated. Enjoy!
Brent — I’m just wondering if there’s an advantage to executing each statement individually as opposed to generating a script to do everything and then execute it. This approach also has the benefit of eliminating the cursor and could be modified to be run in a TRY-CATCH within a transaction:
CREATE PROCEDURE [dbo].[DropIndexes]
@SchemaName NVARCHAR(255) = NULL,
@TableName NVARCHAR(255) = NULL
AS BEGIN
SET NOCOUNT ON;
DECLARE @CurrentCommand NVARCHAR(MAX);
SELECT @CurrentCommand = ISNULL(@CurrentCommand + NCHAR(10), N”) +
N’DROP INDEX [‘ + i.name + N’] ON [‘ + s.name + N’].[‘ + t.name + N’];’
FROM sys.tables t
INNER JOIN sys.indexes i
ON i.object_id = t.object_id
AND i.type = 2
INNER JOIN sys.schemas s
ON s.schema_id = t.schema_id
AND s.name = COALESCE(@SchemaName, s.name)
WHERE t.name = COALESCE(@TableName, t.name);
SELECT @CurrentCommand = ISNULL(@CurrentCommand + NCHAR(10), N”) +
N’DROP STATISTICS ‘ + SCHEMA_NAME(t.schema_id) + N’.’ +
OBJECT_NAME(s.object_id) + N’.’ + s.name
FROM sys.stats s
INNER JOIN sys.tables t
ON t.object_id = s.object_id
WHERE s.name LIKE ‘[_]WA[_]Sys[_]%’
AND OBJECT_NAME(s.object_id) NOT LIKE ‘sys%’;
EXEC(@CurrentCommand);
END
I attended a SQL Saturday session by Erland Sommarskog titled “Don’t Use Cursors! Or why you should”. Based on that and reviewing this I think this is a perfectly good case for a cursor: its a utility script not intended to be run repeatedly through out the day it may be run “once in a while”. There are two improvement suggestions: 1) use CURSOR STATIC LOCAL 2) there’s an anti-pattern in the While loop where you FETCH NEXT 2x. This can be done with a single fetch next using (note the single FETCH NEXT below):
DECLARE result_cursor CURSOR STATIC LOCAL FOR
SELECT Command FROM #commands;
OPEN result_cursor;
WHILE 1=1
BEGIN
FETCH NEXT FROM result_cursor INTO @CurrentCommand;
IF @@FETCH_STATUS 0
BREAK
PRINT @CurrentCommand;
EXEC(@CurrentCommand);
END;
Jeffrey – are you really focusing on performance tuning a cursor that drops your indexes?
Someone else stated “you should not use a cursor” you being YOU Mr. Ozar. I was defending your use of a cursor, fine sir!
HA! Okay.
Running this procedure causes the following error message:
Msg 468, Level 16, State 9, Procedure dbo.DropIndexes, Line 22 [Batch Start Line 0]
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_100_CI_AS_KS_WS_SC” in the equal to operation.
That means you’ve got different system database & user database collations.
Is there a way to run this without removing table contraints?
Thank you.
That would require additional code on your part.