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

( Continued )
utilization required for plan generation by causing the generation to occur only once,
when the stored procedure is first run.
Carefully naming your database objects: Name your tables, functions, and procedures
with the following rules in mind. Procedures should not be named with the prefix
'' sp_. Functions should not be named with the prefix fn_. Tables should not be named
with the prefix sys. The reason is that objects beginning with these names are assumed
to be in the master database. Thus, suppose you have a table named sysevent in a local
database. Further assume you have a query that references that table. Without a fully
qualified reference in the query, SQL Server is going to unnecessarily look in the master
database before it searches the local one.
Ad hoc query text must match exactly: For ad hoc queries, the text needs to be iden-
tical from one invocation to the next. Any difference in the text, including comments,
will cause SQL Server to generate different query plans, and therefore won't re-use
cached plans.

Now that you've learned to gather the facts about queries, it's time to learn how to apply that knowledge.
This application will result in queries better optimized than SQL Server was able to produce.

End Game: Performance Tuning T-SQL
This tuning business can be a slippery slope for analytical types. You can get so distracted by analyzing
the data and the thrill of reducing costs that you spend hours optimizing things that have minimal user
impact. Focus on the things that have the greatest impact to the end user. Ultimately, that means make
it faster. Do you want to make things faster? Reduce the I/O in the query. Focus particularly on I/O
that is caused when disk heads are moving around. Assuming that the hardware guys have optimized
disk placement, we are left with making changes to our T-SQL requests that make use of good indexing
schemes and as few passes as possible on these indexes. Measure results, measure results again, give up
some minor gains in efficiency for maintainability, add some common sense, and you've got it.

The methodology that we are using involves reading the query execution plan, measuring actual results
or key performance indicators (KPIs), and examining index and schema designs for performance enhance-
ments. When performance tuning T-SQL this is our general process flow:

1.

Continue to educate yourself on fundamentals of database design, indexes, and optimizer
logic. This is really a prerequisite and an iterative task that through familiarity will enable
you to see patterns well before you can back it up with analytical data.

2.

Run the query and study the execution plan. Study is the operative word. The more you
study, the easier the patterns will be to see.

3.

Attack the high cost operators in the Execution plan, but evaluate whether there could even
be another way. Know when enough is enough. Go to the next highest operator. Look for
patterns.

4.

Examine the Profiler results. Look for clues where estimated plans don't match real results.
You may need to force the optimizer to see the light.