By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,854 Members | 849 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,854 IT Pros & Developers. It's quick & easy.

Poor Performance with Lazy Spool Operator

P: 3
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 ahp11@psu.edu to get the query plans.

Thanks!
Alex Pollock

Here is the query:

Expand|Select|Wrap|Line Numbers
  1. end
  2. else
  3. begin
  4. -- team access display = 0, so all access settings should come from the default even if settings
  5. -- are by team.
  6. SELECT LSN_ENTRIES.ENTRY_ID, LSN_ENTRIES.PARENT_ID, LSN_ENTRIES.COURSE_ID, LSN_ENTRIES.EDITABLE_BY,
  7. LSN_ENTRIES.VIEWABLE_BY, LSN_ENTRIES.TRACKING, LSN_ACCESS.HIDDEN, LSN_ENTRIES.PASSWORD,
  8. LSN_ACCESS.TEAM_ID AS USER_GROUP, LSN_ENTRIES.START_DATE, LSN_ENTRIES.END_DATE, LSN_ENTRIES.ENTRY_AUTHOR,
  9. LSN_ENTRIES.ENTRY_CREATED, LSN_ENTRIES.SEQUENCE, LSN_OBJECTS.OBJECT_ID, LSN_OBJECTS.OBJECT_TYPE,
  10. LSN_OBJECTS.EDIT_MASK, LSN_OBJECTS.LINK_MASK, LSN_OBJECTS.TITLE, LSN_OBJECTS.SUBTITLE, LSN_OBJECTS.PAGE_FORMAT,
  11. LSN_OBJECTS.LINK_URL, LSN_OBJECTS.LINK_TARGET, LSN_OBJECTS.OBJECT_EDITABLE_BY, LSN_OBJECTS.ICON_URL,
  12. LSN_OBJECTS.HELP_URL, LSN_OBJECTS.OBJECT_AUTHOR, LSN_OBJECTS.OBJECT_CREATED, LSN_OBJECTS.DELETED,
  13. LSN_OBJECTS.DRM_PROTECTED, LSN_OBJECTS.ATTRIBUTES, LSN_OBJECTS.PAGE_TEXT
  14. FROM LSN_ENTRIES
  15. JOIN LSN_OBJECTS ON LSN_OBJECTS.OBJECT_ID = LSN_ENTRIES.OBJECT_ID
  16. JOIN LSN_ACCESS ON LSN_ACCESS.ENTRY_ID = LSN_ENTRIES.ENTRY_ID AND LSN_ACCESS.COURSE_ID = LSN_ENTRIES.COURSE_ID
  17. WHERE LSN_ENTRIES.PARENT_ID = @PARENT_ID AND LSN_ENTRIES.COURSE_ID = @COURSE_ID
  18. AND (LSN_OBJECTS.DELETED <> 1) AND (LSN_ENTRIES.VIEWABLE_BY <= @USER_RIGHTS ) AND (LSN_ACCESS.HIDDEN = 0)
  19. AND ((LSN_ENTRIES.START_DATE IS NULL) OR (LSN_ENTRIES.START_DATE < @CURRENT_DATE))
  20. AND ((LSN_ENTRIES.END_DATE IS NULL) OR (LSN_ENTRIES.END_DATE > @CURRENT_DATE))
  21. --            AND CHARINDEX(';'+LSN_ACCESS.TEAM_ID+';', @TEAM_LIST) > 0
  22. 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')
  23. AND LSN_ACCESS.TEAM_ID = (SELECT TOP 1 TEAM_ID FROM LSN_ACCESS
  24. WHERE LSN_ACCESS.ENTRY_ID = LSN_ENTRIES.ENTRY_ID AND LSN_ACCESS.COURSE_ID = LSN_ENTRIES.COURSE_ID
  25. --            AND CHARINDEX(';'+LSN_ACCESS.TEAM_ID+';', @TEAM_LIST) > 0
  26. 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')
  27. AND (CASE LSN_ENTRIES.USER_GROUP WHEN 'ALL' THEN LSN_ACCESS.TEAM_ID ELSE 'ALL' END = 'ALL')
  28. ORDER BY TEAM_PRIORITY desc, TEAM_ID)
  29. ORDER BY LSN_ENTRIES.COURSE_ID, LSN_ENTRIES.SEQUENCE, LSN_ENTRIES.ENTRY_CREATED

Here is the original stored proc that contains the query above:
Expand|Select|Wrap|Line Numbers
  1. SET ANSI_NULLS ON
  2. GO
  3. SET QUOTED_IDENTIFIER ON
  4. GO
  5.  
  6. -- @TEAMACCESS_DISPLAY is the value for environment variable TEAMACCESS_DISPLAY
  7. -- @PARENT_ID is the parent entry id for the folder being displayed
  8. -- @ISEDITABLE is 1/0 based on whether the user entering the folder has editor rights -- Content Editor isEditable()
  9. -- @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;'
  10. -- @USER_RIGHTS is the right of the user entering the folder
  11. -- @COURSE_ID is the id for the course in which the folder resides
  12. -- @CURRENT_DATE is the current date time used to validate against the start and end date
  13.  
  14. /*
  15. CJ:
  16. removed line: AND CHARINDEX(';'+LSN_ACCESS.TEAM_ID+';', @TEAM_LIST) > 0
  17. 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')
  18. removed Team_list parameter
  19. added User_id parameter
  20. */
  21. CREATE PROCEDURE [dbo].[GetUserAccessFolderList]
  22. @TEAMACCESS_DISPLAY smallint,
  23. @PARENT_ID nvarchar(100),
  24. @ISEDITABLE bit,
  25. --    @TEAM_LIST nvarchar(250),
  26. @USER_RIGHTS smallint,
  27. @COURSE_ID nvarchar(250),
  28. @CURRENT_DATE datetime,
  29. @USER_ID nvarchar(50)
  30. AS
  31. if @ISEDITABLE = 1
  32. begin
  33. SELECT LSN_ENTRIES.ENTRY_ID, LSN_ENTRIES.PARENT_ID, LSN_ENTRIES.COURSE_ID, LSN_ENTRIES.EDITABLE_BY, LSN_ENTRIES.VIEWABLE_BY,
  34. LSN_ENTRIES.TRACKING, LSN_ENTRIES.HIDDEN, LSN_ENTRIES.PASSWORD, LSN_ENTRIES.USER_GROUP, LSN_ENTRIES.START_DATE, LSN_ENTRIES.END_DATE,
  35. LSN_ENTRIES.ENTRY_AUTHOR, LSN_ENTRIES.ENTRY_CREATED, LSN_ENTRIES.SEQUENCE, LSN_OBJECTS.OBJECT_ID, LSN_OBJECTS.OBJECT_TYPE,
  36. LSN_OBJECTS.EDIT_MASK, LSN_OBJECTS.LINK_MASK, LSN_OBJECTS.TITLE, LSN_OBJECTS.SUBTITLE, LSN_OBJECTS.PAGE_FORMAT, LSN_OBJECTS.LINK_URL,
  37. LSN_OBJECTS.LINK_TARGET, LSN_OBJECTS.OBJECT_EDITABLE_BY, LSN_OBJECTS.ICON_URL, LSN_OBJECTS.HELP_URL, LSN_OBJECTS.OBJECT_AUTHOR,
  38. LSN_OBJECTS.OBJECT_CREATED, LSN_OBJECTS.DELETED, LSN_OBJECTS.DRM_PROTECTED, LSN_OBJECTS.ATTRIBUTES, LSN_OBJECTS.PAGE_TEXT
  39. FROM LSN_ENTRIES JOIN LSN_OBJECTS ON (LSN_OBJECTS.OBJECT_ID = LSN_ENTRIES.OBJECT_ID)
  40. WHERE LSN_ENTRIES.PARENT_ID = @PARENT_ID AND LSN_ENTRIES.COURSE_ID = @COURSE_ID AND (LSN_OBJECTS.DELETED <> 1)
  41. ORDER BY LSN_ENTRIES.COURSE_ID, LSN_ENTRIES.SEQUENCE, LSN_ENTRIES.ENTRY_CREATED
  42. end
  43. else
  44. begin
  45. if @TEAMACCESS_DISPLAY = 2
  46. begin
  47. SELECT LSN_ENTRIES.ENTRY_ID, LSN_ENTRIES.PARENT_ID, LSN_ENTRIES.COURSE_ID, LSN_ENTRIES.EDITABLE_BY,
  48. LSN_ACCESS.VIEWABLE_BY, LSN_ACCESS.TRACKING, LSN_ACCESS.HIDDEN, LSN_ACCESS.PASSWORD,
  49. LSN_ACCESS.TEAM_ID AS USER_GROUP, LSN_ACCESS.START_DATE, LSN_ACCESS.END_DATE, LSN_ENTRIES.ENTRY_AUTHOR,
  50. LSN_ENTRIES.ENTRY_CREATED, LSN_ENTRIES.SEQUENCE, LSN_OBJECTS.OBJECT_ID, LSN_OBJECTS.OBJECT_TYPE,
  51. LSN_OBJECTS.EDIT_MASK, LSN_OBJECTS.LINK_MASK, LSN_OBJECTS.TITLE, LSN_OBJECTS.SUBTITLE, LSN_OBJECTS.PAGE_FORMAT,
  52. LSN_OBJECTS.LINK_URL, LSN_OBJECTS.LINK_TARGET, LSN_OBJECTS.OBJECT_EDITABLE_BY, LSN_OBJECTS.ICON_URL,
  53. LSN_OBJECTS.HELP_URL, LSN_OBJECTS.OBJECT_AUTHOR, LSN_OBJECTS.OBJECT_CREATED, LSN_OBJECTS.DELETED,
  54. LSN_OBJECTS.DRM_PROTECTED, LSN_OBJECTS.ATTRIBUTES, LSN_OBJECTS.PAGE_TEXT
  55. FROM LSN_ENTRIES
  56. JOIN LSN_OBJECTS ON LSN_OBJECTS.OBJECT_ID = LSN_ENTRIES.OBJECT_ID
  57. JOIN LSN_ACCESS ON LSN_ACCESS.ENTRY_ID = LSN_ENTRIES.ENTRY_ID AND LSN_ACCESS.COURSE_ID = LSN_ENTRIES.COURSE_ID
  58. WHERE LSN_ENTRIES.PARENT_ID = @PARENT_ID AND LSN_ENTRIES.COURSE_ID = @COURSE_ID
  59. AND (LSN_OBJECTS.DELETED <> 1)
  60. --        AND CHARINDEX(';'+LSN_ACCESS.TEAM_ID+';', @TEAM_LIST) > 0
  61. 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')
  62. AND LSN_ACCESS.TEAM_ID = (SELECT TOP 1 TEAM_ID FROM LSN_ACCESS
  63. WHERE LSN_ACCESS.ENTRY_ID = LSN_ENTRIES.ENTRY_ID AND LSN_ACCESS.COURSE_ID = LSN_ENTRIES.COURSE_ID
  64. --AND CHARINDEX(';'+LSN_ACCESS.TEAM_ID+';', @TEAM_LIST) > 0
  65. 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')
  66. AND (LSN_ACCESS.VIEWABLE_BY <= @USER_RIGHTS ) AND (LSN_ACCESS.HIDDEN = 0)
  67. AND ((LSN_ACCESS.START_DATE IS NULL) OR (LSN_ACCESS.START_DATE < @CURRENT_DATE))
  68. AND ((LSN_ACCESS.END_DATE IS NULL) OR (LSN_ACCESS.END_DATE > @CURRENT_DATE))
  69. ORDER BY TEAM_PRIORITY desc, TEAM_ID)
  70. ORDER BY LSN_ENTRIES.COURSE_ID, LSN_ENTRIES.SEQUENCE, LSN_ENTRIES.ENTRY_CREATED
  71. end
  72. else
  73. begin
  74. if @TEAMACCESS_DISPLAY = 1
  75. begin
  76. SELECT LSN_ENTRIES.ENTRY_ID, LSN_ENTRIES.PARENT_ID, LSN_ENTRIES.COURSE_ID, LSN_ENTRIES.EDITABLE_BY,
  77. LSN_ACCESS.VIEWABLE_BY, LSN_ACCESS.TRACKING, LSN_ACCESS.HIDDEN, LSN_ACCESS.PASSWORD,
  78. LSN_ACCESS.TEAM_ID AS USER_GROUP, LSN_ACCESS.START_DATE, LSN_ACCESS.END_DATE, LSN_ENTRIES.ENTRY_AUTHOR,
  79. LSN_ENTRIES.ENTRY_CREATED, LSN_ENTRIES.SEQUENCE, LSN_OBJECTS.OBJECT_ID, LSN_OBJECTS.OBJECT_TYPE,
  80. LSN_OBJECTS.EDIT_MASK, LSN_OBJECTS.LINK_MASK, LSN_OBJECTS.TITLE, LSN_OBJECTS.SUBTITLE, LSN_OBJECTS.PAGE_FORMAT,
  81. LSN_OBJECTS.LINK_URL, LSN_OBJECTS.LINK_TARGET, LSN_OBJECTS.OBJECT_EDITABLE_BY, LSN_OBJECTS.ICON_URL,
  82. LSN_OBJECTS.HELP_URL, LSN_OBJECTS.OBJECT_AUTHOR, LSN_OBJECTS.OBJECT_CREATED, LSN_OBJECTS.DELETED,
  83. LSN_OBJECTS.DRM_PROTECTED, LSN_OBJECTS.ATTRIBUTES, LSN_OBJECTS.PAGE_TEXT
  84. FROM LSN_ENTRIES
  85. JOIN LSN_OBJECTS ON LSN_OBJECTS.OBJECT_ID = LSN_ENTRIES.OBJECT_ID
  86. JOIN LSN_ACCESS ON LSN_ACCESS.ENTRY_ID = LSN_ENTRIES.ENTRY_ID AND LSN_ACCESS.COURSE_ID = LSN_ENTRIES.COURSE_ID
  87. WHERE LSN_ENTRIES.PARENT_ID = @PARENT_ID AND LSN_ENTRIES.COURSE_ID = @COURSE_ID
  88. AND (LSN_OBJECTS.DELETED <> 1)
  89. --AND CHARINDEX(';'+LSN_ACCESS.TEAM_ID+';', @TEAM_LIST) > 0
  90. 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')
  91.  
  92. AND LSN_ACCESS.TEAM_ID = (SELECT TOP 1 TEAM_ID FROM LSN_ACCESS
  93. WHERE LSN_ACCESS.ENTRY_ID = LSN_ENTRIES.ENTRY_ID AND LSN_ACCESS.COURSE_ID = LSN_ENTRIES.COURSE_ID
  94. --            AND CHARINDEX(';'+LSN_ACCESS.TEAM_ID+';', @TEAM_LIST) > 0
  95. 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')
  96.  
  97. AND (LSN_ACCESS.VIEWABLE_BY <= @USER_RIGHTS ) AND (LSN_ACCESS.HIDDEN = 0)
  98. AND ((LSN_ACCESS.START_DATE IS NULL) OR (LSN_ACCESS.START_DATE < @CURRENT_DATE))
  99. AND ((LSN_ACCESS.END_DATE IS NULL) OR (LSN_ACCESS.END_DATE > @CURRENT_DATE)) AND
  100. (CASE LSN_ENTRIES.USER_GROUP WHEN 'ALL' THEN LSN_ACCESS.TEAM_ID ELSE 'ALL' END = 'ALL')
  101. ORDER BY TEAM_PRIORITY desc, TEAM_ID)
  102. ORDER BY LSN_ENTRIES.COURSE_ID, LSN_ENTRIES.SEQUENCE, LSN_ENTRIES.ENTRY_CREATED
  103. end
  104. else
  105. begin
  106. -- team access display = 0, so all access settings should come from the default even if settings
  107. -- are by team.
  108. SELECT LSN_ENTRIES.ENTRY_ID, LSN_ENTRIES.PARENT_ID, LSN_ENTRIES.COURSE_ID, LSN_ENTRIES.EDITABLE_BY,
  109. LSN_ENTRIES.VIEWABLE_BY, LSN_ENTRIES.TRACKING, LSN_ACCESS.HIDDEN, LSN_ENTRIES.PASSWORD,
  110. LSN_ACCESS.TEAM_ID AS USER_GROUP, LSN_ENTRIES.START_DATE, LSN_ENTRIES.END_DATE, LSN_ENTRIES.ENTRY_AUTHOR,
  111. LSN_ENTRIES.ENTRY_CREATED, LSN_ENTRIES.SEQUENCE, LSN_OBJECTS.OBJECT_ID, LSN_OBJECTS.OBJECT_TYPE,
  112. LSN_OBJECTS.EDIT_MASK, LSN_OBJECTS.LINK_MASK, LSN_OBJECTS.TITLE, LSN_OBJECTS.SUBTITLE, LSN_OBJECTS.PAGE_FORMAT,
  113. LSN_OBJECTS.LINK_URL, LSN_OBJECTS.LINK_TARGET, LSN_OBJECTS.OBJECT_EDITABLE_BY, LSN_OBJECTS.ICON_URL,
  114. LSN_OBJECTS.HELP_URL, LSN_OBJECTS.OBJECT_AUTHOR, LSN_OBJECTS.OBJECT_CREATED, LSN_OBJECTS.DELETED,
  115. LSN_OBJECTS.DRM_PROTECTED, LSN_OBJECTS.ATTRIBUTES, LSN_OBJECTS.PAGE_TEXT
  116. FROM LSN_ENTRIES
  117. JOIN LSN_OBJECTS ON LSN_OBJECTS.OBJECT_ID = LSN_ENTRIES.OBJECT_ID
  118. JOIN LSN_ACCESS ON LSN_ACCESS.ENTRY_ID = LSN_ENTRIES.ENTRY_ID AND LSN_ACCESS.COURSE_ID = LSN_ENTRIES.COURSE_ID
  119. WHERE LSN_ENTRIES.PARENT_ID = @PARENT_ID AND LSN_ENTRIES.COURSE_ID = @COURSE_ID
  120. AND (LSN_OBJECTS.DELETED <> 1) AND (LSN_ENTRIES.VIEWABLE_BY <= @USER_RIGHTS ) AND (LSN_ACCESS.HIDDEN = 0)
  121. AND ((LSN_ENTRIES.START_DATE IS NULL) OR (LSN_ENTRIES.START_DATE < @CURRENT_DATE))
  122. AND ((LSN_ENTRIES.END_DATE IS NULL) OR (LSN_ENTRIES.END_DATE > @CURRENT_DATE))
  123. --            AND CHARINDEX(';'+LSN_ACCESS.TEAM_ID+';', @TEAM_LIST) > 0
  124. 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')
  125. AND LSN_ACCESS.TEAM_ID = (SELECT TOP 1 TEAM_ID FROM LSN_ACCESS
  126. WHERE LSN_ACCESS.ENTRY_ID = LSN_ENTRIES.ENTRY_ID AND LSN_ACCESS.COURSE_ID = LSN_ENTRIES.COURSE_ID
  127. --            AND CHARINDEX(';'+LSN_ACCESS.TEAM_ID+';', @TEAM_LIST) > 0
  128. 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')
  129. AND (CASE LSN_ENTRIES.USER_GROUP WHEN 'ALL' THEN LSN_ACCESS.TEAM_ID ELSE 'ALL' END = 'ALL')
  130. ORDER BY TEAM_PRIORITY desc, TEAM_ID)
  131. ORDER BY LSN_ENTRIES.COURSE_ID, LSN_ENTRIES.SEQUENCE, LSN_ENTRIES.ENTRY_CREATED
  132. end
  133. end
  134. end
  135. GO
  136.  
  137. SET ANSI_NULLS OFF
  138. GO
  139. SET QUOTED_IDENTIFIER OFF
  140. GO
Jan 31 '08 #1
Share this Question
Share on Google+
2 Replies


P: 3
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
Mar 12 '08 #2

P: 3
Another example of a good plan.
Feb 2 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.