1033
Chapter 45: Indexing Strategies
45
- Generate a trace script using File ➪ Export ➪ Script Trace Defi nition ➪ for SQL
Server 2005-SQL11. - Check the script. You may need to edit the script to supply a fi lename and path and
double-check the start and stop times. Execute the trace script on the production
server for 24 hours. - Pull the trace fi le into Profi ler. This can be done through the Open ➪ Trace File
dialog in SQL Profi ler. Then save it to a table using File ➪ Save As ➪ Trace Table. - Profi ler exports the TextData column as nText data type, and that just won’t
do. The following code creates an nVarChar(max) column that is friendlier with
string functions:
ALTER TABLE trace
ALTER COLUMN textdata NVARCHAR(MAX); - Run the following aggregate query to summarize the query load. This query
assumes the trace data was saved to a table creatively named trace:
select substring(textdata, 1, CHARINDEX(' ',qtextdata, 6)),
count(*) as 'count',
sum(duration) as 'SumDuration',
avg(duration) as 'AvgDuration',
max(duration) as 'MaxDuration',
cast(SUM(duration) as numeric(20,2))
/ (select sum(Duration) from trace) as 'Percentage',
sum(rowcounts) as 'SumRows'
from trace
group by substring(textdata, 1, charindex(' ',textdata, 6))
order by sum(Duration) desc;
The top queries are obvious.The Database Engine Tuning Advisor is a SQL Server utility that can analyze a single query or a set of queries and
recommend indexes and partitions to improve performance.Selecting the Clustered Index
A clustered index can affect performance in several ways:■ (^) When an index seek operation fi nds a row using a clustered index, the data is right
there — no bookmark lookup is necessary. This makes the column used to select
the row, probably the primary key, an ideal candidate for a clustered index.
■ Clustered indexes gather rows with the same or similar values to the smallest pos-
sible number of data pages, thus reducing the number of data pages required to
retrieve a set a rows. Clustered indexes are therefore excellent for columns that
are often used to select a range of rows, such as secondary table foreign keys like
OrderDetail.OrderID.
c45.indd 1033c45.indd 1033 7/31/2012 10:16:43 AM7/31/2012 10:16:43 AM
http://www.it-ebooks.info