SQL Server Performance Forum – Threads Archive
Data files / file groups and multiple disks
Hello I just read http://www.sql-server-performance.com/ac_filegroup_performance.aspand
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx I have some questions about creating multiple data files and filegroups when you have multiple disks. Today we have 14 disks in a SAN.
We only have one small database (20GB). We place the transaction log on 2 disks (raid 1)
we place the system databases/tempdb and transaction log backup on 2 disks (raid 1)
we place the data files on 10 disks (raid 10) We are going to extend our SAN with 6 more disks. I am thinking of having two more for the data and then separate the system db/tempdb/t.log backup on 6 disks instead of 2. After that we will have a setup like 2 disks for tempdb (raid 1)
2 disks for system databases (raid 1)
2 disks for trasaction log backup (raid 1)
2 disks for transaction log (raid 1)
12 disks for user database (raid 10) Now to my questions. I read some recommendations that
quote:It is recommended to have .25 to 1 data files (per filegroup) for each CPU on the host server. This is especially true for TEMPDB where the recommendation is 1 data file per CPU.
Does this mean that if we have 4 CPUs, that I will create 4 data files for the temp-db? But we would still only have 2 disks. What is happening in the background here? Why is it better to have more data files? Then in the other article (from sql-server-performance), i read this:
quote:If you have several physical disk arrays, try to create as many files as there are physical disk arrays so that you have one file per disk array.
What does that mean in my case?
And another one from the same article
quote:For heavily accessed tables, place these tables in one filegroup and place the table’s indexes in a different filegroup on different physical disk arrays.
If we today use 10 disks (raid 10) for booth the data and the indexes, how can i measure how many disks that is needed for the indexes?
And the last question.Do I just create an extra datafile and then it will be used by SQL-Server, or do I need to specify it in any more way? Some more information about how multiple data files is appreciated.
A lot of questions! About the disks you are going to add for the user database files: it depends on how you configure the SAN whether it makes sense to add file groups. If you expand the virtual drive you have , I don’t see the point in adding filegroups. If you add another virtual drive, then you’d have to add filegroups to the database for SQL to use them. Plus, you’d have to move objects to this filegroup; SQL doesn’t do this automatically. This would require you to figure out how to best divede the object between the filegroups.
With regard to your question about the number of data files for tempdb, the suggestion of one per CPU is in relation to contention on the data files, so they could site on the same disk. I would think you only need to put them on seperate disks if you have an I/O problem for the data files. Do you have any I/O issues at all?
Tempdb concurrency issue was resolved in SQL 2000 SP4, and I don’t think it still exists in 2005. In the following article MS recommended to use one file per CPU…
When you use SAN, sometimes it not going effect anything even if you create multiple files based on how LUNs were created… FIX: Concurrency enhancements for the tempdb database
http://support.microsoft.com/kb/328551 Mohammed U.
There is a Whitepaper on the Microsoft site, which does discuss how to monitor and alleviate tempdb contention problems on SQL 2005 – although I admit I have never seen any contention. http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx Mark
]]>