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

An alternate physical layout might have multiple partitions sharing the same file group. The exact detail
of laying out physical disks is beyond the scope of this chapter but is touched on in Chapter 6.
PartitioningandDTA
DTA will provide partitioning recommendations if you ask it to. To do this you need to change the
tuning options on the Tuning Options tab. In the group titled Partitioning strategy to employ, change the
default setting of No Partitioning to full partitioning. You also need to determine what kind of queries to
tune. It's no good using your INSERT , DELETE ,or SWITCH queries as DTA isn't really interested in them.
Youhavetotunethe SELECT statements that go against the non-partitioned table. The reason DTA isn't
interested in INSERT , DELETE ,or SWITCH statements is that it can't improve their performance by adding
indexes so it ignores them and looks only at the statements it can tune: SELECT .
If the table is big enough and the queries would benefit from partitioning, DTA will provide scripts to
create the partition function, the partition scheme, and the clustered index. Here is an example of a DTA
partitioning recommendation received from a different database. The underlying table has 57,000,000
rows, uses 1.8 GB of data space, and 3.6 GB of index space.
CREATE PARTITION FUNCTION [_dta_pf__2533](int) AS RANGE LEFT FOR VALUES (103863552,
196930103, 203421423, 246065168, 269171113, 269702979, 270375078, 273695583,
276447808, 280951053, 298459732, 298855583, 299375843, 299810346, 301474640)
CREATE PARTITION SCHEME [_dta_ps__8258] AS PARTITION [_dta_pf__2533] TO ([PRIMARY],
[PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],
[PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],
[PRIMARY])
CREATE NONCLUSTERED INDEX [_dta_index_myTable_6_741577680_23810504_K1_K2_K3] ON
[dbo].[myTable]
(
[Col1] ASC,
[Col2] ASC,
[Col3] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE =
OFF) ON [_dta_ps__8258]([Colx])
Summary
This chapter covered four key areas, starting off with a solid overview of SQL Server 2005 indexing before
moving on to tuning indexes with the Database Tuning Advisor and comprehensively covering the
various options available and how implement the recommendations. Then you looked at how to check
and remove fragmentation from your database followed by an examination of the details of partitioning.
You looked at how to set up partitioning and covered three main scenarios: creating a partition from an
existing table, adding a new partition, and deleting an old partition.