We are working to find out the best root cause analysis of performance troubleshooting for MS SQL Server .
1.Incorrect plan used by Stored Procedure
2.No indexes
3.Inefficient queries – Bad estimates causing has joins and index scan
4.Inaccurate\missing statistics
5.To much data retrieval . Not using WHERE clauses
6.DBAs relying on Perfmon rather than SQL Server Profiler to troubleshoot SQL Server Performance Problem
7.Using cursors rather than set based T-SQL
8.Slow performance on SQL Transaction Logs causing serious bottlenecks
9.SQL Data file autogrow causing transactions to stall
10. Starving the OS of memory due to poor memory configuration on SQL Server
11.Placing all databases on the same drives causing IO delay issues
12.Executing bulk imports at Production peak times causing performance issues.
13.Using SQL Server triggers and following best practises, particularly using nested or recursive triggers 14. DBA not performance testing a new disk subsystem
15.DBA relying on the “Kill it by Iron” approach. Not attempting to find the bottlenecks
16.Queries not hitting indexes and forcing scans, when using VARCHAR(max)
17.AntiVirus scanning database files
18.Screensavers with intensive CPU usage
19.High levels of blocking and deadlocking
20.High row count estimates causing hash joins with table/index scans , result:high CPU
