Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Splitting DBs into multiple files for IO spread Expand / Collapse
Author
Message
Posted Thursday, July 09, 2009 7:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 24, 2012 6:59 AM
Points: 58, Visits: 184
We have a shared SQL server with a few hundred small DBs on it. Currently all the DBs consist of a single data file and a single log file. We have 2 SAN presented LUNs, 1 for data, 1 for log. The server is a Win2k8x64 box running SQL2005x64 SP3 Ent with dual quad core CPU.

What we want to do now is switch to 4 data and 1 log LUN for every DB. This means splitting the data file into 4. The goal of this task isn't to increase performance on the SQL server but rather to spread the IO on the SAN fabric so that it lessens the impact the SQL server has on other systems sharing the SAN disk subsystem. I've been told by our architect/infrastructure guys that adding more LUNs will help.

Ideally we want the 4 data files to be the same size to facilitate disk management. What options do I have for doing this?

One method I've thought of is to add a temp data file then empty the current data file by using shrinkfile -> empty file. Then I could add another 3 data files, empty the temp data file and delete it. I believe this should give me an even spread across the 4 files. Is this a viable option?

Another thing I've thought of is to do a full dump and load, though I'm not really sure how I can do this in SQL. Using SSMS08 I see you can now generate scripts to dump everything (is it everything?) including table data, though I have no idea how I would then run a script that's a few GB big.

Also, a quick question on proportional fill. Would I need to grow all 4 data files at the same time to maintain proportional fill in the event they fill up? My understanding is that SQL only grows 1 file at a time, thus proportional fill is lost as soon as all 4 files fill up and SQL only grows the one.
Post #750178
Posted Thursday, July 09, 2009 7:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, January 26, 2014 11:13 AM
Points: 2,242, Visits: 3,641
What we want to do now is switch to 4 data and 1 log LUN for every DB

Why do you want to do this to all databases? I guess managing will pose a problem later with so many data files. I would identify top 5 or 10 percent databases which are heavily used and would split those databases into multiple filegroups.

Also what i understand is you are going to increase 1 datafile to 2 and 1 log file to 2 each. Adding another log file wont help you at all in performance as writes to the log files are sequential. Until one of the log files get full, sql server wont write to the other file. so you wont achieve any performance improvement here.

Also, i suppose you are planning to create seperate filegroups onto the new LUNs that u're planning to add. This way you can separate tables on different luns OR you can have tables on one LUN and indexes on other. This way you can achive good performance due to IO bottleneck.

My understanding is that SQL only grows 1 file at a time, thus proportional fill is lost as soon as all 4 files fill up and SQL only grows the one.

This happens with log files and not datafile. say your database has 2 datafiles with 100mb and 200mb in sizes. If you're entering data worth 6mb in size, according to proportional fill algorithm, 2 mb of data will go to the first file and 4mb to the 2nd file (1:2).





Pradeep Singh
Post #750185
Posted Thursday, July 09, 2009 8:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 24, 2012 6:59 AM
Points: 58, Visits: 184
We're actually going from 1 data file to 4 data files per DB. The log file is staying the same at a single file per DB. This will give us 5 files per DB, each one on a separate LUN.

The goal isn't SQL DB performance improvement, it's actually SAN controller performance improvement by spreading the IO for the DBs over multiple LUNs instead of just the 2 we currently have (1 for log, 1 for data). TBH, I'm not convinced that this is going to help us, but the decision is over my head, I'm just doing the work, and looking for the best way to do it.

So to have the DB grow all 4 data files uniformly we need to start out with files that are the same size. Are you saying the SQL will grow all data files at the same time when the DB is full (I'm assuming autogrowth is enabled here).

Post #750288
Posted Thursday, July 09, 2009 8:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 2,285, Visits: 4,222
What we want to do now is switch to 4 data and 1 log LUN for every DB
I assume you mean 3 data files and 1 log file for each database. Multiple datafiles are useful when the IO on a particular file exceeds the IO capacity of the underlying disk or there is contention on the space management.

As an alternative, how about leaving the number of file for each database as is but moving some of the database files to new LUNS ? You can determine the number of IO for each file with the below SQL and then try to balance the IO across the LUNS. Look at tempdb first, as this is often a source of IO bottlenecks and multiple files for tempdb are recommended.

With small files, you can just detach the database, move the underlying files, and then attach the databases with the new file locations. Note that this may change the DBID value and as login default databases are stored internally with the DBID and not the database name, you may need to reset the database defaults.

SELECT  FileStats.NumberReads + FileStats.NumberWrites AS IOUsageCnt
, sysdatabases.name AS DBName
, FileStats.DbID
, FileStats.FileId
, FileStats.NumberReads
, FileStats.NumberWrites
, FileStats.IoStallReadMs
, FileStats.IoStallWriteMs
FROM fn_virtualfilestats ( NULL , NULL ) AS FileStats
JOIN master.dbo.sysdatabases AS sysdatabases
ON sysdatabases.DbId = FileStats.DbId
ORDER BY FileStats.NumberReads + FileStats.NumberWrites DESC



SQL = Scarcely Qualifies as a Language
Post #750329
Posted Thursday, July 09, 2009 3:50 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 11:44 PM
Points: 750, Visits: 3,084
I agree with Carl ... just allocate the existing databases across the 4 LUNs. Given the number of databases and your server hardware, I'm guessing that they aren't used very heavily. You would only need to allocate multiple files to a database if it's an exception with very high IO relative to the others.
Post #750712
Posted Friday, July 10, 2009 2:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 24, 2012 6:59 AM
Points: 58, Visits: 184
For the most part the DBs on the server are pretty small and low usage. It's just got a couple larger databases that have pretty heavy usage. We've found when some of these DBs start working hard, while SQL is generally fine and responds without issue, it literally brings other systems sharing the same SAN to their knees. We've nailed the problem down to the controllers getting saturated with IO intensive tasks on the SQL server, and our infrastructure guys said that splitting the DBs data files specifically across multiple LUNs will help somewhat. We've had to move these DBs to internal disk until we can resolve the issue.

Tempdb is already on its own LUN with 8 data files; should I be splitting tempdb across multiple LUNs? Also, I really do mean 4 data + 1 log. Our setup has drives j,k,l,m for data and i for log, x for tempdb, d (internal) for SQL + system DBs, and e (internal) for the heavily used moved DBs (d & e are separate arrays).

Maybe what I should do is just spread the IO by adding multiple files for the DBs that are giving us headaches? But that obviously brings me back to my original query on how to spread the data evenly.

@carl, I'm not entirely sure how to read the results of your query, but this is the main DB that is giving us problems:
IOUsageCnt	DBName		DbID	FileId	NumberReads	NumberWrites	IoStallReadMs	IoStallWriteMs
38363261 AVI_DPM_Data 5 1 19795594 18567667 3864406124 9515609767
10753892 AVI_DPM_Data 5 2 520289 10233603 24869947 72105949

Post #750881
Posted Friday, July 10, 2009 5:20 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:40 PM
Points: 5,849, Visits: 12,583
sounds to me like the basic problem is the fact the sql server i/o is shared with i/o from other types of servers. the i/o patterns for SQL are radically different to say a file server and the two should not go through the same i/o channels.

you would get the biggest bang for your buck by seperating them out. that would probably give you improvements in SQL performance as well. I don't think adding more luns if the i/o is still mixed is the best option.


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

Post #750969
Posted Monday, July 13, 2009 3:04 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 24, 2012 6:59 AM
Points: 58, Visits: 184
The other main system is an OpenEdge RDBMS that has a much higher usage and business impact than our MSSQL environment.

Splitting them out would require purchasing another EVA, and those things don't exactly come cheap. We're only getting a new one mid to late next year; so until then, it's not even an option.
Post #751871
Posted Friday, July 13, 2012 3:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 1:36 AM
Points: 5, Visits: 120
I guess there will be other people reading this post, even though it's old enough.
One way to spread data accross multiple datafiles is to create four datafiles of the same size and then rebuild all the indexes of the database. You can then shrink the datafiles as appropriate.
Export/Import of data is a more complicated task.
Post #1329321
Posted Monday, August 13, 2012 4:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 5,958, Visits: 12,839
Cairbre (7/9/2009)
I've been told by our architect/infrastructure guys that adding more LUNs will help.

Hmmm, yes. Pushing more I\O queues down to the disks will likely just saturate them, then your performance will really suck and SQL Server will start throwing regular "Slow I\O taking longer 15 seconds" messages.
Also, adding all those files will now increase the management complexity. You suddenly have 4 times the amount of files to look after.
I've worked with many systems that have 1 data file and 1 log file some databases as large as 400Gb or more.



Cairbre (7/9/2009)
Ideally we want the 4 data files to be the same size to facilitate disk management.

No, they must be the same size and growth for proportionate fill to work properly.
Are you planning multiple filegroups for the multiple files or are they going to reside in the primary filegroup?

You have databases already deployed so let's take one database as an example. Say it has a 15GB data file in the primary filegroup with 4GB free space. You add 3 new files 15GB each all with default growth sizes. Will the proportionate fill work efficiently?
No, one file is already 60-70% full so more I\O will be directed to the emptier files, even with the growth rates set the same you still have more I\O directed to certain files, that's not what i call evenly spreading I\O



Cairbre (7/9/2009)
Also, a quick question on proportional fill. Would I need to grow all 4 data files at the same time to maintain proportional fill in the event they fill up? My understanding is that SQL only grows 1 file at a time, thus proportional fill is lost as soon as all 4 files fill up and SQL only grows the one.

Yes the fils grow one at a time, as theyre written to. One file will grow before the others but the rest will grow at the rate specified (which should be the dsame for all of them) as they're written to. There is a trace flag you can use to force the filegroups in unison.



george sibbald (7/10/2009)
sounds to me like the basic problem is the fact the sql server i/o is shared with i/o from other types of servers. the i/o patterns for SQL are radically different to say a file server and the two should not go through the same i/o channels.

you would get the biggest bang for your buck by seperating them out. that would probably give you improvements in SQL performance as well. I don't think adding more luns if the i/o is still mixed is the best option.

+1
i dont think more LUNs are the answer.


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

"Ya can't make an omelette without breaking just a few eggs"
Post #1344464
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse