MS SQL Server Performance Killers

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


About Devendra Singh

A guy with self learning capability , Analytical Ability , Exploring in nature , Just looking for ways for spreading happiness among everyone. Little aggressive some time. Just want peace everywhere in this world.
Aside | This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s