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 )
( 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;
create table t1 (c1 int, c2 char(5000) not null);
declare @x int
set @x = 0
while (@x < 100000)
insert into t1 values (@x, 'I like daddy')
set @x = @x + 1
Query the Default trace for the sort event
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
Query the heap table
SET STATISTICS IO ON;
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.
Query tempdb transaction log.
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
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.
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
create clustered index idx1 on t1 (c1) with SORT_IN_TEMPDB,DROP_EXISTING ;