Sql Server Performance considerations

1.       Ensure that joins formed correctly and also ensure that all the parts of keys are included in the ON clause.

2.       Pay extra attention to avoid cross product result (Cartesian product) from the query by missing ON or Where clause in the join.

3.       Do not automatically add the DISTINCT Clause in the select statement. The duplicate data may be result of incorrect data model or an incorrect join.

4.       Return only the necessary rows and columns. The Select * statement not only returns all the data; It also forces clustered index scan for query plan even though there is a index defined on a column. The large amount of data having impact in the performance of server and client side processing.

5.       Try to avoid explicit or implicit functions in the where clause. The column in the where clause is always seen as a expression rather than column. Therefore those columns are used in the execution plan optimization.

6.       Use stored procedure or parameterized queries. Having advantages like Logic separation, tuning and deployment, Network bandwidth, simplified batching of commands, improved data security and integrity and avoiding sql injection (dynamically generated sql by user input).

7.       Avoid using cursors. Cursor forces the database engine to repeatedly fetch the rows, negotiate block and manage locks and transmit the result.

8.       Avoid long actions in the triggers. The long run action may hold the lock longer than intended. It may block other queries.

9.       Use temporary tables and table variable appropriately. If application creates temporary tables frequently, you can consider for table variable. The table variables are cleared up automatically at the end of functions, stored procedures or the batch in which table variable is defined.

10.    Indexes: Indexes are important to efficient data access. However, there is a cost associated with the creating and maintaining the index structure. Obviously the more number of indexes will increase the data access performance but it will degrade the insert, update, and delete operations performance.

Clustered index:  In the clustered index, the leap level nodes of B – Trees, contains actual data rows for the table. There is only one clustered index per table.

Non - Clustered index:  In the non clustered index, the

a.       Create Indexes based on uses:  Evaluate the most commonly used, complex and problematic queries and defines the indexes for those tables. Do not create the indexes if the table is rarely queried.

b.      Keep Clustered index keys as small as possible:  Because the non-clustered indexes store the clustered index keys as their row locator. The row locator references the actual data row. Therefore keep clustered index keys as small as possible.

c.       Consider Range data for clustered indexes:  The database is queried frequently for range of data using clauses such as BETWEEN or Operators >, < in the where clause, consider the clustered index on the column specified in the where clause.

d.       Create index on all foreign keys: Be sure on creating indexes on all the foreign keys. Since the foreign keys are used frequently in the joins.

a.       Create highly selective Index: Create indexes on the columns which have many distinct values.

b.      Covering index for often used and high impact queries: The queries that are called frequently, problematic queries and queries which consume more resources are good candidate for covering index.

c.       Consider indexes on columns which are used in the WHERES and aggregate operations such as Group By, Distinct, Max, Min or Oder By.

d.      Remove unused indexes:  Remove all the un-used or out-of-date indexes continue to impact the write operation. They are still used by the optimizer for execution plan considerations.

11.   Transactions:

a.       Avoid long run transactions:  Locks are held during the transaction. So it is critical to keep the transaction as short as possible.

b.      Avoid transaction that required user input to commit: Still user commits, the locks are held.

c.       Access the heavily used data at the end of the transaction.

d.      Try to access the resources in same order.  Like the SP1 and SP2 access the T1 and T2 in the same order.

e.      Ensure the explicit Commit or Rollback the transactions.

12.   Stored Procedure:

a.       Use SET NOCOUNT ON in the stored procedure.

b.      Do not use the Sp_ Prefix for the custom stored procedure. Since it is prefix for the system stored procedure, when it is executed, the system first looks master database for its execution.