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

Table 9-31: Paging with Temp Tables and CTE Comparison

Method % Cost CPU Reads Duration

First Page

TEMP 99 7300 19208 9799ms

CTE 1 30 + 300 82 + 1743 121ms + 2200ms

Compare the numbers between the CTE and the temp table method after adding in the additional count
operation to see that the cost saved with the @@ROWCOUNT is not offset by the amount of I/O that is
consumed.

Tuning the T-SQL Statement with Hints
Sometimes you might run into some issues that can't be resolved. Your data or situation just runs counter
to how the optimizer approaches the solution, and you can only get the performance you need by tuning
the T-SQL statement to get the plans that you've been able to simulate. This section will cover a common
issue of plan caching, how to identify the issue, and how to resolve it.

Query Plan Caching Issues
Query plan caching is a wonderful thing. The idea is that a parameterized statement can be processed by
SQL Server and only incur the costs of planning the best way to execute it once. This works
wonderfully as long as the parameters represent evenly distributed data or you don't get too fancy
and write multi-use, multi-parameter stored procedures. Performance issues occur when the first plan
encountered by the optimizer isn't representative of the high-percentage usage of the procedure. The
issue is that the low-percentage usage of the procedure is now cached and the high-percentage usage will
have to use whatever plan was cached first. This is not optimal when the cached version is
optimized for only a few rows of retrieval and the high-percentage usage is for a large row set. Rows
will then be retrieved with nested loop operators that could more efficiently be retrieved with merge join
operators. The opposite could also occur, but may be more difficult to detect.

To see an example of this in action, you need to produce a really low and high-selectivity predicate on a
large row set. You'll use the SalesOrderHeader table for a large row set and change one of the customer's
addresses to Khorixas, Namibia, for a low selectivity address. Run this query to doctor up the data in the
address schema.

INSERT INTO person.StateProvince
SELECT 'KX', 'NA', 0, 'Khorixas', 1, newid(), getdate()
UPDATE pa
SET StateProvinceId = scope_identity()
FROM Sales.customerAddress ca
INNER JOIN person.address pa
ON ca.addressid = pa.addressid
WHERE customerid = 22103