Solución:
Puede agregar la sugerencia de consulta OPTION (RECOMPILE).
Esto le dice efectivamente a SQL Server, “Dame un plan para ejecutar esta consulta una vez, pero no lo guardes en la caché del plan”.
Eche un vistazo al artículo de Kendra Little sobre RECOMPILE Sugerencias y almacenamiento en caché del plan de ejecución. Cubre todos los usos y efectos potenciales.
RECOMPILAR Hints and Execution Plan Caching
Ver también:
Opciones de rastreo, incrustación y RECOMPILACIÓN de parámetros
Con respecto a deshabilitar la función de índice que falta, la única forma admitida de hacerlo es iniciar SQL Server desde la línea de comando con el -x cambiar. Eso no es lo que quiere, pero establece un límite en sus opciones.
Limpieza interna:
Estoy usando SQL Server Microsoft SQL Server 2012 – 11.0.5343.0 (X64)
Solución:
Puede utilizar WITH RECOMPILE en su procedimiento almacenado.
Comience creando una base de datos de prueba, una tabla, algunos índices y, finalmente, datos de prueba.
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
Agregue un procedimiento almacenado:
USE [test];
GO
CREATE PROCEDURE dbo.TestProc @i INT
--WITH RECOMPILE
AS
SELECT DISTINCT
c1
FROM
dbo.tbl
WHERE
c1 = @i;
GO
Ahora, libera tu caché (¡no hagas esto en producción!) Y compruébalo.
-- 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
Ejecute su procedimiento una vez y verifique el caché.
-- 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
Su plan está ahí y se ha utilizado una vez:
Ahora, modifique su procedimiento CON 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
Libere su caché una vez más, ejecute su procedimiento y luego verifique si hay una entrada en el caché.
-- 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>
¡No está aquí! Esto debería ayudarte.
Ahora, en cuanto a los índices, podemos probar este experimento:
USE [test];
GO
SELECT
c2
FROM
dbo.tbl
WHERE
c2 > '09-15-2015';
GO
Aquí está el plan de ejecución para esta consulta:
Puedo ver el requisito de índice ahora.
SELECT
index_handle
, database_id
, object_id
, equality_columns
, inequality_columns
, included_columns
, statement
FROM
sys.dm_db_missing_index_details;
GO
¿Hay alguna manera de ejecutar una consulta única en una base de datos de SQL Server de producción y decirle que no haga juicios futuros basados en esa consulta? Por ejemplo, almacenar planes de ejecución, recomendar índices, etc.
Antes de responder a su pregunta, debe comprender los antecedentes.
Caché del plan:
Antes de que un lote de consultas comience a ejecutarse en SQL Server, el lote se compila en un plan. Luego, el plan se ejecuta por sus efectos o para producir resultados.
La compilación de planes de ejecución es una operación relativamente costosa, por lo que se intenta evitar estos costos almacenando en caché los planes compilados en una región de memoria de SQL Server llamada Plan Cache. Cuando es necesario ejecutar otro lote de consultas, SQL Server busca en la caché del plan posibles oportunidades de reutilización del plan. Si se logra la reutilización del plan, se evitan los costos de compilación.
SQL Server decide la asignación adecuada de memoria al Plan Cache desde el Buffer Pool.
Dado que su pregunta está dirigida a Consultas ad-hoc, vamos a entender cómo afectan las consultas ad-hoc a la caché del plan.
Una consulta ad-hoc es un lote de TSQL que no contiene parámetros y no está preparado previamente.
SQL Server requiere una coincidencia de texto exacta para dos consultas ad-hoc antes de que pueda ocurrir la reutilización. La coincidencia de texto distingue tanto entre mayúsculas y minúsculas como espacios, incluso en servidores que no distinguen entre mayúsculas y minúsculas.
-- 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"
Las respuestas anteriores sugieren utilizar una sugerencia de consulta OPTION (RECOMPILE) o -x cambiar.
Ya que conoces un especifico ad-hoc consulta que está ejecutando y no desea almacenar en caché el plan (más adelante veremos que faltan detalles del DMV del índice), puede usar 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***
Esto no impide que la consulta ad hoc aparezca en la lista de índices sugeridos. Ejecuté una consulta que se beneficiaría de un índice y apareció en sys.dm_db_missing_index_details. seleccione x de y donde z> opción ‘8/1/15’ (recompilar)
Hay de ninguna manera para borrar / restablecer las estadísticas de dm_db_missing_index_columns, dm_db_missing_index_groups, dm_db_missing_index_group_stats y dm_db_missing_index_details al igual que DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR). Referirse a Borrar sugerencias de “índice faltante” para una sola tabla
O quizás no debería estar tan preocupado.
No debe preocuparse si la consulta es solo “UNA SOLA”. Si cree que necesitará ejecutar más consultas “AD HOC”, busque activar el optimize for ad hoc workloads sp_configure opción.
Referencia: Planificar el almacenamiento en caché y la recompilación en SQL Server 2012