January 29, 2016 at 9:06 am
I've never heard of any performance issue with using mount points. On the contrary, as mount points can be on different disks \ raid's \ SAN's, you get flexibility in terms of performance - for example you can put your TempDB on expensive fast disks, while reserving your slower, cheaper, disks for less IO intensive tasks.
HTH,
Martin
January 29, 2016 at 9:42 am
dajonx (1/29/2016)
Hi,What do you think about using mount points for SQL Server? Is there a performance difference between using a mount point or a drive letter?
Any insight is greatly appreciated!
Mount points are fully supported under sql server from 2005 onwards. Please see my article at this link[/url] for more info.
webtekkie (1/29/2016)
I've never heard of any performance issue with using mount points. On the contrary, as mount points can be on different disks \ raid's \ SAN's, you get flexibility in terms of performance - for example you can put your TempDB on expensive fast disks, while reserving your slower, cheaper, disks for less IO intensive tasks.HTH,
Martin
That's correct there are no performance issues when using mounted volumes as the I\O is directed to the individual volume and not the root drive.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 29, 2016 at 10:16 am
Thank you!
I was wondering what you think about having two volumes for TempDB (data and log) or should I split up my tempdb files (I have eight) into two volumes for TempDB data (four on each volume)? Would that increase performance since it's utilizing two volumes instead of one?
January 29, 2016 at 10:38 am
dajonx (1/29/2016)
Thank you!I was wondering what you think about having two volumes for TempDB (data and log)
You could split tempdb data and log, these must be kept separate fro user database files.
dajonx (1/29/2016)
or should I split up my tempdb files (I have eight) into two volumes for TempDB data (four on each volume)? Would that increase performance since it's utilizing two volumes instead of one?
First question is, do you understand why you have multiple files for TempDB?
You would only usually split Tempdb data files across multiple volumes when I\O performance becomes an issue, even then the volumes must all have the same performance characterstics.
For instance putting 4 data files on a pair of mirrored sata disks and the other 4 on a SAS high speed array would be pretty pointless as one set would end up waiting for the other.
My point is, understand why you have multiple files in the first place (to overcome allocation contention) and then think about whether you need multiple disks (to spread I\O load).
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 29, 2016 at 12:07 pm
Yes, I do. I also read that eight data files should be the minimum if the server has more than eight cores (server will have 16 cores on two CPUs). (http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/)
The storage we are using is a Dell Equallogic (7 SSD/17 SAS hybrid) and SQL volumes would be going on that array.
I guess if I was to use two volumes to hold the tempdb data files, it would be the same performance as one volume since it's on the same array. Is that accurate?
January 29, 2016 at 4:16 pm
dajonx (1/29/2016)
Yes, I do. I also read that eight data files should be the minimum if the server has more than eight cores (server will have 16 cores on two CPUs). (http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/)The storage we are using is a Dell Equallogic (7 SSD/17 SAS hybrid) and SQL volumes would be going on that array.
I guess if I was to use two volumes to hold the tempdb data files, it would be the same performance as one volume since it's on the same array. Is that accurate?
Yes I have read that article but I still prefer to match 1\4 to 1.
So, 16 cores start with 4 files.
As Paul's article says if you're suffering pagelatch_xx waits then add more files.
If pageiolatch_xx waits then move files to faster disks or spread them out.
With the improvements to bitmap allocation some systems may not even experience contention, monitor for it first
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 29, 2016 at 9:48 pm
Mount points allow you to mount multiple physical devices under one drive letter, where they look similar to directories. Using mount points is really straightforward.Check out the following article to know the advantages of using mount point in sql server: http://www.sqlserverlogexplorer.com/using-mount-points-in-cluster/
January 30, 2016 at 1:59 am
Theyre useful for standalone instances as well as clustered
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 1, 2016 at 8:06 am
Thank you very much! I'm going to test this out. I will be upgrading from 2008 R2 to 2016 (whenever it comes out) so I would like to decrease drive letters since currently it's taking up a lot.
One more question about mount points. You wrote that the root volume must be small to prevent files/folders being created on that volume. So really, I can create a 1 MB root volume and that's ok?
February 1, 2016 at 8:56 am
dajonx (2/1/2016)
One more question about mount points. You wrote that the root volume must be small to prevent files/folders being created on that volume. So really, I can create a 1 MB root volume and that's ok?
That's probably a little too small, the recommended is about 100MB but can be governed by whatever size volume your storage system will allow you to create.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 2, 2016 at 3:00 am
I think 1GB should be ideal for a base drive letter and then configure mountpoints for that drive letter with required sizes.
February 2, 2016 at 11:28 am
I am having issues with mount points... For some reason, SQL Server is saying that I don't have enough space to restore a database when I have (according to Computer Management) 900 GB volume with 100% free.
Is there something I'm missing?
EDIT: Nevermind! I messed something up... Sorry!
February 3, 2016 at 1:45 am
😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply