Morgan Leppink avatar image
Morgan Leppink asked

Simplest UDF ever blocking entire database

I have been fighting with this for months now, and I give up. I need suggestions from one of the knowledgeable gurus here on SSC. We're running SQL 2012. I have a fairly busy transactional database for an eCommerce application. In the application, each item in an order can have sveral different statuses, ranging from 0 to eventually whatever. These statuses are saved in a simple lookup table that currently has exactly 6 rows and 8 columns beyond the PK. I need to be able to look up a specific status "number" (the PK) and determine whether certain things apply for that status. This is needed EVERYWHERE and for everything, pretty much. IMPORTANT: This is a lookup table that NEVER gets updated or changed. Just to be clear, it's static lookup data only. Here is the table structure: CREATE TABLE [dbo].[Status]( [Status] [INT] NOT NULL, [Code] [VARCHAR](20) NOT NULL, [Description] [VARCHAR](60) NOT NULL, [IsReportable] [BIT] NOT NULL CONSTRAINT [DF_Status_IsReportable] DEFAULT ((1)), [IsValuable] [BIT] NOT NULL CONSTRAINT [DF_Status_IsValuable] DEFAULT ((1)), [IsAcceptable] [BIT] NOT NULL CONSTRAINT [DF_Status_IsAcceptable] DEFAULT ((1)), [IsVisible] [BIT] NOT NULL CONSTRAINT [DF_Status_IsVisible] DEFAULT ((1)), [IsRefunded] [BIT] NOT NULL CONSTRAINT [DF_Status_IsRefund] DEFAULT ((0)), [IsTenderable] [BIT] NOT NULL CONSTRAINT [DF_Status_IsTenderable] DEFAULT ((0)), CONSTRAINT [PK_TicketStatus] PRIMARY KEY CLUSTERED ( [Status] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Here's the current data: Status Code Description IsReportable IsValuable IsAcceptable IsVisible IsRefunded IsTenderable 0 NORMAL Issued 1 1 1 1 0 1 1 REFUND Refunded 1 1 0 0 1 0 2 REISSUE Reissued 0 0 0 0 1 0 3 RELEASE-UNPAID Released / Unpaid 0 0 0 0 1 0 4 RELEASE-RESALE Released for Resale 1 1 0 1 0 1 5 RELEASE-PARTIAL Released / Partially Paid 1 1 0 0 0 1 So, no big deal, right? The problem is, at seemingly random times, and rarely for no apparent reason, the entire database starts blocking, one SPID blocking another, until nearly the entire thing is in brain freeze mode. I have a script I stole from somewhere (probably SSC) that shows me what each SPID is doing, whether it's blocked, and if so, who is blocking it. USE [master] GO DECLARE @DatabaseFilter NVARCHAR(MAX) SET @DatabaseFilter = 'ABC' DECLARE @Table TABLE( SPID INT, [Status] VARCHAR(MAX), [Login] VARCHAR(MAX), [HostName] VARCHAR(MAX), [BlockedBy] VARCHAR(MAX), [DBName] VARCHAR(MAX), [Command] VARCHAR(MAX), [CPUTime] INT, [DiskIO] INT, [LastBatch] VARCHAR(MAX), [ProgramName] VARCHAR(MAX), SPID_1 INT, REQUESTID INT ) INSERT INTO @Table EXEC [master].[dbo].[sp_who2] DECLARE @MySPID INT; SELECT @MySPID = @@SPID SELECT *, [master].[dbo].[udf_GetLastSQLExecutedForSPID](SPID) AS [Query] FROM @Table WHERE [DBName] = CASE WHEN @DatabaseFilter = '' THEN [DBName] ELSE @DatabaseFilter END AND [SPID] <> @MySPID ORDER BY [BlockedBy] DESC, SPID, [LastBatch] DESC The script works great, and when I run this under these blocking conditions, I am able to trace back and see who started it all. And when I do that, the offending SPID is ALWAYS running the simple UDF I wrote to lookup up whether or not a certain status is "tenderable." If I kill that one SPID, all the blocks instantly go away all the way up the line and we're back in business. Here's the UDF, including several things I have tried (you can see them commented in the code). Here's the question: HOW CAN THIS STUPID, SIMPLE LITTLE UDF BE BLOCKING THE ENTIRE DATABASE? Any help you can offer would be MUCH MUCH appreciated, not only by me, but by our customers as well. -- ========================================================================== -- Author: Morgan Leppink -- Description: Check IsTenderable setting for a specified status code. -- ItemType is ignored for now. -- ========================================================================== CREATE FUNCTION [dbo].[udf_StatusIsTenderable] ( @Status int, @ItemType char(1) ) RETURNS BIT AS BEGIN DECLARE @Result BIT = 0 -- Still getting some locking. Added READPAST on 9/11/2017 IF EXISTS( SELECT TOP 1 1 FROM [dbo].[Status] WITH (READPAST) WHERE [Status] = @Status AND [IsTenderable] = 1 ) SET @Result = 1 RETURN @Result -- Changed this UDF 8/30/2017 Morgan -- for some reason this UDF was blocking everything. SOMETIMES. Even WITHOUT the (NOLOCK) hint. -- Anyway, trying the above approach using EXISTS() and we'll see what happens ^^ --SELECT @Result = [IsTenderable] -- FROM [dbo].[Status] -- WITH (NOLOCK) -- WHERE [Status] = @Status END Is there some other less invasive approach I can be using? Suggestions on how to troubleshoot this deeper? I have UDFs to check for each of the other statuses also, and those are never a problem. What am I missing? Thanks in advance.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

Kev Riley avatar image
Kev Riley answered
You might want to look at [Adam Machanic's SP_WhoISActive procedure][1] as a better way of working out what is blocking and why. I'm not sure what `udf_GetLastSQLExecutedForSPID` is doing but if it is getting the last SQL that was run, then often in high transaction systems this can be out of date by the time you've queried it! Anyhow onto the real problem.... When the UDF is the lead blocker, what is it waiting on? Again sp_WhoIsActive will help you identify that. Scalar UDFs prevent parallelism so you may be forcing your entire application into a single-threaded bottleneck! See So what I would do is: 1. really identify what is the lead blocker 2. if this UDF is still the issue, try turning this scalar udf into an inline table-valued function 3. if that helps consider removing this function altogether - you can get the same functionality with a simple join [1]:
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Morgan Leppink avatar image Morgan Leppink commented ·
Kev - Thanks for the response - WhoIsActive was a big help, and I think I see what is happening now. I'm pretty sure that it IS the fact that this UDF is scalar, because when I need to update a lot of records it has to be called over and over again, once for every record. Since that bug update is happening inside a transaction, it's not really BLOCKING it's just taking forever. Make some changes based on your suggestions - changed this one update to use a CTE and a JOIN so we'll see if that fixes it. I'm betting it will. Thanks again.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.