Hello, i need some help, because of too much page lock during process even if records lock is wanted because SQL server decide himself to escalate the record lock to page lock then to table lock (depending on memory usage) I plan to use on Server Properties Startup in SQL Server Parameters: /T1211 (disable all lock escalation). Side Effect: Database slower Has somebody some experience with this parameter or any workaround to resolve this locking problem. Thanks.
Could you please describe more detailed what is the problem with the PAGE lock? Once we had a case, where the pagelock was a problem when writing to the “Whse. Entry” table (PK/CI “Entry No.”, Integer): Whenever an Entry was inserted, of course, this happened on the last Page. This Page was also blocked, thus other transactions had to wait until the previous writing was finished. This could be solved by implementing a field type GUID. This GUID is also a unique number, but it is not created in sequence. So, when creating a “Clusterd Index” on this field, everytime a record is inserted, this will happen “physically” on a different page - no more problems with PAGE locks! hope this could help a little …
When we trace the lock on the SQL server, we have a page lock on nearly all tables used by the application. This is because we have a very heavy activity of warehouse management, sales and purchases simultaneously with 80 users online (Fresh product Management) in a short time.
This sounds quite familiar [V][Duh!] But as mentioned: this GUID stuff helped a lot - we used it within the Warehouse Management! Another option to prevent “lock escalation”: - You insert a dummy record in the table you want to have rowlocked only - You perfrom a write-transaction (separate process!) on this dummy record, that never ends; e.g. begin transaction select * from theTable where ID = DummyID (holdlock) This will establish an exclusive lock (X) for this record and intended exclusive locks (IX) on higher levels as Page, Extent, Table, etc… This could also help in your case … I never would set this traceflag, as I suppose this would be a real performance killer: Because, if SQL Server decides to escalate the lock level, there is always a reason for this - it’s faster! And also, in some cases you may want to have this pagelock behaviour …
Ok, thanks a lot. Will try the GUID stuff. need just a little more explanation on it. You just have to create this field, create a clustered index on it and initialize it with GUID when insert ? No more code to change ?
What was done is this: - Added a field type GUID to the “Whse. Entry” table - GUID is created in table OnInsert trigger - Set GUID field as “Primary Key” (this was done because then the “Clusterd Index” is autom. created on this field; but meanwhile I think that wasn’t really necessary, as the CI could be defined within SQL Server independently) - For “Entry No.” a “Secondary Key”/“Non Clusterd Index” was created - Some application code was modified to fit … As here the records are inserted physically on different pages, it is crucial to have sufficient maintenance - frequent re-indexing to restore the original fillfactors (depending on the transaction volume) - to avoid spage-splitting! But please be aware: TEST CAREFULLY! The impact of this change - of course - strongly depends on your business logic, how you write to/read from this table!
The primary key stuff is defintely not necessary. Just change the CI to the new GUID field.