Playing with OPTION KEEPFIXED PLAN hint in SQL Server

My previous article was on Compilation & Recompilation of Stored Procedure. After reading that article, two of my reader sent me an email about more clarification on some terminology and that is the base of writing this article.

Actually SQL Server Query Optimizer is really one of the smart tools which used to find or generate best execution plan for query or stored procedure and make the task easy for DBA. Basically it uses Cost Based Optimizer (CBO) to do this task, even sometime feels to have specific Execution Plan for query so Microsoft have provided many different hints at different level.

Query Optimizer (QO) used to save execution plan when it compiles Stored Procedure first time and use the same execution plan when the same stored procedure is being used again so that you can get rid of overhead of generating execution plan again and again. 

When index statistics changes heavily, QO used to recompile the statement to get best plan for your stored procedure but if you are sure that the existing Plan would works fine than one should go for OPTION KEEPFIXED PLAN for that statement so that you can save your resources and boost up performance.

I will demonstrate this with few queries run in SQL Server Management Studio (SSMS) and capture the results in Profiler. 

I will select following Events in Profiler while creating new Trace for my server.

Stored Procedure

• SP:Completed
• SP:Recompile
• SP:smtpCompleted
• SP:smtpStarting

 

Now execute following TSQL in your SSMS.

--create one database which you can delete after running this example

create database SQLHub

GO

USE SQLHub

GO

--if orders table is already there. you can delete it than create new one with name "Orders"

IF OBJECT_ID('orders', 'U') IS NOT NULL BEGIN

      DROP TABLE orders

END

GO

--creating table

CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)

GO

--creating nonclustered index

CREATE NONCLUSTERED INDEX IDX_ORD ON Orders(amount)

GO

--inserting only 1 record in our table

INSERT INTO orders VALUES(GETDATE(),1001,111)

GO

--creating SP which will SELECT all records from Orders table where Amount is 1001

CREATE PROC spGet_ord

AS

SELECT * FROM orders WHERE Amount=1001

GO

--execute SP

exec spGet_ord

GO

If you will see in profiler than you would get following event captured

Now, make change in Index statistics by inserting so many records in table so that we can test the effect in profiler after executing SP again.

--inserting 50000 fack rows into table

INSERT INTO orders (OrderDate, Amount, Refno)

SELECT TOP 50000

      DATEADD(minute, ABS(a.object_id % 50000 ), CAST('2010-02-01' AS DATETIME)),

      ABS(a.object_id % 10),

      CAST(ABS(a.object_id) AS VARCHAR)

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

GO

--execute SP again

exec spGet_ord

GO

Now, if you see the profiler, you will see “SP:Recompile” event as your statistics are updated so it need recompilation of statement. If you again few more thousands record in same table and execute the same SP again, you will see recompilation again.

Now, after droping table and SP, we will make one small change in Stored Procedure and will use “KEEPFIXED PLAN” hint so that we can avoid recompilation.

drop proc spGet_ord

GO

drop table orders

go

--creating table

CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)

GO

CREATE NONCLUSTERED INDEX IDX_ORD ON Orders(amount)

GO

INSERT INTO orders VALUES(GETDATE(),1001,111)

GO

CREATE PROC spGet_ord

AS

SELECT * FROM orders WHERE Amount=1001

--adding below statement other than that, same code as previous SP

OPTION (KEEPFIXED PLAN);

GO

exec spGet_ord

GO

After recreating table along with fresh records, we are going to see what happened while executing SP in above statement.

Since our table now has only 1 record so it is time to change statistics with bulk insert as follows again.

--inserting 100000 fack rows into table (BTW, thank to Jacob Sebastian, copy in INSERT script from one of his demo to generate big table)

INSERT INTO orders (OrderDate, Amount, Refno)

SELECT TOP 50000

      DATEADD(minute, ABS(a.object_id % 50000 ), CAST('2010-02-01' AS DATETIME)),

      ABS(a.object_id % 10),

      CAST(ABS(a.object_id) AS VARCHAR)

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

GO

exec spGet_ord

GO

--if you wish, you can uncomment below code and delete SQLHub database

--use master

--go

--drop database sqlhub

Here is the screen capture of Profiler which doesn’t show Recompilation.

 

You can even add few more thousands of rows and execute SP again, you won’t get recompilation. 

Reference: Ritesh Shah
 

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”