4

My SQL Server instance has a small 32 GB SSD which is mostly unusedw while the remaining drives are SAN storage. I considered moving my tempdb to the SSD but my fear is the odd occasion when the tempdb exceeds the SSD size will cause the query to stop. I've read that multiple files will allow the tempdb to split usage across drives but is there a way to put a preference on the SSD drive until full in which case it will resume on the SAN storage?

1

there a way to put a preference on the SSD drive until full in which case it will resume on the SAN storage?

You can limit the tempdb autogrowth for the primary datafile (that you have on SSD) to 30GB and then you can create additional datafiles on the SAN.

The drawback will be that you will see imbalance in the tempdb datafile growth.

SQL Server allocates space in a round-robin fashion.

Important Trace flags when dealing with Tempdb :

-T1117 (helps keep data base files equally sized over their lifetime, by triggering all database files in a filegroup for autogrow at the same time.). Note that you wont need this trace flag as you will be eventually restricting the tempdb to NOT grow on SSD once you reach the threshold.

-T1118 (this trace flag switches allocations in tempdb from single-page at a time for the first 8 pages, to immediately allocate an extent (8 pages). It’s used to help alleviate allocation bitmap contention in tempdb under a heavy load of small temp table creation and deletion.) for tempdb.

Refer to Misconceptions around TF 1118 by Paul Randal and SQL Server 2008 Trace Flag -T 1117

1
  • 1
    The other option is to pre-size the SSD file to fill the disk (with auto-grow disabled) and have a second, minimum-size file on the SAN, with auto-grow enabled (at a sensible size!) so only in the event of the SSD filling does the SAN file start to grow. Obviously instant file init needs to be enabled. – Paul White May 17 '14 at 8:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.