Task:How to transfer the TempDB.
Problem:MS SQL ran slow on 6Gb/s SAS hard drive (HHD). Moved the user database (DB) to PCIe x4 (i.e., 31.52 Gb/s), MLC solid-state (SSD) storage -and it still runs slow!
Solution:Does the TempDB run on the old HHD drive or the new MLC SSD? Consider how the TempDB can generate serious disk I/O. Transfer the TempDB to the high-speed SSD as well. N.B., this entire process gave new life to my old DPM2010 server.
Use master GO SELECT name AS [LogicalName] ,physical_name AS [Location] ,state_desc AS [Status] FROM sys.master_files WHERE database_id = DB_ID(N'tempdb'); GO
tempdev C:\Program Files\Microsoft DPM\SQL\MSSQL10.MSDPM2010\MSSQL\DATA\tempdb.mdf templog C:\Program Files\Microsoft DPM\SQL\MSSQL10.MSDPM2010\MSSQL\DATA\templog.ldf
Step 2: TSQL script to move TempDB to new location.
USE master; GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'X:\SQL\Data\tempdb.mdf'); GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'X:\SQL\Data\templog.ldf'); GO
The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started. The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.
Step 3: Restart SQL services
Conclusion:SQL server runs quick with new storage. Disk queue lengths, for all disks, are a thing of the past. Individual results may vary based on load (duh).