Wednesday, January 5, 2011

SQL Server Performance Killers

There might be several individual factors that can kill the performance of your database. If you aware of the main performance killers in SQL Server in advance, you will be able to focus your tuning efforts on the likely causes.
You also need to look the hardware, operating and SQL Server settings
The main performance killers in SQL SERVER performance are as follows
  • Poor Indexing
  • Inaccurate statistics
  • Excessive blocking and deadlocks
  • No-set-based operations, usually T-SQL cursors
  • Non set-based thinking leads to excessive use of cursors and loops rather than exploring more efficient joins and sub-queries. SQL has rich mechanism to get the data instead of trying to loop or row by row approach to be used in the code, this will kill the performance
  • Poor query design
  • Querying the data from a table that is not required, or using a table in joins that is not required.
  • Poor database design
  • No reusable execution plans
  • Poor execution plans, usually caused by parameter sniffing
  • Frequent recompilation of execution plans
  • Improper use of cursors
  • Always use set based tsql query. By using cursors, you add a large amount of overhead on SQL SERRVER.
  • But if you are forced to use cursors, try to use the efficient cursor types such as fast-forward only.
  • Remember cursors are called the performance killers.
  • Improper configuration of database log
  • Excessive use or improper use configuration of tempdb
  • Every SQL Server instance has only one tempdb, which is used to store the information such as operations involving temporary tables, table variables, also operations such as sorts and row versioning, tempdb can become a bottleneck.

Share This