Many of us more experienced (NOT older) techies like to speak of learning things the hard way. This definitely applies to me and the way I learned about SQL Server 2000. I've had to learn it all on the job, on an as-needed basis. This means that there are gaps in my knowledge. Every once in a while, I get to learn something about SQL Server that I never knew before. This time, it was the fact that SQL Server supports critical sections.
For those of you that may not know this term, a critical section is a piece of code that can be safely run by only one process at a time. In a multi-threaded application, this applies to each individual thread; only one thread can safely execute a piece of code at a time. The term “Critical Section“ is common in languages with roots in C or C++.
I found myself with just this situation with a stored procedure. I found that there are two system stored procedures, sp_getapplock and sp_releaseapplock, that implement this concept. Here is an excerpt from SQL Books Online:
sp_getapplock
Places a lock on an application resource.
Syntax
sp_getapplock [ @Resource = ] 'resource_name',
[ @LockMode = ] 'lock_mode'
[ , [ @LockOwner = ] 'lock_owner' ]
[ , [ @LockTimeout = ] 'value' ]
Arguments
[@Resource =] 'resource_name'
Is a lock resource name specified by the client application. The application must ensure the resource is unique. The specified name is hashed internally into a value that can be stored in the SQL Server lock manager. resource name is nvarchar(255), with no default.
[@LockMode =] 'lock_mode'
Is a lock mode. lock_mode is nvarchar(32), with no default, and can be one of these values: Shared, Update, Exclusive, IntentExclusive, IntentShared.
[@LockOwner =] 'lock_owner'
Is the lock owner. lock_owner is nvarchar(32) and can be Transaction (the default), or Session. When the lock_owner value is the default, or when Transaction is specified explicitly, sp_getapplock must be executed from within a transaction.
[@LockTimeout =] 'value'
Is a lock time-out value, in milliseconds. The default value is the same as the value returned by @@LOCK_TIMEOUT. To indicate that lock requests that cannot be granted immediately should return an error rather than wait for the lock, specify 0.
Return Code Values
>= 0 (success) or < 0 (failure)
| Value |
Result |
| 0 |
Lock was successfully granted synchronously. |
| 1 |
Lock was granted successfully after waiting for other incompatible locks to be released. |
| -1 |
Lock request timed out. |
| -2 |
Lock request was cancelled. |
| -3 |
Lock request was chosen as a deadlock victim. |
| -999 |
Parameter validation or other call error. |
Cool. This is exactly the kind of “semaphore-lock“ that I cut my teeth on. Of course, it turns out that I didn't really need this. I discovered a better way to implement my task. However, I thought that I would pass this along, since I had never heard of it before. Maybe it will help someone else.
I managed to outsmart myself again....
I've been working on an application that processes large XML files. One of the routines involves passing the XML to a stored procedure, which then inserts certain parts of the XML into various tables. This is done via sp_xml_preparedocument and openxml. This is fairly trivial and works quite well.
Now, this system processes a lot of XML. Some of the tables involved have reached 15+ million rows, and insert performance has decreased to a point where I had to take action. I decided that, in addition to normal SQL tuning, I would break this task into two parts. First, the XML would be placed, unparsed, into a staging table. A separate process would take the data from this table, perform the proper parsing via openxml, and insert the resulting data into the various destination tables. This separation of work would allow the main process to operate more quickly.
I created the staging table and tested the system performance. The system ran 400% faster. Cool.
Next, I had to write the process that would retrieve the data from this staging table and insert it into the destination tables. This is when I discovered my mistake.
The XML is stored in NTEXT fields in the staging table. I needed to read this into a local variable, so that I could pass it to the sp_xml_preparedocument stored procedure. This can't be done, because local variables can't be declared as NTEXT. I was stuck.
I found a good article on the net that explained a workaround. However, in the article, the author states that he has used this workaround on documents that were “over 36,000 bytes in size.” My documents sometimes reach 20 MB in size. His method doesn't work on documents over about 300K. (You should check it out, though. It's a good read.) I had to solve my own problem.
So, as I was running out of time, I decided to fall back to the one solution that I knew would work. I wrote a .NET process that runs on a background thread. This process reads the XML from the staging table (via an FOR XML EXPLICIT query), parses this data via a couple of calls to the XmlDocument's SelectSingleNode method, and then uses another stored procedure to insert the data into the destination tables. This works, but I don't like it. I would rather have all of this data manipulation occur on the SQL Server, instead of being passed back and forth between the SQL Server and the Application Server. If anyone wants to send some alternatives my way, I would be most appreciative.
I can't wait for SQL 2005. Maybe this kind of thing will go away....