October 6, 2022 at 3:04 pm
This is one of the procs, which having issues:
/****** Object: UserDefinedTableType [dbo].[Identifier_type]******/
CREATE TYPE [dbo].[Identifier_type] AS TABLE(
[id] [BIGINT] NOT NULL
)
GO
CREATE PROCEDURE [dbo].[WorkerABC_prc]
(@workerIds Identifier_type READONLY)
AS
BEGIN
SELECT swm.SyncWorkerId
FROM SyncWorkerMap swm
INNER JOIN @workerIds w
ON swm.SyncWorkerId = w.id
WHERE swm.IsAuditWorker = 0;
END;
GO
October 6, 2022 at 9:00 pm
Thank you for posting the code - I believe the problem you have is going to be the estimation of rows for the table-valued parameter. You can try modifying the code using OPTION(RECOMPILE) or you can create a regular temp table from the parameter and use that temp table in the actual query, or you can experiment with enabling trace flag 2453.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 9, 2023 at 10:58 pm
What you are describing looks like the cache is being cleared when the database property is changed. The execution plan and data were removed from the cache and it will run longer the first time it executes.
Test this by querying the cache before changing the compatibility level. You can execute a query or procedure you wrote, and find it in the cache. Then change the database compatibility level and query the cache again. Most likely your query or procedure that was in the cache is now gone.
[font="Verdana"]Sal Young[/font]
[font="Verdana"]MCITP Database Administrator[/font]
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply