February 9, 2017 at 4:01 pm
In the "old days" is was common for MDF/NDF files of large databases or MDFs of different databases to be spread across multiple logical disks. The reasons for this included.
1) Spinning hard drives have a physical size limit.
2) Increased performance by spreading I/O over separate hardware paths and separate RAIDs.
3) MBR partitions are limited to 2TB.
One of the downsides to this is that when one drive approaches full capacity, you sometimes had to take a database offline to move a file to a drive with room to spare.
But in this age of non-spinning mass storage and GPT disks, I/O speeds are faster and you are no longer limited by the size of the disks you have installed or the MBR size limit. Does it make more sense now to build a server with a massive logical disk on solid-state storage than to spread you files across Data1, Data2, Data3, etc.?
February 9, 2017 at 4:34 pm
my two cents, by spreading database data-files to multi-pal files you can still gain SOME performance improvements as the OS has to use multi-pal separate threads to read/write to the files, and it can read/write in parallel, as for the downside you mentioned you can create another data file on other disk and then move the data from one file to another, you do not have to take the database offline..
February 10, 2017 at 5:05 am
dan-572483 - Thursday, February 9, 2017 4:01 PMIn the "old days" is was common for MDF/NDF files of large databases or MDFs of different databases to be spread across multiple logical disks. The reasons for this included.
1) Spinning hard drives have a physical size limit.
2) Increased performance by spreading I/O over separate hardware paths and separate RAIDs.
3) MBR partitions are limited to 2TB.One of the downsides to this is that when one drive approaches full capacity, you sometimes had to take a database offline to move a file to a drive with room to spare.
But in this age of non-spinning mass storage and GPT disks, I/O speeds are faster and you are no longer limited by the size of the disks you have installed or the MBR size limit. Does it make more sense now to build a server with a massive logical disk on solid-state storage than to spread you files across Data1, Data2, Data3, etc.?
If these disks are LUNs on the same SAN RAID group you have no benefit. In fact by using multiple paths you'll just saturate the storage
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 10, 2017 at 4:51 pm
How about with XtremIO or similar flash storage? That's what the server I'm building will have access to.
February 10, 2017 at 6:32 pm
dan-572483 - Friday, February 10, 2017 4:51 PMHow about with XtremIO or similar flash storage? That's what the server I'm building will have access to.
does SDD make it start doing parallel reads/writes without multi-pal files?
February 12, 2017 at 5:38 am
goher2000 - Friday, February 10, 2017 6:32 PMdoes SDD make it start doing parallel reads/writes without multi-pal files?
SQL can always do reads and writes with multiple threads, no matter what the underlying file structure is. The 'one thread per drive' is a very old myth.
Any thread in SQL can issue reads/writes to any file, any time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2017 at 1:24 am
GilaMonster - Sunday, February 12, 2017 5:38 AMgoher2000 - Friday, February 10, 2017 6:32 PMdoes SDD make it start doing parallel reads/writes without multi-pal files?SQL can always do reads and writes with multiple threads, no matter what the underlying file structure is. The 'one thread per drive' is a very old myth.
Any thread in SQL can issue reads/writes to any file, any time.
Sorry to Hijack goher2000 thread.
Gail , you meant to say that single file and multiple files doesnt make any difference?
February 14, 2017 at 2:25 am
Rechana Rajan - Tuesday, February 14, 2017 1:24 AMGail , you meant to say that single file and multiple files doesnt make any difference?
That's not what I said.
Multiple files on the same disk can have overheads if there's enough. On different might spread IO load out (if really multiple disks), multiple files allows for backup/restore to be more granular.
What I meant is what I said, that 'SQL uses one thread per file/disk' is a very old myth and is not true.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2017 at 4:45 am
GilaMonster - Tuesday, February 14, 2017 2:25 AMRechana Rajan - Tuesday, February 14, 2017 1:24 AMGail , you meant to say that single file and multiple files doesnt make any difference?That's not what I said.
Multiple files on the same disk can have overheads if there's enough. On different might spread IO load out (if really multiple disks), multiple files allows for backup/restore to be more granular.What I meant is what I said, that 'SQL uses one thread per file/disk' is a very old myth and is not true.
Thanks Gail.
Didnt understand the term "if really multiple disks" . By "More Granular" i hope you meant about filegroup backup\restore.
Thanks Again.
February 14, 2017 at 4:55 am
By "More Granular" i hope you meant about filegroup backup\restore.
Backing Up Specific Files or Filegroups
BACKUP DATABASE { database_name | @database_name_var }
<file_or_filegroup> [ ,...n ]
TO <backup_device> [ ,...n ]
[ <MIRROR TO clause> ] [ next-mirror-to ]
[ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ]
[;]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2017 at 5:32 am
GilaMonster - Tuesday, February 14, 2017 4:55 AMBy "More Granular" i hope you meant about filegroup backup\restore.
Backing Up Specific Files or Filegroups
BACKUP DATABASE { database_name | @database_name_var }
<file_or_filegroup> [ ,...n ]
TO <backup_device> [ ,...n ]
[ <MIRROR TO clause> ] [ next-mirror-to ]
[ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ]
[;]
Thanks Gail.
February 14, 2017 at 7:03 am
I hope no-one minds me saying this, but 'large disks or many disks' already seems a yesterday question.
If you are in the cloud then you have the option of SSD or spinning rust spread over so many physical devices this question is not relevant.
If you are in-house then you totally need to look at hyper-convergence. Our place is replacing its complete compute and storage resource with hyper-converged kit, planned live date end of March. It is costing about the same as the planned SAN capacity increase we were looking at but will increase available IOPS by well over 1 order of magnitude, improve available CPU GHz and memory, and reduce ongoing operational costs. The pixie dust that does the IOPS bit is NVMe storage, which will be all SSD, and it is NVMe that makes the large or many disk question obsolete.
Hyper-convergence in 1Q 2017 is still a bit leading edge, but by the end of this year will be commonplace. Windows 2016 has all the OS software needed to take advantage of it, and hardware vendors are now releasing commodity kit that does the hardware bit. The cost advantage of moving to this platform is tremendous, even before you factor in the IOPS boost.
To do hyper-convergence right still needs good old project planning, but without doubt it offers a hardware platform that gives significantly better performance at a much lower cost base than any previous kit.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply