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?
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.