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

Improve this question
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. Commented Nov 22, 2018 at 8: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? Commented Nov 22, 2018 at 8: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 Commented Nov 22, 2018 at 8:12
Related questions
16
How tempDB works?
6
SQL Server 2008 TempDB on other HD
1
TempDB one physical datafiles per CPU core in SQL Server 2000
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.