Monitoring and Performance Tuning Tips for SQL Server database

Performance tuning in SQL Server is essential topic and Microsoft defined set of process in back end to bring the data from database. This is interest chapter for developers and those should follow the Microsoft evolved rules to access the data from the database.

There are so many ways to achieve the performance tuning in SQL server and below topics are covering the different scenarios.

Performance tuning in Database Architecture

1. Understand the business logic thoroughly and define the appropriate data type and length for the fields.


2. Define the primary key on each table and add foreign key if the fields referred from another table.


3. Use relevant constraints on the fields such as UNIQUE, CHECK and NOT NULL.


4. Choose CHAR, VARCHAR data type instead of NCHAR and NVARCHAR. Because it is occupying double memory rather than CHAR and VARCHAR data types.

Performance tuning in SQL Queries

1. Follow the ANSI/Standard format while writing the SQL query and avoid irregular formats.


2. Avoid using * in SELECT and define columns list needed for the scenario.


3. Use WHERE clause for scenario basis to filter the particular rows instead of bringing all rows.


4. Define alias name when using JOIN and mention the alias name in front of columns in SELECT/UPDATE.


5. Try to use JOIN/EXISTS and avoid NOT IN, IN clause for filter the data from another table.


6. Use JOIN instead sub queries.


7. Understand the temporary tables, table variables scope and create appropriately. 


8. Create minimal process/logic in DML triggers.


9. Understand the isolation level scope and use it accordingly.


10. Create stored procedures for frequently used SQL Queries, complex SQL Queries and high impact queries.


11. Use SET NOCOUNT ON and TRY - CATCH in Stored procedures to avoid Blocking and Dead Locking.


12. Use WHILE Loop instead CURSOR since CURSOR is a network round trip and it is affecting the performance.


13. Use Schema name before using SQL objects name.


14. Avoid prefix "sp_" while defining user defined stored procedure name since it is a system defined procedure.

Performance tuning using Indexes

1. Create the indexes based on the query usage and should not create blindly.


2. Define the index for all foreign key and query joining columns across the database.


3. Create covering index for frequently using and high expensive queries.


4. Consider the index on columns used in WHERE, ORDER BY, GROUP BY and SELECT columns respectively.


5. Evaluate index usage using DMV(sys.dm_db_index_usage_stats), trace the query hits using SQL profiler and drop unused/unwanted indexes from the table. Define the FILL FACTOR value on index appropriately based on the table usage.


6. Better to create indexes on INTEGER fields instead of character fields.

Performance tuning using Execution Plan

1. Evaluate the query performance using execution plan.


2. Bring index seek for the query and try to avoid index scan.


3. Create proper index on the table and avoid table scan.


4. Evaluate the sorts and filters in execution plan.


5. Avoid key look up in execution plan and include the missed column on existing index.


6. Check the cost and predicate of hash match and cover the column on existing index.

Performance tuning in Monitoring

1. Identify the long running queries in SQL Server using profiler or DMV/DMF and evaluate the query performance using execution plan.


2. Find and evaluate the locking and blocking in server using sp_who2 and sp_lock.


3. Monitor the data feed and database growth day by day.


4. Identify the high impact queries on SQL Server using DMV/DMF.


5. Monitor the index fragmentation values and use rebuild or reorganize index based on the value.

Also see below articles

SQL Server/Developer Interview Questions and Answers for Experienced Professional
Difference between ROW_NUMBER, RANK, DENSE_RANK and NTILE in SQL Server
Generate or Export the data dictionary of SQL Server database Schema using SQL Query