Move TempDB for Performance – SQL in Sixty Seconds #107

Just another day while working with a client Comprehensive Database Performance Health Check, we figured out that they had slow overall performance because they had TempDB located on the slow drive. Today we will learn how we can move TempDB for Performance.

Before I continue I must say that you only need to do this if your TempDB on the drive which is slow. Additionally, when you install SQL Server by default and do not customize it, it is quite possible that your TempDB is located on the same drive where your Operating System is installed. It is never a good idea to install any database on the same drive as Operating System.

Here is another episode of SQL in the Sixty Seconds, where I explain how you can move TempDB to the different drive.

Lots of people also get confused looking at the name of the TempDB, they think it is not something very important or this database is not as helpful. Honestly, the truth is TempDB is one of the most important databases for SQL Server as pretty much most of the operations which can’t fit in the memory happens there (link joins, triggers, order by etc). If I name the TempDB, I would name it as an ImportantDB.

Please make sure that the drive where you are planning to move your TempDB is accessible by your SQL Server or you may have problems with starting the SQL Server. Additionally, after moving your TempDB to a new drive, you will have to restart SQL Server services to take the new place

Script for TempDB Move

In this blog post, I have all the necessary script to move TempDB: Moving TempDB to New Drive. Additionally, If you want to watch more such videos, you subscribe to my YouTube Channel.

Reference: Pinal Dave (https://blog.sqlauthority.com)