April 2, 2010 at 1:55 pm
SQL Server 2005 SP3 x64
We have a third-pary application and the users are complaining about performance. The perfmon metrics look fine. No memory, CPU or IO pressure.
When I look at process activity, I see a lot of processes being blocked because another process has placed a lock on a resource.
To get a high level view, I ran the following query:
create table #Temp_Locks (spid int, [dbid] int, [Objid] int, IndID int, [type] varchar(30), Resource varchar(500), Mode varchar(50), [Status] varchar(50))
insert into #Temp_Locks
exec sp_lock
select [dbid], [type], count(*)
from #Temp_Locks
group by [dbid], [type]
order by count(*) desc
drop table #Temp_Locks
The results are as follows:
7 DB 148
7 APP 20
8 DB 4
4 DB 3
7 TAB 2
15 DB 1
1 TAB 1
2 TAB 1
Database ID = 7 represents a user database that the application populates. I was suprised to see so many Database locks. I've reviewed BOL and can't find an explanation of the events that cause a lock to be placed on a database.
Can someone enlighten me?
April 2, 2010 at 2:03 pm
If I remember this correctly, lots of changes to things like schemas result in database locks. The idea is that you don't want the database to be changed, in terms of some options/settings, while this are in process. As long as they're shared locks, I think it's fine.
If you have performance issues, I would look at more granular locks, not the db ones.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 2, 2010 at 2:14 pm
I'm sure you're correct. I ran the following query -
SELECT resource_type, request_session_id, resource_database_id, resource_associated_entity_id, resource_subtype, resource_description, request_status, request_owner_type, request_mode
FROM sys.dm_tran_locks
order by resource_type
All the database locks have a request_owner_type of "SHARED_TRANSACTION_WORKSPACE" and a request_mode of "S" (shared). So, I don't think database locks are the cause of our performance issues.
Interestingly, the above query shows a number of Application locks, and all of those consistently have a request_status of "WAIT".
April 2, 2010 at 2:47 pm
The database lock prevents a DBA from detaching or dropping the database while it's being used. If you've ever attempted to detach a database and couldn't because it's "In use" and you can't see who is using it in sysprocesses or a DMV, you can look for the SPIDS that have a database lock using sp_lock and kill them. That will allow you to then detach / drop the database. I've seen this many times when a query is executed from one database and joins to objects in another database.
April 3, 2010 at 7:46 am
Bill Kline-270970 (4/2/2010)
All the database locks have a request_owner_type of "SHARED_TRANSACTION_WORKSPACE" and a request_mode of "S" (shared). So, I don't think database locks are the cause of our performance issues.
They're not. Shared database locks are taken by every session that connects to a database. This is how SQL Server keeps track of whether anyone is using a database or not. Ignore there locks.
Interestingly, the above query shows a number of Application locks, and all of those consistently have a request_status of "WAIT".
When you say Application locks, do you mean locks acquired through sp_getapplock?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 5, 2010 at 8:44 am
Thanks for the responses, George and Paul.
Paul, the query to sys.dm_tran_locks returns a resource_type of "APPLICATION" for the spids that are waiting. The actual sql stmt being executed by these spids is "xp_userlock". Research tells me that sp_getapplock is a wrapper for xp_userlock. There are 4 procs in the database that make calls to sp_getapplock.
I guess a profiler trace is in order to verify if these procedures are the source of the waits due to locks. I will also research sp_getapplock further to determine if there can be a performance impact.
April 5, 2010 at 9:00 pm
Bill Kline-270970 (4/2/2010)
We have a third-pary application and the users are complaining about performance....
Can someone enlighten me?
Yes... it seems that everyone approaches the problem of 3rd party code incorrectly... they try to fix it themselves. Get that bloody vendor off his haunches and make the vendor fix it! 😉
--Jeff Moden
April 6, 2010 at 1:23 am
Bill Kline-270970 (4/5/2010)
I guess a profiler trace is in order to verify if these procedures are the source of the waits due to locks. I will also research sp_getapplock further to determine if there can be a performance impact.
Application locks are certainly slower to acquire than standard locks, but that is almost certainly not the problem. If you are seeing long waits on custom locks, it is a consequence of the application design.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 6, 2010 at 8:52 am
Jeff Moden (4/5/2010)
Yes... it seems that everyone approaches the problem of 3rd party code incorrectly... they try to fix it themselves. Get that bloody vendor off his haunches and make the vendor fix it! 😉
Speaking as one of those bloody vendors, checking with them can save a lot of time if they've already seen this issue. Even if they haven't heard of it before they'll have more application knowledge to should be find out what's going on more quickly. Now, if this is their DB but you've written custom code to run against it that's another matter. We usually find that using sp_who2 to track down the blocking SPID and dbcc inputbuffer goes a long way towards finding the cause.
April 6, 2010 at 10:43 am
Many thanks to everyone for their comments.
Please see my follow up post "SP_GetApplock - Understanding what is being locked". I did provide the information to my internal customers who have in turn forwarded to the vendor.
No attempts for fix 3rd party code on my part! 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply