When to Place tempdb in RAM to Improve ORDER BY Performance

Sort in Tempdb

Download Truly Level 400 SQL Server Performance Monitoring and Tuning Webcast 3 (3 parts) - FREE Download; Firewall might block downloads, if you have issues downloading let us know, we will provide you with alternate download links.

When to place tempdb in RAM (or Solid State Disk) or to use query rewrite workarounds to improve ORDER BY performance

About the article:

This is an excerpt from the 3 Day Level 400 Microsoft SQL Server Performance Monitoring & Tuning Hands-on Workshop; I explain the concepts in the workshop with examples. I try to do the best while writing this article, but it is not the same!!

This article discusses when to use Solid State Disk (or RAMDISK) or to use query rewrite workarounds to improve ORDER BY performance.

This article helps you to understand when to use Solid State Disk (or RAMDISK) or to use query rewrite workarounds to improve ORDER BY performance.

The purpose of this article is to help you better understand SQL Server Performance Monitoring & Tuning. The purpose of this article is not to discuss whether this is a bug or not.

Description:

When a customer asks you: I have a query that does ORDER BY, the query is slow, how can I improve the performance of the query ? One limitation that I have is I cannot create index for all possible combination of ORDER BY as users choose which columns to ORDER BY on the fly. This article will help you answer the question.

When a query contains ORDER BY and if there is a supporting index then SQL Server can use the index to avoid a sort. When there is no supporting index SQL Server has to sort the rows, especially when sorting over columns from different tables on a join, sort can be in-memory or it could spill to tempdb. By running the query in isolation and observing tempdb activity one can tell if the sort is spilling to tempdb. It is possible to monitor sort spilling to tempdb from the profiler event ‘Sort Warnings’.

If the sort is spilling to tempdb – the first thing to check is if the statistics are up to date. SQL Server estimates memory grants for sort based on statistics, when statistics are out of date, the estimation might be low leading to low memory grants which could be a reason for the sort spilling to tempdb. The other reason for sort spilling to tempdb is that the server does not have enough (workspace) memory available.

One way to speed up the sort is to keep tempdb on a faster disk, like Solid State Disk or RAMDISK. The other way is to avoid sort spilling to tempdb in the first place, this could be achieved by rewriting the query (in such a way that more memory would be granted for this query avoiding sort spilling to tempdb).

In some cases it is not possible to create an index to optimize ORDER BY as the columns to ORDER BY could be from different tables that are joined (like in the below example).

Example:

This SQL Server example needs tables that you have to create. The script to create the table is located at the end of this article(www.sqlworkshops.com/sort_in_tempdb). Based on your server configuration, it might be necessary to alter the test query predicate (5000 / 7000) to observe the stated behavior.

1. Save tempdb data file statistics before executing the test query:

--example provided by www.sqlworkshops.com

--save old tempdb data file statistics

select vfs.* into dm_io_virtual_file_stats_start fromsys.master_files mf

inner join sys.dm_io_virtual_file_stats(NULL, NULL)

vfs on (mf.database_id = vfs.database_id and mf.file_id = vfs.file_id)

where mf.type = 0 and mf.database_id = 2

Don't miss our SQL Server Performance Tuning Hands-on Workshop! Register

SQL Server Performance Tuning Consulting & Health Check

sp_whopro - SQL Server Activity Monitoring and Logging Stored Procedure - Free Download

SQLTest - SQL Server Performance, Load, Stress and Unit Test Tool - Free Download

SQLVideo - SQL Server Performance Tuning and Troubleshooting - Free Youtube Videos, now also available on MSDN Channel 9!

2.1: Sort having enough granted memory does not spill to tempdb:

--example provided by www.sqlworkshops.com

--update statistics to have better estimates

update statistics tab71 with fullscan

--set statistics time to see how long the query took to complete

set statistics time on

--set rowcount 1 to stop the results after 1 row has been output, to avoid filling-up the query window

set rowcount 1

--query that does the below sort has enough memory that it does not spill to tempdb

select * from tab71 where c1 <= 5000 order by c2 option (maxdop 1)

set rowcount 0

set statistics time off

The above query completes in about 15ms (depends on your server).

2.2: Sort without having enough granted memory spills to tempdb:

--example provided by www.sqlworkshops.com

--update statistics to have better estimates

update statistics tab71 with fullscan

--set statistics time to see how long the query took to complete

set statistics time on

--set rowcount 1 to stop the results after 1 row has been output, to avoid filling-up the query window

set rowcount 1

--query that does the below sort does not have enough memory and it spill the sort to tempdb

select * from tab71 where c1 <= 7000 order by c2 option (maxdop 1)

set rowcount 0

set statistics time off

The above query completes in about 190ms (depends on your server). Notice that we did update statistics to have better estimates. The reason for this query taking longer than the first test query is that this query spills the sort to tempdb.

The amount of data written to tempdb is about 8MB during this test query execution. You can find this yourself if you follow step 3 & 4.

I was able to rewrite the second test query to achieve 28ms execution time. This was done by rewriting the query (without changing the schema or adding indexes) in such a way SQL Server grants more memory avoiding sort spilling to tempdb. Solid State Disk or RAMDISK can also be used to improve performance of the above query, query rewrite will get the best execution time in this case as we are well below workspace memory limit.

2.3: Top 500 sort without having enough granted memory spills to tempdb:

--example provided by www.sqlworkshops.com

--update statistics to have better estimates

update statistics tab71 with fullscan

--set statistics time to see how long the query took to complete

set statistics time on

--set rowcount 1 to stop the results after 1 row has been output, to avoid filling-up the query window

set rowcount 1

--query that does the below top 500 sort does not have enough memory and it spill the sort to tempdb

select top 500 * from tab71 where c1 <= 7000 order by c2 option (maxdop 1)

set rowcount 0

set statistics time off

The above query completes in about 500ms (depends on your server). Notice that we did update statistics to have better estimates. The reason for this query taking longer than the first test query is that this query spills the top sort to tempdb.

The amount of data written to tempdb is about 11.5MB during this test query execution. You can find this yourself if you follow step 3 & 4. The high tempdb usage is due to multiple passes SQL Server makes over sorted output.

I was able to again rewrite the third test query to again achieve 28ms execution time. This was done by rewriting the query (without changing the schema or adding indexes) in such a way SQL Server grants more memory avoiding sort spilling to tempdb. Solid State Disk or RAMDISK can also be used to improve performance of the above query, query rewrite will get the best execution time in this case as we are well below workspace memory limit.

2.4: Sort on a join without having enough granted memory spills to tempdb:

--example provided by www.sqlworkshops.com

--update statistics to have better estimates

update statistics tab71 with fullscan

--set statistics time to see how long the query took to complete

set statistics time on

--set rowcount 1 to stop the results after 1 row has been output, to avoid filling-up the query window

set rowcount 1

--query that does the below sort on a join does not have enough memory and it spill the sort to tempdb

select * from tab71 t1

inner join tab71 t2 on (t1.c1 = t2.c1)

where t1.c1 <= 7000 order by t1.c2, t2.c2 option (maxdop 1

set rowcount 0

set statistics time off

The above query completes in about 300ms (depends on your server). Notice that we did update statistics to have better estimates. The reason for this query taking longer than the first test query is that this query spills the top sort to tempdb.

The amount of data written to tempdb is about 14MB during this test query execution. You can find this yourself if you follow step 3 & 4.

I was able to again rewrite the fourth test query to again achieve 67ms execution time. This was done by rewriting the query(without changing the schema or adding indexes or indexed views) in such a way SQL Server grants more memory avoiding sort spilling to tempdb. Solid State Disk or RAMDISK can also be used to improve performance of the above query, query rewrite will get the best execution time in this case as we are well below workspace memory limit.

3. Save tempdb data file statistics after executing the test query:

--example provided by www.sqlworkshops.com

--save old tempdb data file statistics

select vfs.* into dm_io_virtual_file_stats_end from sys.master_files mf

inner join sys.dm_io_virtual_file_stats(NULL, NULL) vfs on (mf.database_id = vfs.database_id and mf.file_id = vfs.file_id)

where mf.type = 0 and mf.database_id = 2

4. Calculate tempdb data file activity that occurred during for the test query:

--example provided by www.sqlworkshops.com

--delta tempdb data file statisticsselect (sum(t2.num_of_reads) - sum(t1. num_of_reads)) as num_of_reads, (sum(t2.num_of_writes) - sum(t1. num_of_writes)) as num_of_writes, (sum(t2.num_of_bytes_read) - sum(t1. num_of_bytes_read)) / 1024.0 as num_of_bytes_read_KB,

(sum(t2.num_of_bytes_written) - sum(t1. num_of_bytes_written)) / 1024.0 as num_of_bytes_written_KB

from dm_io_virtual_file_stats_start t1 inner join dm_io_virtual_file_stats_end t2 on (t1.file_id = t2.file_id)

Tempdb on Solid State Disk or RAMDISK:

When sort spills to tempdb, performance can be significantly worse. There are cases where sort can spill to tempdb even when you have lot of (workspace) memory and statistics are up to date (like in the above example). In this case placing tempdb on Solid State Disk or RAMDISK will provide significant performance improvement.

Placing tempdb on RAM or Solid State Disk is ideal when your query needs more that the available workspace memory, when you are below this limit, you can workaround with query rewrite.

Query Rewrite:

In most cases, without using Solid State Disk or RAMDISK, one can workaround the issue by rewriting the query to have extra memory granted to avoid sort spilling to tempdb (up to workspace memory limitation). I have found ways to achieve this even for TOP N queries.

Query rewrite procedure is now explained in the webcasts: http://www.sqlworkshops.com/webcasts

To receive example SQL Scripts used in the webcasts: subscribe to our Newsletter.

Table

--example provided by www.sqlworkshops.com

create table tab71 (c1 int primary key clustered, c2 int, c3 char(1000))

begin tran

declare @i int

set @i = 1

while @i <=5000

begin

insert into tab71(c1, c2, c3) values (@i, @i, 'a')

set @i = @i + 1