Post reply

Splitting Indexes To Another Drive

  • February 6, 2008 at 12:04 pm

    #119890

    I have my User and System databases split on different SAN partitions, as well as Logs and TempDB. Now I am analyzing the split of Indexes onto their own drive.

    I need to do some additional analyzing for what indexes should go on separate drives, but generally speaking, should only non-clustered indexes be split out?

    Is it better to create a new FileGroup / File to contain the indexes, or will just a new File suffice? I would rather keep backups and restores for both data and indexes together.

    I have read different articles but if anyone could confirm or provide additional insight, I would greatly appreciate it.

    TIA!!

    EDIT: Additional Information Size of entire database is 50GB (22GB Index, 23GB Data). Index is large due to SalesHistory table which makes up 19GB of total Index space.

  • February 6, 2008 at 12:24 pm

    #775740

    I prefer to keep all non-clustered indexes in their own file group on a separate I/O path (LUN) altogether. By splitting the I/O you should see a measurable increase in performance.

    Tommy

    Follow @sqlscribe

  • February 6, 2008 at 12:34 pm

    #775743

    I thought that clustered indexes have to go on the same filegroup as their table in any case.

    I would look to put the larger indexes on their own filegroup and not bother with the small or static ones.

    LinkedIn Profile

  • February 6, 2008 at 12:34 pm

    #775744

    You'll need another filegroup.

    Keep in mind that the clustering index actualy contains all table data, you may not want to move that to your indexes filegroup.

    Also keep in mind, you must include your indexes in your backup scenario.

    You cannot restore a table without it's indexes or it will stay unavailable.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • February 6, 2008 at 1:17 pm

    #775756

    Thanks for all the info. I appreciate it very much!

  • February 6, 2008 at 1:19 pm

    #775758

    Here is something you may want to read.

    http://blogs.msdn.com/sqlcat/archive/2005/11/21/495440.aspx

    EDIT: With 50 GB this doesn't look like a "too" huge database.

    What issues do you currently have with the performance of your database?

    How much improvement do you expect with moving indexes to different filegroups?

    Best Regards,

    Chris Büttner

  • February 6, 2008 at 5:02 pm

    #775819

    Thanks for the post

    Although the actual size of the database is small, the system is growing in both records and users. Peak hours register about 600 - 700 batch requests a second, and we'll possibly be deploying Citrix to remote offices which will immediately add 100+ additional users.

    Bottom line is that we would be just fine without splitting these objects. However, I want to configure this appropriately and be able to take on any additional load without having to perform major reconfigurations.

    I do not expect to see much performance gain, if any at all, immediately.

    Thanks again!

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

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