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

cost of 0 percent but the CPU and Page reads are off the chart. What is going on? To make sense of this
you need to examine the query plan in Figure 9-8.

Figure 9-8

Hovering over the Operator does not tell you what is going on at this step. The best way to peer into
this step is looking at the Showplan Statistics Profile Event Class in the profiler. Here you'll find
an explanation:

Compute Scalar(DEFINE:([Expr1002]=[AdventureWorks].[dbo]
.[ufnGetProductStandardCost]...

Since we are sure that this computation is performed by calling the user-defined function and this
function is being called per row, surely this must cost something. You can see this by again looking
into the Profiler at each time the statement within the UDF is executed. Typically, after the first time the
statement is run and cached, you'll see performance numbers similar to the following:

❑

CPU: 10

❑

Reads: 411

❑ Duration: 8ms

What's happening here is that the optimizer doesn't do a very good job at estimating the cost of executing
user defined functions. In fact, the optimizer is essentially punting instead of providing cost information.
If you just base your decisions upon the execution plans, you'd be missing a big I/O requirement of the
UDF-based query. Understand that a UDF must be executed like a correlated subquery per row, but
can not be optimized in combination with the SQL statement that calls them. If you are calling these
separate, atomic queries for each row, you'll find that this gets more expensive the larger the result set.
This doesn't mean that UDFs should never be used. The encapsulation benefits many times outweigh the
performance or code complexity caused by removing them. Just make sure that the WHERE predicates are
limiting results to small result sets; they just don't scale well into large result sets.

Reworking SELECT *
You can't always tune what you are being required to select, but doing so can surprisingly improve
performance. It comes down to the number of pages that have to be read to produce the result. If a
non-clustered index exists where all the columns you are selecting are available, then the index can be
read directly instead of reading the clustered index and subsequent pages. This type of index is called a
covered index. If the rows are wide or contain large data types that you don't need in your results, the
punishment for the * character will be the number of pages that must be read to contain the data. Here's
a simple example that demonstrates the performance costs.