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

5.

Rework the T-SQL to take advantage of different methods of doing the same thing until you
find a faster way. Trial and Error with persistence supplements knowledge in a real way.

Essential Tools
Before you can start applying some performance tuning techniques and problem solving, there are
prerequisite skills and knowledge that can be categorized as essential tools. The next few sections will
enumerate these skills so that you can add these to your daily performance tuning toolkit.

MinimizingCachingEffectsforBestKPIs
To get good performance tuning KPIs, you have to be aware of caching effects, both from a data page and
a query plan perspective. One way to do this is to run a query and then cycle the server, but this is not
practical. Fortunately, if you are not tuning a production environment and have ALTER SERVER STATE
permissions on the server, you can run the commands found in Table 9-9 to free up cache:

Table 9-9: Methods of Eliminating Caching Effects

Command Description

DBCC FREEPROCCACHE Frees all cached query execution plans.

DBCC FREESYSTEMCACHE Frees unused cached query execution plans (and any cached entries

from all caches).

CHECKPOINT Writes any dirty pages to disk.

Note that you should not use these commands if you are performance tuning in a production environ-
ment. Even if you are running these types of commands in a shared test environment, you should warn
users of that environment that you are doing performance tuning.

SettingUpProfilertoCaptureActualResults
Gathering empirical data from the profiler is a long-standing method of gathering KPIs for performance
tuning. However, note that running the profiler is expensive and it only needs to run for short evaluation
periods. There are technical arguments for spooling the trace file out to a file or to the Profiler user
interface (UI). In the grand scheme of things, it is more convenient to read the trace in the Profiler UI
tool while you tinker until you have all the EventClasses and columns with the right filters. If you need
to query the results, just save the file and use the sys.fn_trace_GetTable function to load the file into a
table. When you are targeting specific data to profile and have the trace like you want it, it may make
sense to create a trace using T-SQL code and have this running in the background straight to a file. To
create the T-SQL code just navigate the Profiler menu options File

For
SQL Server 2005. You can find the exact Trace Template used in this for both profiler and T-SQL versions
ready for download at www.wrox.com .

➪ Export ➪ Script Trace Definition ➪

Querying the profiler trace file is handy when you are looking for specific problems either by event
class or even within the plans themselves. When you query the file, you'll notice that the rowset is not