Understanding and Troubleshooting SQL Server Performance Issues

SQL Server Performance Tuning is a major topic for Database Administrators and it is also a challenge for them to trace the issues and tune it. We should know what are the major areas where SQL Server performance issues may reside.

Here, I am listing major areas for database performance issues.

• Poor Database Indexing

• Poor Query Design

• Old Statistics

• Poor and Non-Reusable Execution Plans

• Excessive Blocking and Deadlocks

• Improper Use of Cursors

• Poor Database Design

• Excessive Database Fragmentation

• Frequent Recompilation of Queries

• Improper Configuration of Database Log

• Improper Configuration of tempdb and Excessive Use

You can also refer below links to troubleshoot performance Issues.

• Configure SQL Server Memory To Optimize Performance
• Identify Database Bottlenecks with Performance Monitor Counters
• Troubleshooting Database Slowness and Blocking with sp_who, sp_who2 and sp_WhoIsActive
• SQL Server Wait Types – Trace and Tune Database Performance Issues
• Activity Monitor in SQL Server 2012
• Database Engine Tuning Advisor Step By Step

Reference: Manzoor Siddiqui [www.SQLServerLog.com]