Sql-server – Force to cache SQL query exection plan

execution-planplan-cachesql-server-2012t-sql

I have a problem with long execution of select query first time in the morning. I guess it is because this query is not cached and SQL Server is caching it. Other few possible causes were checked and set aside.

I would like to make an stored procedure that will execute each 8 a. m. or each few hours, for example, and cache my sql query.

I can't figure out does this method to fight with long performance in first time is fine? How else I can force to cache my query?

Best Answer

You could force the SQL Server instance to cache a query plan for the statement or procedure that you think is missing from the query plan cache. Or you could verify that it is indeed a query plan cache issue or not.

Caching a Query Plan

This is accomplished with the stored procedure sp_create_plan_guide (Transact-SQL)

sp_create_plan_guide takes the following parameters:

sp_create_plan_guide [ @name = ] N'plan_guide_name'  
    , [ @stmt = ] N'statement_text'  
    , [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'  
    , [ @module_or_batch = ]  
      {   
                    N'[ schema_name. ] object_name'  
        | N'batch_text'  
        | NULL  
      }  
    , [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }   
    , [ @hints = ] { N'OPTION ( query_hint [ ,...n ] )'   
                 | N'XML_showplan'  
                 | NULL }

Please read the online manual on how to use the various parameters.

Example on how you could cache the statement of a stored procedure:

exec sp_create_plan_guide
    @name = N'Forced_Plan_Guide_For_MyProc',
    @stmt = N'SELECT pi.product_name FROM product.items as pi WHERE pi.product_id = @product_id',
    @type = N'OBJECT',
    @batch_or_module = N'MyProc', --name of your procedure 
    @params = NULL,
    @hints = N'OPTION (OPTIMIZE FOR (@product_id > 1000)';

This would create a query plan for the procedure named MyProc and optimize the query plan for all product.items which have a pi.product_id greater than 1000.

This is just an example on how to create a query plan for a procedure. It is slightly different for a stand-alone SQL Statement.

You can then edit this into a scheduled job and let it run once in the morning or whatever your preferences are.

Alternatives

The above solution will not result in the data being stored in SQL Server's data cache. You might be encountering an issue with data cache and not actually with the plan cache.

Check out Brent Ozar's sp_BlitzCache stored procedure to give your SQL Server's cache a quick check.

Disclosure: Not affiliated with Brenz Ozar's company

Related Question