7

Is there a way to run a one-off query against a production SQL Server database and tell it not to make any future judgments based on that query. E.g. Store execution plans, recommend indexes, etc.

Or perhaps I shouldn't be so concerned.

2

Is there a way to run a one-off query against a production SQL Server database and tell it not to make any future judgments based on that query. E.g. Store execution plans, recommend indexes, etc.

Before I answer your question, you need to understand the background.

Plan Cache:


Before a query batch begins execution on SQL Server, the batch is compiled into a plan. The plan is then executed for its effects or to produce results.

The compilation of execution plans is a relatively expensive operation so an attempt is made to avoid these costs by caching the compiled plans in a SQL Server memory region called the Plan Cache. When another query batch needs to be executed, SQL Server searches the Plan Cache for possible plan reuse opportunities. If plan reuse is achieved, the compilation costs are avoided.

SQL Server decides the appropriate allocation of memory to the Plan Cache from the Buffer Pool.

enter image description here

Since your question is geared towards Ad-hoc queries, lets get an understanding of How Ad-hoc queries affect plan cache ?

An Ad-hoc query is a TSQL Batch that does not contain parameters and is not pre-prepared.

SQL Server requires an exact text match for two ad-hoc queries before reuse can occur. The text match is both case- and space-sensitive, even on case-insensitive servers.

-- get a count of the number of adhoc query plans use
select count(*) as CountOfAdHocQP from sys.dm_Exec_Cached_plans
where cacheobjtype = 'Compiled Plan'
and objtype = 'Adhoc'

The answers above suggest to use query hint OPTION (RECOMPILE) or -x switch.

Since you know a specific ad-hoc query that you are running and you dont want to cache the plan (we will come to missing index DMV detail later), you can use DBCC FREEPROCCACHE (@plan_handle);

-- for details, refer above ooutwire's code : http://dba.stackexchange.com/a/112571/8783
declare  @plan_handle as varbinary(64)

SELECT  @plan_handle = cp.plan_handle 
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE  N'%FROM
    dbo.tbl
WHERE
    c2%' -- here goes your EXACT query (along with WHITE SPACE, case UPPER or lower, etc) text to search !!
option (recompile)
select @plan_handle as PlanHandle
DBCC FREEPROCCACHE (@plan_handle); -- remove the plan handle !! ***CAUTION***

enter image description here

This doesn't stop the ad hoc query from showing up in the list of suggested indexes. I ran a query that would benefit from an index and it showed up in sys.dm_db_missing_index_details. select x from y where z > '8/1/15' option(recompile)

There is no way to clear/reset statistics for dm_db_missing_index_columns, dm_db_missing_index_groups, dm_db_missing_index_group_stats and dm_db_missing_index_details just like DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR). Refer to Clearing “missing index” suggestions for a single table

Or perhaps I shouldn't be so concerned.

You should not be concerned if the query is just "ONE OFF". If you feel that you will need to run more "AD HOC" queries, then look for turning ON the optimize for ad hoc workloads sp_configure option.

Reference : Plan Caching and Recompilation in SQL Server 2012

7

You can add the query hint OPTION (RECOMPILE).

This effectively tells SQL Server, "Give me a plan to execute this query one time, but don't save it in the plan cache".

Take a peek at Kendra Little's article on RECOMPILE hints and execution plan caching. She covers all the uses and potential effects.

RECOMPILE Hints and Execution Plan Caching

Also see:

Parameter Sniffing, Embedding, and the RECOMPILE Options

Regarding disabling the missing index feature, the only supported way to do that is to start SQL Server from the command line with the -x switch. That is not what you want, but it does set a limit on your options.

4

Housekeeping:

I am using SQL Server Microsoft SQL Server 2012 - 11.0.5343.0 (X64)

Solution:

You can use WITH RECOMPILE in your stored procedure.

Start by creating a test database, table, some indexes, and finally test data.

USE [master];
GO

IF DATABASEPROPERTYEX (N'test', N'Version') > 0
BEGIN
    ALTER DATABASE [test] SET SINGLE_USER
        WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [test];
END
GO

CREATE DATABASE [test];
GO

USE [test];
GO

IF OBJECT_ID('dbo.tbl','U') IS NOT NULL
BEGIN
    DROP TABLE dbo.tbl;
END;
CREATE TABLE dbo.tbl
(
      c1 INT NOT NULL
    , c2 DATETIME NOT NULL
    , c3 CHAR(7000) NOT NULL  
);
GO

-- Insert 20,000 different dates
INSERT INTO dbo.tbl 
    SELECT DISTINCT TOP 1000
        number
      , DATEADD(DAY , number , GETDATE())
      , 'a'  
    FROM
        master..spt_values
    WHERE
        number > 0;
GO 20


-- Add a couple indexes
-- This is a non-unique clustered index
--   so, there will be a 4-byte uniqueifier column
--   added to the row within the page
CREATE CLUSTERED INDEX CIX_tbl_c1
    ON dbo.tbl (c1 ASC);
GO
--CREATE NONCLUSTERED INDEX IX_tbl_c2
--    ON dbo.tbl (c2 ASC);
--GO

Add a stored procedure:

USE [test];
GO

CREATE PROCEDURE dbo.TestProc @i INT
       --WITH RECOMPILE
AS 
       SELECT DISTINCT
           c1
       FROM
           dbo.tbl
       WHERE
           c1 = @i;
GO

Now, free your cache (do not do this in production!) and check it.

-- Free your cache
DBCC FREEPROCCACHE;
GO

-- What is in my cache?
USE [master];
GO

SELECT
    usecounts
  , refcounts
  , cacheobjtype
  , objtype
  , DB_NAME([dbid])
  , [sql]
FROM
    syscacheobjects
ORDER BY
    [dbid]
  , usecounts DESC
  , objtype;
GO

Execute your procedure one time and check the cache.

-- Execute my stored procedure once
USE [test];
GO
EXEC dbo.TestProc @i = 10;
GO

-- Re-check the cache
USE [master];
GO

SELECT
    usecounts
  , refcounts
  , cacheobjtype
  , objtype
  , DB_NAME([dbid])
  , [sql]
FROM
    syscacheobjects
ORDER BY
    [dbid]
  , usecounts DESC
  , objtype;
GO

Your plan is there and has been used once:

enter image description here

Now, alter your procedure WITH RECOMPILE.

-- Now, alter your procedure
USE [test];
GO

ALTER PROCEDURE dbo.TestProc @i INT
       WITH RECOMPILE
AS 
       SELECT DISTINCT
           c1
       FROM
           dbo.tbl
       WHERE
           c1 = @i;
GO

Free your cache one more time, execute your procedure, and then check the cache for an entry.

-- Free your cache
DBCC FREEPROCCACHE;
GO

-- Execute my stored procedure once
USE [test];
GO
EXEC dbo.TestProc @i = 10;
GO

-- Re-check the cache
USE [master];
GO

SELECT
    usecounts
  , refcounts
  , cacheobjtype
  , objtype
  , DB_NAME([dbid])
  , [sql]
FROM
    syscacheobjects
ORDER BY
    [dbid]
  , usecounts DESC
  , objtype;
GO
</pre>

It is not there! This should help you. enter image description here

Now, as for indexes, we can try this experiment:

USE [test];
GO

SELECT
    c2
FROM
    dbo.tbl
WHERE
    c2 > '09-15-2015';
GO

Here is the execution plan for this query:

Execution Plan

I can see the index requirement now.

SELECT
    index_handle
  , database_id
  , object_id
  , equality_columns
  , inequality_columns
  , included_columns
  , statement
FROM
    sys.dm_db_missing_index_details;
GO

Missing Index

  • @ooutwire unfortunately what I wanted was to avoid cluttering up my missing indexes views with any ad hoc queries since many ad hoc queries are unoptimized and will show up in that view. Based on the other posts this doesn't seem possible. – Kevin Kalitowski Aug 31 '15 at 12:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.