Home Dashboard Directory Help
Search

Lock escalation no longer kicks in for INSERTs in SQL Server 2008 by Adam Machanic


Status: 

Closed
 as Fixed Help for as Fixed


35
0
Sign in
to vote
Type: Bug
ID: 506453
Opened: 10/30/2009 6:55:26 AM
Access Restriction: Public
0
Workaround(s)
view
12
User(s) can reproduce this bug

Description

The lock escalation algorithm seems to have changed in SQL Server 2008, and it no longer kicks in for INSERTs. This means that when inserting large sets of rows without explicitly using a locking hint, we can end up with an equally-large number of locks. This is not good behavior. It wastes a lot of RAM and can cause monitoring software to fail if the software depends on sys.dm_tran_locks to get information. I hope that we can get a fix and return the escalation algorithms to the behavior they had in SQL Server 2005.
Details
Sign in to post a comment.
Posted by Joe_Hell on 9/13/2010 at 12:19 PM
So was PCU2 CU2 for SP1? I have applied that and still see the 40666
When I run the query
USE tempdb
GO

CREATE TABLE x
(
    i INT NOT NULL PRIMARY KEY
)
GO

BEGIN TRAN
    INSERT x
    SELECT TOP (40000)
        ROW_NUMBER() OVER
        (
            ORDER BY (SELECT NULL)
        ) AS r
    FROM
        master..spt_values a,
        master..spt_values b
    ORDER BY
        r

    SELECT
        COUNT(*)
    FROM sys.dm_tran_locks
    WHERE
        request_session_id = @@SPID
ROLLBACK
GO

DROP TABLE x
GO
Posted by Sankar Reddy on 5/15/2010 at 7:38 AM
MSFT,

What is PCU2 in the context below?

>>Posted by Microsoft on 4/23/2010 at 1:32 PM
This will be fixed on SQL2008/PCU2. thanks
Posted by Microsoft on 4/23/2010 at 1:32 PM
This will be fixed on SQL2008/PCU2. thanks
Posted by Robert L Davis on 3/22/2010 at 12:08 PM
To answer Adam's question, my laptop has 4 GB of RAM (dual proc).
Posted by Microsoft on 11/15/2009 at 12:08 PM
Adam: This is a regression in lock escalation algorithm in SQL2008. We know what the issue is and it can be fixed. We will target to fix this in the next major release of SQL Server or in the next PCU (i.e. service pack) of SQL Server. The customer vote is already very high for fixing it

To respond to Thiago's point, you can always disable lock escalation at object level if you want.

Thanks
Sunil

Thanks
Sunil
Posted by Thiagogvdasa on 11/11/2009 at 9:12 AM
Hello,


I think this behavior happen because you have enough free memory on your test environment, else lock scalation algoritm will be executed when necessary or when 40% of sql server memory was used to lock structures.

I think this behavior is best to provide high concurrent between sessions, In SQL server 2005 If I execute same inserts with diferent range, the second session will be in block, wait for first session, In SQL Server 2008 both session will be executed in parallell, and on situations when I need to block other session, I need use INSERT INTO x WITH(TABLOCK)...


I think de behavior os SQL Server 2008 is best than SQL Server 2005, no?

Best regards,

Thiago
Posted by Microsoft on 11/5/2009 at 8:02 PM
Hi all... this looks "interesting". We are investigating... Thanks for reporting.

Michael
Posted by Adam Machanic on 10/31/2009 at 12:29 PM
Robert_Davis, thanks for testing to that level of detail. How much RAM does your test machine have? BOL says something about escalation kicking in when 40% of the available RAM is consumed by locks, but it doesn't say anything about differentiating between INSERTs and SELECTs so this is confusing at best (and I still think the behavior is wrong--40% is a lot of RAM!)

Do you have around 1 GB, by any chance (or max server memory set to 1 GB)? On the server I discovered this on, we had almost 60 million open locks on one table. The SQL Server service has a max server memory setting of 58 GB, and that is still well within the 40% range. At almost 6 GB, I think that's way too much memory for locks, especially when queries against the lock DMVs take 25-30 minutes.
Posted by Robert L Davis on 10/30/2009 at 1:11 PM
I verified this also on Windows 7 Ent. x86 and SQL 2008 Dev. x86 (10.0.2531).

I was able to determine that my tipping point for escalating to a table lock is 3,109,999 rows. If I insert 3,109,998 rows, it consistently does not escalate to a table lock. If I insert 3,109,999 rows, it consistently does escalate to a table lock.

Here are the locks still in effect at the end of the repro after inserting 3,109,998 rows:

resource_type - request_mode - count
DATABASE     - S - 1
KEY - X - 3109998
OBJECT - IX - 2
PAGE - IX - 5000

At 100 bytes per lock, that's still less than 300 MB of memory. If there are multiple large inserts going on, this can add up pretty quickly.
Posted by A.Lockwood on 10/30/2009 at 12:10 PM
SQL Server 2005 x64 (9.00.4226) : 17

All SQL 2008 tested = 40,000+
SQL 2008 WITH(TABLOCK) = 35
Posted by AaronBertrand on 10/30/2009 at 8:08 AM
Yeah, this is scary. I get the following results:

SQL Server 2008 x86 (10.0.2531) : 40,135
SQL Server 2005 x86 (9.0.4207) : 1
SQL Server 2008 x64 (10.0.2734) : 40,066

I can't seem to find a 2005 x64 to repro but I believe you that it is 1/2 there as well.
Sign in to post a workaround.