SQL Server Performance Forum – Threads Archive
Server chooses different locktype
Today I copied my production database to a developer’s database. Same server, same disk, same everything. It’s a 6 Gig DB on SQLServer2000 under Win2003. In production a maximum of 8 users. Usually the server is not very busy. I noticed that a query is 30-50% slower in production compared to execution in my new DB copy. Since there were no changes at all, the two DBs should be identical, including Indexes, Query plans etc. The speed difference is quite constant, I’ve tried more than a hundred times – it always takes 30-50% more time in production. It took me a while to find out what happens. In production, my select statement obviously uses row locks. In development, SQL server uses page locks. SLQ Profiler shows about 10,000 Lock-acquired/released cycles in production and aboput 1,000 cycles in development. This makes dev faster. But why does SQL server use different Lock types? Is it some optiimization, because users are present in production? sp_lock does not show any locks on the table I use. Updating statistics / reindexing indexes did not make a change. Anybody any ideas?Same SQL, same Services Pack, same configuration (sp_configure)? Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Yes, everything is the same. Both DBs run in the same SQL Server instance on a single Proliant Server. The .MDF / .LDF files of prod and dev sit next to each other in a single directory. The development DB was built using a "restore" of last night’s production DB. Just to be sure, I ran sp_configure in either DB and compared – all the same. But still SQL Server chooses different lock types on the same query. The query is:
select min(HTOT_REQUEST_FROM_DATE)
from hdtot
where HTOT_CLIENT = ‘155 780 00’ Even though I do not see any locks on the table, I can’t think of any other reason than the users’s presence in production. I have a feeling though there’s something I haven’t thought of…..
Is it possible that there is some data added into production since last night, which affected the execution plan of the query. Try running the same query (with execution plan on) in both databases and see if there is any difference in the plans.
No, data was not added or changed.
And yes, execution plans are identical. However, the query uses different lock types, as SQL profile shows.
I have no idea how this choice is made…
http://www.sql-server-performance.com/lock_contention_tamed_article.asp for information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Also, just for kicks and grins, try updating the stats on the involved tables in production. Then run recompile the query and see what you get. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
I have updated stats with fullscan on prod and dev, still the query plans are identical, but in dev the server chooses page locks, in prod it chooses row locks. I heard that "lock escalation" may happen on various occasions – does anyone know when the server decides to do this ?
From BOL
quote:
Lock Escalation
Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead. Microsoft® SQL Server™ 2000 automatically escalates row locks and page locks into table locks when a transaction exceeds its escalation threshold. For example, when a transaction requests rows from a table, SQL Server automatically acquires locks on those rows affected and places higher-level intent locks on the pages and table, or index, which contain those rows. When the number of locks held by the transaction exceeds its threshold, SQL Server attempts to change the intent lock on the table to a stronger lock (for example, an intent exclusive (IX) would change to an exclusive (X) lock). After acquiring the stronger lock, all page and row level locks held by the transaction on the table are released, reducing lock overhead. SQL Server may choose to do both row and page locking for the same query, for example, placing page locks on the index (if enough contiguous keys in a nonclustered index node are selected to satisfy the query) and row locks on the data. This reduces the likelihood that lock escalation will be necessary. Lock escalation thresholds are determined dynamically by SQL Server and do not require configuration.
IIRC, you can add "lock escalation" to a profiler trace, so you can better watch this.
———————–Lock Escalation
Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead. Microsoft® SQL Server™ 2000 automatically escalates row locks and page locks into table locks when a transaction exceeds its escalation threshold. For example, when a transaction requests rows from a table, SQL Server automatically acquires locks on those rows affected and places higher-level intent locks on the pages and table, or index, which contain those rows. When the number of locks held by the transaction exceeds its threshold, SQL Server attempts to change the intent lock on the table to a stronger lock (for example, an intent exclusive (IX) would change to an exclusive (X) lock). After acquiring the stronger lock, all page and row level locks held by the transaction on the table are released, reducing lock overhead. SQL Server may choose to do both row and page locking for the same query, for example, placing page locks on the index (if enough contiguous keys in a nonclustered index node are selected to satisfy the query) and row locks on the data. This reduces the likelihood that lock escalation will be necessary. Lock escalation thresholds are determined dynamically by SQL Server and do not require configuration.
–Frank
http://www.insidesql.de
———————–
This might be also interesting:
http://support.microsoft.com/default.aspx?scid=kb;en-us;323630 ———————–
–Frank
http://www.insidesql.de
———————–
quote:Originally posted by kay27
But why does SQL server use different Lock types? Is it some optiimization, because users are present in production?
Did you try with a second user logged in to your dev DB?
ThomasBut why does SQL server use different Lock types? Is it some optiimization, because users are present in production?
]]>