Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

we are experiencing unresponsive Sql Server 2008 R2 Web Edition. The only way how to connect to the server is via DAC.

The dm_os_tasks return 539 tasks almost all SUSPENDED or PENDING. There is one task with blocking_session_id same as session_id. It's waiting for LCK_M_SCH_M, the others tasks are waiting for TEMPOBJ.

Both LCK_M_SCH_M and TEMPOBJ are weird. There is no scripts for changing/creating/droping schema. We are not using any temporary tables, only table variables and table-UDT as input parameters for several functions and stored procedures.

The task with LCK_M_SCH_M is inline-table-function with table-UDT (TblGuid). The UDT TblGuid is table with single column:

CREATE TYPE [dbo].[TblGuid] AS TABLE(
    [Guid] [uniqueidentifier] NULL
)

The table-valued-function is unchanged for months and is called a lot. This behaviour became "out of nowhere".

If we restart the sql server service, all became ok. This state occures +- 10x in day.

I suspect the same session_id and blocking_session_id is invalid state and could not appears. Am I right?

EDIT: The problematic function is:

CREATE FUNCTION [dbo].[GetUnits_By_IDsBattleTargets_As_CurrentUnitModel] 
(   
    @IDsBattleTargets [dbo].[TblGuid] READONLY,
    @IDCurrentPlayer uniqueidentifier
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT
        DISTINCT
        U.IDUnitClass,
        U.IDBattleTarget,
        U.Amount
    FROM @IDsBattleTargets AS IDS
    INNER JOIN [dbo].[tblBattleTarget] AS BT
        ON BT.BattleTargetID = IDS.Guid
    INNER JOIN [dbo].[tblUnit] AS U
        ON U.IDBattleTarget = IDS.Guid
    LEFT JOIN [cache].[tblHeroSharingWithPlayerClan] AS HSWPC
        ON HSWPC.IDHero = IDS.Guid
            AND HSWPC.IDPlayer = @IDCurrentPlayer
    LEFT JOIN [cache].[tblHeroSharingWithAlliance] AS HSWPA
        ON HSWPA.IDHero = IDS.Guid
            AND HSWPA.IDPlayer = @IDCurrentPlayer
    WHERE U.Amount > 0
        AND (BT.IDBattleTargetOwner = @IDCurrentPlayer
            OR HSWPA.IDPlayer = @IDCurrentPlayer
            OR HSWPC.IDPlayer = @IDCurrentPlayer)
)

The function is called from another function or from C# (.NET 4.0) via SqlConnection,SqlCommand and SqlParameter.

SOLUTION: After advices I decided for change TVP to something another. I choosed the varbinary(max) where one Guid/uniqueidentifier value has 16 bytes. I created sql-clr function with varbinary(max) input and table output Guid uniqueidentifier. I won't use spliting text because it's not much efficient for my purpose (this function is called very often). After the 30 hours in production (the problem occured only on one server in production, and we was not able reproduce them in testing environment) there was no problem. Usualy the problem occures several times a day. This will be propably solution.

In the final the function looks:

CREATE FUNCTION [dbo].[GetUnits_By_IDsBattleTargets_As_CurrentUnitModel] 
(   
    @IDsBattleTargets varbinay(max),
    @IDCurrentPlayer uniqueidentifier
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT
        DISTINCT
        U.IDUnitClass,
        U.IDBattleTarget,
        U.Amount
    FROM [dbo].[GetGuidsFromBinary](@IDsBattleTargets) AS IDS
    INNER JOIN [dbo].[tblBattleTarget] AS BT
        ON BT.BattleTargetID = IDS.Guid
    INNER JOIN [dbo].[tblUnit] AS U
        ON U.IDBattleTarget = IDS.Guid
    LEFT JOIN [cache].[tblHeroSharingWithPlayerClan] AS HSWPC
        ON HSWPC.IDHero = IDS.Guid
            AND HSWPC.IDPlayer = @IDCurrentPlayer
    LEFT JOIN [cache].[tblHeroSharingWithAlliance] AS HSWPA
        ON HSWPA.IDHero = IDS.Guid
            AND HSWPA.IDPlayer = @IDCurrentPlayer
    WHERE U.Amount > 0
        AND (BT.IDBattleTargetOwner = @IDCurrentPlayer
            OR HSWPA.IDPlayer = @IDCurrentPlayer
            OR HSWPC.IDPlayer = @IDCurrentPlayer)
)

If someone will have similar issue, please contact me. I would like to more investigate this behaviour.

share|improve this question
add comment

migrated from stackoverflow.com Jun 19 '12 at 20:21

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 4 down vote accepted

It's not blocking itself, this is just an odd artifact of how the waits are being reported. (And no, this isn't an invalid state, it happens all the time and is not indicative of any bug or deeper problem.) This has been reported a few times with table variables, and I think TVPs is a relatively newer one where there might still be some unresolved issues.

Some minor thoughts on optimization. First of all, why is the Guid column in the table type nullable? Why isn't it a primary key? The optimizer will be able to do a lot more with this if it understands the cardinality better, and your C# code is certainly capable of ensuring that it doesn't populate the parameter with duplicates or null values.

CREATE TYPE [dbo].[TblGuid] AS TABLE(
    [Guid] [uniqueidentifier] PRIMARY KEY
);

Now, the function has a lot of joins that I think are unnecessary, and if you can perform the grouping outside the function that may be better. I don't have sample data to verify that this returns the same results as your existing function, but if it's off it shouldn't be off by much. When you get it returning the same results, please do compare the actual execution plans and I am confident you will find this version is better.

CREATE FUNCTION [dbo].[GetUnits_By_IDsBattleTargets_As_CurrentUnitModel] 
(   
    @IDsBattleTargets [dbo].[TblGuid] READONLY,
    @IDCurrentPlayer  UNIQUEIDENTIFIER
)
RETURNS TABLE
WITH SCHEMABINDING -- added this 
AS
RETURN 
(
    SELECT
        U.IDUnitClass,
        U.IDBattleTarget,
        U.Amount
    FROM dbo.tblUnit AS U
    WHERE U.Amount > 0 
    AND EXISTS 
    (
      SELECT 1 FROM dbo.tblBattleTarget AS BT 
        WHERE BT.BattleTargetID IN (SELECT Guid FROM @IDsBattleTargets
          WHERE Guid = U.IDBattleTarget))
    )
    AND EXISTS 
    ( 
      SELECT 1 FROM cache.[tblHeroSharingWithPlayerClan]
        WHERE IDHero IN (SELECT Guid FROM @IDsBattleTargets)
        AND IDPlayer = @IDCurrentPlayer
      UNION ALL 
      SELECT 1 FROM cache.[tblHeroSharingWithAlliance]
        WHERE IDHero IN (SELECT Guid FROM @IDsBattleTargets)
        AND IDPlayer = @IDCurrentPlayer
    )
    -- only if necessary:
    -- GROUP BY U.IDUnitCass, U.IDBattleTarget, U.Amount
);
share|improve this answer
    
The function is inline-table-valued function. I need pass the array of Guid to the function and I need it as function (for further JOINs). The only workaround for using TblGuid type I know the parsing CSV passed as nvarchar. However it's not much good in performance. Is there another way? –  TcKs Jun 18 '12 at 19:26
    
How are you splitting the GUIDs now? Try my answer here which does exactly that - splits a comma-separated list of GUIDs, using an inline table-valued function. If you want help improving your function, please post the function code. You should also let us know how many GUIDs we're talking about. –  Aaron Bertrand Jun 18 '12 at 19:30
    
I know difference between multistatement and inline function :). I added the "edit" section to the question. –  TcKs Jun 18 '12 at 19:36
    
That wasn't clear initially, sorry. Anyway I am just suggesting a possible workaround. While the TVP might be a logical choice because it should be faster, if it is causing this issue you might not get to the bottom of why, or it may take a long time. In the meantime an "old-school" method might not be as fast, but if it doesn't hang up your server and require you to use DAC to overcome, I'd prefer that. A rocket will get me to Buffalo faster than my car, but if I can't steer the rocket, I think I'd rather arrive alive than late. –  Aaron Bertrand Jun 18 '12 at 19:40
    
Or arrive alive and late, even. (Must be Monday.) –  Aaron Bertrand Jun 18 '12 at 19:47
show 6 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.