Common Mistakes in SQL Server - Part 3

Last week in Common Mistakes in SQL Server - Part2 we discussed the advantages and disadvantages of using Identity columns. In this article I would like to shed some light on the advantages of recompile , and in what cases you should attempt recompilation!

Generally, we create views and stored procedures (“proc” here after) to avoid the re-write  T-SQL  which is called often by an application or on ad-hoc basis. And, there is strong reasoning to do so: both -  views and procs provides am edge over executing T-SQL as they can reuse cached plan stored in the system table syscacheobjects ( you can query it using DMV sys.syscacheobjects). 

We are aware that once we have created a view and/or proc and call it for the first time it gets compiled and executed(and the execution plan is stored in the syscacheobjects system table).  Then, every time we call this view or proc, it uses the same cached execution plan. By doing this, SQL Server Engine is reducing the overhead of recompiling the same set of T-SQL.

What exactly is an Execution Plan?

There are number of connections that can be made to the database server,  from one or  more different locations, and each of them are querying the database server to return some data. The specific data structure is called an execution plan (i.e. select * from Product where ProductSubCategoryId = 4).

What the SQL Server engine does here is - if the T-SQL came from a view or proc, it searches for the cached execution plan for the same T-SQL statement.  The SQL Server Engine will reuse the plan if it  is found ; otherwise, it will compile and execute the statement.  For example  we have to query Product table where SubCategoryId is 4 and 11 and this query is being called frequently( 500 times a day). If you query the syscacheobjects system table, or query DMV sys.syscacheobjects, you will notice that the Usecount value is not incremented.

Now, if you were to, create a view and execute it 5 times, and then query DMV sys.syscachedobjects again,  you will notice that the Usecount value has been incremented. This is because the SQL Server engine has used the cached execution plan.

Why should we recompile the proc or view?

There are certain events which invalidate the cached execution plan,  and it is advisable to recompile*  a proc or view if one of these events occur:

·         Schema changes / alter table

·         Alter index or drop index

·         Calling sp_recompile or using the  ‘with recompile’ or option (recompile)when executing the T-SQL

·         Large insertion or deletion of data in the tables

·         If you call DML and DDL in single set of T-SQL

·         Change the value of SET option

*Note: Recompilation is an expensive operation,  but it will help to avoid use of old/invalid plans.

Let us query the Product table in the AdventureWorks database; note that it uses the clustered Index Scan (PK_Product_ProductId)

Now we will create a non-clustered index on column ProductSubCategoryId in Product table.

Now we will create a procedure that uses the same select statements that we have used earlier (CMPart3_Proc1)

As we have the non-clustered index on the column ProductSubCategoryId, the relational database engine should use the non-clustered index. Let’s check if it does it by executing the query

Let's create a proc here, and execute it. If you notice, the database engine still uses the same execution plan even though we have  a newly created index on the ProductSubCategoryId.

We will free the procedure cache now and recompile the proc so that relational engine can create a new execution plan and use a newly created non-clustered index

You will note that as soon as we execute DBCC Freeproccache & sp_recompile ‘usp_ProdSubCatId’  Relational engine has created a new plan and is now using the correct index (in our case idx_ProdSubCatId).

That is why it is advisable to recompile proc or view as I've described above. You may download the demo script here.