This next series of posts aims to provide an introduction to resolving
big and sudden performance degradation. I will also add a few tips for general performance related database best practice and tidying.Introduction
In this post I will cover the reasons and main methods of investigating performance issues in your database.Most of the systems I have worked on and managed experienced performance problems due to disk IO bottlenecks. Often this was due to inefficiently written stored procedures and/or inappropriate indexes. Generally, when applications do not, by design, put much pressure on memory or the CPU any high usage of either or both is usually down to a disk bottleneck of some sort.
Please keep in mind that performance tuning is part science part art. Below you will find guidelines detailing what to look for and things to keep in mind and try. Try is the key word here. All changes must be tested. Depending on the construction of the query, the tables and the data sets involved, what worked for one stored procedure will not necessarily work as well for another.
Reasons for poor performance
In my experience problems are often due to a combination of the following factors:- Inefficiently written SQL code, combined with
- Less than optimal disk configurations, exacerbated by
- A huge increase in usage, both volume of data and numbers of users, capped by
- Long intervals between performance reviews
Investigating Performance
Tools
There are four main tools used to identify performance bottlenecks:- SQL Server Profiler
- SQL Server Dynamic Management Views and Functions (DMV): see DMV Starter Pack.
- Actual Execution Plans
- Your brain
The fourth tool is not a joke! Performance tuning is not an easy task: there are many factors to consider; it is a true balancing act; and there is not computer based tool that can give you all the answers. So you need to think!
SQL Server Profiler
It may be worthwhile to run a quick profiler trace to locate an offending query in the event of sudden performance degradation. There are cases, for example large data imports, or development releases, that cause the database’s indexes or table statistics to become stale. A SQL Profiler trace can quickly highlight queries that are running slowly. The indexes and statistics for the tables being accessed by those queries can then be rebuilt. If performance is restored your work is done and the rest of the guidelines in this document can be saved for when time permits.However, as traces can impact system performance when they are running it is best to leave them as a last resort. Use the strategies below in the first instance to tune the database.
Queries
Expensive queries
The first two scripts show stored procedures that use high amounts of disk IO. Once the below queries are run, take the most expensive SPs and run them with the Execution Plan in SSMS. See Execution Plan section for what to do.Frequently run queries
Another option is to look for stored procedures that are run very often. A small per cent improvement on a frequently run query can prove beneficial to overall system performance.Indexes
Indexes should be maintained and optimised on a regular basis. As more data is being entered and constant features being added, the nature of how the data is accessed changes. The following queries will aid in identifying indexes that need to be created, dropped or modified.Unused Indexes
A good place to start is to identify indexes that are not being used at all. This will help tidy up the server by getting rid of conspicuous indexes that provide no benefit.Possible bad indexes
The next step in tidying up and the first in improving performance is to find indexes that are written to but not read from. The query below finds indexes with no user_seeks, user_scans or user_lookups (reads). It is sorted by index writes from most to least. Such indexes can be dropped.Execution Plans
Interpreting execution plans can be tricky but there are some areas that are rather straight forward. Below are a few key things to look for to aid performance tuning. For in-depth information on execution plans see Dissecting SQL Server Execution Plans (PDF).Identifying main query bottlenecks
The first steps for interpreting the plan are:- Look for the most expensive part(s) of the query
- Look for the most expensive part of the query within that section
- Look for any missing index suggestions. Be careful not to blindly create indexes based on these suggestions. They should be used more to indicate that something should be done.
Interpreting key icons
Once you have identified the main bottlenecks within the query there are several key icons to look for in the execution plan.- Table Scan
A table scan indicates that the query optimizer cannot find an appropriate index to speed up the related query. In most cases, especially for larger tables, the addition of even a moderately effective index will vastly improve performance.- Index Scan
An index scan is not always bad. As opposed to an Index Seek it indicates that the database engine is trawling through the entire index to find what it needs. In some cases this is necessary, for instance, when either a large proportion of the index’s or table’s data is to be used or if the index has many duplicate values. However, an index scan, as with a table scan, may indicate that an appropriate index does not exist and therefore, it should be investigated.
- Bookmark/RID Lookup
The Lookup icon indicates that a non-clustered index was used to find the necessary rows but there was additional data needed from the table’s clustered index or heap, requiring an additional read. In some cases, especially when such a read is very expensive, it is worth modifying (or in the case of a Primary Key: adding) an index to “cover” the query. See section 8 for information on indexes.In the next post I will go into resolving the performance problems now that you have identified them.
No comments:
Post a Comment