Database Performance Tuning

Why Performance Tuning?

1. Prevention is better than Cure: The solution to most slow data centric applications is taking care of issues proactively rather than reactively which include:
2. Thorough analysis of the user’s needs
3.  Thoughtful design
4.  Optimal coding
5.  Appropriate implementation

How Queries are Executed

 

Common causes of poor SQL Server performance

1. Poorly written queries and database schema
2. Inadequate disk performance
3. Memory pressure leading to low page life expectancy
4. Long running queries (blocking and locking)

Focus: Query Optimization

1. Execution plans and cost-based optimizations
2. Optimization phases
3. SARG-able clauses – (This basically refers to a condition in a WHERE clause that is able to use an index if one exists)
4. Indices and Distribution statistics
5. Join Selection
6. Execution plan notation

Optimization Process

 

Performance Tuning Best Practices – DON’Ts

1. DO NOT use scalar functions in queries which process a large number of rows. Due to the way SQL Server processes this sort of operation, long query durations can result (although typically without heavy resource use – a common cause of slow running queries).
2. DO NOT use sub-queries unless it is absolutely needed. The entire sub-query result set is often evaluated, even if many of the rows are eliminated immediately by the way the sub-query is joined to the main query.
3. DO NOT use the DISTINCT operator unless it is absolutely necessary. In many cases duplicate rows can be eliminated through restructuring of a query – there are very few circumstances where DISTINCT is actually necessary.
4. DO NOT apply the ORDER BY clause to a set of data unless it is absolutely required (e.g. for front-end presentation). Never sort data into a table where the clustered index sequence differs from the ORDER BY. Sorting data is expensive, and sorting against the clustered index is pointless as SQL Server will automatically re-sequence the data upon insertion.
5. DO NOT use cursors.They use memory; they lock tables in weird ways, and they are slow.
6. DO NOT use the TEXT datatype. Unless you are using it for really large data. The TEXT datatype is not flexible to query, is slow and wastes a lot of space if used incorrectly. Sometimes a VARCHAR will handle your data better.
7. DO NOT use triggers to maintain data in regularly accessed tables. While useful for change-logging, firing triggers at (or from) busy tables can cause serious contention issues.

Performance Tuning Best Practices – DOs

1. DO prefix the identifiers of all objects with the name of their owner to ensure SQL Server immediately selects the correct context. E.g. Prefix all Stored procedures, Tables, Functions and Views owned by the database owner with “dbo”.
2. DO use UNION ALL when possible, instead of UNION – adding the modifier “ALL” prevents SQL Server from performing a DISTINCT operation on the data sets during merging.
3. DO minimize the length of any explicit transactions. The longer a transaction, and the more objects involved, the worse the impact which it will have on concurrency.
4. DO reference indexed columns in join predicates and where clauses. This will allow SQL Server to leverage indexes and minimize the size of the data set being processed earlier rather than later.
5. DO keep code as simple as possible. Several short, simple SQL statements will almost always operate more efficiently than one huge query. This also improves maintainability.

Conclusion:        Query performance tuning is an important part of today’s database applications.
Often you can achieve large savings in both time and money with proper query performance tuning.