Post reply

Where do I put my data

  • June 11, 2014 at 11:00 am

    #396898

    Can anyone recommend some good articles on how and where to spread my data files across file groups/disks.

    The database consists of 300+ tables some of which contain many billions of rows. Partitioning is already in place on the larger tables. The whole system is moving to a new architecture, so it gives us the chance to have a rethink about how our data is stored on a brand new SAN.

    As the new DBA on the team I've been asked for input on the move from a new perspective as someone who is looking at the system without any 'history'.

  • June 11, 2014 at 11:25 am

    #1720842

    You might want to check the books section on this site. There are some that might help.

    Troubleshooting SQL Server: A Guide for the Accidental DBA by Jonathan Kehayias and Ted Krueger[/url]

    SQL Server Hardware by Glenn Berry

    SQL Server Tacklebox by Rodney Landrum

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • June 11, 2014 at 11:45 am

    #1720850

    The first decision that needs to be made is whether you're splitting the data across disks for performance reasons or for backup/restore/recovery reasons.

    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

  • June 12, 2014 at 1:03 am

    #1720938

    It wil be for performance

  • June 12, 2014 at 1:41 pm

    #1721228

    In that case you need to analyse first whether you have IO bottlenecks. Pointless splitting files if you aren't bottlenecked on IO. You need to look for tables accessed together so you can split them into different filegroups

    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

  • June 12, 2014 at 2:22 pm

    #1721240

    You can do number of things. I'm inline with Gail's comments, unnecessary splitting the file wont help in improving the performance.

    Please consider following steps..

    1.List down all the performance issues that you have currently in the system.

    2. First understand the IO bottle necks. Check and analyze the DISK IO(SQLIO) capacity. Understand your hardware requirements and potential issues.

    3.Split files on multiple drives based on their utilizations.

    4.Make sure to move the tempdb to separate drive and split the files based on number of CPU’s

    5.Consider implementing the file groups.

    6. Better data retention/archival strategy.

    Thank you,

    Regards

    Hema.,

  • June 27, 2014 at 8:06 am

    #1725429

    Have tried out suggested list of books and working through them.

    Thanks for the advice

Viewing 7 posts - 1 through 6 (of 6 total)

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