Recompiling Queries: Even if you have optimized queries by looking at the Execution Plan and the code, you may still have a problem that the vast majority of developers do not even consider. This problem is the performance cost taken every time a query has to create a new plan or recompile. Recompiling queries are usually the easiest to fix and can make a dramatic improvement on the performance of many applications. You should keep in mind as you look at the list of recompiling stored procedures that there are legitimate reasons that a stored procedure recompiles. It is just the ones that seem to recompile each time they are executed that you should worry about in the short term. You can look at the others later and determine if they are recompiling for legitimate reasons or not.
- Look for a mixture of DML and DDL statements. Do not mix the creation of objects and use of those objects in your code. Move all object creation to the top of your query and you should be able to avoid this problem.
- Look for ANSI SET commands. Changing the status of SET ANSI_DEFAULTS, SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS, and SET CONCAT_NULL_YIELDS_NULL will cause the query to recompile each time it is executed. Make sure you really need to change these settings before you do so inside of a query.
- If you must have a cursor in your code, make sure you did not reference temporary objects in the cursor. Referencing a temporary object in a cursor will cause the query to recompile every time it is executed.
Note: You can review my article Optimizing Stored Procedures to Avoid Recompiles for a more in depth discussion of avoiding stored procedure recompiles.
Okay, you looked at the Execution Plan, analyzed the code, and look for recompiles; now what? Just because everything looks fine, doesn’t mean that the query will run satisfactorily in every single scenario.
Come up with all the different test cases you can think of for the query. How does it run against different sizes of data sets? How does it run with different sets of parameters? What happens if you run the query multiple times using multiple Query Analyzer connections? Whatever you can think of in your particular case, needs to be traced and analyzed. Don’t just run the query once and say “I tuned this query and it is ready.” Do the hardest thing in the world and convince your clients and managers that you need time to thoroughly test a new query or recently fixed query. This task is more important than just throwing a fix into place and shoving it back into production to meet some deadline.
Like I said, this is just a basic plan. Incorporate any new steps you come up with. Expand the steps I have included with your own ideals and tips you have picked up over the years, and with solutions you have come up to solve the common problems you find in your database. Whatever you decide to do to optimize your queries, write it down so that you have something “formal” that you can give others in your group or simply to use to provide the answers to the interview question that took me by surprise should you ever have a need for it.
Copyright 2003 by Randy Dyess, All Rights Reserved
]]>