Sunday, July 7, 2013

0 Tempdb usage: sort

 

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

image

  • 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;

 

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;
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.

image

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

image\

Convert the hex to the number with powershell

image

Look up the page header

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

The page type 7 means it is the sort page.

image

 

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

image

Index

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

image

Default trace shows the sort event .

image

Now, lets try SORT_IN_TEMPDB

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

image

image

Reference

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates