July 9, 2009 at 7:01 am
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.
July 9, 2009 at 7:11 am
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).
July 9, 2009 at 8:32 am
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).
July 9, 2009 at 8:52 am
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.nameAS DBName
,FileStats.DbID
,FileStats.FileId
,FileStats.NumberReads
,FileStats.NumberWrites
,FileStats.IoStallReadMs
,FileStats.IoStallWriteMs
FROMfn_virtualfilestats ( NULL , NULL ) AS FileStats
JOINmaster.dbo.sysdatabasesAS sysdatabases
ON sysdatabases.DbId = FileStats.DbId
ORDER BY FileStats.NumberReads + FileStats.NumberWrites DESC
SQL = Scarcely Qualifies as a Language
July 9, 2009 at 3:50 pm
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.
July 10, 2009 at 2:18 am
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:
IOUsageCntDBNameDbIDFileIdNumberReadsNumberWritesIoStallReadMsIoStallWriteMs
38363261AVI_DPM_Data51197955941856766738644061249515609767
10753892AVI_DPM_Data52520289102336032486994772105949
July 10, 2009 at 5:20 am
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.
---------------------------------------------------------------------
July 13, 2009 at 3:04 am
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.
July 13, 2012 at 3:29 am
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.
August 13, 2012 at 4:02 pm
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" 😉
August 14, 2012 at 11:36 am
I tend to agree with others. EXAMPLE: Just because you have a 200mb db and split the data up evenly (or so you think) into 4 50MB data files on 4 different luns may or may NOT help you. You have to remember that 3 of those 4 50MB data files may have mostly static data that is hardly used and the 4th file may have mostly one large table that is read, inserted to and deleted from heavily. So, you might do all of this work and get very little bang for the buck.
Your best bet is to find out which specific dbs are the most heavily used and what times of days they are and simply backup/restore them to a different drive/lun.
Also, if you run Update Stats and Integrity Checks on a regular basis make sure they are running during the most quiet time of day. The integrity check job is VERY I/O intensive.
January 12, 2014 at 9:25 am
A lot of people also forget about the "striping" that SANs do for things like RAID 5 or Raid 10. There are usually 5 sets of spindles and 5 sets of R/W heads in play. Except for TempDB, splitting an
MDF to multiple files probably won't hurt but it also most likely won't help unless you can guarantee that those other files will be on a separate set of spindles with their own R/W heads. SANs usually don't work that way. The only thing that we've forced to separate spindles is large amounts of legacy data and we've moved that to less expensive (and slower) hardware.
To wit, "Spreading the I/O on the SAN fabric" will simply mean that the R/W heads need to move more times to read the same data and that can really slow you down. My recommendation is to spend the time and the money on fixing performance challenge queries instead of messing around with the SAN. You can usually get a 10-60X and sometimes a 100X+ performance improvement by fixing problematic queries and adding the correct indexes. Messing with the SAN will be aggrevating and problem net you only a 25-50% (1/4-1/2X) improvement... that's [font="Arial Black"]IF [/font]you don't actually decrease the performance by messing with the SAN (which is most likely).
Even splitting TempDB out to a separate set of spindles will only help a little bit compared to the massive performance improvements you can get by fixing performance challenged code (and, sometimes, DB Design).
Hardware helps but the real performance is in the code.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 12, 2014 at 11:11 am
Please note: 4 year old thread (bumped by a spam post)
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
January 12, 2014 at 12:28 pm
GilaMonster (1/12/2014)
Please note: 4 year old thread (bumped by a spam post)
Why does that matter to you so much? Someone could read the post and any addition information doesn't hurt no matter how old the post is.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 12, 2014 at 12:54 pm
Jeff Moden (1/12/2014)
GilaMonster (1/12/2014)
Please note: 4 year old thread (bumped by a spam post)Why does that matter to you so much?
So that people who don't check the dates don't spend large chunks of time (as I have done several times) writing out questions and requests for clarification for a problem likely long-solved. If it bothers you, ignore the note.
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
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply