0

We find, for our application, locating TempDB files on RAMDisk improves performance significantly. But we don't want to allocate more RAM to RAMDisk than necessary. Nor do we want SQL Server to fail because it can't grow TempDB. I remember reading that you could put one TempDB file (one data, one log) on physical disk, and permit them to grow, and have the rest (several data, 1 log) on RAMDisk, and do not permit them to grow.

But ... is there any way to stop SQL Server from using those slow TempDB files until the other fast files are full?

Thank you in advance

Paul Davis
  • 13
  • 3
  • Easy. Fix your queries instead of trying to trick SQL Server. Instead of improving performance you are *reducing it* by *reducing the amount of RAM available for buffering*. SQL Server like all databases will use *all available memory* to cache as much data as possible to avoid IO. – Panagiotis Kanavos Nov 22 '18 at 08:07
  • 1
    Your problem is high tempdb usage, not that tempdb is slow. You get high tempdb usage when there's not enough RAM available (*hint,hint*) to store intermediate results. The fact that there's so much intermediate data is *also* a sign of trouble - are there missing indexes? Is SQL Server forced to read and sort a lot of data because there are no indexes on the fields used by a query? Are you using temporary tables and table variables excessively perhaps? – Panagiotis Kanavos Nov 22 '18 at 08:09
  • 1
    Tempdb is used by normal processes too, like snapshot isolation. Identify the *real* reason for high tempdb usage. If it's normal operations, use fast storage like an SSD to hold the tempdb files – Panagiotis Kanavos Nov 22 '18 at 08:12

0 Answers0