June 1, 2021 at 2:11 pm
I plan to deploy a 2019 standard with two instances on a virtual machine (vmware with backend iscsi san for datastore). For a physical machine, I would definitely allocate separate volume for tempdb. But wanted to get some ideas on how to go about designing when it came to a VM. Should I create a separate disk/volume for tempdb and if so, would you recommend creating two disks/volumes for tempdb of each instance?
With multipath datastore that has a SAN in backend I don't know if separating tempdb has any significant benefits.
My disk plans are - a separate data and log disk/volume for each instance. And depending on suggestions from this forum place tempdb either on a separate volume or within the data drive of each instance.
Thanks.
June 1, 2021 at 4:47 pm
My opinion - with it being on a SAN, there isn't a lot of need to put it on separate disks UNLESS your SAN admin can guarantee that the separate partitions will ALWAYS be on separate disks.
You will likely still get a benefit of the OS overhead by having it on different disk, but on the SAN back-end if the SAN admin can't promise that it will be on different physical disks, you could end up having some contention there and may have I/O performance issues due to the SAN setup.
Same thing applies to the different disk for data, log, and instances. Hypothetically, if the ONE physical disk in the SAN is larger than all of the data and log files, even though to the OS it is a bunch of separate disks, it MAY reside on ONE physical disk and you MAY have a lot of contention due to that.
What I would encourage you to do is to talk to your SAN admin to see how you can allocate dedicated disk to your OS and what sizes they can dedicate to you. Once you know what you can get for dedicated disk, you can allocate the data and log files for each instance per dedicated disk.
If your SAN is anything like mine, you are going to have a mix of disk speeds and sizes too which can make things interesting. One tip (that I picked up at PASS Summit from a presentation by Pinal Dave a few years back) - put your log files onto the fastest disk you can to get the most "bang for your buck". If possible, put your data file there too, but based on his presentation where he demoed putting both on HDD, data on SSD log on HDD, log on SSD data on HDD, both on SSD, the performance boost of putting log on SSD was the most substantial with data on SSD helping, but not as much as log on SSD. If you can put both on SSD, do that as it is definitely a performance booster, but if you can only do data OR log, put log on SSD.
As for if your tempdb should live on its own disk or shared with another database, that is really dependent on how much it is utilized. If your database is write heavy, read light, then tempdb may not get used much (I say may as it can still be used heavily if things are written to tempdb or temporary objects before a final write). If your database is read heavy, write light, then tempdb may benefit from being on its own disk (I say may again as if the reads are all from single tables or don't result in spills or temporary objects, it may not use tempdb that much). You need to analyze your workflow and see how often tempdb is utilized to know for 100% certain if it needs its own disk or not. Plus it depends on the demand of the system, space available, etc.
In the end, if you put it on its own partition but on the SAN it is on a shared disk, you may not notice any difference. With a SAN backend, your best bet is to focus more on "should this be on SSD or HDD?". On the OS side, the overhead of multiple reads/writes to a single disk is likely going to be very light and you may not even notice a performance difference between them.
If possible, I would request both and do some load testing on your system and see what the performance boost is like having it on its own disk vs shared with the instance. Keeping in mind that the more disks you have, the more overhead you have watching free space and being prepared for low disk alerts. Single disk means you only have 1 drive to watch as things grow. Having 100 drives means you have 100 drives to watch for and allocate space accordingly. Having 1 10 TB disk is easier to manage than having 100 100 GB disks for example.
The above is entirely my opinion.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 1, 2021 at 5:27 pm
If you take the SAN out of the equation - and only look at it from a management of each instance on that server - you should see that separate volumes would be better than a single shared volume, especially for tempdb.
Forget about performance and think about what happens if one instance runs a 'bad' query that forces tempdb to grow out to fill that volume. If it is a shared volume and the second instance needs to grow tempdb - it will fail because there isn't any space available because the first instance is using all of the space on the volume.
The same goes for all database files - you don't want one instance impacting the other instance when something unusual happens. If instance1 runs a large transaction causing one of the databases to grow to fill the drive - and instance2 needs to expand a transaction log it will fail.
Regardless of how the SAN is configured - you should have separate volumes. At a minimum you should have a volume for system databases, a volume for user databases and a volume for tempdb. Separate out tempdb from system - and transaction logs from user databases for larger systems or systems with a high amount of activity.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 2, 2021 at 5:58 pm
I tend to agree with Jeffrey. While the separate volumes might not give performance improvement, they certainly make it easier to grow/move/change if you separate instances at some point.
June 3, 2021 at 7:32 pm
Thank you all!
I think I will have a separate disk for tempdb of each of the instances. But have system databases reside in the drive of the user databases since performance doesn't seem to be the issue from what I read.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply