SQL Server query performance tuning

Performance is a primary focus area for many enterprise applications. While there are numerous articles & information available on how to improve performance, here are the quick list of pointers which one should consider for performance tuning (based out of my experience) .

• Stored procedures are always more performance oriented than the inline queries since these are pre-compiled objects and the execution plan is already created in the database server (exception being the dynamic queries).

• AVOID Dynamic Queries - This is a simple rule which needs to be followed while tuning SQL queries. Dynamic queries are always slow to execute since the plan for execution is generated at run time.

• SET NOCOUNT ON - Another rule while working on a Stored Procedure, always SET NOCOUNT ON.  This one line of code is put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed. If you run the SELECT, INSERT, UPDATE and DELETE, the affected rows are displayed in the query window, but while executing the stored procedure from your application server (generally through services in a SOA model), this information is not required.

• Never use WITH INDEX or Index Hints - You might come across few links which would suggest to provide WITH INDEX to improve performance, but my experience says NO!! If you have joins in your queries and you specify an index (means you are forcing the server to use the index), this would first perform an Index Lookup (which is an overhead). Please check your execution plan while using With Index. (I have come across few scenarios where the execution time reduced from 15 sec to 2 sec by removing WITH INDEX).

• There should be minimum table scans in the execution plan. Always create indexes on the columns which you are using to join the tables. This ensures the execution plan is always an INDEX SEEK on the table instead of Table Scan (which is a costly operation). Below is a sample screenshot for a query having an ideal execution plan with INDEX SEEK.

•    Only select the columns which are actually required in the select query.For Example: Write the query as

[code language="sql"]

SELECT clientid, clientname FROM cli_client;

[/code]

Instead of

[code language="sql"]

SELECT * FROM cli_client;

[/code]

• Wherever UNION is used, evaluate if you really need distinct records out of the query. Wherever possible, use UNION ALL.

• Avoid calling table or scalar valued functions in the Where clause. This causes a huge performance impact specially if the resultset returned is large. Check if this can be accomplished by using an EXISTS clause with the function logic within a sub-query.