Sample Header Ad - 728x90

Recompilations in stored procedure SQL Server

0 votes
0 answers
50 views
I have this piece of code inside a stored procedure. The stored procedure executes around 28k times, but the number of re-compilations is around 170k. I think this piece of code may be one of the reasons for the recompilations. My guess is because an index is being created on the temp table after the data is inserted, it's causing a recompile. What do you think about it? What are some other reasons why this SP may be getting recompiled so many times?
-- Create a temporary table to store user activity logs
CREATE TABLE #tmpUserLogs  (
    log_id INT PRIMARY KEY IDENTITY(1,1),
    session_id UNIQUEIDENTIFIER,
    activity_status UNIQUEIDENTIFIER );

-- Create sample session data
DECLARE @UserSessions TABLE (
    session_id UNIQUEIDENTIFIER PRIMARY KEY,
    user_id INT,
    status_category INT,
    session_type_id INT,
    approval_flag INT,
    activity_status UNIQUEIDENTIFIER,
    is_restricted BIT,
    timestamp_last_updated DATETIME );

-- Create sample user profiles 
DECLARE @UserProfiles TABLE (
    user_id INT PRIMARY KEY,
    session_id UNIQUEIDENTIFIER );

-- Create sample activity status data 
DECLARE @ActivityStatus TABLE (
    activity_status UNIQUEIDENTIFIER PRIMARY KEY );

-- Create sample status filter data 
DECLARE @StatusFilter TABLE (
status_id UNIQUEIDENTIFIER PRIMARY KEY );
    
-- Insert mock data 
INSERT INTO @UserSessions  VALUES 
    (NEWID(), 101, 1, 2, 1, NEWID(), 0, GETDATE()),
    (NEWID(), 102, 2, 1, 0, NEWID(), 1, GETDATE()),
    (NEWID(), 103, 3, 3, 1, NEWID(), 0, GETDATE());

INSERT INTO @UserProfiles 
VALUES (101, (SELECT TOP 1 session_id FROM @UserSessions)); 
INSERT INTO @ActivityStatus VALUES (NEWID()); 
INSERT INTO @StatusFilter VALUES (NEWID());

-- Define parameters 
DECLARE @applySecurityCheck BIT = 0; 
DECLARE @startIndex INT = 0; 
DECLARE @batchSize INT = 10;
    
-- Insert user activity logs into the temp table 
INSERT INTO #tmpUserLogs 
SELECT u.session_id, a.activity_status 
FROM @UserSessions u 
INNER JOIN @UserProfiles up ON up.session_id = u.session_id 
INNER JOIN @ActivityStatus a ON a.activity_status = u.activity_status
INNER JOIN @StatusFilter sf ON 
    (@applySecurityCheck = 0 AND sf.status_id = u.activity_status) 
    OR 
    (
        @applySecurityCheck = 1 
        AND u.is_restricted = 1 
        AND sf.status_id = u.activity_status
    ) 
ORDER BY u.timestamp_last_updated DESC 
OFFSET @startIndex ROWS 
FETCH NEXT @batchSize ROWS ONLY;
    
/* Create an index on session_id */ 
CREATE NONCLUSTERED INDEX idx_session 
ON #tmpUserLogs(session_id) INCLUDE (activity_status);
Asked by lifeisajourney (751 rep)
Feb 3, 2025, 08:25 PM
Last activity: Feb 5, 2025, 05:24 AM