Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server 2008 R2 - Lock Excalation question Expand / Collapse
Author
Message
Posted Thursday, December 12, 2013 1:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 29, 2014 4:18 AM
Points: 15, Visits: 190
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.
Post #1522195
Posted Thursday, December 12, 2013 2:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 41,531, Visits: 34,448
Stale statistics perhaps? What happens if you update stats before running the delete?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1522204
Posted Thursday, December 12, 2013 2:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 29, 2014 4:18 AM
Points: 15, Visits: 190
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
Post #1522209
Posted Thursday, December 12, 2013 3:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 41,531, Visits: 34,448
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1522213
Posted Thursday, December 12, 2013 5:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:49 AM
Points: 14,802, Visits: 27,281
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1522258
Posted Thursday, December 12, 2013 11:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 29, 2014 4:18 AM
Points: 15, Visits: 190
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
Post #1522443
Posted Thursday, December 12, 2013 11:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:49 AM
Points: 14,802, Visits: 27,281
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1522445
Posted Friday, December 13, 2013 12:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 29, 2014 4:18 AM
Points: 15, Visits: 190
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:

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.


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.


  Post Attachments 
Screenshot of query execution plan.jpg (3 views, 96.32 KB)
Post #1522593
Posted Friday, December 13, 2013 2:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 29, 2014 4:18 AM
Points: 15, Visits: 190
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.
Post #1522603
Posted Friday, December 13, 2013 3:14 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 41,531, Visits: 34,448
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1522611
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse