Mohan Kumar-480059 (10/25/2009)

We have some databases which are around 10gb now. Due to performance issues I was instructed to partition the tables. I've gone through some sites & got information about partitioning the existing tables. I have few questions:

1. Can we do partitioning without having multiple filegroups? If so, will there be any performance difference?

Yes you can have as many or as few Filegroups as you like on Partitioned tables. You won't get any performance improvements just by partitioning.

Bear in mind Partitioning is a technology primarily for data Management & is not designed as performance enhancing - though as you have hinted performance gains can be made by splitting the data over filegroups.

However I wouldn't have thought 10GB of data is enough to bother with the hassle of partitioning.

2. What is the best way to partition tables with data (if possible without disturbing existing clustered index)?

I'm not sure there is a "best" way it depends on your data but time based partitioning is often used. In our case more recent data is on filegroups on a very fast disk volume as this tends to be "hot" i.e. it is the data people are using most often.

Older data is in Filegroups on slower, less expensive storage, performance is slower but the data is used less so it's not so important.

Anyway - hope this helps!

S C Penguin