Chris Harshman - Monday, January 16, 2017 9:27 AM

DamianC - Monday, January 16, 2017 3:44 AM

Hi

On sunday morning I had an overnight issue with a late ETL build
Not necessarily an issue with lateness as sunday is basically a free day
It is the day we run some key maintenance tasks

One task that runs is update statistics in full

Part of our overnight job builds quite sizable fact tables
It builds by disabling indexes; build the table; re-enable indexes

This disables:

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 = 'F_CLAIM_TRANSACTION'

EXEC (@sql)

The code above failed due to deadlock priorities
Here, it looks to have prioritised the statistics update
I've obtained this from the SQL log generated (Trace 1222 is switched on)

So, I'm now thinking, am I doing this correctly - probably not as it's causing a deadlock!
Is there a best practice approach here as my fact tables build significantly faster using the disable, re-enable method
I want to use this method and I also want to update statistics and guarantee that no deadlocks occur
Also wondering, why is it causing a deadlock and a waiting block

Thanks

If you're disabling indexes to do your data loads, then you need to rebuild them to enable them again.  Rebuilding an index causes statistics to be regenerated automatically from the full scan performed on rebuilding the index:
https://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/