Best Practices for Designing for Performance from the Start - Professional SQL Server 2005 Performance Tuning - page 513

With SHOWPLAN_ALL set to on, you can see that more is going on with this scalar computation than
was shown visually. Look at the profiler or run the query with SET STATISTICS IO to ON, and you'll
see that a large amount of I/O (170,000 reads) is taking place somewhere other than an index scan on the
production.product table. It makes sense that a function querying other tables should incur that cost on
each row materialized in the view. What isn't so clear is why the optimizer hides the ultimate impact of
those details from you. The result is that programmers relying upon optimizer feedback in the query plan
may create situations where the queries become performance issues as the result sets grow. To counter
this, continue to educate your developers. Outside of that, you can look for function calls in syscomments,
assuming they are named with the dbo.fn prefix by using this query:

SELECT * FROM syscomments
WHERE Text like '%dbo.fn%'

Evaluating Indexes for Performance
There is just not enough index design activity occurring in today's data modeling activities. It seems like
all the time is spent in the table modeling, but indexes can really change the performance of a database
negatively or positively depending upon how they are implemented. If you are working on improving
performance for an application that has some transactional history, you're in luck. You can evaluate
index performance by using the new Dynamic Management View (DMV) that provides index usage
statistics named sys.dm_db_index_usage_stats . In this view you can determine which indexes have
never been used and which indexes are consistently being used. If you follow the recommendations in
the benchmarking section of this chapter, you'll be able to observe usage patterns on your index schemes
of time and make meaningful adjustments.

If there are indexes on a table that aren't being used, then performance is being affected because indexes
are being built that are never used. These statistics will help back up explanations to others who insist on
doing things like placing an index on a column that has low selectivity (that is, a small number of distinct
values relative to the number of rows). This creates useless indexes that can reduce performance. The
database server has to not only search the entire set of rows that match the key value, but it must also
lock all the affected data and index pages. This process can slow down or even block the performance of
incoming update requests.

No-Thought/Default Indexing
If you are in the beginning of a database project and are unsure about how to model indexes, start with
the no-thought or default indexing schemes. Adjust your indexing once you are able to re-evaluate the
database with actual transaction information. Consider following these suggestions for starters:

❑ Create indexes on all primary keys. (This should be a given.)

❑ Create indexes on all foreign keys. Assuming you are going into a development phase on a

new database, this won't hurt. There are some situations where foreign key indexes may not
be needed, but go with the 80/20 rule and evaluate later.

❑ Don't create additional indexes for columns that are already in an index. This is unnecessary

and may result in the index not being used.

Order columns in your composite indexes by selectivity. The columns with the most selectiv-
ity should go first. You may also combine two not very selective columns in a composite index,

❑