Tony Rogerson's ramblings on SQL Server

Grab your attention? This entry came out of trying to reproduce PFS / GAM / SGAM contention and trying to put some science around seeing it and what the benefit of using multiple files or trace flag 1118 really is. I got half way through writing that and then realised I had behaviour in tempdb that went against how I thought writing to tempdb worked – probably a throwback to 6.5 days.

Look at the script below, it basically creates a temporary table, populates it with about 100Mbytes of data and then drops it and repeats that logic for 5 minutes.

set nocount on

declare @i int

set @i = 1

declare @st datetime

declare @st2 datetime

set @st = current_timestamp

while current_timestamp <= dateadd( minute, 5, @st )

begin

      create table #t ( mycol char(8000) not null )

      set @st2 = current_timestamp

      while current_timestamp <= dateadd( second, 5, @st2 )

      begin

            insert #t values( 'abc' )

            set @i = @i + 1

      end

      drop table #t

end

print 'iterations ' + cast( @i as varchar(20) )

Running this on a machine that is doing nothing other than the script above, that has 512Mbytes allocated to SQL Server, what do you think happens in tempdb? The LDF (transaction log) is written with ‘undo only’ records, there is no redo on tempdb because it’s cleared down on SQL Server restart, what about the data files? Do you think the data files get written too even for this short lived temporary table?

The answer is it depends, in my tests if the cumulative size of the data files for tempdb is larger than the available data cache then it will write to the data files even though logically we don’t want it to.

I was expecting it to find and reuse the extents that had just been freed up by the drop table but that doesn’t happen, if you use DBCC EXTENTINFO( 2 ) to look at what extents are being allocated you will see that it progresses through the database until it runs out of free extents and then starts to round robin back through the used and free pages and starts using them which is why in that particular scenario we won’t get the writes out to the data files – the LAZYWRITER never gets chance to flush the page out to disk.

How can you tell if SQL Server is writing out to the data files, run this:-

select * from ::fn_virtualfilestats( 2, 2 )

union all

select * from ::fn_virtualfilestats( 2, 1 )

FileId 2 is the log, FileID <> 2 are the data files, check the NumberWrites column and if its increasing by a high rate then you’ve got a lot of writes going out to the data files in tempdb and tempdb is probably a bottleneck that needs fixing – remember, the only difference between tempdb and any other application database is that tempdb does not log redo records so if you have a heavily used database – it might even be more write intensive than your application one because of ORDER BY, Hashing, CREATE TABLE #, table variables etc… you probably need to get it off onto its own disk and maybe even separate the tempdb log and data.

There is another way; remember that tempdb is recycled every time SQL Server is restarted so with that in mind the old tempdb in RAM springs to mind; that was a feature removed many releases ago but my feeling is that for some systems that make heavy use of tempdb a RAM drive will benefit.

I’ve done some tests using a software RAM drive (http://www.amtsoftware.com/Ramdisk-Plus/) and the results are outstanding, installation is easy and it installed in minutes on my Windows 2003 Server R2 64bit OS; you create the drive and format it using their tool, the drive is then seen as a local drive (try exec master..fixeddrives).

Another method is to using Gigabytes i-RAM which looks pretty cool, it’s a PCI card that has 4 banks of DDR RAM that can hold up-to 4GB it uses the SATA interface so it just looks like another hard drive to your system, the benefit is that the seek times are memory speed (nanoseconds rather than milliseconds) and the Mbytes/second throughput is high. Due to dosh I haven’t evaluated it, the card costs £93 from scan and you could fill it out with 4GBytes for about another £200.

The tests where run on SQL 2000 Standard SP4 with hotfix 2187 and trace flag 1118 was applied so as not to use mixed extents; I basically run the script shown early with 10 concurrent connections. It’s probably worth noting that the disk is a 74GB SATA 1.5Gbit 10Krpm drive and the memory is DDR2 800, the motherboard supporting dual DDR, it’s also probably worth noting that no animals where hurt whilst conducting these tests – only my head against a brick wall….

Tempdb was configured as below, except when doing the RAM drive test, I kept one of the files on disk (the 1MByte one) so that if pressure where put on tempdb then it could autogrow successfully albeit to disk but your application wouldn’t fail, and why only 1Mbyte its because of the round robin write algorithm I describe in my other blog entry.

Tempdev                       200Mbytes         NO autogrowth

Tempdev2                     200Mbytes         NO autogrowth

Tempdev3                     200Mbytes         NO autogrowth

Tempdev4                     1Mbytes            Autogrowth 10MBytes

Templog                        50Mbytes          Autogrowth 10MBytes

Results

Tempdb: 4 data, 1 log files on disk                                 130,520 iterations

Tempdb: 3 data, 1 log files in RAM, 1 data on disk        1,587,359 iterations

Bit of a difference there!

To move tempdb to another drive run this and restart SQL Server…

ALTER DATABASE tempdb

      MODIFY FILE ( NAME = templog , FILENAME = 'f:\sql2000\templog.ldf' )

ALTER DATABASE tempdb

      MODIFY FILE ( NAME = tempdev , FILENAME = 'f:\sql2000\tempdev.mdf' )

ALTER DATABASE tempdb

      MODIFY FILE ( NAME = tempdev2, FILENAME = 'f:\sql2000\tempdev2.mdf' )

ALTER DATABASE tempdb

      MODIFY FILE ( NAME = tempdev3, FILENAME = 'f:\sql2000\tempdev3.mdf' )

ALTER DATABASE tempdb

      MODIFY FILE ( NAME = tempaug, FILENAME = 'f:\sql2000\tempaug.mdf' )

How does this translate into your own environment? Check your tempdb usage by monitoring ::fn_virtualfilestats(2, <fileid>) and also tempdb sizing. What are the IO characteristics of your applications? Does your existing machine facilitate enough memory for a RAM drive or PCI card for the Gigabyte i-RAM approach? Unfortunately without seeing and understanding your SQL Server system then I can’t comment but if you need some consultancy you know where I am ;).

I’ll get back to the PFS / GAM / SGAM contention thing sometime in the next couple of days – got to get back to client work…