| |
 |
|
SQL Server Tips by Robin Schumacher
|
Look for Object-Based
Solutions
Reworking join predicates and using subqueries instead of joins can
produce dramatic differences in query response time. However, the
technique required to help a slow running piece of SQL often
includes adding or altering something at the physical design level.
An upcoming section of this chapter will deal with this topic in
more detail. For now, just know that one should always be thinking
about what can be done at database level to enhance query speed. For
example:
-
Adding new indexes to eliminate scan
operations.
-
Altering existing indexes to create
covering indexes or increase the effectiveness of composite
indexes.
-
Altering the clustered index choice for
a particular table.
-
Removing indexes for tables that are the
targets of heavy BCP or INSERT, UPDATE and DELETE operations.
Indexes may be added once data modification tasks have finished.
-
Denormalizing an excessively normalized
database to decrease join operations.
-
Building a reporting sub-structure that
can support resource intensive SELECT queries.
-
Partitioning data to reduce table width
or data volumes. This must be manually performed in SQL Server 7
– 2000, but can be done through DDL in SQL Server 2005.
-
Pinning small lookup tables in memory to
speed access to data that is often referenced.
-
Investigating the use of data archive
software that archives older, seldom used data onto other
servers, which can still be accessed if need be, and leaves
often referenced data on the primary server.
-
Putting a plan in place to automatically
eliminate fragmentation in indexes and tables that are subject
to actions that frequently disorganize them.
The above book excerpt is from:
High-Performance SQL Server DBA
Tuning & Optimization Secrets
ISBN:
0-9761573-6-5
Robin Schumacher
http://www.rampant-books.com/book_2005_2_sql_server_dba.htm |
