Right here is first article in regards to what I hope are a series on leveraging the rich and varied metadata for us in SQL Server for automating common operations.
Little one working with data in SQL Server when temporarily disabling indexes may bring about much better performance. Two prime instances of this would be:
performing purges of historical data from large tables
bulk insertion of strategy.
The first one painless to witness. All amazing do is look for a large table sporting a number of indexes about it, write an effortless “DELETE TOP (100000) FROM table” statement, and grab the estimated execution plan from Management Studio.
If we actually run the delete, we’ll realize the amount of IO is rather high, and so will be the number of log records generated. Thus, often times, it is advantageous to first disable just about the clustered index (really don’t want to do that, since “Disabling a clustered index for a table prevents access for the data”).
Unfortunately a popular pattern I’ve come across in code is that often someone has scripted out individual drop statements skin color indexes on just the table and pasted it into a related block of code (maybe a purge procedure). Intensive . is wrong varied reasons, the main being that the code is expensive to maintain. Whenever you need an index is added, removed, or modified, the code require to be updated. Not surprisingly this can get tiresome, you will discover this operation need to be done in multiple places.
An infinitely more flexible and resilient approach utilizing the rich metadata exposed by SQL Server’s system views to auto-generate commands either to disable or re-enable indexes over particular table. This template driven approach can certainly be generic enough used anywhere, that the code is written once and used repeatedly. At the end I’ll connect to a complete stored procedure and these can be used for this very purpose, but first let’s walk through some of the steps involved, and even some tips.
First, what’s determine which indexes should really be disabled? Given and schema and table name, nevertheless this is rather trivial. All amazing do is join a few system views together, specifically, sys.schemas, sys.objects, and sys.indexes.
SELECT
ssch.name AS schema_name,
sobj.name AS object_name,
sidx.name AS index_name
FROM
sys.schemas ssch join sys.objects sobj
on ssch.schema_id = sobj.schema_id
and ssch.name = ‘dbo’
and (sobj.name = ‘some table’)
join sys.indexes sidx
on sobj.object_id = sidx.object_id;
To exclude the clustered index, we merely add a filter into the join on sys.indexes for index_id > 1, since an index_id of 1 indicates a clustered index (an index_id of 0 would indicate a heap, which we obviously cannot disable).
All of us want to exclude the main key, and additionally (at least like an option) any unique indexes. You can easlily do this by excluding rows while the sys.indexes.is_primary_key column equals 1, or where your sys.indexes.is_unique column equals 1.
on sobj.object_id = sidx.object_id
and sidx.is_primary_key = 0 — exclude primary keys
and sidx.is_unique = 0
Furthermore, we can enable the user to see if they would you like to disable the unique indexes are not.
and sidx.is_unique = case when @i_Exclude_Unique_Fl = 1 then 0 else sidx.is_unique end — exclude unique indexes
Now, we find a parameter so that we recognise if we’re working to enable or disable indexes. It becomes foolish to rebuild indexes which we’ve not disabled, nor would you want to attempt to disable indexes which may be, well, already disabled! Again, we’re envious this as flexible and resilient as feasible. Fortunately this can be easy enough, because “is_disabled” column on the sys.indexes view.
and sidx.is_disabled = case @i_EnableDisable_Fl
when ‘E’ then 1 — only include disabled indexes whenever the “Enable” option is set
when ‘D’ then 0 — only include enabled indexes in the event the “Disable” option is set
END
Finally, we desire to add two nice-to-have features: first, we should give the chance to use online index rebuilds when the engine edition supports it (i.e. we’re using SQL Developer Edition or Enterprise Edition), we want to be capable to specify a maximum degree of parallelism for ones rebuild operation.
Putting human body . together, and therefore we can generate the actual ALTER INDEX statements easily, and either print them out or actually execute them (a not-for-real mode could be an often overlooked but critical feature in my opinion).
The actual SQL that puts a lot of these pieces together compares with this:
SELECT
‘RAISERROR(‘
+ QUOTENAME(CASE @i_EnableDisable_Fl
WHEN ‘E’ THEN ‘Enabling ‘
ELSE ‘Disabling ‘
END + ‘ index ‘ + sidx.name + ‘ on table ‘ + ssch.name + ‘.’ + sobj.name,
””) + ‘,10,1) with nowait;’
+’ALTER INDEX ‘
+ quotename(sidx.name)
+ ‘ ON ‘
+ quotename(ssch.name)
+ ‘.’ + quotename(sobj.name)
+ ‘ ‘
+ case @i_EnableDisable_Fl
when ‘E’ then ‘REBUILD’ + ‘ WITH (MAXDOP=’+CASE
WHEN @i_MaxDOP IS NULL THEN convert(varchar,1)
ELSE convert(varchar,@i_MaxDOP)
END +
‘,ONLINE=’ + CASE
WHEN (@i_Online = 1 AND SERVERPROPERTY(‘EngineEdition’) = 3)
THEN ‘ON’
ELSE ‘OFF’
END +
‘)’
when ‘D’ then ‘DISABLE’
END
+ ‘;’
FROM
sys.schemas ssch join sys.objects sobj
on ssch.schema_id = sobj.schema_id
and ssch.name = @i_Schema_Name
and (sobj.name = @i_Table_Name or @i_Table_Name = ”)
join sys.indexes sidx
on sobj.object_id = sidx.object_id
and sidx.is_primary_key = 0 — exclude primary keys
and sidx.is_unique = case when @i_Exclude_Unique_Fl = 1 then 0 else sidx.is_unique end — exclude unique indexes
and sidx.index_id > 1 — exclude clustered index and heap
and sidx.is_disabled = case @i_EnableDisable_Fl
when ‘E’ then 1 — only include disabled indexes in the event the “Enable” option is set
when ‘D’ then 0 — only include enabled indexes if the “Disable” option is set
END
Because generally discover do offers like bulk deletes by filtering on the column, we strive to be able to specify that indexes having that column being the leading key may not be disabled, thereby inducing the purge operation to require a painful scan on the table.
WHERE
NOT EXISTS (
SELECT 1
FROM sys.index_columns ic JOIN sys.columns c
ON c.column_id = ic.column_id
AND c.object_id = ic.object_id
WHERE ic.index_id = sidx.index_id
AND ic.object_id = sidx.object_id
AND c.name = @i_Column_To_Exclude_Nm
AND ic.key_ordinal = 1
AND ic.is_included_column = 0
With the nifty trick of your FOR XML PATH clause, we concatenate all the statements into one row, which we are then either use or execute when we like.
Put all of these together, and now we now have a flexible type of, re-usable routine that will be defined in a and can be called everywhere you go. We can even mark it as a system object within ‘master’ database. Thereby allowing it to be called in the context of a typical database.
One word of caution though: don’t take such this operation lightly. Rebuilding indexes is a really costly amount of processing, and definately will always be tested before including practically batch process. There could well be occasions the cost of rebuilding indexes will never outweigh won’t come cheap . maintaining them during bulk operations; caveat emptor is great advice web page ..