October 23, 2008 at 9:09 am
We are working with a 3rd party consultant to help us in performance improvement of a vendor DB. They are suggesting that we install a new array of RAID 1 + 0 disk ( 4 physical drives ) and move our .mdf file to this new array. They are also suggesting that we create additional .ndf files and spread the data over multiple data files in the DB. These concepts and the way they're suggesting to accomplish this are all new to us. I have read several posts/articles on this website which describe the method of using shrinkfile w/emptyfile which is what the consultant is proposing. Should simply moving the .mdf to the new RAID array ( currently RAID 5 ) improve performance ? Do the multiple data files help in some way ? It's not that I don't fully trust the consultant, just looking for another "real" take on this situation. Thanks .....
October 23, 2008 at 10:16 am
Moving to Raid 1+0 is definitely recommended (Raid 5 is not good for SQL Server files).
Splitting out the .mdf to alternate data files can help, if done correctly, but only if they are going to different physical volumes. If they are all going to be on the same volume, then forget about it: it will just make performance worse (except possibly for TempDB, on SQL 2000 it is a special case).
However, if you have multiple separate physical volumes to use, then splitting your mdf, is not the first thing that you want to try. The first thing that you want to do is to get your database log files (.LDF's) on a separate physical volume from your MDF's (this is by far the biggest bang for your buck with multiple volumes). If you have another volume, then the second thing that you want to do is to separate your TempDB MDF from your other MDF's. After that, you can think about splitting up your application database's MDF to multiple volumes.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 23, 2008 at 10:23 am
watson_mike (10/23/2008)
Do the multiple data files help in some way ? It's not that I don't fully trust the consultant, just looking for another "real" take on this situation.
Maybe. It depends whether or not your performance problems are as a result of IO bottlenecks. If they are, then yes. If they're not, then you'll see little benefit.
Before going that route, you should make sure that your queries are written optimally and your indexes support those queries. If not, then fixing those problems will get far better returns than splitting the mdf file.
That said, do make sure that the ldf is separate and tempDB has it's own drive, like rbarry suggested. Those are more important than splitting the mdf apart.
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
October 23, 2008 at 11:19 am
Thank you both for your input. Points well taken. We did have in our plan to move tempdb, .ldf and .mdf to separate volumes. It was the multiple file proposal that was throwing us a little bit.
October 23, 2008 at 11:44 am
Glad we could help. If you can, let us know how it works out.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 17, 2008 at 11:55 am
Well, so far, so good. We have made it through our first month end closing with barely a word from the user community about poor performance. This is the first month end since this system went live in Feb 2008 that the users haven't been ready to kill someone in IT. Looks like our next endeavor for this system, which is absolutely organization critical, is to cluster a couple servers ( 1 active, 1 passive ) and provide better disaster recovery. We are also looking at several more disk arrays to spread out not only SQL but also Vendor data requirements. Thanks for all your help.
November 17, 2008 at 4:47 pm
What changes did you end up making?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 18, 2008 at 6:56 am
We now have the .mdf and .ldf files for the production db and tempdb all on separate disk arrays. The .mdf is a Raid 1 + 0 configuration spread over 4 physical drives.
While I've got you, different subject please. I have a user ( programmer ) that recently has a need to query a table that contains 33 + million rows. Each row is about 300 bytes and he's selecting ALL rows. He's using SQL 2005 and keeps getting the error : "An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown."
I can't find much about this problem anywhere except for several articles that describe a "Known Microsoft problem". Any insight from you folks would be appreciated. As always, thanks.
November 18, 2008 at 9:25 am
Well, I am leaving for PASS '08 right now, and I know that Gail is already there, so I'd recommend reposting this as a new thread, and hope that Jeff Moden (who specializes in million row tables 🙂 ) or someone else picks it up.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply