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 Tuesday, August 14, 2012 11:36 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:23 AM
Points: 1,226, Visits: 2,721
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.



Post #1344916
Posted Sunday, January 12, 2014 9:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:23 PM
Points: 36,612, Visits: 31,051
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1530112
Posted Sunday, January 12, 2014 11:11 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:19 PM
Points: 42,332, Visits: 35,386
Please note: 4 year old thread (bumped by a spam post)


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1530130
Posted Sunday, January 12, 2014 12:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:23 PM
Points: 36,612, Visits: 31,051
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1530141
Posted Sunday, January 12, 2014 12:54 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:19 PM
Points: 42,332, Visits: 35,386
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 2008, MVP
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

Post #1530148
Posted Sunday, January 12, 2014 2:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:28 AM
Points: 6,131, Visits: 13,259
Now now you two, no fighting

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

"Ya can't make an omelette without breaking just a few eggs"
Post #1530155
Posted Monday, January 13, 2014 7:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:23 PM
Points: 36,612, Visits: 31,051
Perry Whittle (1/12/2014)
Now now you two, no fighting


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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1530280
Posted Monday, January 13, 2014 8:57 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 3, 2014 10:59 AM
Points: 79, Visits: 247
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
Post #1530524
Posted Tuesday, January 14, 2014 7:29 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:23 PM
Points: 36,612, Visits: 31,051
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1530675
Posted Wednesday, January 15, 2014 6:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:28 AM
Points: 6,131, Visits: 13,259
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"
Post #1531076
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse