Database Reference
In-Depth Information
Excessive Fragmentation
While analyzing data retrieval operations, you can usually assume that the data is organized in an orderly way, as
indicated by the index used by the data retrieval operation. However, if the pages containing the data are fragmented
in a nonorderly fashion or if they contain a small amount of data because of frequent page splits, then the number of
read operations required by the data retrieval operation will be much higher than might otherwise be required. The
increase in the number of read operations caused by fragmentation hurts query performance. In Chapter 13, you will
learn how to analyze and remove fragmentation.
Nonreusable Execution Plans
To execute a query in an efficient way, SQL Server's query optimizer spends a fair amount of CPU cycles creating a
cost-effective execution plan. The good news is that the plan is cached in memory, so you can reuse it once created.
However, if the plan is designed so that you can't plug parameter values into it, SQL Server creates a new execution
plan every time the same query is resubmitted with different values. So, for better performance, it is extremely
important to submit SQL queries in forms that help SQL Server cache and reuse the execution plans. I will also
address topics such as plan freezing, forcing query plans, and using “optimize for ad hoc workloads.” You will see in
detail how to improve the reusability of execution plans in Chapter 15.
Frequent Recompilation of Queries
One of the standard ways of ensuring a reusable execution plan, independent of values used in a query, is to use a
stored procedure or a parameterized query. Using a stored procedure to execute a set of SQL queries allows SQL
Server to create a parameterized execution plan
.
A
parameterized execution plan
is independent of the parameter values supplied during the execution of the
stored procedure or parameterized query, and it is consequently highly reusable. Frequent recompilation of queries
increases pressure on the CPU and the query execution time. I will discuss in detail the various causes and resolutions
of stored procedure, and statement, recompilation in Chapter 15.
Improper Use of Cursors
By preferring a cursor-based (row-at-a-time) result set—or as Jeff Moden has so aptly termed it, Row By Agonizing
Row (RBAR; pronounced “ree-bar”)—instead of a regular set-based SQL query, you add a large amount of overhead to
SQL Server. Use set-based queries whenever possible, but if you are forced to deal with cursors, be sure to use efficient
cursor types such as fast-forward only. Excessive use of inefficient cursors increases stress on SQL Server resources,
slowing down system performance. I discuss how to work with cursors properly, if you must, in Chapter 22.
Improper Configuration of the Database Transaction Log
By failing to follow the general recommendations in configuring a database transaction log, you can adversely affect
the performance of an online transaction processing (OLTP)-based SQL Server database. For optimal performance,
SQL Server heavily relies on accessing the database logs effectively. Chapter 3 covers some aspects of how to configure
the database transaction log properly.
