December 12, 2013 at 1:47 am
Hi All,
I wonder if anyone has any experience in this area:
I have a table which has about 4000 records in it, and has a stored procedure running against it to delete records.
The issue I am seeing is that SQL Server is acquiring one KEY lock at a time, incrementally, and taking forever to carry out the delete (about 10 records per minute).
The process has taken 7 hours. Sometimes this process is quick, other times it is really slow.
The delete is DELETE from table where table.ID in (SELECT ID from TEMPTABLE where tableName = 'table'). The temp table has a non-clustered index on it, which is not fragmented, and the 'table' table has a primary key which is CLUSTERED. None of the indexes have serious fragmentation (10% fragmented etc...)
I have checked to see if anything else is locking the table, and there are no other processes accessing the table object. Can anyone point me to anything I can look at, or does anyone have any experience with this kind of issue?
Many thanks for any assistance.
Laurence Proctor DBA.
December 12, 2013 at 2:21 am
Stale statistics perhaps? What happens if you update stats before running the delete?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 12, 2013 at 2:44 am
Hi there,
Good call. I'll have a look into this. I will find out when table statistics are updated.
Does anyone have any further ideas?
Many thanks,
Laurence
December 12, 2013 at 3:15 am
Can you post the delete's execution plan? Also what are the waits you see for the delete?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 12, 2013 at 5:45 am
I'm with Gail. Probably statistics, but looking at the execution plan would sure help narrow it down.
"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
December 12, 2013 at 11:51 am
Hi Grant and everyone else assisting me,
Thank you for your analysis. I am sorry for not responding sooner; I have been in meetings about this issue all day.
I will attach the execution plan tomorrow. Grant, I just wanted to thank you for your book on execution plan analysis. I have been reading it for over 2 months now, and it has proved invaluable.
Thanks again to all.
Sincerely,
Laurence
December 12, 2013 at 11:55 am
That's very kind, thank you!
You are reading the second edition, right? The guy who wrote the first edition was an idiot.
"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
December 13, 2013 at 12:56 am
Hi Everyone,
Btw Grant, I believe I have your 2nd edition book (it's awesome!).
A little more background into this issue:
1. The procedure runs every night. Oddly, 2/3 times a week it takes 7 hours to complete.
2. I ran the following query to find cached query plans:
[font="Courier New"]select DB_NAME(sql_text.dbid) dbName, sql_text.text, plans.usecounts, stat.last_clr_time /1000000 last_clr_time_secs, stat.max_clr_time /1000000 max_clr_time_secs, stat.max_worker_time /1000000 max_worker_time_secs, plan_.query_plan from sys.dm_exec_cached_plans plans, stat.last_execution_time
cross apply sys.dm_exec_query_plan(plans.plan_handle) plan_
inner join sys.dm_exec_query_stats stat on stat.plan_handle = plans.plan_handle
cross apply sys.dm_exec_sql_text(stat.sql_handle) sql_text
where DB_NAME(sql_text.dbid) = 'DB_Name' and sql_text.text like '%Housekeeping%'
order by DB_NAME(sql_text.dbid), last_execution_time desc, usecounts desc.[/font]
I found that I was getting up to 13 plans (same plan handle) for the one procedure with anything from 1 - 2 usecounts for each plan (with a different last_execution date against each one). This worried me a little, as I am not used to seeing this. (Please correct me if I'm wrong) I think this is an indication that the optimiser cannot use the same plan due to stale statistics? Is this a fair assumption? Can anyone shed a little light on this for me please? Could my query be wrong?
Please see a screenshot of the execution plan I captured for the query which was eating up
3. After looking at the execution plan (which might not be useful to attach, as I only grabbed one out of 20 of the plans), I found that the resource which was being locked up had very little wrong with it (as in 0% out of the other queries inside, and very little data being fetched for the delete). Index seeks were being used (instead of scans). See a screenshot of the plan.
Many thanks for everyone's help.
Kind regards,
Laurence.
December 13, 2013 at 2:05 am
Sorry, I forgot to mention, the table that I am deleting from has 161k of records in it, and the deletion is based on the primary key which should be quick.
In the case above, we deleted 4000 out of 161,000, but the delete should be quick.
December 13, 2013 at 3:14 am
Not the cached plans and not a screenshot please.
To see if it's a stats problem, we need the actual execution plans with runtime info (capture using profiler or use SSMS 'include actual execution plan') and we need the plan itself because the properties are where all the useful info is, not the picture of the operators.
I think this is an indication that the optimiser cannot use the same plan due to stale statistics? Is this a fair assumption
No. Stale stats can't cause multiple plans. If you have multiple plans it's because you have different SQL statements and they don't map to the same plan.
I found that the resource which was being locked up had very little wrong with it (as in 0% out of the other queries inside, and very little data being fetched for the delete). Index seeks were being used (instead of scans). See a screenshot of the plan.
But that could actually be the problem. 0% is an estimate and may well be an incorrect estimate and seeks are not always better than scans.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 13, 2013 at 7:50 am
Hi Gail,
Thank you very much for your informative reply. I unfortunately did not capture all of the query plan info from the last issue. I have just started work at this company, and have not had all required permissions to carry out the work required.
I can be rest assured that by next week there will be another issue like this. When I'm faced with this issue again, I will post back to this thread, with all of the facts.
Many thanks,
Laurence
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply