Thursday, 14 November 2013

SQL Server - Database Performance Tuning Tips


Database Performance is playing vital role in most of the applications. It also depends on other System activities such as CPU, Memory and Disk Utilization. Performance needs to be considered\monitored on each and every step of database build.

Basic performance tuning steps are given below as part of T-SQL Coding, Indexing and Database Design.

T-SQL Tuning

1.    Always use SELECT with specific columns rather than using ‘*’ in order to avoid Disk, network and memory contention of Server.
2.    Avoid frequently querying Database from Application which may cause network blockage. Instead use a method of caching data on Application server memory and update database in one shot.
3.  Avoid using Longer Transaction which may block other users. Keep transactions as simple as possible and don’t let it be in OPEN State if it is not used.
4.   Use CURSOR only if it is required. It may cause Server performance bottlenecks.
5.   Always try to use same data type for variables and parameters as in Table columns. Because Implicit\Explicit conversion will lead to Table SCAN which may slow down the performance.
6.    Avoid using functions on Columns for WHERE clause and JOINS in which SQL Optimizer can’t use indexing properly which affects performance due to full Table scan.
7.   Avoid using GROUP BY, ORDER BY, DISTINCT and UNION unless required.
8.  Query Hint needs to be used very carefully which is controlling query optimizer while preparing execution plan. Avoid using query hints such as NO_LOCK instead use Snapshot Isolation which avoids blocking.
9. Try using Common Table Expression (CTE) or Table Variable instead of Temporary table in which statistics update happens while inserting data into temporary table which leads to recompile.

Index Tuning

Index needs to be handled carefully. Performance problem may occur either for no index or too many indexes.

1. Clustered Index needs to be created on right place where the columns are frequently accessed or which defines the data structure of storage. We need not to be having Primary Key and Clustered Index which is default behavior.
2. Avoid using Clustered Index on column where it is updated frequently.
3.    Non-Clustered index can be created after analyzing execution plan of the query. Create only if it is required. Too many indexes can slow down the performance.
4.    Always use Index on foreign key columns which may avoid Table Scan.
5. Avoid using Clustered index on GUID which may lead to fragmentation due to random nature of GUID. Instead use NEWSEQUENTIALID () to generate GUID which avoids fragmentation.
6.     Try using INCLUDE option in order to have additional columns in Non-Clustered index which stores extra columns at the Leaf level of the Index.
7. Try adding index on temporary table usage to enhance performance.
8.   Try setting SQL Server to update Statistics automatically in order to have up-to-date execution plan.

Database Design Tuning

1.  Always create database in normalized manner in order to avoid inconsistent data.
2.    Choose appropriate data type for Columns.
3. Always maintain separate database (OLAP) for Historical data which enhances OLTP process.
4.  Try having multiple physical disks for database which speed up I\O operations by having parallel process. Also separate Data and Log files.
5.   Pay Attention to Log Files. Log growth can be expensive, better to ensure we have the required free space. Also ensure we don’t have too many virtual log files (VLFs).
6.  Minimize temp DB Contention. Temp DB can be used by all the other databases on the server, and it is often the most used database.
7.  Change the MAX Memory Limit. Remember to leave at least 1 or 2GB of memory for other processes. Also consider the impact of multiple instances.