SQL Server 2005 Memory Pressure and tempdb writes problem

Question

We are having some issues with our production SQL Server.

Server: Dual Quad Core Xeon 8 GB RAM Single RAID 10 Array Windows 2003 Server 64-bit SQL Server 2005 Standard 64-Bit

There is about 250MB of free RAM on the machine right now. SQL Server has around 6GB of RAM, and our monitoring software says that only half of the SQL Server allocated RAM is actually being used.

Our main database is approximately 20GB, with about 12GB being used with any frequency. Our tempdb is at 700MB. Both are located on the same physical disk array.

Additionally, using Filemon, I was able to see that the tempdb file had 100's or 1000's of writes of length 65536. Disk queue length was over 100 nearly 80% of the time.

So, here are my questions-

1. What would cause all those writes on the tempdb? I'm not sure if we have always had that much activity, but it seems excessive and these problems are recent.

2. Should I just add more memory to the server?

3. On high load servers, should tempdb and db files be located on separate arrays?

Answer 1

Excellent question, +1

tempdb is used far more heavily in SQL 2005+. At least: Snapshot isolation levels, online index rebuild, reading INSERTED/DELETED in triggers(used to read the log file!)

This in addition to the usual order by clauses, temp tables etc.

You'd probably be better splitting your log and data files (also for recoverability). More memory is always good but see this 64 bit specific stuff, Grumpy Old DBA below.

Finally, and maybe most important probably, you can have contention of space allocation in tempdb: Explanations from Linchi Shea and SQL Server storage team

Late edit:

Paul Randall added an entry "Comprehensive tempdb blog post series" which offers good links

Answer 2

Yes, the recommendation on high load servers is to put TempDB on a separate set of drives from the user databases:

SQL Server 2005 Books Online: Optimizing tempdb Performance

Answer 3

1. Writes to the tempdb can be anything. Internal hash tables, temp tables, table variable, stored procedure calls, etc.

2. If you only have 250 Megs of free RAM, then yes more RAM would be good.

3. It is always recommended that you split tempdb and user databases to different disks.

All writes to the tempdb will be 64k in size as that's the size of each database extent.

Answer 4

Not directly an answer on your question but this might be a good tip: Restarting your SQL Server instance will clear the tempdb, this might be a good start when investigating the actions which are done on the tempdb.

Answer 5

A high disk queue length does not mean you have an I/O bottleneck if you have a SAN or NAS, you may want to look at other additional counters. Check out SQL Server Urban Legends discussed for more details.

1: The following operations heavily utilize tempdb

• Repeated create and drop of temporary tables (local or global)
• Table variables that use tempdb for storage purposes
• Work tables associated with CURSORS
• Work tables associated with an ORDER BY clause
• Work tables associated with an GROUP BY clause
• Work files associated with HASH PLANS

These SQL Server 2005 features also use tempdb heavily:

• row level versioning (snapshotisolation)
• online index re-building

As mentioned in other SO answers read this article on best practice for increasing tempdb performance.

2: Looking at the amount of free RAM on the server i.e. looking at the WMI counter Memory->Available Mbytes doesn't help as SQL Server will cache data pages in RAM, so any db server that's running long enough will have little free RAM.
The counters you should look at that are more meaningful in telling you if adding RAM to the server will help are:
SQL Server Instance:Buffer Manager->Page Life Expectancy (in seconds) A value below 300-400 seconds will mean that Pages are not in memory very long and data continually is being read in from disks. Servers that have a low page life expectancy will benefit from additional RAM.
and
SQL Server Instance:Buffer Manager->Buffer Cache hit Ratio This tells you the percentage of pages that were read from RAM that didn't have to incur a read from disk, a cache hit ratio lower then 85 will mean that the server will benefit from additional RAM

3: Yes, can't go wrong here. Having tempdb on a separate set of disks is recommended. Look at this KB article under the heading: Moving the tempdb database on how to do this.