February 16, 2016 at 7:35 am
Hi
I am looking to amend a fact table build job such that it disables indexes, builds the table then re-enables the indexes (all non-clustered)
I have T-SQL code to perform the task that works:
-- Disable indexes
use xxx
go
DECLARE @sql AS VARCHAR(MAX);
SET @sql = '';
SELECT
@sql = @sql + 'ALTER INDEX [' + i.name + '] ON [' + o.name + '] DISABLE; '
FROM sys.indexes AS i
JOIN sys.objects AS o ON i.object_id = o.object_id
WHERE i.type_desc = 'NONCLUSTERED'
AND o.type_desc = 'USER_TABLE'
and o.name =
EXEC (@sql)
* Build Table *
-- Enable indexes
use xxx
go
DECLARE @sql AS VARCHAR(MAX);
SET @sql = '';
SELECT
@sql = @sql + 'ALTER INDEX [' + i.name + '] ON [' + o.name + '] REBUILD; '
FROM sys.indexes AS i
JOIN sys.objects AS o ON i.object_id = o.object_id
WHERE i.type_desc = 'NONCLUSTERED'
AND o.type_desc = 'USER_TABLE'
and o.name =
EXEC (@sql);
Now, I want to wrap this up as a stored procedure that's called passing the database and table
For that I have (and I'll just post the disable code as enable is pretty similar)
create procedure DisableNonClusteredIndexes
@dbName varchar(100), @tableName varchar(100)
as
set nocount on;
declare @var as varchar(max)
set @var = 'DECLARE @sql AS VARCHAR(MAX), @tableName varchar(100);
SET @sql = '''';
SET @tableName = ''' + @tableName + '''
SELECT @sql = @sql + ''ALTER INDEX ['' + i.name + ''] ON ['' + o.name + ''] DISABLE; ''
FROM sys.indexes AS i
JOIN sys.objects AS o ON i.object_id = o.object_id
WHERE i.type_desc = ''NONCLUSTERED''
AND o.type_desc = ''USER_TABLE''
and o.name = @tableName';
Exec('Use '+ @dbName + ';' + @var)
I call using exec DisableNonClusteredIndexes [database],
To check, I'm using this code and looking at is_disabled:
select * from [database].sys.indexes
where object_id = (select object_id from [database].sys.objects where name =
)
It just doesn't seem to do anything and I can't work out why (is_disabled stays as false)
Feel like I've got a missing ; or go or ???
I've used this method before (use a DB and running a statement against it)
Again, this works if I don't wrap it up as an sp
Any thoughts?
Thanks
- Damian
February 16, 2016 at 8:17 am
1) What's the purpose of this evolution? I presume you modify a sufficient fraction of data each day that it is worth it to do this? Personally I just prefer to drop and recreate but that could be an old-school preference. Do make sure ALL of the optional indexing parameters (fillfactor, sort_in_tempdb, etc) are set appropriately since your method won't do that for you.
2) When building dynamic sql I ALWAYS use SET QUOTED_IDENFITIER OFF so I can use outer double quoted and then NEVER have to worry about how many stupid single quotes to use when constructing my string(s).
3) Also when doing this I ALWAYS first print out my queries that are built to make sure something isn't hosed up. Did you do that in the sproc first?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 16, 2016 at 8:19 am
Yes. Just because an index is of the non-Clustered variety, doesn't mean it can simply be disabled without other considerations. For example, if the non-Clustered index is UNIQUE, there's a very strong chance that it's a part of DRI and you must check for which FKs are pointed to it because they'll need to be "reenabled with a CHECK", as well. Disabling such a UNIQUE index may also allow for dupes to creep into the table during the time that it's disabled.
--Jeff Moden
February 16, 2016 at 9:06 am
Thanks for the responses Kevin and Jeff
1) The real purpose is improving build times
I have a small number of aggregated fact tables that build on a flush and fill basis, daily
Drop and rebuilding indexes save around 40 minutes on a morning
I could drop and recreate and that's what I was originally going to do, then I came across this disable/rebuild method on a couple of sites
If I was to create code that drops and recreate, how do I maintain the list of indexes and their structure dynamically i.e. what happens if a new index is added
2) Take your point regarding quoted identifier - overlooked that one
3) Yes, I've used print to view the dynamic code and a simple copy and paste works
In this instance, I do not have any (non-clustered) unique indexes, it's an aggregated fact table
I do take your point regarding uniqueness and it would make sense to leave the index intact, in that instance
Thanks
- Damian
February 16, 2016 at 9:13 am
Adding a new index REALLY should be part of a controlled, documented process, and source controlled too. And as part of that the impact analysis will make sure that your build sproc gets updated as well.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 16, 2016 at 1:13 pm
These are unlikely, but let's:
1) add brackets around the db name, just in case.
2) specify a 'dbo' schema for the table name, just in case. It's easy to accidentally get tables in a user schema by mistake.
Also, if you want use the QUOTED_IDENTIFIER OFF "trick" to build a string, use it ONLY to build the string. Then revert to QUOTED_IDENTIFIER ON before running any DDL or DML. It's way too dangerous to have that setting off when working with SQL objects.
Finally, for the record, let me state that the shown method of concatenation is not guaranteed to work, although I've only once or twice seen it fail. You could use the XML method, or a quick local, fast_forward cursor and concat the values yourself.
CREATE PROCEDURE DisableNonClusteredIndexes
@dbName varchar(100),
@tableName varchar(100)
AS
SET NOCOUNT ON;
DECLARE @var AS varchar(max)
SET @var = '
DECLARE @sql AS VARCHAR(MAX), @tableName varchar(100);
SET @sql = '''';
SET @tableName = ''' + @tableName + '''
SELECT @sql = @sql + ''ALTER INDEX ['' + i.name + ''] ON ['' + o.name + ''] DISABLE; ''
FROM sys.indexes AS i
JOIN sys.objects AS o ON i.object_id = o.object_id
WHERE i.type_desc = ''NONCLUSTERED''
AND o.type_desc = ''USER_TABLE''
AND o.name = @tableName
AND o.schema_id = SCHEMA_ID(''dbo'')
';
EXEC('Use ['+ @dbName + ']; ' + @var)
Edit: Changed "specify a 'dbo' for" to "specify a 'dbo' schema for".
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply