SQL Server 2005 files and file group implementation

Question

SQL Server 2005 files and file group implementation

SQL_Helper

SSC Eights!

Points: 837
More actions
June 17, 2011 at 1:45 am

#252024

Hi;
Team; I have a plan to migrate SQL server 2005 databases from one server to another server (having multiple SAN paritions)
Now I want to implement following feutures
1) Right now I have databases in single mdf and single ldf file, on new server I want to create multiple data files and multiple log files (as we have 4 SAN partitions for DATA and 4 SAN partition for LOG) for performance enhancement.
2) by simple restoration on new server, I will have only 1 data and 1 log file, so is it required to add data files and log files manually?
3) what will be the best option, should I use default primary file group for all data files or should I crete new filegroup for data files
Help me on this Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

Please refer the below article it would give you a better idea about how to go about dealing with files and filegroups.

It might we worth considering splitting different database on each partition such as master , model and msdb on one , temp db on another , data files for a user database on one and log files for user database on another.

http://msdn.microsoft.com/en-us/library/ms179316.aspx

Jayanth Kurup[/url]

SQL_Helper SSC Eights! Points: 837 More actions · Answer 2

Thanks for the resonse

But here I want to discuss about one user database having multiple data files on differnet san partitions.

george sibbald SSC Guru Points: 104210 More actions · Answer 3

note, there is no point in having multiple log files, SQL only writes to one at a time so there is no performance gain in multiple log files.

As for how you split your data up, 'it depends'. you need to decide if you would be better of dividing out non-clustered indexes from the data, or dividing out based on tables. You may just need to create multiple files in your primary filegroup. You would see a performance improvement from that if the LUNS are truly sperate (different I\O channels). You need to discuss that with your SAN people, the drives may be purely logical and they all sit on the same fabric but are striped across multiple LUNS.

---------------------------------------------------------------------

SQL_Helper SSC Eights! Points: 837 More actions · Answer 4

Thanks really a good response.

Yes I have confirmed with SAN team, all partition are seprate and sure that it will enhance read write performance.

So it will be a good option if I create multiple datafiles of a databass.

But I also have SAN partitions for Log files, If I also create multiple log files for a database, can I take any advantage?

or any advice what approach should I follow as I m having 4 SAN paritions for data files and 4 SAN partitions for Log files.

Thanks

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 5

multiple data files as well as multiple log files give you benefits on loaded servers

you check the Delays milisecond with the help of this query

select DB_NAME(database_id) DB_NAME, di.file_id,df.name,io_stall_read_ms ,num_of_reads

,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'

,io_stall_write_ms,num_of_writes

,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'

,io_stall_read_ms + io_stall_write_ms as io_stalls

,num_of_reads + num_of_writes as total_io

,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'

from sys.dm_io_virtual_file_stats(null,null) di inner join sys.database_files df on df.file_id = di.file_id

where DB_NAME(database_id) = 'your database name'

order by avg_io_stall_ms desc

First Try with the same and then try with multiple files,Note it ,Create new indexes in the new or separate File Group and Files

you must increase the performance

How many Raid groups in your SAN ?

My Blog
www.aureus-salah.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Syed Jahanzaib Bin hassan (6/18/2011)
multiple data files as well as multiple log files give you benefits on loaded servers

Multiple log files do NOT give any benefit. SQL uses log files sequentially (one at a time) not in parallel. Hence, since it is only writing to one at a time having more than one gives no benefit.

Please stop spreading this misinformation

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

george sibbald SSC Guru Points: 104210 More actions · Answer 7

SQL_Helper (6/17/2011)
But I also have SAN partitions for Log files, If I also create multiple log files for a database, can I take any advantage?

No. It would only be of use if one drive is not big enough for the maximum log size you need.

Use the other LUNs intended for the logs as drives for data you expect to be write intensive. If its all the same raid level just use them for data that would benefit from further separation.

Or backups, where are they going to go?

---------------------------------------------------------------------

Steve Jones - SSC Editor SSC Guru Points: 734667 More actions · Answer 8

Another confirmation, multiple log files do not provide any benefits.

For multiple data files, you get benefits only if you can separate out the reads and writes of heavy objects to they can proceed in parallel. Otherwise you aren't necessarily getting a performance benefit.

If you have different access patterns for sets of data, or archived tables, you might partition those off to separate drives as well for the queries that need them, reducing the amount of data to scan over or access on other partitions.

What sizes of data are we talking about? Why do you think you need separate files for performance?