Post reply

Clustered Indexes and non clustered indexes on file groups

  • July 10, 2009 at 9:19 am

    #132950

    Guys

    Currently we have 9 databases on single instance wiht each database of 30gb SIZE, the box and sql instance are 64 bit. Across these databases I am planning to pick all the large tables have the clustered indexes and non clustered indexes from these tables to write to different file group containing one file each to separate SAN drive. So I am planning to have 2 data drives one for data, one for indexes of these large table and one for log drive.

    The concern I have here will there be any contention if all the indexes of these large tables for 9 databases read from the same drive. Also is the approach of having the indexes in separate file group a good one. Any suggestions and inputs wouid help.

    Thanks

  • July 10, 2009 at 9:30 am

    #1023141

    am (7/10/2009)

    The concern I have here will there be any contention if all the indexes of these large tables for 9 databases read from the same drive. Also is the approach of having the indexes in separate file group a good one. Any suggestions and inputs wouid help.

    Thanks

    if you were to have both the data and index filegroups on the same drive, obviously contention would have been bigger. Keeping them on seperate spindles means index IO and table IO can be done at the same time and it is considered as a best practice. Also, the point here to keep in mind that both the filegroups must be backed up simultaneously.


    Pradeep Singh

  • July 10, 2009 at 9:40 am

    #1023154

    It's a balancing act, and this is where it becomes an art. The heads are very good at moving around, but to reduce contention, you have to try and separate things out. You'll have to experiment a bit in moving the indexes and data around to different drive to see what works best for you. Sorry, but it's trial and error.

    Remember that your SAN drives already have multiple spindles in there, as well as caching. I'd do some analysis with profiler and with execution plans, and try to identify which are the most heavily accessed, and separate those first, then split out others behind them, making some guesses as to which would be better on which drive.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

Viewing 3 posts - 1 through 2 (of 2 total)

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