More Partition Function and Partition Scheme questions.

Howdy again. Many of my queries are grouped by months. I want to potentially
make use of Partition Functions (PF) and Partition Schemes (PS) but I don't
want to have an out of control amount of of Files and File Groups to create
my PS's on. If I simply partition by year, will my month queries still be
sped up? Or is the only way to accomplish a speed advantage to partition by
month? Im a few weeks out from being able to do a real test, and the
suspence will kill me by then.
TIA, ChrisRPartitions help where the majority of the queries align themselves with your
partitions. In your case you will probably get some, but not the same amount
if you partition by month and year.
Note that you can park your partitions on different disks to spread the IO,
so you might not have 24 disks to spread your io, but you might have data
distribution patterns so that the volatile data could be on separate disks
and partition which are not queries as frequently could be on a single disk.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:%234a9SbNBHHA.3604@.TK2MSFTNGP04.phx.gbl...
> Howdy again. Many of my queries are grouped by months. I want to
> potentially
> make use of Partition Functions (PF) and Partition Schemes (PS) but I
> don't
> want to have an out of control amount of of Files and File Groups to
> create
> my PS's on. If I simply partition by year, will my month queries still be
> sped up? Or is the only way to accomplish a speed advantage to partition
> by
> month? Im a few weeks out from being able to do a real test, and the
> suspence will kill me by then.
> TIA, ChrisR
>|||> I want to potentially
> make use of Partition Functions (PF) and Partition Schemes (PS) but I don't
> want to have an out of control amount of of Files and File Groups to create
> my PS's on.
You don't have to direct each partition to an unique filegroup. All can go to the same, or you can
distribute the partitions over the filegroups any way you want.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message news:%234a9SbNBHHA.3604@.TK2MSFTNGP04.phx.gbl...
> Howdy again. Many of my queries are grouped by months. I want to potentially
> make use of Partition Functions (PF) and Partition Schemes (PS) but I don't
> want to have an out of control amount of of Files and File Groups to create
> my PS's on. If I simply partition by year, will my month queries still be
> sped up? Or is the only way to accomplish a speed advantage to partition by
> month? Im a few weeks out from being able to do a real test, and the
> suspence will kill me by then.
> TIA, ChrisR
>|||In addition to the fact that you can place multiple partitions on the same
filegroup, you can also take advantage of the ability to merge partitions. It
is a common practice to have many finer-grained partitions for more recent
and often more active data and few larger partitions for aged and often less
active data.
Linchi
"ChrisR" wrote:
> Howdy again. Many of my queries are grouped by months. I want to potentially
> make use of Partition Functions (PF) and Partition Schemes (PS) but I don't
> want to have an out of control amount of of Files and File Groups to create
> my PS's on. If I simply partition by year, will my month queries still be
> sped up? Or is the only way to accomplish a speed advantage to partition by
> month? Im a few weeks out from being able to do a real test, and the
> suspence will kill me by then.
> TIA, ChrisR
>
>