September 30, 2002 at 7:56 am
I am studying for the my MCDBA certification and I am confused on some basic issues:
1) I'm still uncertain on what the criteria is when creating multiple files (.mdf's). When is this usually done and why?
2) Same thing with Filegroups, my book gives an explanation of it and then says its usually not necessary to create multiple FileGroups.
Any help/explanation on these topics would be most appreciated.
Thanks.
September 30, 2002 at 8:31 am
If you had separate physical drives, you could create multiple .mdf files on each of the drives in order to reduce disk contention. That's the main reason for doing so.
As for filegroups, if you have say a group of tables that are hit very heavily, you may want to locate them on different physical drives. In this case you would create different filegroups with files on the appropriate drives and assign the tables to those filegroups. You can also do the same thing if you want to say put the indexes on a different set of physical drives from the data. Create the filegroups and assign the data tables to one filegroup and the indexes to another.
These are simplified examples, but generally that is how multiple files and filegroups can contribute to performance, at least with respect to the exams.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
October 1, 2002 at 6:26 am
Thanks Brian, that helps. In regards to locating files on separate drives are you saying that must be physically separate drives or can this be done in a RAID. I would assume since a RAID spans multiple drives that creating several files would not help. Am I understanding this correctly or is there a way to specify separate locations within a RAID? I appreciate your help. I'm still learing. 🙂
Thanks
quote:
If you had separate physical drives, you could create multiple .mdf files on each of the drives in order to reduce disk contention. That's the main reason for doing so.As for filegroups, if you have say a group of tables that are hit very heavily, you may want to locate them on different physical drives. In this case you would create different filegroups with files on the appropriate drives and assign the tables to those filegroups. You can also do the same thing if you want to say put the indexes on a different set of physical drives from the data. Create the filegroups and assign the data tables to one filegroup and the indexes to another.
These are simplified examples, but generally that is how multiple files and filegroups can contribute to performance, at least with respect to the exams.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
October 1, 2002 at 7:18 am
I think the idea of multiple files and filegroups is typically not employed in conjunction with RAID for the test.
In a production environment, if you're using RAID 0, RAID 1+0, or RAID 5, data is going to be striped across all the drives of the RAID set, and if you are using hardware RAID, the RAID set will look like a single drive to the operating system.
You could carry out the multiple files/filegroups across different RAID sets. For instance, I might have two RAID sets for the data files, one for data and one for indexes all in the eye of improving performance. The same can be true if you want to isolate certain tables from the rest of the database. Again, it's all about reducing disk contention. Except now instead of just multiple disks sharing the load, you've got multiple sets of multiple disks sharing.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply