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.
No comments:
Post a Comment