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.
