Splitting Data Files

Question

Splitting Data Files

Viewing 8 posts - 16 through 22 (of 22 total)

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

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

we'll agree to disagree then! Everything I've ever learned in xxx years says raid 5 generates 4 io per write regardless of the number of spindles in the array.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

Better than agreeing to disagree Colin is to KNOW. Review this page, which has very clear and concise examples of why you really do need to read ALL other drives to calculate the parity when you do a random write that only encompasses 1 drive of N in the stripe: http://www.scottklarr.com/topic/23/how-does-raid-5-work/

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 3

TheSQLGuru (1/11/2008)
1) It has been my experience that queries are almost NEVER as optimized as my clients think they are.

Spot on, Kevin...

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 4

Tobi White (1/11/2008)
Thank you for your reply, but it really didn't have anything to do with my question. I really just want information related to my question about splitting files within the same filegroup. I know its not a panacea to performance, its just one small factor that I am trying to research.
Thanks

Sure it does... you're the one that said...

In many cases we cannot really tune performance of the T-SQL much more than we already have.

... and as Kevin pointed out, that's "usually" just not true. I'm sure he's seen the same thing I have many times... customer goes out an spends a wad of money on lightning fast servers, disk arrays, and controllers all equiped with cache-memory out the wahzoo... they spend days "tuning" the system by splitting TempDb and other high usage DBs... they spend days partioning large tables, tweaking indexes, etc, etc, etc... only to find out that their 8 hour batch jobs still take 6 hours to run and their GUI "dips" on the database still take seconds instead of milliseconds.

Then, someone, who understands that the real performance gains are realized in the code, goes in, rewrites a couple of "key" procedures, tweaks a few others, and suddenly those 6 to 8 hour batch jobs only take 5 to 15 minutes to run and the GUI code starts returning results in the blink of an eye. Then, all the hardware tuning "experts" throw a party for themselves and say "See, we told ya!". 😛

The real performance is in how you write the code... sure, the hardware tricks all help, but the biggest ROI is in how you write the code. The code that you "cannot really tune performance of the T-SQL much more" would be the first place to look 😉

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Jeff, your description is right out of my latest client's playbook!! They have spent the last 2+ years throwing mid 7 figures at hardware to address flagging performance (and to be fair pretty significant growth). But they have essentially been treading water - most jobs and queries take essentially the same amount of time. So they finally broke down and decided to bring in an expert (at Microsoft's recommendation) for a 'lunch-and-learn' to get a feel for how they were doing and what they might be missing. Lets just say "eyes were opened". :w00t: During a 1.5 hour presentation and subsequent 2 hours of chatting with staff lots of "oh, yeah, that makes sense" and "oops, we shouldn't do that?", etc type comments were issued. 🙂 Looks like I will be picking up another long-term client. HOORAY for suboptimal SQL Server usage!! :hehe:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Toby White SSCertifiable Points: 6187 More actions · Answer 6

I don't dissagree with you at all about optimizing code. Perhaps I should have narrrowed my question to begin with so that I could have been clear that I already know about performance tuning and optimization with regards to code, and just really want heuristics related to physical files and filegroups.

My client is not having performance problems that anyone is trying to address with hardware. So I guess thanks for your feedback and I will just do my own testing.

Regards

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 7

The folks that gave those types of answers didn't help?

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Toby White SSCertifiable Points: 6187 More actions · Answer 8

What they said didn't hurt, and I don't dissagree with any of it. I was just specifically interested in Files, Filegroups, log files and tempdb file strategy related to the particular hardware configuration my client has.

I know it's difficult because there are so many variables involved with the hardware, specifically the disks, that it makes definitive answers impossible. So I was just looking for some heuristics related to the subject.

Of particular interest to me was heuristics around using multiple files with the primary filegroup being the only filegroup for the database. I am proposing to my client that they should use filegroups for separating clustered indexes from indexes onto filegroups that have their physical files on separate raid sets. My client asked me how they should configure their files so this is what I told them, and I was just looking for some resources and informational support.

Thanks