question

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.