By: Matteo Lorini | Updated: 2021-04-01 | Comments (9) | Related: > Performance Tuning
Problem
Have you ever come across a situation where a SELECT query with a TOP clause will perform well most of the time, but as soon as you change the TOP value the same query is 10 to 20 times slower? In this tip I will show you why this may happen and ways to prevent this.
Solution
The TOP clause specifies that only the first set of rows will be returned from the query result. In this tip I will show how to make the query results predictable by avoiding "Sort Warning".
Let's create our sample table.
--Source code provided by: www.sqlworkshops.com SET NOCOUNT ON CREATE TABLE tab7 (c1 INT PRIMARY KEY CLUSTERED, c2 INT, c3 CHAR(2000)) GO BEGIN TRAN GO DECLARE @i INT SET @i=1 WHILE @i<=50000 BEGIN INSERT INTO tab7 VALUES (@i,RAND()*200000,'a') SET @i=@i+1 END COMMIT TRAN GO
Let's update the statistics with a full scan to make the optimizer work easier.
UPDATE STATISTICS tab7 WITH fullscan GO
Test 1
Let's set statistics time on and execute the following query.
SET STATISTICS time ON
GO
--Source code provided by: www.sqlworkshops.com
SELECT num_of_reads, num_of_bytes_read,
num_of_writes, num_of_bytes_written
FROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
SELECT TOP 100 c1, c2,c3
FROM tab7
WHERE c1<30000
ORDER BY c2
GO
SELECT num_of_reads, num_of_bytes_read,
num_of_writes, num_of_bytes_written
FROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
CPU time = 124 ms, elapsed time = 91 ms Before Query execution num_of_reads num_of_bytes_read num_of_writes num_of_bytes_written -------------------- -------------------- -------------------- -------------------- 725864 46824931328 793589 51814416384 After Query execution num_of_reads num_of_bytes_read num_of_writes num_of_bytes_written -------------------- -------------------- -------------------- -------------------- 725864 46824931328 793589 51814416384
The query ran fast (91ms).
The number of reads and writes on tempdb before and after the execution of our query are the same. This means that our query was able to complete the sort in memory without spilling to tempdb.
Test 2
Now, lets execute the following query. Please note the new value in the TOP clause which was changed from 100 to 101.
SELECT num_of_reads, num_of_bytes_read,num_of_writes, num_of_bytes_written
FROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
SELECT TOP 101 c1, c2, c3
FROM tab7
WHERE c1<30000
ORDER BY c2
GO
SELECT num_of_reads, num_of_bytes_read,num_of_writes, num_of_bytes_written
FROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
CPU time = 376 ms, elapsed time = 726 ms Before Query execution num_of_reads num_of_bytes_read num_of_writes num_of_bytes_written -------------------- -------------------- -------------------- -------------------- 725864 46824931328 793589 51814416384 After Query execution num_of_reads num_of_bytes_read num_of_writes num_of_bytes_written -------------------- -------------------- -------------------- -------------------- 726880 46881570816 795356 51928072192
The query runs much slower (726 ms).
The sort operation spilled over to tempdb, which we can see by the read/write values before and after execution of our query have increased.
Ways to Fix the Problem
If you are running SQL 2008 or later 64bit, the work around to make the sort happen in memory is to change the query, so the optimizer can allocate more memory allowing the sort operation to take place in memory as shown below. Please see my previous tip for further explanation.
SELECT TOP 101 c1, c2, CONVERT(VARCHAR(4500),c3) FROM tab7 WHERE c1<30000 ORDER BY c2
If If you are running SQL 2005 or later TOP (@variable) does the trick.
SELECT num_of_reads, num_of_bytes_read, num_of_writes, num_of_bytes_writtenROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
DECLARE @i INT
SET @i=101
SELECT TOP(@i) c1, c2, CONVERT(VARCHAR(5000),c3)
FROM tab7
WHERE c1<30000
ORDER BY c2
GO
SELECT num_of_reads, num_of_bytes_read,num_of_writes, num_of_bytes_written
FROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
CPU time = 267 ms, elapsed time = 124 ms Before Query execution num_of_reads num_of_bytes_read num_of_writes num_of_bytes_written -------------------- -------------------- -------------------- -------------------- 729075 47004737536 799045 52165402624 After Query execution num_of_reads num_of_bytes_read num_of_writes num_of_bytes_written -------------------- -------------------- -------------------- -------------------- 729075 47004737536 799045 52165402624
As we can see, the query is fast again and there is no activity on tempdb.
Next Steps
- Play around with the above query and make some changes to see how SQL Server behaves with different predicate values.
- Review my prior tip SQL Server 2008 64bit Query Optimization Trick
- Check out www.sqlworkshops.com for webcasts related to SQL Server.
About the author
Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2021-04-01
