Is it better to put the tempdb on a different normal IOPS drive, compared to the main data drive which is about 10 times higher IOPS?

Question

Hi All

Is it better to put the tempdb on a different normal IOPS drive, compared to the main data drive which is about 10 times higher IOPS?

Thank you

Answer 1

How should we answer your question (without knowing any numbers)?

Normally I would recommend someone to install tempdb and UserDBs on different disks on different controllers with equal performance.

As we for exampe don't know how much data is in your database, how your workload looks like and what your understanding of normal and high IOPS is, we cannot make a recommendation.

Maybe it is worth to move tempdb-datafiles to a separat disc even if that disk is slower but maybe not...
for example it doesn't make any (or just a little) difference if both disk are connected to the disk controller on mainboard (system is hardware with local disks and without special controller).
or if your database just is 5GB and your tempdb also has just 4 files with 512MB and you are running a small website on it...

Without getting more details on your environment it will be hard to recommend a good solution... other then "give it a try, move tempdb-datafiles to that new (slower) drive and test your system with that new configuration"

Answer 2

Hi BjoernPeters and thank you for your very interesting and helpful comments.

Our DB is hosted on a virtual instance in the cloud on AWS so the machines and drives are both very well supported infrastructure wise. All our drives are SSD.

Our main DB is 118GB (although there are a number of other smaller databases on the same installation).

The data is on it's own D: drive separate to the OS on the C: drive, and this D: drive is AWS's top spec IOPS drive giving us 5000 IOPS (and is ridiculously expensive).
If we move the tempdb to a separate drive, this would be a normal drive due to cost, which will be 300 IOPS (same as the C: drive and all other drives on all our servers).

The database is actually pretty busy as it drives 2 websites and more, but I don't know how to check the drives because there are lots of stats that don't make any sense to me.
(One thing I did notice is the drive idle time seems high on both C: & D: drives although I know, this is just one of many factors).

As for disk controllers I don't really know much about this unfortunately, and in this case it would be all behind the scenes as Windows will just add the drive when it boots up after it's added to the configuration .

Does this help?

Thanks again

Answer 3

You would have to test it in your configuration to determine if it affects your performance significantly or not.

Normally, tempdb is recommended to be very fast, separate drives. However, with modern SAN/Virtualization technology this is less and less of a consideration.

Answer 4

Hi @CrystalDevelopment ,

Most cloud providers offer different levels of storage as it pertains to performance. For example, Microsoft Azure and Amazon AWS have multiple options for SSD disk, as well as HDD disk offerings. For most cloud-provisioned instances, you have two options for achieving a target goal of IOPS (the unit of measure of input and output operations per second). Depending on your cloud provider, you can provision a class of drive where the amount of IOPS is based on the size of the drive in GB, or you can purchase a class of drive that allows you to provision a specific level of minimum IOPS. There are also allowances for “bursting” the IOPS as necessary if the provisioned level of IOPS is not suitable for the load--one more plus for cloud-provisioned SQL Server instances. Most storage changes that involve adjusting the amount of IOPS provisioned for a SQL Server are online operations. Adjusting your provisioned IOPS up or down usually does not require a service restart or a server reboot.
For more information, please read this article: https://www.itprotoday.com/cloud-storage/are-you-making-right-cloud-storage-decisions-sql-server-tempdb

Our DB is hosted on a virtual instance in the cloud on AWS so the machines and drives are both very well supported infrastructure wise. All our drives are SSD.

Here is a AWS Support site i fond from google: https://aws.amazon.com/contact-us/

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.