IntroductionIn 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 performanceIn 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
ToolsThere 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 ProfilerIt 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.
Expensive queriesThe 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 queriesAnother 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.
IndexesIndexes 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 IndexesA 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 indexesThe 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 PlansInterpreting 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 bottlenecksThe 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 iconsOnce you have identified the main bottlenecks within the query there are several key icons to look for in the execution plan.
- Table Scan
- Index Scan
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
In the next post I will go into resolving the performance problems now that you have identified them.