Transfer SQL TempDB

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.

  Step 1:  TSQL script to identify TempDB location:

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

   TSQL output:

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

   TSQL output:

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

  Step 4:  Verify Change - See Step 1.

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

That's It!

References:

http://dbadiaries.com/tempdb-best-practices
http://dbadiaries.com/sql-server-tempdb-whats-it-for
http://www.mytechmantra.com/LearnSQLServer/How-to-Move-TempDB-to-New-Drive-in-SQL-Server/