August 24, 2013 at 11:34 am
Hi, I have a strange problem that pops up on a few production sites from time to time and hope I could get some advise here.
Each site is running a very busy application that calls an SP constantly to get the next value on a Sequence ID column from a small table with 200+ rows.
Here is the SP being called.
CREATE PROCEDURE [dbo].[SP_GetNextValue] ( @SeqID int out, @NextVal int out )
AS
Begin
Begin Transaction
UPDATE SeqTable with (ROWLOCK) SET NextValue = COALESCE(NextValue,0) + 1, @NextVal=NextValue WHERE SeqID = @SeqID
Commit Transaction
return
End
The SeqTable table is simple like this and there is a clustered primary key on SeqID column.
SeqID int
NextValue int
SeqName varchar(20)
Here is some sample data from the table:
SeqID NextValue SeqName
1 1234 MemberID
2 3222 CategoryID
3 4433 TopicID
4 6300 SubscriptionID
5 10800 PostID
...
During regular hours, the application may call this SP_GetNextValue 10-50 times per second. During peak time, when this may increase to 100-500 times per second. The problem is, from time to time, the application throws out 'query timeout' message when waiting for the next sequence value.
This last incident lasted over several hours where certain parts of the application was throwing Query Timeout messages in the application log. I was able to do some tests on the production db server. My test was done like this.
In one query window (SPID533), I ran the following code to call the SP. It returned a value immediately.
declare @csidtest int
Exec SP_GetNextValue 14, @csidtest OUT
select @csidtest
Then, I ran the same code in a second query window (SPID619). This second query ran forever and I had to kill it after 30 seconds. I went back to the first query window and ran the same code again and I got a result immediately. Before killing this second query, I captured the following:
SELECT session_id ,status ,blocking_session_id,wait_type ,wait_time ,wait_resource, transaction_id
FROM sys.dm_exec_requests WHERE status = N'suspended';
session_id status blocking_session_id wait_type wait_time wait_resource transaction_id
619 suspended 533 LCK_M_X 3594 KEY: 7:290366346166272 (0e00d057643e) 2446104665
SELECT o.name ObjName, i.name IdxName FROM sys.partitions p
JOIN sys.objects o ON p.object_id = o.object_id
JOIN sys.indexes i ON p.object_id = i.object_id
AND p.index_id = i.index_id WHERE p.hobt_id='290366346166272'
ObjName IdxName
SeqTable PKSeqID
It looks like the first query session was holding a lock on the table even after a successful execution.
So in essence, I was faced with two issues at the time. One was that the application was getting query timeouts sporadically. It affected certain aspects of the application, such as account activation, but not the overall production. Secondly my test was showing a strange problem that I had no answer to. In out of desperation we rebooted the DB server.
In the haste I forgot to check Page Life Expectancy. A few hours after we rebooted the db server, the problem returned again and this time I saw PLE bouncing between 1 and 200. So I knew we had a memory pressure issue. But why would it only affect this SP that was updating such a small table? The application was reading from and writing to other much larger tables without any issue even during the low PLE.
Thanking in advance for any comments.
August 24, 2013 at 1:03 pm
Open the Explorer window in SSMS by pressing the {f8} key. Then right click on the server/instance and select "Properties". In the properties window, select "Connection" and see if "implicit transactions" has a check mark against it. Don't change it... just not its condition.
Do similar for the current connection by clicking on "Tools, Options, Query Execution, SQL Server, ANSI" and the see if there's a check mark next to "SET IMPLICIT TRANSACTIONS".
If either one of these are set (checked), particularly the last one, that's your "problem". In the Implicit Transaction mode, every query (and a couple of other things) will automatically start a transaction which must either be committed or rolledback in order to release any locks it may place on the database. It's very much like how the default setting of Oracle work.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 24, 2013 at 2:11 pm
Hi Jeff,
Implicit Transaction setting for both SQL Server 2008 and my query sessions is OFF.
After the reboot I couldn't replicate the problem anymore, I guess not until we have another memory pressure issue.
Thanks.
August 24, 2013 at 4:11 pm
It sounds like your application does not have proper hanlding of query timeouts.
First you should ask yourself, if you want these suckers at all, and in such case what timeout value you want. In some applications they make sense. In some not at all. The default of 30 seconds is very bad default. (0 = forever is the only reasonable.)
When a timeout occurs, it is the client API that grews tired of waiting and sends SQL Server an attention signal, which instructs SQL Server to abort execution of the current batch. SQL Server will roll back the current statement. However, as along as XACT_ABORT is OFF (the default), SQL Server will not rollback an open transaction.
Assume that your SP_GetNextValue (by the way, the sp_ prefix is reserved for system procedures, and SQL Server first looks in the master/resource database for these) is called in a nested transaction and this transaction runs for some time. The process will then hold a lock on the row in SeqTable for a longer time, blocking other process that want to access the same key.
Eventually, they will time out, and if you take no further action, that statement started in SP_GetNextValue will still be a alive. Meaning that next time they call the procedure, they will acquire a lock - and hold it. And then all this mess starts.
It's instrumental what when you get a query timeout (and any other unexpected error for that matter) that you submit IF @@trancount > 0 ROLLBACK TRANSACTION.
I also recommend that you make sure that you have XACT_ABORT ON by default, although if you have CATCH handlers where you take alternate actions, beware that the transaction will always be doomed when XACT_ABORT is ON.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 24, 2013 at 4:27 pm
Hi Erland,
Thank you very much. I'll ask my our developers to look into the benefit of XACT_ABORT.
Your explanation is spot on on that the SP_GetNextValue was called by another SP. In this calling SP, SP_GetNextValue was enclosed in a transaction. The developer had since took it out of the transaction and we have seen much improvement since.
The calling SP already has something like IF @@error <> 0 ROLLBACK TRANSACTION.
The SP names all have an app name prefix. I'm calling it SP_xxx here to simplify the description. Thanks.
I really appreciate your input.
August 24, 2013 at 4:30 pm
Jan Arnoldus (8/24/2013)
The calling SP already has something like IF @@error <> 0 ROLLBACK TRANSACTION.
But that does not help if execution is cancelled because of a query timeout. SQL Server will stop executing. (And SQL Server knows nothing about the timeout. It only knows that it was told to stop working.)
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 24, 2013 at 4:52 pm
Thank you, Erland. I shall pass on your comments to the developers.
August 24, 2013 at 5:17 pm
One other thing that will help a bit. There's no need for the explicit transaction in the SP_GetNextValue sproc. It's just a waste of clock cycles because of the single UPDATE statement in the proc which will form its own transaction. Since the proc only has one statement in it, there's no real need for SET XACT_ABORT in the proc.
I'd also remove the BEGIN/END. A lot of other database engines require such a thing in stored procedures but it's not required in SQL Server. If someone asks about the code being more portable, remind them that the type of UPDATE that is being used is essential for helping prevent deadlocks on this high usage proc and its underlying table and that I can guarantee that form of UPDATE isn't portable to most other engines except for Sybase.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 24, 2013 at 10:14 pm
Thanks, Jeff et al. I'm glad that I posed my problem here. Now I can't think of a better way to learn from the truly experienced. I have to refrain from thanking too profusely as it would clutter the thread. I've read the two Helpful Links too and will make a better effort next time.
Much obliged to you all.
August 25, 2013 at 2:16 pm
There's one other thing that you might actually want to add to the proc if it's ever used by ccalls from the front end.
SET NOCOUNT ON;
That will keep the rowcounts from being created and returned to the GUI as a possible result set which, may in turn, be looked at as some kind of error. If nothing else, it saves some clock cycles and maybe a little I/O traffic.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply