Poor Performance with Lazy Spool Operator

[apollock]

Our application is having an issue where a query in a stored procedure periodically gets a bad plan with a Lazy Spool Operator that changes the query execution time from 5ms to 900ms and kills our CPU. We are running x64 SQL 2005 SP2 on Windows 2003 SP2.

We thought it might be parameter sniffing that was causing the bad plan, so we re-wrote the query to disable parameter sniffing. Unfortunately, that made the problem worse as we always got the Lazy Spool Operator.

I'd appreciate it if anyone could explain how to re-write the query to avoid getting the Lazy Spool Operator.

I tried to post the xmlshowplans for the good and bad plans but it did not work. Please e-mail me at [e-mail] to get the query plans.

Thanks!
Alex Pollock

Here is the query:

end
else
begin
-- team access display = 0, so all access settings should come from the default even if settings
-- are by team.
SELECT LSN_ENTRIES.ENTRY_ID, LSN_ENTRIES.PARENT_ID, LSN_ENTRIES.COURSE_ID, LSN_ENTRIES.EDITABLE_BY,
LSN_ENTRIES.VIEWABLE_BY, LSN_ENTRIES.TRACKING, LSN_ACCESS.HIDDEN, LSN_ENTRIES.PASSWORD,
LSN_ACCESS.TEAM_ID AS USER_GROUP, LSN_ENTRIES.START_DATE, LSN_ENTRIES.END_DATE, LSN_ENTRIES.ENTRY_AUTHOR,
LSN_ENTRIES.ENTRY_CREATED, LSN_ENTRIES.SEQUENCE, LSN_OBJECTS.OBJECT_ID, LSN_OBJECTS.OBJECT_TYPE,
LSN_OBJECTS.EDIT_MASK, LSN_OBJECTS.LINK_MASK, LSN_OBJECTS.TITLE, LSN_OBJECTS.SUBTITLE, LSN_OBJECTS.PAGE_FORMAT,
LSN_OBJECTS.LINK_URL, LSN_OBJECTS.LINK_TARGET, LSN_OBJECTS.OBJECT_EDITABLE_BY, LSN_OBJECTS.ICON_URL,
LSN_OBJECTS.HELP_URL, LSN_OBJECTS.OBJECT_AUTHOR, LSN_OBJECTS.OBJECT_CREATED, LSN_OBJECTS.DELETED,
LSN_OBJECTS.DRM_PROTECTED, LSN_OBJECTS.ATTRIBUTES, LSN_OBJECTS.PAGE_TEXT
FROM LSN_ENTRIES
JOIN LSN_OBJECTS ON LSN_OBJECTS.OBJECT_ID = LSN_ENTRIES.OBJECT_ID
JOIN LSN_ACCESS ON LSN_ACCESS.ENTRY_ID = LSN_ENTRIES.ENTRY_ID AND LSN_ACCESS.COURSE_ID = LSN_ENTRIES.COURSE_ID
WHERE LSN_ENTRIES.PARENT_ID = @PARENT_ID AND LSN_ENTRIES.COURSE_ID = @COURSE_ID
AND (LSN_OBJECTS.DELETED <> 1) AND (LSN_ENTRIES.VIEWABLE_BY <= @USER_RIGHTS ) AND (LSN_ACCESS.HIDDEN = 0)
AND ((LSN_ENTRIES.START_DATE IS NULL) OR (LSN_ENTRIES.START_DATE < @CURRENT_DATE))
AND ((LSN_ENTRIES.END_DATE IS NULL) OR (LSN_ENTRIES.END_DATE > @CURRENT_DATE))
--            AND CHARINDEX(';'+LSN_ACCESS.TEAM_ID+';', @TEAM_LIST) > 0
AND (LSN_ACCESS.TEAM_ID IN (SELECT team_id from team_members where user_id in (select user_id from course_roster where course_id = @course_id and user_id = @user_id) and course_id = @course_id) OR LSN_ACCESS.TEAM_ID = 'ALL')
AND LSN_ACCESS.TEAM_ID = (SELECT TOP 1 TEAM_ID FROM LSN_ACCESS
WHERE LSN_ACCESS.ENTRY_ID = LSN_ENTRIES.ENTRY_ID AND LSN_ACCESS.COURSE_ID = LSN_ENTRIES.COURSE_ID
--            AND CHARINDEX(';'+LSN_ACCESS.TEAM_ID+';', @TEAM_LIST) > 0
AND (LSN_ACCESS.TEAM_ID IN (SELECT team_id from team_members where user_id in (select user_id from course_roster where course_id = @course_id and user_id = @user_id) and course_id = @course_id) OR LSN_ACCESS.TEAM_ID = 'ALL')
AND (CASE LSN_ENTRIES.USER_GROUP WHEN 'ALL' THEN LSN_ACCESS.TEAM_ID ELSE 'ALL' END = 'ALL')
ORDER BY TEAM_PRIORITY desc, TEAM_ID)
ORDER BY LSN_ENTRIES.COURSE_ID, LSN_ENTRIES.SEQUENCE, LSN_ENTRIES.ENTRY_CREATED

Here is the original stored proc that contains the query above:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
-- @TEAMACCESS_DISPLAY is the value for environment variable TEAMACCESS_DISPLAY
-- @PARENT_ID is the parent entry id for the folder being displayed
-- @ISEDITABLE is 1/0 based on whether the user entering the folder has editor rights -- Content Editor isEditable()
-- @TEAM_LIST is a list of teams which the user is a member.  The list should be ; separated.  Example ';ALL;Team 1;Team 2;Team 10;'
-- @USER_RIGHTS is the right of the user entering the folder
-- @COURSE_ID is the id for the course in which the folder resides
-- @CURRENT_DATE is the current date time used to validate against the start and end date
 
/*
CJ:
removed line: AND CHARINDEX(';'+LSN_ACCESS.TEAM_ID+';', @TEAM_LIST) > 0
added line: AND (LSN_ACCESS.TEAM_ID IN (SELECT team_id from team_members where user_id in (select user_id from course_roster where course_id = @course_id and user_id = @user_id) and course_id = @course_id) OR LSN_ACCESS.TEAM_ID = 'ALL')
removed Team_list parameter
added User_id parameter
*/
CREATE PROCEDURE [dbo].[GetUserAccessFolderList]
@TEAMACCESS_DISPLAY smallint,
@PARENT_ID nvarchar(100),
@ISEDITABLE bit,
--    @TEAM_LIST nvarchar(250),
@USER_RIGHTS smallint,
@COURSE_ID nvarchar(250),
@CURRENT_DATE datetime,
@USER_ID nvarchar(50)
AS
if @ISEDITABLE = 1
begin
SELECT LSN_ENTRIES.ENTRY_ID, LSN_ENTRIES.PARENT_ID, LSN_ENTRIES.COURSE_ID, LSN_ENTRIES.EDITABLE_BY, LSN_ENTRIES.VIEWABLE_BY,
LSN_ENTRIES.TRACKING, LSN_ENTRIES.HIDDEN, LSN_ENTRIES.PASSWORD, LSN_ENTRIES.USER_GROUP, LSN_ENTRIES.START_DATE, LSN_ENTRIES.END_DATE,
LSN_ENTRIES.ENTRY_AUTHOR, LSN_ENTRIES.ENTRY_CREATED, LSN_ENTRIES.SEQUENCE, LSN_OBJECTS.OBJECT_ID, LSN_OBJECTS.OBJECT_TYPE,
LSN_OBJECTS.EDIT_MASK, LSN_OBJECTS.LINK_MASK, LSN_OBJECTS.TITLE, LSN_OBJECTS.SUBTITLE, LSN_OBJECTS.PAGE_FORMAT, LSN_OBJECTS.LINK_URL,
LSN_OBJECTS.LINK_TARGET, LSN_OBJECTS.OBJECT_EDITABLE_BY, LSN_OBJECTS.ICON_URL, LSN_OBJECTS.HELP_URL, LSN_OBJECTS.OBJECT_AUTHOR,
LSN_OBJECTS.OBJECT_CREATED, LSN_OBJECTS.DELETED, LSN_OBJECTS.DRM_PROTECTED, LSN_OBJECTS.ATTRIBUTES, LSN_OBJECTS.PAGE_TEXT
FROM LSN_ENTRIES JOIN LSN_OBJECTS ON (LSN_OBJECTS.OBJECT_ID = LSN_ENTRIES.OBJECT_ID)
WHERE LSN_ENTRIES.PARENT_ID = @PARENT_ID AND LSN_ENTRIES.COURSE_ID = @COURSE_ID AND (LSN_OBJECTS.DELETED <> 1)
ORDER BY LSN_ENTRIES.COURSE_ID, LSN_ENTRIES.SEQUENCE, LSN_ENTRIES.ENTRY_CREATED
end
else
begin
if @TEAMACCESS_DISPLAY = 2
begin
SELECT LSN_ENTRIES.ENTRY_ID, LSN_ENTRIES.PARENT_ID, LSN_ENTRIES.COURSE_ID, LSN_ENTRIES.EDITABLE_BY,
LSN_ACCESS.VIEWABLE_BY, LSN_ACCESS.TRACKING, LSN_ACCESS.HIDDEN, LSN_ACCESS.PASSWORD,
LSN_ACCESS.TEAM_ID AS USER_GROUP, LSN_ACCESS.START_DATE, LSN_ACCESS.END_DATE, LSN_ENTRIES.ENTRY_AUTHOR,
LSN_ENTRIES.ENTRY_CREATED, LSN_ENTRIES.SEQUENCE, LSN_OBJECTS.OBJECT_ID, LSN_OBJECTS.OBJECT_TYPE,
LSN_OBJECTS.EDIT_MASK, LSN_OBJECTS.LINK_MASK, LSN_OBJECTS.TITLE, LSN_OBJECTS.SUBTITLE, LSN_OBJECTS.PAGE_FORMAT,
LSN_OBJECTS.LINK_URL, LSN_OBJECTS.LINK_TARGET, LSN_OBJECTS.OBJECT_EDITABLE_BY, LSN_OBJECTS.ICON_URL,
LSN_OBJECTS.HELP_URL, LSN_OBJECTS.OBJECT_AUTHOR, LSN_OBJECTS.OBJECT_CREATED, LSN_OBJECTS.DELETED,
LSN_OBJECTS.DRM_PROTECTED, LSN_OBJECTS.ATTRIBUTES, LSN_OBJECTS.PAGE_TEXT
FROM LSN_ENTRIES
JOIN LSN_OBJECTS ON LSN_OBJECTS.OBJECT_ID = LSN_ENTRIES.OBJECT_ID
JOIN LSN_ACCESS ON LSN_ACCESS.ENTRY_ID = LSN_ENTRIES.ENTRY_ID AND LSN_ACCESS.COURSE_ID = LSN_ENTRIES.COURSE_ID
WHERE LSN_ENTRIES.PARENT_ID = @PARENT_ID AND LSN_ENTRIES.COURSE_ID = @COURSE_ID
AND (LSN_OBJECTS.DELETED <> 1)
--        AND CHARINDEX(';'+LSN_ACCESS.TEAM_ID+';', @TEAM_LIST) > 0
AND (LSN_ACCESS.TEAM_ID IN (SELECT team_id from team_members where user_id in (select user_id from course_roster where course_id = @course_id and user_id = @user_id) and course_id = @course_id) OR LSN_ACCESS.TEAM_ID = 'ALL')
AND LSN_ACCESS.TEAM_ID = (SELECT TOP 1 TEAM_ID FROM LSN_ACCESS
WHERE LSN_ACCESS.ENTRY_ID = LSN_ENTRIES.ENTRY_ID AND LSN_ACCESS.COURSE_ID = LSN_ENTRIES.COURSE_ID
--AND CHARINDEX(';'+LSN_ACCESS.TEAM_ID+';', @TEAM_LIST) > 0
AND (LSN_ACCESS.TEAM_ID IN (SELECT team_id from team_members where user_id in (select user_id from course_roster where course_id = @course_id and user_id = @user_id) and course_id = @course_id) OR LSN_ACCESS.TEAM_ID = 'ALL')
AND (LSN_ACCESS.VIEWABLE_BY <= @USER_RIGHTS ) AND (LSN_ACCESS.HIDDEN = 0)
AND ((LSN_ACCESS.START_DATE IS NULL) OR (LSN_ACCESS.START_DATE < @CURRENT_DATE))
AND ((LSN_ACCESS.END_DATE IS NULL) OR (LSN_ACCESS.END_DATE > @CURRENT_DATE))
ORDER BY TEAM_PRIORITY desc, TEAM_ID)
ORDER BY LSN_ENTRIES.COURSE_ID, LSN_ENTRIES.SEQUENCE, LSN_ENTRIES.ENTRY_CREATED
end
else
begin
if @TEAMACCESS_DISPLAY = 1
begin
SELECT LSN_ENTRIES.ENTRY_ID, LSN_ENTRIES.PARENT_ID, LSN_ENTRIES.COURSE_ID, LSN_ENTRIES.EDITABLE_BY,
LSN_ACCESS.VIEWABLE_BY, LSN_ACCESS.TRACKING, LSN_ACCESS.HIDDEN, LSN_ACCESS.PASSWORD,
LSN_ACCESS.TEAM_ID AS USER_GROUP, LSN_ACCESS.START_DATE, LSN_ACCESS.END_DATE, LSN_ENTRIES.ENTRY_AUTHOR,
LSN_ENTRIES.ENTRY_CREATED, LSN_ENTRIES.SEQUENCE, LSN_OBJECTS.OBJECT_ID, LSN_OBJECTS.OBJECT_TYPE,
LSN_OBJECTS.EDIT_MASK, LSN_OBJECTS.LINK_MASK, LSN_OBJECTS.TITLE, LSN_OBJECTS.SUBTITLE, LSN_OBJECTS.PAGE_FORMAT,
LSN_OBJECTS.LINK_URL, LSN_OBJECTS.LINK_TARGET, LSN_OBJECTS.OBJECT_EDITABLE_BY, LSN_OBJECTS.ICON_URL,
LSN_OBJECTS.HELP_URL, LSN_OBJECTS.OBJECT_AUTHOR, LSN_OBJECTS.OBJECT_CREATED, LSN_OBJECTS.DELETED,
LSN_OBJECTS.DRM_PROTECTED, LSN_OBJECTS.ATTRIBUTES, LSN_OBJECTS.PAGE_TEXT
FROM LSN_ENTRIES
JOIN LSN_OBJECTS ON LSN_OBJECTS.OBJECT_ID = LSN_ENTRIES.OBJECT_ID
JOIN LSN_ACCESS ON LSN_ACCESS.ENTRY_ID = LSN_ENTRIES.ENTRY_ID AND LSN_ACCESS.COURSE_ID = LSN_ENTRIES.COURSE_ID
WHERE LSN_ENTRIES.PARENT_ID = @PARENT_ID AND LSN_ENTRIES.COURSE_ID = @COURSE_ID
AND (LSN_OBJECTS.DELETED <> 1)
--AND CHARINDEX(';'+LSN_ACCESS.TEAM_ID+';', @TEAM_LIST) > 0
AND (LSN_ACCESS.TEAM_ID IN (SELECT team_id from team_members where user_id in (select user_id from course_roster where course_id = @course_id and user_id = @user_id) and course_id = @course_id) OR LSN_ACCESS.TEAM_ID = 'ALL')
 
AND LSN_ACCESS.TEAM_ID = (SELECT TOP 1 TEAM_ID FROM LSN_ACCESS
WHERE LSN_ACCESS.ENTRY_ID = LSN_ENTRIES.ENTRY_ID AND LSN_ACCESS.COURSE_ID = LSN_ENTRIES.COURSE_ID
--            AND CHARINDEX(';'+LSN_ACCESS.TEAM_ID+';', @TEAM_LIST) > 0
AND (LSN_ACCESS.TEAM_ID IN (SELECT team_id from team_members where user_id in (select user_id from course_roster where course_id = @course_id and user_id = @user_id) and course_id = @course_id) OR LSN_ACCESS.TEAM_ID = 'ALL')
 
AND (LSN_ACCESS.VIEWABLE_BY <= @USER_RIGHTS ) AND (LSN_ACCESS.HIDDEN = 0)
AND ((LSN_ACCESS.START_DATE IS NULL) OR (LSN_ACCESS.START_DATE < @CURRENT_DATE))
AND ((LSN_ACCESS.END_DATE IS NULL) OR (LSN_ACCESS.END_DATE > @CURRENT_DATE)) AND
(CASE LSN_ENTRIES.USER_GROUP WHEN 'ALL' THEN LSN_ACCESS.TEAM_ID ELSE 'ALL' END = 'ALL')
ORDER BY TEAM_PRIORITY desc, TEAM_ID)
ORDER BY LSN_ENTRIES.COURSE_ID, LSN_ENTRIES.SEQUENCE, LSN_ENTRIES.ENTRY_CREATED
end
else
begin
-- team access display = 0, so all access settings should come from the default even if settings
-- are by team.
SELECT LSN_ENTRIES.ENTRY_ID, LSN_ENTRIES.PARENT_ID, LSN_ENTRIES.COURSE_ID, LSN_ENTRIES.EDITABLE_BY,
LSN_ENTRIES.VIEWABLE_BY, LSN_ENTRIES.TRACKING, LSN_ACCESS.HIDDEN, LSN_ENTRIES.PASSWORD,
LSN_ACCESS.TEAM_ID AS USER_GROUP, LSN_ENTRIES.START_DATE, LSN_ENTRIES.END_DATE, LSN_ENTRIES.ENTRY_AUTHOR,
LSN_ENTRIES.ENTRY_CREATED, LSN_ENTRIES.SEQUENCE, LSN_OBJECTS.OBJECT_ID, LSN_OBJECTS.OBJECT_TYPE,
LSN_OBJECTS.EDIT_MASK, LSN_OBJECTS.LINK_MASK, LSN_OBJECTS.TITLE, LSN_OBJECTS.SUBTITLE, LSN_OBJECTS.PAGE_FORMAT,
LSN_OBJECTS.LINK_URL, LSN_OBJECTS.LINK_TARGET, LSN_OBJECTS.OBJECT_EDITABLE_BY, LSN_OBJECTS.ICON_URL,
LSN_OBJECTS.HELP_URL, LSN_OBJECTS.OBJECT_AUTHOR, LSN_OBJECTS.OBJECT_CREATED, LSN_OBJECTS.DELETED,
LSN_OBJECTS.DRM_PROTECTED, LSN_OBJECTS.ATTRIBUTES, LSN_OBJECTS.PAGE_TEXT
FROM LSN_ENTRIES
JOIN LSN_OBJECTS ON LSN_OBJECTS.OBJECT_ID = LSN_ENTRIES.OBJECT_ID
JOIN LSN_ACCESS ON LSN_ACCESS.ENTRY_ID = LSN_ENTRIES.ENTRY_ID AND LSN_ACCESS.COURSE_ID = LSN_ENTRIES.COURSE_ID
WHERE LSN_ENTRIES.PARENT_ID = @PARENT_ID AND LSN_ENTRIES.COURSE_ID = @COURSE_ID
AND (LSN_OBJECTS.DELETED <> 1) AND (LSN_ENTRIES.VIEWABLE_BY <= @USER_RIGHTS ) AND (LSN_ACCESS.HIDDEN = 0)
AND ((LSN_ENTRIES.START_DATE IS NULL) OR (LSN_ENTRIES.START_DATE < @CURRENT_DATE))
AND ((LSN_ENTRIES.END_DATE IS NULL) OR (LSN_ENTRIES.END_DATE > @CURRENT_DATE))
--            AND CHARINDEX(';'+LSN_ACCESS.TEAM_ID+';', @TEAM_LIST) > 0
AND (LSN_ACCESS.TEAM_ID IN (SELECT team_id from team_members where user_id in (select user_id from course_roster where course_id = @course_id and user_id = @user_id) and course_id = @course_id) OR LSN_ACCESS.TEAM_ID = 'ALL')
AND LSN_ACCESS.TEAM_ID = (SELECT TOP 1 TEAM_ID FROM LSN_ACCESS
WHERE LSN_ACCESS.ENTRY_ID = LSN_ENTRIES.ENTRY_ID AND LSN_ACCESS.COURSE_ID = LSN_ENTRIES.COURSE_ID
--            AND CHARINDEX(';'+LSN_ACCESS.TEAM_ID+';', @TEAM_LIST) > 0
AND (LSN_ACCESS.TEAM_ID IN (SELECT team_id from team_members where user_id in (select user_id from course_roster where course_id = @course_id and user_id = @user_id) and course_id = @course_id) OR LSN_ACCESS.TEAM_ID = 'ALL')
AND (CASE LSN_ENTRIES.USER_GROUP WHEN 'ALL' THEN LSN_ACCESS.TEAM_ID ELSE 'ALL' END = 'ALL')
ORDER BY TEAM_PRIORITY desc, TEAM_ID)
ORDER BY LSN_ENTRIES.COURSE_ID, LSN_ENTRIES.SEQUENCE, LSN_ENTRIES.ENTRY_CREATED
end
end
end
GO
 
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

[apollock]

I ended up solving this issue with a plan guide and the OPTIMIZE FOR hint.

It turned out that it was parameter sniffing, but the SQL2000 method to disable parameter sniffing (the one using local variables) no longer works on SQL2005.


Alex Pollock

[apollock]

Another example of a good plan.