October 16, 2014 at 5:12 am
Hi,
We have suddenly acquired the above message in a process that has been happily running for years. The full text is:
The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.
The SQL statement that produces this error is:
delete from CessionMovement
where pk_Cession in (Select pk_cession from dbo.Cession where pk_Source = 2)
The inner select statement returns 11 values and replacing the "delete from" with "select count(*) from" returns a result of 509, so there's not a lot going on here. However, running the delete and keeping an eye on sp_lock confirms that to delete these 509 records, the statement is generating 3.9million locks before SQL Server gives up.
I can't imagine what's causing this (I've tried a good old-fashioned reboot) and the event log just repeats the reported error message sic times with no more data. Does anyone have any idea where I can look for the cause or solution?
Thanks,
Tony.
October 16, 2014 at 6:40 am
Possibly a change in indexes? Statistics are out of date or missing? Check the execution plan. It sure sounds like the optimizer is making some poor choices in executing this simple query.
"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
October 16, 2014 at 6:46 am
Grant,
Thanks for that - I did wonder if it was something to do with an index corruption or failure. We've instituted a re-indexing of the database in question and hopefully we will know within a few hours if that does the trick. the DB in question has a weekly re-index in any case but I've just heard from the guy who reported the problem to me that someone restored the database from backup recently so it may have been from a backup taken before the re-index.
It's great to get some potential light at the end of the tunnel.
Thanks again,
Tony.
October 16, 2014 at 6:52 am
If updating the indexes doesn't work, try updating the statistics, possibly with FULL SCAN.
"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
October 16, 2014 at 6:54 am
Grant,
Duly noted - thanks for the extra tips. One day I swear I will get the boss to send me on a SQL Server DBA course of some sort.
Cheers,
Tony.
October 16, 2014 at 6:59 am
Is the instance running with any trace flags enabled? Are row or page locks disabled on the index?
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
October 16, 2014 at 7:07 am
As far as I know the answer to those two questions is "no". I'll certainly look into it though.
UPDATE: the re-indexing has not solved the problem so w're updating the statistics. If this fails to resolve things, we will be restoring the database from a backup from the beginning of the week and then rolling forward all the data, testing the process after each update to establish where the error starts occurring.
October 17, 2014 at 4:30 am
Well, the solution has been found: the guy who designed the database didn't anticipate the size it would grow to and failed to create an index on the table in the select statement including the columns pk_Cession and pk_CessionMovement, which seems to have been causing the delete statement to scan the entire thing and lock out millions of rows. The index has been added and things seem to be running again. We must have just reached a tipping point with data volumes.
Thanks for all the help,
Tony.
October 17, 2014 at 4:56 am
thespratty (10/16/2014)
UPDATE: the re-indexing has not solved the problem so w're updating the statistics.
Rebuilding an index updates the statistics on that index. There's no need to do it again, unless you're just updating the non-index statistics.
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
October 17, 2014 at 10:48 am
The index you need for that DELETE would be keyed on:
( pk_Source, pk_cession )
If you only ever do this for pk_source = 2 (and not any other source numbers), you could make it a filtered index, i.e. add "WHERE pk_Source = 2" in the index definition.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 20, 2014 at 9:16 am
That's very interesting - I got the index definition from the query analyzer so I just kind of trusted it. I'll try adding another index as you have defined it and see if that speeds things up a bit. Thanks for the info - it's much appreciated.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply