Splitting Indexes To Another Drive

Question

Splitting Indexes To Another Drive

JuanBob

SSCertifiable

Points: 5059
More actions
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.

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

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

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 1

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

David.Poole SSC Guru Points: 76001 More actions · Answer 2

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

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

JuanBob SSCertifiable Points: 5059 More actions · Answer 4

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

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 5

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

JuanBob SSCertifiable Points: 5059 More actions · Answer 6

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!