1

When I look at resource usage, SQL Server does a lot of writes into tempdb for some big queries.

I don't use temp tables or table variables or any writes, I execute only select statements of read-only database on that server.

So I wonder if I upgrade that Server with plenty of RAM, will SQL Server be capable to use that additional RAM and eliminate usage of tempdb or I still have to allocate part of RAM and move tempdb to RAM Disk ?

Question SQL Server 2005 Memory Pressure and tempdb writes problem is related to that, but does not address this situation.

Community
  • 1
  • 1
alpav
  • 2,972
  • 3
  • 37
  • 47

1 Answers1

2

You may find this presentation very helpful: Optimizing tempdb Performance.

Specifically, refer to the following tips:

  • Minimize the use of tempdb
  • Add more RAM to your server
  • Leave Auto Create Statistics & Auto Update Statistics on
  • Pre-allocate tempdb space – everyone needs to do this
  • Don’t shrink tempdb if you don’t need to
  • Locate tempdb on its own array
  • Locate tempdb on a fast I/O subsystem
  • Divide tempdb among multiple physical files
  • Avoid using Transparent Data Encryption (2008)
BluesRockAddict
  • 15,525
  • 3
  • 37
  • 35
  • It says "If the buffer cache does not have enough available space, then the operation may have to spill to tempdb.", my question is will it never spill anything to tempdb if there is plenty of RAM for buffer cache ? I wonder if there is situation where SQL will not give enough RAM for buffer cache and it makes sense to force that allocation with RAM Disk. – alpav Apr 23 '12 at 16:28
  • "Locate tempdb on its own array", "Locate tempdb on a fast I/O subsystem" and "Divide tempdb among multiple physical files" are not relevant to my question because I want to avoid use tempdb at all. "Add more RAM to your server" - this is what I already know and going to do and this is why I am asking, so it does not help. This is why I don't accept that answer. – alpav Apr 24 '12 at 15:50
  • Your question is too generic and there is no "correct" answer for it. With small datasets and large amount of RAM you *might* be able to avoid using tempdb, but even then, if you use complex queries (i.e. multiple JOINs/aggregates etc) and have a heavy load (i.e. large amount of queries running at once which can't be cached), SQL Server might still end up using tempdb. Also, using RAM Disk isn't officially supported by SQL Server. – BluesRockAddict Apr 24 '12 at 16:09