March 11, 2011 at 12:59 pm
I understand that when the number of locks obtained is equal to 40% of the total SQL Server memory that it will start throwing 1204 errors.
What I am finding is if I hit 10M locks, I get this error yet have a very large amount of unused memory. (This server is a special purpose box, and eats a lot of locks.)
Question is if I stop using dynamic locks and set the number of locks to 20M (example only), would this override the 40% rule and allow for the set number of locks?
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
March 11, 2011 at 2:23 pm
sjimmo (3/11/2011)
I understand that when the number of locks obtained is equal to 40% of the total SQL Server memory that it will start throwing 1204 errors.What I am finding is if I hit 10M locks, I get this error yet have a very large amount of unused memory. (This server is a special purpose box, and eats a lot of locks.)
Question is if I stop using dynamic locks and set the number of locks to 20M (example only), would this override the 40% rule and allow for the set number of locks?
I think it refers to 40% of directly addressable memory - have you enabled AWE?
On the other hand, I understand that 40% limit refers to the limit when using dynamic configuration otherwise limit is whatever you setup via sp_configure.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 14, 2011 at 5:51 am
(
I think it refers to 40% of directly addressable memory - have you enabled AWE?
On the other hand, I understand that 40% limit refers to the limit when using dynamic configuration otherwise limit is whatever you setup via sp_configure.
) I agree with you bro!!..
March 14, 2011 at 6:15 am
have you enabled AWE
AWE is enabled. /PAE switch is turned on as is lock pages in memory.
Steve
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
March 15, 2011 at 8:21 am
sjimmo (3/11/2011)
I understand that when the number of locks obtained is equal to 40% of the total SQL Server memory that it will start throwing 1204 errors.What I am finding is if I hit 10M locks, I get this error yet have a very large amount of unused memory. (This server is a special purpose box, and eats a lot of locks.)
Question is if I stop using dynamic locks and set the number of locks to 20M (example only), would this override the 40% rule and allow for the set number of locks?
1) please define "special purpose box"
2) please state why you think the box "eats a lot of locks"
3) please provide server specs (ram, cpu), os version, sql server version and patch level. max memory setting in sql server
4) I see in a separate post that you have AWE and PAE on and lock pages in memory enabled. Thus I presume this is a 32 bit box. If you have a lot of RAM you are starved for lower memory space (<2GB) which is the ONLY place where locks and other important things can go (memtoleave). You can definitely have all kinds of memory related problems on a 32 bit system with large memory.
5) run dbcc memorystatus and go online to see what the output tells you (undocumented in BOL but microsoft has an article detailing usage)
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 15, 2011 at 8:40 am
Kevin,
please define "special purpose box
This box is being running our datawarehouse currently. in the future it is being transformed into the central distribution for our replication.
please state why you think the box "eats a lot of locks"
Monitoring the number of locks being used, I see that once the server hits 10 million locks I get a 1204 error.
please provide server specs (ram, cpu), os version, sql server version and patch level. max memory setting in sql server
24GB RAM with 4 CPU's. The OS is W2K3 SP2 (32 BIT) with SS 2K5 SP4. Max memory is 20GB
At no time am I seeing any memory issues. Just asking if I manually configure the locks will I be able to override the 40% default.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
March 15, 2011 at 3:14 pm
My gut tells me that something unusual is going on for a DW to be consuming so many locks. Normally (in my experience) DW queries take few high-level locks. Can you provide any input as to why your locking is happening like it is? Lots of cursors? Scalar UDFs? Table variables? ORM in play?
As for controlling locking behavior, there are some undocumented trace flags but outside of those we don't have actual knobs to tweek.
You say you aren't having memory problems, but I question that with a 20GB 32 bit box. That 2GB lower memory space is getting awfully cramped at that level of RAM.
BTW, you don't have /3GB enabled do you?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 15, 2011 at 4:53 pm
The /3GB switch is not set. The locking is occuring during the weekly load which is performed using an openrowset from various other servers. Once we are through the load, then we are good.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
March 16, 2011 at 9:03 am
sjimmo (3/15/2011)
The /3GB switch is not set. The locking is occuring during the weekly load which is performed using an openrowset from various other servers. Once we are through the load, then we are good.
You may well be getting index seek/nested loop actions or in other ways doing row-by-row processing with that load mechanism. That could be the reason for extraordinary lock counts for what should be batch processes that simply take table locks (or page locks). If possible a change in your ETL process would probably both make it run faster and eliminate the locking issues.
Can you verify that trace flags 1211 and 1224 are NOT on?
Oh, maybe you can simply specify PAGLOCK or TABLOCK as a locking hint for your openrowset calls? Note this could cause serious problems if you have multiple processes happening at once.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 17, 2011 at 9:00 am
I'm with Kevin on this one.
If you're running SQL Server out of lock space, your problem isn't a lack of memory, it's that you're running something that takes and holds a ludicrous number of locks.
Once you start configuring your server around crappy code/design, you start circling the drain, because bad practice isn't improved by abusing SQL Server - bad practice that you manage to configure around just leads to more bad practice, and at some point you will reach the limit of what you can configure.
I have worked on systems like that, on systems where this cycle had gone on so long that it was irreversible - bad code and lack of design led to long transactions, large numbers of locks, deadlocks, and lock timeouts, and (this was on DB2) the locklist was so large that it was seriously impacting performance in other areas (smaller bufferpools, for example).
If you regularly get 10 million locks, your problem isn't configuration. It's code, and you need to do some serious work to identify what is causing this and fix THAT.
March 29, 2011 at 6:22 am
There are numerous circumstances which can cause locking as well as lock escalation.
I thank all of those who made their comments but have finally found the answer to my question, which is yes.
When adjusting manually the number of locks the amount of memory allocated by SQL Server for locking is statically set thus allowing for the increase in the amount of memory actually set aside for locks.
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/05/17/lock-escalation.aspx
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply