5

So I've been beating my head against the wall for two days about this and I can't find a solution. I'm running a script to stage a large amount of data. This script requires roughly 35 million locks in order to complete; this translates into needing 3.36GB of memory available for locks. I set the number of locks to 37 million in the configured values, however after the reboot the running values are only allowing for 16,695,296 locks.

This corresponds to roughly 14% of available memory. As SQL Server 2012 can use 40% of available memory for locks, I should have plenty a room, but I don't. My question is: why can't SQL Server utilize the extra 26% of available lock memory? Everything I've read says that it should be able to.

Also, I did try running the locks on dynamic, but that also failed for the same reasons and with the same error codes: error 1204, instance can not obtain a lock resource. I know it's the server configuration and not the script, because I ran the exact same script, with the exact same data set on an older server (SQL Server 2008) without issue.

I appeal to the database admin gods for help; help me oh great ones!

Edit: This is a VM on a fail-over cluster, the VM has 16 GB of memory allotted to it. I've given SQL Server 12GB and left 4GB available for the host operating system.

Improve this question
8
  • What happens when you set the number of locks to 0 (meaning let sql server decide how much of them to use instead of you giving a hard value) ? no of locks will be removed in future versions of SQL Server. Can you tell, whats the reason behind using no of locks as SQL Server is smart enough to allocate lock memory dynamically. If it was SybaseASE, you could have tune that.
    – Kin Shah
    Commented Jun 11, 2014 at 2:30
  • Locks = 0 was how I had originally configured the SQL Server and in the locks = 0 configuration, it still gives the 1204 lock resource error. The reason why I manually defined the number of locks was based off a hunch that SQL Server was not dynamically allocating enough memory. Also, the first time I encountered this error, the server was set to lock = 0. My first thought was that it really did need more memory, so I added an extra 4GB of RAM, however I still got the 1204 error. This machine now has more RAM then my 2008 SQL Server, but my 08 SQL Server can run this job without the lock error
    – Saren
    Commented Jun 11, 2014 at 14:21
  • What is the exact version of your sql server on both server (working and nonworking) select SERVERPROPERTY ('Edition') AS ServerEdition, CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) + ' + ' + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build' + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion, RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.]. Also, have you compared the sys.configurations on both servers ? Especially Max Memory setting.
    – Kin Shah
    Commented Jun 11, 2014 at 15:09
  • Also, can you confirm that this is a dedicated sql server box without having other apps like IIS running on it ? Run this query SELECT request_session_id, COUNT (*) num_locks FROM sys.dm_tran_locks GROUP BY request_session_id ORDER BY count (*) DESC to find out what is using up all the locks.
    – Kin Shah
    Commented Jun 11, 2014 at 15:13
  • @Kin This is a dedicated SQL box, there are no other apps running on it. The lock use is legitimate, it's the fact that SQL Server isn't able to use all of the memory available for locks that's the issue. It should be able to accommodate the 35 million locks without a problem, but it will only use 14% of the MaxMemory for locks as opposed to the 40% that it should. There are only 5 locks when I run the above query with the server in resting state.
    – Saren
    Commented Jun 11, 2014 at 23:40

1 Answer 1

Reset to default
1

Is there any trace flag enabled or any tweaking into startup parameters of SQL server can you please check that.Have a look at below Microsoft support article

http://support.microsoft.com/kb/2199576/en-us Keep max lock value to default

EXEC sp_configure 'locks', 0 ( I guess you tried this option already)

Now if you face this issue again best possible workaround will be to decrease the amount of rows being transferred. Every lock has memory associated with it so if there is lock contention I believe its somehow related to SQL Server buffer pool memory. Did you run DBCC MEMORYSTATUS and check value for

OBJECTSTORE_LOCK_MANAGER (Total) KB

please do tell us what is value of single page and multi page allocator for above clerk

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.