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

if you are troubleshooting a production environment since the overhead is much lower. Download the
SQL profiler trace template that we've created for you at www.wrox.com to see the metrics that we review
in this chapter, or create your own. Notice in the query plan for the WHERE query that there are several
efficiencies that have been gained. First, the predicate is being realized at Step 22 instead of later in the
plan. In real terms this means that the number of rows of concern have been reduced to three rows almost
immediately. This means that only these three rows are read from disk into memory instead of all 395
rows that result from the JOIN of the Product and ProductListPriceHistory tables. The second efficiency
is that the access methods for reading the indexes have been turned into SEEK operations instead of
SCAN operations. Lastly, the sort operation in the HAVING plan has been eliminated. Sort operations
typically use CPU and memory. Eliminating this step by using the ORDERED FORWARD SEEK cursors
and Nested-Loop join operators reduce the CPU and memory demand as well in this plan.

The optimizer assumes that you know what you are doing and there are valid uses of the application of
HAVING filters, so there are definitely times when you can tweak your T-SQL statements and get better
plans. The issue here is making sure your queries match your intentions, because the optimizer can't
read minds.

Using Temp Tables for Intermediary Results
If you write any reporting procedures, you undoubtedly have come upon situations where it just makes
sense to store an intermediary result. If you are dealing with a highly relational OLTP structure that
requires multiple traversal paths, it is almost a necessity. Creating a temp table incurs four separate cost
components: the creation of the structure, the initial reading of the data, the writing of the data into the
structure, and the reading of the data back out again. However, even though there is redundancy here,
there are times that this is the highest-performing method. Here are some of the reasons that you want to
rewrite your queries to use temp tables.

❑ You are retrieving results of a stored procedure call. In SQL Server, you have no other choice but

to store the results in a temporary structure.

❑ You can reuse scenarios for complicated or involved traversals or aggregations. In this case,

the temp tables are more efficient because you only have to travel the index structures once. If
you are aggregating data, you are eliminating multiple CPU and Memory usages to perform
redundant calculations.

❑

You are reducing the number of JOINs. In very large databases, the process of joining two large
tables can be an expensive operation. Pulling the results from each table independently into
temp tables and joining these results can be more efficient if the results are small.

❑ You are decreasing the complexity for maintenance purposes. Sometimes you have to give a

little on the performance side to be able to maintain the query. In our shop, some of the report
queries are involved. It is nice to be able to look at intermediate results to get an early indication
of where a problem exists in the data when troubleshooting or code reviewing the query.

With the introduction of table variables in SQL Server 2000, there has been some debate on when to use
table variables versus #temp tables. One common misconception is that table variables are created in
memory and only #temp tables are created in the tempDB database. This is not true. Both are backed by
pages in tempdb, but may not incur any I/O depending upon how much memory is available. If enough
memory is available, then the structure is created in the data cache. If the data storage requirement
exceeds memory capacity, then I/O incurs in tempDB. Table 9-10 contains a list of the differences from a
performance perspective.