Saturday, September 10, 2011

Performance Tuning Guidelines for SQL Server Database Application

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.

Database Performance Tuning Guide. Performance Tuning Guidelines for SQL Server Database Application. SQL Server 2012 Performance Tuning. SQL Server 2008 Performance Tuning. SQL Server 2005 Performance Tuning. R2. Improving SQL Server Performance. Query Performance Tuning. SQL Server Code.Tips and Tricks. Performance Tuning. SQL Server Performance Tuning for SQL Server Developers. SQL Server Developer performance tuning steps. step by step guide to tune performance of the application. SQL Server Performance Tuning Tips. Performance Tuning for SQL Server. CPU Bottleneck. performance tuning sql server 2005. performance tuning sql server 2008 r2. performance tuning sql server 2008 queries. performance tuning sql server 2008. performance tuning sql server 2012. sql server performance stored procedures. stored procedures performance tuning


Post a Comment