0

I have a stored procedure that is being passed a User Defined Table Type to retrieve 1 to many records back from a table that is filtered down by a join instead of building out dynamic sql to generate a where clause or calling the procedure once for every needed set of data.

The user defined table type is defined as

CREATE TYPE [dbo].[GuidList] AS TABLE(
    [ID1] [uniqueidentifier] NOT NULL,
    [ID2] [uniqueidentifier] NOT NULL
)
GO

The procedure is defined as

(@IDs GuidList READONLY)
as
BEGIN

    SELECT
         s.column1, s.column2, s.column3
    FROM 
        dbo.table s 
    INNER JOIN @IDs ids 
        ON (s.column4 = ids.ID1 and s.column5 = ids.ID2)
    WHERE
        s.column3 = '297EFEC7-81DE-4E97-8B36-9FA8CDE2185A' and s.column2 = 5
END

Recent load tests are showing a lot of THREADPOOL waits once we hit a certain batch request/s level (which is about 20% lower than the last time we had THREADPOOL issues.)

Tracing down what is getting blocked I've noticed a lot of PAGELATCH_SH and PAGELATCH_EX in the tempdb as well as drop table DDL calls that are take 10s of seconds to complete. From what I've been able to gather in the investigation many of the issues I'm seeing with the blocking and tempdb appear to be caused by this procedure that is using that user defined table type.

I've seen and read and read (Thanks @brentozar) the performance issue that udf scalars have with not going parallel and producing an less than ideal query plan.

Do user defined table types have the same performance issues as UDFs?

I'm running a 2008r2 EE version (for reasons) with 128 cores and 2TB of memory that is configured with MAXDOP of 8 and Parallelism Threshold of 50.

7
  • Are the pagelatch waits on allocation pages? How many tempdb files?
    – Dan Guzman
    Jul 9 '21 at 18:37
  • How many rows are you passing in the UDT? Can you share the execution plan (pastetheplan.com) Jul 9 '21 at 18:39
  • @DanGuzman 37 datafiles.
    – Aaron
    Jul 9 '21 at 19:10
  • @LowlyDBA-JohnM Average number of rows is 400ish. Each request can be larger or smaller based on certain criteria. So the profiler isn't capturing the input value of the parameter, if I can get a plan generated I'll post an update.
    – Aaron
    Jul 9 '21 at 19:15
  • SQL Profiler can slow down Table Parameters massively, because it pulls out every single value and converts it to text. Are you seeing this with profiler off? Jul 11 '21 at 1:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.