SQL SERVER – Best Practices for Optimizing Any Query

 

January 20, 2009 by pinaldave

This subject is very deep subject but today we will see it very quickly and most important points. May be following up on few of the points of this point will help users to right away improve the performance of query. In this article I am not focusing on in depth analysis of database but simple tricks which DBA can apply to gain immediate performance gain.

  • Table should have primary key
  • Table should have minimum of one clustered index
  • Table should have appropriate amount of non-clustered index
  • Non-clustered index should be created on columns of table based on query which is running
  • Following priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause
  • Try to replace views with original source table
  • Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
  • Remove any adhoc queries and use Stored Procedure instead
  • Check if there is atleast 30% HHD is empty – it improves the performance a bit
  • If possible move the logic of UDF to SP as well
  • Remove * from SELECT and use columns which are only necessary in code
  • Remove any unnecessary joins from table
  • If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)

There are few hardware upgrades can be considered as well like separating index on different disk drive or moving tempdb to another drive. However, I am not suggesting them here as they are not quick way to improve the performance of query.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Tony comments on the blog item above

I’d to comment on some of the previous statements:

– Yes, dynamic queries are cached but they may not be reused and would be taking up space in the plan cache. If you’re going to use dynamic SQL, use parameterized queries which will promote plan reuse.

– Views perform about as well as accessing the tables directly as long as the query in the view has been optimized and the tables indexed appropriately. However, using a view may require slightly more overhead than using the tables directly because the view must be rendered before the results are returned, similar to a derived table.

– Be careful when using indexes views as these views are materialized in the database. This means that every time a base table in the index view is updated, the materialied table must also be updated (along with it’s indexes). Indexed views are great for read-only reporting or OLAP databases, but may cause performance issues with OLTP databases.

– Using triggers requires more overhead in the database than just incorporating the logic in a stored procedure. That does not mean you should not use triggers, just use them wisely and only were necessary.

MORE COMMENTS

There are a couple of things you can look at to optimize your query performance.

  1. Ensure that you just have the minimum of data. Make sure you select only the columns you need. Reduce field sizes to a minimum.

  2. Consider de-normalising your database to reduce joins

  3. Avoid loops (i.e. fetch cursors), stick to set operations.

  4. Implement the query as a stored procedure as this is pre-compiled and will execute faster.

  5. Make sure that you have the correct indexes set up. If your database is used mostly for searching then consider more indexes.

  6. Use the execution plan to see how the processing is done. What you want to avoid is a table scan as this is costly.

  7. Make sure that the Auto Statistics is set to on. SQL needs this to help decide the optimal execution. See Mike Gunderloy’s great post for more info. Basics of Statistics in SQL Server 2005

  8. Make sure your indexes are not fragmented. Reducing SQL Server Index Fragmentation

  9. Make sure your tables are not fragmented. How to Detect Table Fragmentation in SQL Server 2000 and 2005
Advertisements