January 27, 2008 at 11:21 am
I want to separate my data and indexes (non-clustered of course).
I have added a new file and placed it on a seperate LUN, but how to I create a filegroup to use that file so I can seperate my data and indexes on to different LUNs?
January 27, 2008 at 11:38 am
chris, did you add the file via SSMS? In the background that is using the 'Alter database ' command. You use that command to add a new filegroup as well. See BOL.
---------------------------------------------------------------------
January 27, 2008 at 11:53 am
I did use SSMS
thanks for the info
January 27, 2008 at 6:58 pm
To move the data, move the clustered index to the new filegroup. To move the indexes, rebuild them on the new filegroup.
January 28, 2008 at 12:31 am
and whyle you're at it ....
Split user object and system objects.
Create another filegroup, set it to be the default.
Move all your tables/clustered indexes to it.
There will be no immediate profit, but if you start using more filegroups you'll be able to perform partial restores in stead of always having to restore the whole database.
You'll just have to restore the catalogue (primary filegroup) and the exact filegroup(s) you want to have (always keeping in mind data and indexes need to be restored together).
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
January 29, 2008 at 10:18 am
See BOL for CREATE INDEX and ALTER DATABASE. Also note that if your LUN isn't on different spindles you may not receive quite the perf boost you were hoping for.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply