December 1, 2014 at 9:54 am
I am looking for advice on installing SQL Server 2012 on a VM. Let's say you have 8 cores and 1.5 TB of drive space and 16 Gigs of RAM. There is only one production database which about 40 Gigs in size. The Max memory will be set 12 Gigs.
How would you recommend partitioning the space (I am assuming you want 3 drives)?
Where would you place the transaction logs (I am assuming one per core - even though there is just one main database)?
Which drive would place the SQL install on (on a different drive than the OS)?
How do you avoid the VM accessing the same alotment of disk?
Is it really necessary to have different drives for the VM (performance wise since they all access the same alotment of disks)?
December 1, 2014 at 10:57 am
I am looking for advice on installing SQL Server 2012 on a VM. Let's say you have 8 cores and 1.5 TB of drive space and 16 Gigs of RAM. There is only one production database which about 40 Gigs in size. The Max memory will be set 12 Gigs.
You should test this setup if this is how you are going to size a single VM, unless you are referring to the host possibly? More does not always mean better when it comes to VMs. Just as a reference the previous PASS Summit 2014 included a session that is on PASS TV for sizing VMs and the speaker spoke of a large VM that had a high number of cores assigned to it. In that situation they received more performance by lowering the number of the cores on the server. It is something you just need to test with your environment.
How would you recommend partitioning the space (I am assuming you want 3 drives)?
I try to keep 6 drives standard on a VM:
1 - System OS
2 - App (SQL Server binaries, root instance directory)
3 - Data files
4 - Log files
5 - tempdb (data and logs)
6 - backups (optimal depending on how backups are done)
On average you generally are not going to see much difference in performance with tempdb data and log files being split so initially I keep them on the same drive unless performance dictates otherwise.
Where would you place the transaction logs (I am assuming one per core - even though there is just one main database)?
You will never need multiple log files on user databases for day-to-day activity, there is no performance improvement with it.
Which drive would place the SQL install on (on a different drive than the OS)?
As noted above I try to if space can be carved out for it simply because it just keeps that file access separate from the OS. Will you see a big performance improvement out of it, not likely right off. This is more of a preference for most folks.
How do you avoid the VM accessing the same alotment of disk?
This is a bit of a detailed answer I think and not one I can fully answer. I would suggest getting to be friends with your local VM and/or SAN admin on this one. It all depends on how your VM infrastructure is managed I think.
Is it really necessary to have different drives for the VM (performance wise since they all access the same alotment of disks)?
Yes, I think it will. When you have multiple drives assigned to a VM, that is multiple paths being used to access the disk at the other end. So when you go to write data the traffic required against both your data and log file can take multiple paths to those disk, instead of down one, logically speaking. I posted a screenshot of the slide from the PASS TV session I mentioned earlier on my Twitter account that can give you an idea of complexity of storage.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
December 1, 2014 at 11:06 am
Thanks Shawn.
I was wondering also how you would allocate space for each drive. Let's say there is 1 TB space available and you have 40 or 50 Gig Database.
How much space for each drive: OS, binaries, etc?
December 1, 2014 at 11:18 am
TJT (12/1/2014)
Thanks Shawn.I was wondering also how you would allocate space for each drive. Let's say there is 1 TB space available and you have 40 or 50 Gig Database.
How much space for each drive: OS, binaries, etc?
This is partly based on things like OS version and growth potential of your database(s). With the OS I generally try to get 80GB, no less than 60GB probably if you are on anything above Window Server 2008 R2. If you end up having to make the OS drive smaller just remember it will require more space management for things like security tools that may cache definition files on the system drive or Windows Event Error Reporting (would disable this if allowed).
I would say generally that the App drive can be about 50GB, if SQL Server is the only thing to be installed. It leaves room for patch files to be uploaded for local installs and if you ever need to run a trace or Extended Event with a file target.
The remainder is based on the needs of the database(s) as to how you carve it up for data, log, and tempdb. Obviously your log drive shouldn't need to that much space if you are managing the log size properly. I might keep tempdb drive at 25GB just to leave room, but again is based on your needs.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
December 1, 2014 at 11:37 am
Thanks Shawn, sorry if I have a ton of questions
In the installation there is:
Shared Feature directory
Shared Feature directory (x86)
(I'm guessing that if the OS is on C:\, you would normally place this on D:\ for example)
Then
Next there is Instance Root Directory and once again I am guessing this would be D:
After this there is the Data Engine Configuration section and more specifically the Data Directories tab
Data Root Directory: (I'm guessing this D:\)
User Database directory: E:\ ???
User Database Directory: E:\ ???
Temp DB Directory: F:\
Temp DB log Directory: F:\ ???
Backup Directory: G:
December 1, 2014 at 11:48 am
Shared feature directory should not be changed from default, those are just the management tools and clients. The instance root directory is what you would set to your app drive if you are going to use one.
The data directories you break up according to how you carved up your space and the drive letters assigned to each.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
December 2, 2014 at 9:28 am
B:\ Backups
C:\ OS
D:\ Installation Files, Paging file etc
E:\ SQLData .mdf .ndf (usually make this drive the root for SQL)
F:\ SQLLogs .ldf (including TempDB Log)
G:\ TempDB .mdf .ndf
December 3, 2014 at 6:08 am
TJT (12/1/2014)
How would you recommend partitioning the space (I am assuming you want 3 drives)?
You'll need as many drives as your design dictates, typically its BP to separate
OS = C:
SQL Binaries\Apps = D:
SQL Data files = E:
SQL Log files = F:
SQL Tempdb = G:
SQL Backup = H:
Obviously the drive letters above are your choice, or you may use Mount Points instead.
Apps and binaries are separated from the boot\system volume to avoid the situatiion whereby the OS drives gets filled by app files and Blue screens the server.
TJT (12/1/2014)
Where would you place the transaction logs (I am assuming one per core - even though there is just one main database)?
You're not the first here, read the old one file per core baloney and go barmy with it :rolleyes:
It does not apply to transaction log files, it even states this in the notes.
TJT (12/1/2014)
Which drive would place the SQL install on (on a different drive than the OS)?
Yes for the reason above, stop the app files filling the system volume.
TJT (12/1/2014)
How do you avoid the VM accessing the same alotment of disk?
Not sure what you mean here, do you mean how do you stop 2 different VMs accessing the same virtual disks?
TJT (12/1/2014)
Is it really necessary to have different drives for the VM (performance wise since they all access the same alotment of disks)?
The virtual machine virtual HDDs may well occupy the same ESX datastore as other VMs, oversubscribing is a big killer in virtual environments.
What you need to remember is that for every virtual network card, virtual hard disk, virtual scsi adapter you attach to a VM it requires a portion of resources from the host machine, these resources being host memory and host physical CPU time.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 3, 2014 at 2:12 pm
There has been a lot of great feedback to my questions and I have a few more questions:
Would you recommend splitting TempDB into multiples files equal to the number of CPUs, for support of one major database? If so, can someone explain the rational behind this? I am just trying to learn as much as possible
I liked Shawn's explaination for having multiple drives on a VM "When you have multiple drives assigned to a VM, that is multiple paths being used to access the disk at the other end. So when you go to write data the traffic required against both your data and log file can take multiple paths to those disk, instead of down one, logically speaking". I am wondering if anyone else has anything more to add to why multiples drives on a VM are beneficial for a SQL installl on a VM?
December 3, 2014 at 3:05 pm
TJT (12/3/2014)
There has been a lot of great feedback to my questions and I have a few more questions:Would you recommend splitting TempDB into multiples files equal to the number of CPUs, for support of one major database? If so, can someone explain the rational behind this? I am just trying to learn as much as possible
Using multiple cores helps to alleviate contention; that is, you don't have multiple processes waiting for one another (as much) because each can write to a different file. A good rule of thumb is one file per CPU; others might suggest one file per core. For example, my main production server has four quad-core processors; we have four tempdb files, and that seems to have worked out well enough that he haven't bothered changing it. It performs well in our case, and it's very manageable.
Depending on your systems, you might find more or less to be appropriate; the nice thing is that it's relatively easy to change the number of files that tempdb uses.
I liked Shawn's explaination for having multiple drives on a VM "When you have multiple drives assigned to a VM, that is multiple paths being used to access the disk at the other end. So when you go to write data the traffic required against both your data and log file can take multiple paths to those disk, instead of down one, logically speaking". I am wondering if anyone else has anything more to add to why multiples drives on a VM are beneficial for a SQL installl on a VM?
I think he sums it up nicely... you're basically allowing SQL to access the drive on multiple logical paths. Ideally you would have your data and logs (and system, installation, and tempdb) on separate physical drives but even if it's one single lump of drives (or even one drive), having those multiple paths is an advantage.
You might even find it beneficial to separate higher-usage databases to their own additional drives; so using Shawn's example:
1 - System OS
2 - App (SQL Server binaries, root instance directory)
3 - Data files
4 - Log files
5 - tempdb (data and logs)
6 - backups (optimal depending on how backups are done)
7 - Data files (high usage)
8 - Log files (high usage)
On our main server, we have six different sets of data/log drives for various systems. In our case, that is to facilitate our disaster recovery functionality; but it has also given us a noticeable performance improvement.
December 3, 2014 at 3:48 pm
Hi cphite,
Can you explain/expand on this: "it's relatively easy to change the number of files that tempdb uses."
Thanks
December 3, 2014 at 5:42 pm
TJT (12/3/2014)
There has been a lot of great feedback to my questions and I have a few more questions:Would you recommend splitting TempDB into multiples files equal to the number of CPUs, for support of one major database? If so, can someone explain the rational behind this? I am just trying to learn as much as possible
With tempdb data files to number of CPUs I follow the formula that Bob Ward came up with and Paul Randal references here[/url].
With data files in tempdb you can always add additional files at anytime, but you cannot remove as easily, most cases it requires restart of the database engine to remove them.
The formula in the blog post is with servers that have 8 cores or less you have:
#data files = #cores
Servers over 8 you just start with 8 data files and only add more (4 at a time) if you are seeing memory contention on tempdb.
Identifying memory contention on tempdb is using Paul's wait stats query and if you see PAGELATCH_XX waits on average those are likely gonna be from tempdb. To verify, I will use Adam Machanic's sp_WhoIsActive, and it will show the page that is in contention along with what database it is in.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
December 4, 2014 at 4:40 am
TJT (12/3/2014)
Would you recommend splitting TempDB into multiples files equal to the number of CPUs, for support of one major database?
Do you even have tempdb contention?
Rather than just blindly following an age old practice test to see if you do have the issue.
TJT (12/3/2014)
If so, can someone explain the rational behind this? I am just trying to learn as much as possible
To explain this read through Paul Randals post[/url] it gives exact details
TJT (12/3/2014)
I liked Shawn's explaination for having multiple drives on a VM "When you have multiple drives assigned to a VM, that is multiple paths being used to access the disk at the other end. So when you go to write data the traffic required against both your data and log file can take multiple paths to those disk, instead of down one, logically speaking". I am wondering if anyone else has anything more to add to why multiples drives on a VM are beneficial for a SQL installl on a VM?
As i said, for every virtual hard disk, virtual scsi adapter and virtual network card your VM will require resources from the host in the form of memory and CPU to service them.
Add lots of disks, nics and scsi adapters unnecessarily and you could end up having an adverse effect.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply