Tuning Indexes - Professional SQL Server 2005 Performance Tuning - page 398

There is a bit of a catch to this part, in that with archiving, you often want to move the old data to cheaper
or different storage. The switch operation is so fast because all it does is change metadata; it doesn't move
any data around. To get the data from the file group where it lived to the archiving file group on cheaper
storage, you can't just switch; you actually have to move the data. However, you are moving it when the
partition isn't attached to the existing partition table, so although this may take quite some time, it will
have minimal impact on any queries executing against the live data.

FasterQueries
Surely the chance to get faster queries has you very interested. One thing that the Query Optimizer can
do when querying a partitioned table is to eliminate searching through partitions that it knows won't
holdanyresults.Thisisreferredtoas partition elimination . This only works if the data in the partitioned
table or index is aligned with the query. By aligned , we mean that the data has to be distributed through
the partitions in a way that matches the search clause on the query. You can see more about the specifics
of this as you get into the details of how to create a partitioned table.

SlidingWindows
A sliding window is basically the same as the data deletion/archiving scenario described previously,
whereby you add new data and then delete or archive old data. It's kind of like sliding a window of
new data into the current partition table, and then sliding an old window of data out of the partition
table.

Prerequisites for Partitioning
Before you get all excited about partitioned tables, you should remember that partitioning is only
available with SQL Server 2005 Enterprise edition. There are also some expectations about the hard-
ware in use, in particular the storage system, although these are implicit expectations, and you can store
the data anywhere you want to. You just won't get the same performance benefits as if you had a larger
enterprise storage system with multiple disk groups dedicated to different partitions.

CreatingPartitioned Tables
When you decide to create a partitioned table for the first time, you can get pretty well lost in the doc-
umentation for partition functions, range left versus range right, partition schemes, and how to actually
create something that would work. The following sections will run through the way to think about this
process. If you've been reading through the whole chapter and want to continue following the examples,
you should drop and recreate the tables by running createtables.sql and loadnames.sql .Nowload
the people table with 200,000 rows:

EXEC usp_loopPeopleInsert 100000, 10000
-- inserts 100,000 couples and prints progress every 10,000 rows.
GO

EXEC sp_spacedused people
GO

name rows reserved data index_size unused
------------------------------------------------------------------------------
people 199998 28936 KB 28880 KB 8 KB 48 KB