SQL Server Query Performance

This page houses tips and tricks for improving query performance in SQL Server.

POSTED BY: Adam Polon

After running into some very quirky results from seemingly simple queries, I have found the following suggestions to make an enormous difference in query performance. 

Of course, you should always start by making sure that your query is optimized using standard practices, such as the following:

• Make sure fields that are searched over frequently have indexes assigned.  This only makes sense if the field will return less than 10% of the table, so don't index status fields if there are only 5 of them, etc.
  
• Make sure the data type for a field is appropriate.  For instance, if your field contains numeric data, avoid using a varchar field and CASTING AS INTEGER.
  

In some cases, switching around the order of fields in WHERE clauses makes a difference, but I usually do not find this to solve my issues.

Here are some things to try next.

---------------

Debug sluggish queries in SQL Server Management Studio by toggling the “Include Actual Execution Plan” button.  This will allow you to see the execution plan – including any table scans, index seeks, etc, to identify slow points in the query.

---------------

Review the nightly database maintenance plan to ensure that indexes are rebuilt on a nightly basis. 

-----------------

I believe that the rebuilding of indexes on a nightly basis makes the following item unnecessary, but it is listed here just in case in helps where other avenues do not. 

Defragment indexes on large tables. To do this, use the following command:

DBCC INDEXDEFRAG (database_Name, 'table_name', index_name)

Calling this command on key data tables made a huge difference in performance for a few queries that were performing slower than expected.  Queries literally went from 45 seconds to 2 seconds.

It would be a good idea to schedule these commands to run on a nightly or weekly basis.

----------------

Run Update Statistics on key data tables.  In conjunction with DBCC INDEXDEFRAG, I have seen huge performance enhancements as a result of running this.  To do this, use the following command:

update statistics table_name;

It would be a good idea to schedule these commands to run on a nightly or weekly basis.

---------------

Please let me know if you wish to add any additional tips and tricks to this page.