Performance tuning in sql server

Dear users some time we have to increase the server or database performance. So based on that our application give us quick response. Here is the some best tips for performance tunings.

Performance Tuning :

The process of modifying the Application or Database in order to make the entire system runs faster.

1. Application server giving poor performance (App. Users issue)

2. Open performance monitor, monitor application specific counters.

3. Identify application process in SQL Server (SPID), Blocking & Deadlocks in the database server

4. Database level tuning

5. Identify queries & Stored procedures submitted by the application (Query tuning / Stored Procedures optimization) based on “SPID”

6. Hardware resources allocated for SQL Server with respect to users load on server

7. Prepare baseline with measures and metrics.

8. Periodically monitor the measures and compare with baseline 

Identify Blocking & Deadlock in the Database Server.

1. Blocking is a situation where multiple transactions are waiting for resource that was exclusively locked by a transaction for a long period of time.

 

2. Deadlock when two processes each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock unless one of the user processes is terminated. SQL server detects deadlocks and terminates one user’s.

 

3. Blocking and Deadlocks degrades the database server performance as well as application performance.

4. Livelock is one where a request for an exclusive lock is repeatedly denied because a series of over lapping shared locks keeps interfering. A live lock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely. 

Database Level Tuning:

1. Index maintenance on regular basis.

2. Statistics updation on regular basis.

3. Data partitions for huze tables it works from SQL Server 2005 onwards.

Index Level Tuning:

1. Table scan brings a poor performance, add indexes where more table scan is happening.

2. Indexing: Index is nothing but ordered list of values taken from one or more columns of a table and organized into B-Tree (Balanced Tree) structure.

3. Index will improve the select query performance.

4. Indexes will degrade the performance of insert, delete, and update statements.