January 30, 2019 at 3:29 am
I have one app which usually keep checking for some reminder stuff and if find it execute one stored procedure.
the procedure have one delete statement e.g.
DELETE FROM TableA
So we have no control over the app nature and when to execute the reminder , it is automated.
other hand we have weekend SQL optimization job which basically runs and run the update statics on the tables of database.
I have noticed every time when on weekend the SQL optimization job run the above delete statement will create blocking .
to avoid that we also created non clustered index on the TableA but it didn't help.
the blocking happens because of these two statements and it seems row level blocking
the blocking happens because of these two statements
DELETE FROM TableA
UPDATE STATISTICS .[dbo].[TableA] [_WA_Sys_AMOUNT_13DCE752]
Can you please suggest what is the solution to fix this issue?
January 30, 2019 at 3:58 am
RCRock - Wednesday, January 30, 2019 3:29 AMI have one app which usually keep checking for some reminder stuff and if find it execute one stored procedure.
the procedure have one delete statement e.g.DELETE FROM TableA
So we have no control over the app nature and when to execute the reminder , it is automated.
other hand we have weekend SQL optimization job which basically runs and run the update statics on the tables of database.
I have noticed every time when on weekend the SQL optimization job run the above delete statement will create blocking .
to avoid that we also created non clustered index on the TableA but it didn't help.the blocking happens because of these two statements and it seems row level blocking
the blocking happens because of these two statements
DELETE FROM TableA
UPDATE STATISTICS .[dbo].[TableA] [_WA_Sys_AMOUNT_13DCE752]
Can you please suggest what is the solution to fix this issue?
Try running on difference time-frame or reschedule stats update.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
January 30, 2019 at 4:55 am
RCRock - Wednesday, January 30, 2019 3:29 AMI have one app which usually keep checking for some reminder stuff and if find it execute one stored procedure.
the procedure have one delete statement e.g.DELETE FROM TableA
So we have no control over the app nature and when to execute the reminder , it is automated.
other hand we have weekend SQL optimization job which basically runs and run the update statics on the tables of database.
I have noticed every time when on weekend the SQL optimization job run the above delete statement will create blocking .
to avoid that we also created non clustered index on the TableA but it didn't help.the blocking happens because of these two statements and it seems row level blocking
the blocking happens because of these two statements
DELETE FROM TableA
UPDATE STATISTICS .[dbo].[TableA] [_WA_Sys_AMOUNT_13DCE752]
Can you please suggest what is the solution to fix this issue?
If the two statements are completely accurate, you're deleting all records at the same time you're trying to update the statistics of an automatically generated set of statistics, so only a single column. If you need to delete all the rows, get that done before you update the statistics. Statistics updates, even against very large tables, generally completes quickly. Deleting all the rows, even in a small table, is going to run fairly long. Let that one go first.
Also, what would creating a nonclustered index have to do with a DELETE statement with no WHERE clause? What would a nonclustered index have to do with statistics updates, other than adding more statistics to update.
Something about this feels like the information you're providing is not complete. Any answers or solutions we suggest based on incomplete data could be grossly wrong and unhelpful. To get the best possible help, be as clear as possible when describing the situation in which you find yourself.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply