Move TempDB for Performance - SQL in Sixty Seconds 107

TempDB is one of the most important database and often it is located on the slow drive or on the drive where OS is installed. Let us learn how we can move it to different drives quickly and efficiently.

0:00 Introduction
0:25 Real World Scenario
1:21 Demonstration - Change the location of TempDB
2:22 Summary

#SQLServer #PerformanceTuning #SQLAuthority

[nivashangovender]

Your videos are awesome! Thank you Sir!

[nandakumarsukumar]

Great 👍 tip Pinal, keep your good work

[jordonpilling]

In the age of RAID and volumes on virtual disks and SAN's, this best practice (of moving tempdb) is becoming less and less effective. More often then ever these days, your C: and D: will be on same the physical drives. And SAN's get even more confusing as they'll have a mix of spinning drives and SSD's, . data that is accessed often is moved to SSD's and your OS is oblivious to this as it's all hidden by the raid controller / iSCSI interface. I strongly recommend running Crystal Disk Mark or some other such utility on your volumes to verify if one is faster than the other, and crucially, run it on both volumes at once to see if your speeds drops if other volume is busy - if they do, it means your drivers are both sharing physical drives and moving tempDB will have very little impact / if any. That said, if you know the D: is on dedicated spindles or drives, you should be good to go and will see great speed improvements :)
Edit: This info is aimed at people watching the video - i'm sure Pinal already knows this but didn't want to overcomplicate the issue :)
Edit2: Same thing goes for the old best practice of moving your LDF's (log files) to separate volumes.

[Harshk]

Very good explanation.
I request you to make an video for tempDB full issue. For releasing space without restarting SQL SERVER and small demonstration for why tempDB becomes full.

Thanks

[krishnaki]

Pinal I love your blog and video content.. Thanks for educating us in sql server.. May I know why didn't you copy the tempdB mdf, ldf from C to D drive post Alter statement? Seems it creates fresh tempdB in our case. Is it fine to move old tempdb files to new location and restart sql server?

[rajprabuit]

Pinal, Thanks for this video.
Is this applicable for user db?

[davidstaub]

what about using a dedicated SSD for Temp databases and log files?

[chaitanyadhole]

Hello Sir, I always watch your videos. Videos are very useful. Can you please make one video regarding, how to recover master database if it gets corrupted? It will be more helpful.

[sadhanasawant]

Rather than Indexes is there any other way to reduce SP execution time?

[shadow]

You warned that this should be tested before you implement to avoid issues with your SQL Server. What would be the best method to test this out? Also, if all your disk is SAN, will this make much difference? What about splitting your database, logs, and temp to separate disks?

[gemale]

Sir I have about 7 drives in my sql server how I can find hitch is the fasted drive among those? Pls advise. Thanks

[mahadevareddy]

Hi sir,
Can you pls make a video on how to fix Tempdb version store issues?

[brettoconnor]

Pinal - does this same advice apply to a virtual machine in the cloud?