April 5, 2010 at 11:52 am
SQL Server 2005 SP3 x64 Enterprise Edition
This is a companion post to one I opened a few days earlier about database locks. It's been determined that database locks are shared locks that are acquired so that a database can't be dropped/detached/restored while there is an open connection to the database; and that these locks won't affect performance.
I'm troubleshooting performance in a vendor application. Disk IO, CPU usage, paging seem very reasonable. Users are complaining about performance. This application is used to monitor the hardware components of various systems and report on their performance. It creates tables for a given component/time frame, populates the table with data, and then rolls that data up into other tables for reporting purposes. An examples is that it would create a table for Network card A for the past 6 hours and the populate the table with metrics about the card's performance. As you might imagine, a lot of tables (80,000+) will end up being created depending on how many components are being monitored.
Querying sys.dm_os_wait_stats reveals that the wait type of "LCK_M_X" accounts for over 60% of the accumulated wait time. Querying SQL that is currently executing consistently shows SPIDS with a wait type of LCK_M_X are executing the SQL statement "xp_userlock". SP_GetAppLock is a wapper for xp_userlock.
SP_GetAppLock is utilized in 4 procedures, which are frequently called.
Procedure A calls sp_get_applock, creates a table, indexes and a constraint and then calls sp_releaseapplock.
Procedure B calls sp_getapplock, drops 1 or more tables (selected from a cursor) and then calls sp_releaseapplock.
Procedure C calls sp_getapplock, alters a view and sets a view property and then calls sp_releaseapplock.
Procedure D calls sp_getapplock, queries the extended properties of a view and then calls sp_releaseapplock.
Note that the 4 procedures primarily touch system tables in the database.
My research into sp_getapplock (BOL, SSC forums, articles) explains that this is a method used by a process to guarantee serial access to application database code and database objects by issuing an application lock as opposed to trying to manipulate SQL Server's built in locking by using locking hints. A lock generated by sp_getapplock is identified by the @Resouce variable (case sensitive string). Any process calling sp_getapplock with the same @Resource variable will be blocked until the app lock is released.
Am I basically correct in my understanding?
The research hints that this approach might lead to scalability issues.
All 4 procs I've outlined use the same identifier for the @Resource variable. Each proc calls sp_getapplock using parameters for EXCLUSIVE and SESSION. Thus, it seems to me that they might be blocking each other even if they are trying to accomplish entirely unrelated tasks.
Is my analysis correct? That is, the resource being locked is the actual @Resource identifier, not specific tables, indexes, pages, etc. Any process with a given @Resource identifier will block other processes identified by the same @Resource identifier.
My goal is to provide a logical explanation to the system users (internal IT performance monitoring team) as to why the database seems "slow". And by the way, we are collecting various perfmon counters at a 1 minute interval and analysis of these doesn't show anything out of line from a hardware perspective.
April 5, 2010 at 12:10 pm
Bill, your explanation of how app locks work is accurate. App locks do nothing more than allow you to micro-manage an application 'resource' using SQL Server's locking memory space. In your example, it seems like the application vendor purposely wanted to ensure that only 1 of the 4 procedures in question could run at a time. As you've pointed out, this could lead to unnecessary blocking if the procedures are not touching similar DB objects.
With this being said, the vendor may have had some reason to do what they are doing even if it seems odd to us now. It is also entirely possible that you have identified a bug in their logic. I would recommend taking this up with the software vendor for an explanation. Provide them with the ammo that you have discussed showing that their logic is leading to slow performance.
April 5, 2010 at 4:25 pm
John, thanks for your feedback.
I have provided my analysis to the Performance Monitoring Team, and they have forwarded my comments to the vendor.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply