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

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.

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