Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 270

exactly what you are used to seeing in the profiler window. Namely, the EventClass is an Integer, not the
descriptive field in the UI. You can find the translation to the EventClass Ids in the Books On Line
under topic sp_trace_setevent, or you can download a function we wrote at www.wrox.com called
ufn_GetEventClassDescription that will translate the EventClass Ids back into labels. You can then write
queries like this where you refer directly to the EventClass label.
Select EventClass = dbo.ufn_GetEventClassDescription(EventClass),
[T-SQL] = CAST(TextData as NVARCHAR(MAX)),
Duration, Reads, Writes, CPU,
StartTime, EndTime
FROM sys.fn_trace_GetTable('c: \ mytrace.trc', null) as t
WHERE dbo.ufn_GetEventClassDescription(eventclass) = 'SQL:BatchCompleted'
Note that the event class that we are extracting here is the one named SQL:BatchCompleted. Look for this
same event class if you are using the UI. It contains the summarized actual duration, I/O, and CPU KPIs
that you'll be interested in for rudimentary comparison purposes.
Prerequisite Indexes for Examples
The rest of this chapter includes examples that require the addition of some new indexes to the Adventure
Works (AW) database that you can add by running this script.
CREATE NONCLUSTERED INDEX [IX_SalesOrderHeader_AccountNumber] ON [Sales]
.[SalesOrderHeader] ([AccountNumber] ASC ) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SalesOrderHeader_CreditCardApprovalCode] ON [Sales]
.[SalesOrderHeader] ([CreditCardApprovalCode] ASC) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SalesOrderHeader_OrderDate] ON [Sales]
.[SalesOrderHeader] ([OrderDate] ASC ) ON [PRIMARY]
Tuning T-SQL with a New Approach
In the database world, DBAs approach performance issues by naturally assuming that the query is the
limiting constraint. Then you start tinkering with the SQL statement to achieve an excellent low-cost
result only to only have it crash and burn under different conditions in the production environment.
It is an art to know when to yield to the declarative nature of SQL and when to tinker. The optimizer
can interpolate many alternative ways to execute even poorly written queries, but there are times when
correcting an inefficiently written query can often produce dramatic gains in performance and time.
NOT IN and NOT EXISTS Rewrites are in the Past
The conventional wisdom in the past was to rewrite NOT IN statements to use NOT EXISTS statements
if the subquery scanned all the rows in a subtable. Mainly this was because the subquery would perform
a full scan of the clustered index. Because of the advances in the optimizer, this is no longer necessary.
The optimizer allows you to be more declarative, code using either method, and still get the most efficient
implementation. Looking at a proof of this is instructive. An example in the AdventureWorks database