Tempdb usage: sort

Sunday, July 7, 2013

Sort is not the work table. When executing the sort, the perfomon counter “Worktables created/sec” does not change.

Sort is always happen in memory. However, when there is not enough memory for sort, it will split into tempdb disk. To reproduce the sort split, we can reduce the max memory on the sql server.
Page allocation for sort is always uniform extend.
The extend would be de allocate when the sort completed.

This demo is primary base on the Bob ward’s “Inside tempdb” session in the SQL SUMMIT 2011 .

Create the test database and table

CREATE DATABASE [summer_sort] ON  PRIMARY

( NAME = N'summer_sortdata', FILENAME = N'C:\DATA\summer_sort_data.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON

( NAME = N'summer_sortlog', FILENAME = N'C:\DATA\summer_sort_log.ldf' , SIZE = 2048KB  , FILEGROWTH = 10%);

ALTER DATABASE [summer_sort] SET RECOVERY SIMPLE WITH NO_WAIT;

use summer_sort;

create table t1 (c1 int, c2 char(5000) not null);

declare @x int

set @x = 0

while (@x < 100000)

begin

insert into t1 values (@x, 'I like daddy')

set @x = @x + 1

end;

We can bounce the SQL server to ensure there are no leftover events.

SELECT EventClass, *

FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log.trc', default)

where EventClass = 69 order by EventSequence

SET STATISTICS IO ON;

use summer_sort;

select * from t1 order by c1;

After the kick off the query, we can query the default trace. We should see the event 69 shows up.

select Operation, Context, [Transaction ID], AllocUnitId, AllocUnitName, [Page ID],

[Transaction Name], [Description] from sys.fn_dblog(null, null);

If the server has memory pressure, we can see the sort_init in the log. Then we can see the page allocation event like GAM, PFS … etc

Convert the hex to the number with powershell

Look up the page header

dbcc traceon(3604);

dbcc page(2,1,208,2);

The page type 7 means it is the sort page.

with profiler, we can see the sort warming event, however it does not really give us much information other than the warming.

When create the cluster index, the sort operation is happen on the user database unless SORT_IN_TEMP option is used.

use summer_sort;

create clustered index idx1 on t1 (c1);

Look at the transaction log for the summer_sort db and we can see the sort_init operation

Default trace shows the sort event .

Now, lets try SORT_IN_TEMPDB

use summer_sort;

create clustered index idx1 on t1 (c1) with  SORT_IN_TEMPDB,DROP_EXISTING ;