Database Reference
In-Depth Information
You should analyze the stress created by an application on a SQL Server database at two levels:
•
High level
: Analyze how much stress the database application is creating on individual
hardware resources and the overall behavior of the SQL Server installation. The best measures
for this are the various wait states. This information can help you in two ways. First, it helps
you identify the area to concentrate on within a SQL Server application where there is poor
performance. Second, it helps you identify any lack of proper configuration at the higher
levels. You can then decide which hardware resource may be upgraded if you are not able to
tune the application using the Performance Monitor tool, as explained in Chapter 2.
•
Low level
: Identify the exact culprits within the application—in other words, the SQL queries
that are creating most of the pressure visible at the overall higher level. This can be done using
the Extended Events tool and various dynamic management views, as explained in Chapter 6.
SQL Server Performance Killers
Let's now consider the major problem areas that can degrade SQL Server performance. By being aware of the main
performance killers in SQL Server in advance, you will be able to focus your tuning efforts on the likely causes.
Once you have optimized the hardware, operating system, and SQL Server settings, the main performance killers
in SQL Server are as follows, in a rough order (with the worst appearing first):
•
Insufficient indexing
•
Inaccurate statistics
•
Improper query design
•
Poorly generated execution plans
•
Excessive blocking and deadlocks
•
Non-set-based operations, usually T-SQL cursors
•
Inappropriate database design
•
Excessive fragmentation
•
Nonreusable execution plans
•
Frequent recompilation of queries
•
Improper use of cursors
•
Improper configuration of the database transaction log
•
Excessive use or improper configuration of
tempdb
Let's take a quick look at each of these issues.
Insufficient Indexing
Insufficient indexing is usually one of the biggest performance killers in SQL Server. In the absence of proper indexing
for a query, SQL Server has to retrieve and process much more data while executing the query. This causes high
amounts of stress on the disk, memory, and CPU, increasing the query execution time significantly. Increased query
execution time then can lead to excessive blocking and deadlocks in SQL Server. You will learn how to determine
indexing strategies and resolve indexing problems in Chapters 8-12.