SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Splitting DBs into multiple files for IO spread


Splitting DBs into multiple files for IO spread

Author
Message
Summer90
Summer90
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7295 Visits: 3831
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218476 Visits: 41996
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 IF 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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228561 Visits: 46342
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

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


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218476 Visits: 41996
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228561 Visits: 46342
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

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


Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55127 Visits: 17708
Now now you two, no fighting :-D

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218476 Visits: 41996
Perry Whittle (1/12/2014)
Now now you two, no fighting :-D


Ah...no... no fighting. I was just curious.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dbassassin
dbassassin
Old Hand
Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)

Group: General Forum Members
Points: 320 Visits: 290
Perry Whittle (8/13/2012)

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.


I'm with george and perry on this. After setting up a fairly large virtualized environment we ended up with sqldata, sqltempdb, sqllogs all on their own storage groups (multiple sql servers). We also had a storage group that was used for pretty much everything else.

I have a question to add to this...would separating a database into multiple files on the same storage group have much of an impact? (Not considering SAN caching capabilities like EMCs fast cache)

Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218476 Visits: 41996
GilaMonster (1/12/2014)
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.


Thanks Gail. Nah. Doesn't bother me. I've always thought that if an improved answer is posted, it doesn't matter how old a post is because it might help someone else. I've been known to resurrect very old posts simply because there's a better answer to be had for those that may run into it during their search for a solution to a given problem.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55127 Visits: 17708
sql4gnt (1/13/2014)
I have a question to add to this...would separating a database into multiple files on the same storage group have much of an impact? (Not considering SAN caching capabilities like EMCs fast cache)

Are the files part of the same database filegroup (PRIMARY for instance)?

Once you start adding multiple files to a filegroup (a technique used for tempdb to overcome allocation contention) SQL server has to start performing extra ops against those files to track the free space and increase the file sizes. SQL Server applies a proportional fill method and the more files you add the more SQL server has to file switch. This will negate any performance you gained (if any) and cause the opposite reaction, sloooooowwwwwwwwww!

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search