January 16, 2017 at 3:44 am
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
- Damian
January 16, 2017 at 4:00 am
Damian
It's not necessarily purely a priority thing - if both processes have equal priority, then the process that's easier to roll back is chosen as the victim.
If this is just a one-off then, to be honest, I wouldn't spend too much time on it. Try to make sure that the job runs when as little else as possible is going on, put a retry on the bit that failed, and move on.
If you want to understand why the deadlock occurred, I find that this is an excellent resource. It can get a bit tedious working through it, but stick with it, because it usually leads to the cause!
John
January 16, 2017 at 4:16 am
John Mitchell-245523 - Monday, January 16, 2017 4:00 AMDamianIt's not necessarily purely a priority thing - if both processes have equal priority, then the process that's easier to roll back is chosen as the victim.
If this is just a one-off then, to be honest, I wouldn't spend too much time on it. Try to make sure that the job runs when as little else as possible is going on, put a retry on the bit that failed, and move on.
If you want to understand why the deadlock occurred, I find that this is an excellent resource. It can get a bit tedious working through it, but stick with it, because it usually leads to the cause!
John
Thanks for the link, John
It is a bit of a one-off; I always try to use these to understand and improve what's going on, on the server
Think I'm going to put a loop in place that repeats on error and see how that goes
- Damian
January 16, 2017 at 9:27 am
DamianC - Monday, January 16, 2017 3:44 AMHiOn 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 tasksOne 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 indexesThis 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 blockThanks
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/
January 16, 2017 at 10:51 am
Chris Harshman - Monday, January 16, 2017 9:27 AMDamianC - Monday, January 16, 2017 3:44 AMHiOn 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 tasksOne 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 indexesThis 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 blockThanks
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/
Correct, the update stats task is a futile exercise, so remove it
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 17, 2017 at 9:02 pm
Actually, it may not be a futile exercise. I do agree that rebuilding the indexes to reenable them will rebuilt the stats for those indexes but that will not include the Clustered Index nor any of the column stats. Those will also be important after a major load and the major load may not be large enough to trigger a stats update on its own.
Just to show how important it can be, we just went through this a couple of weeks ago with one of our larger tables. Imported 200,000 rows for a new client and rebuilt the non clustered indexes.. Queries for clients of similar size executed in about a second. Queries for the new client had to be stopped after an hour. We did this a couple of times just to be sure. Then, we rebuilt the all the stats on the table and the query for the new client became as speedy as the rest.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply