Unpredictable Slowness and Table Spool(Lazy Read)
1
vote
1
answer
3209
views
I was testing on Stackoverflow database to find out possible cases wherein SQL Server doesn't recommend index in the execution plan however if we introduce one, it would help in great way!
Did it quite easily for Group by, Order by Clause and for aggregate function(count function - smallest copy of table). I wrote a random query wherein I knew that introducing supportive index will certainly help however missing index recommendation will be only on join condition and not on the order by clause.
Query is as below:
select top 100 Location from Users U join Badges B
on B.UserId = U.Id
order by Location desc
Below indexes were introduced to improve performance:
create index Location on Users(Location)
go
create index UsersId on Badges(UserId)
go
Indexes used by optimizer as expected for above query:
Logical reads and time stats are as below:
Now, I wanted to test the performance with only index on Users table at Location column and no Index on Badges (UserId) Table, here performance becomes terrible(takes almost 7 minutes):
Logical reads and Time stats are as below:
Index at Users tables are very much used, as evident from execution plan and logical reads however doing clustered index scan and Table Spool (Lazy Spool) causes most of the issue.
*All above tests are conducted on SQL Server 2019 in SQL Server 2016 compatibility mode(130).*
If someone could please advise on underlying issue, would be of great help.
One more thing to note here, when there is no non-clustered supportive index on either of these two table, same query finishes in 9 seconds. Below is the execution plan:
Logical Read and Time Stats:
For testing purpose, I changed compatibility level to 2019(150) and to my surprise - same previous query which had index only on Users (Location) table and not on Badges table, finished in 2 seconds which was taking 7 minutes in SQL Server 2016 compatibility(130) mode:
Logical Stats and Time Stats:
In 2019 compatibility mode, all the operators before Parallelism are in *batch mode*.
Any input in this regard would help me in understanding this behavior.
Logical reads and time stats are as below:
Now, I wanted to test the performance with only index on Users table at Location column and no Index on Badges (UserId) Table, here performance becomes terrible(takes almost 7 minutes):
Logical reads and Time stats are as below:
Index at Users tables are very much used, as evident from execution plan and logical reads however doing clustered index scan and Table Spool (Lazy Spool) causes most of the issue.
*All above tests are conducted on SQL Server 2019 in SQL Server 2016 compatibility mode(130).*
If someone could please advise on underlying issue, would be of great help.
One more thing to note here, when there is no non-clustered supportive index on either of these two table, same query finishes in 9 seconds. Below is the execution plan:
Logical Read and Time Stats:
For testing purpose, I changed compatibility level to 2019(150) and to my surprise - same previous query which had index only on Users (Location) table and not on Badges table, finished in 2 seconds which was taking 7 minutes in SQL Server 2016 compatibility(130) mode:
Logical Stats and Time Stats:
In 2019 compatibility mode, all the operators before Parallelism are in *batch mode*.
Any input in this regard would help me in understanding this behavior.
Asked by Learning_DBAdmin
(3924 rep)
Feb 17, 2022, 11:43 AM
Last activity: Mar 20, 2025, 05:34 PM
Last activity: Mar 20, 2025, 05:34 PM