execution-planplan-cachesql serversql-server-2012
How can I keep a query execution plan in SQL Server's cache and use it several times?
When I execute a query for the first time it takes 7 seconds. After that it takes only 1 second. This query is running once on daily basis.
Is there any way I can keep the execution plan and use it every day?
The answer is also available as a standalone blog article.
In order to find it out, I did some tests. The goal is to have the same parametrized query executed either directly from C# or by calling a stored procedure and to compare the runtime performance.
I started to create a stored procedure which does a sample query using Adventure Works database:
create procedure Demo
@minPrice int
as
begin
set nocount on;
select top 1 [p].[Name], [p].[ProductNumber], [ph].[ListPrice]
from [Production].[Product] p
inner join [Production].[ProductListPriceHistory] ph
on [p].[ProductID] = ph.[ProductID]
and ph.[StartDate] =
(
select top 1 [ph2].[StartDate]
from [Production].[ProductListPriceHistory] ph2
where [ph2].[ProductID] = [p].[ProductID]
order by [ph2].[StartDate] desc
)
where [p].[ListPrice] > @minPrice
end
Then, I use the following piece of code to compare the performances:
long RunQuery(SqlConnection connection, int minPrice)
{
const string Query = @"
select top 1 [p].[Name], [p].[ProductNumber], [ph].[ListPrice]
from [Production].[Product] p
inner join [Production].[ProductListPriceHistory] ph
on [p].[ProductID] = ph.[ProductID]
and ph.[StartDate] =
(
select top 1 [ph2].[StartDate]
from [Production].[ProductListPriceHistory] ph2
where [ph2].[ProductID] = [p].[ProductID]
order by [ph2].[StartDate] desc
)
where [p].[ListPrice] > @minPrice
option (recompile)";
using (var command = new SqlCommand(Query, connection))
{
command.Parameters.AddWithValue("@minPrice", minPrice);
var stopwatch = Stopwatch.StartNew();
command.ExecuteNonQuery();
stopwatch.Stop();
return stopwatch.ElapsedMilliseconds;
}
}
long RunStoredProcedure(SqlConnection connection, int minPrice)
{
using (var command = new SqlCommand("exec Demo @minPrice with recompile", connection))
{
command.Parameters.AddWithValue("@minPrice", minPrice);
var stopwatch = Stopwatch.StartNew();
command.ExecuteNonQuery();
stopwatch.Stop();
return stopwatch.ElapsedMilliseconds;
}
}
ICollection<long> Execute(Func<SqlConnection, int, long> action)
{
using (var connection = new SqlConnection("Server=.;Database=AdventureWorks2014;Trusted_Connection=True;"))
{
connection.Open();
using (var command = new SqlCommand("DBCC FreeProcCache; DBCC DropCleanbuffers;", connection))
{
command.ExecuteNonQuery();
}
return Enumerable.Range(0, 100).Select(i => action(connection, i)).ToList();
}
}
void Main()
{
var queries = Execute(RunQuery);
var storedProcedures = Execute(RunStoredProcedure);
Console.WriteLine("Stored procedures: {0} ms. Details: {1}.", storedProcedures.Sum(), string.Join(", ", storedProcedures));
Console.WriteLine("Queries: {0} ms. Details: {1}.", queries.Sum(), string.Join(", ", queries));
}
Notice option (recompile) and with recompile. This will force SQL Server to discard previously cached execution plans.
Each query is run une hundred times with a different parameter every time. The time spent by the server is measured at client side.
By running DBCC FreeProcCache; DBCC DropCleanbuffers; before gathering metrics, I make sure that all previously cached execution plans are removed.
Running this code gives the following output:
Stored procedures: 786 ms. Details: 12, 7, 7, 9, 7, 7, 9, 8, 8, 6, 8, 9, 8, 8, 14, 8, 7, 8, 7, 10, 10, 7, 9, 6, 9, 8, 8, 7, 7, 10, 8, 7, 7, 6, 7, 8, 8, 7, 7, 7, 14, 8, 8, 8, 7, 9, 8, 8, 7, 6, 6, 12, 7, 7, 8, 7, 8, 7, 8, 6, 7, 7, 7, 12, 8, 6, 6, 7, 8, 7, 8, 8, 7, 11, 8, 7, 8, 8, 7, 9, 8, 9, 10, 8, 7, 7, 8, 8, 7, 9, 7, 6, 9, 7, 6, 9, 8, 6, 6, 6.
Queries: 799 ms. Details: 21, 8, 8, 7, 6, 6, 11, 7, 6, 6, 9, 8, 8, 7, 9, 8, 7, 7, 7, 7, 7, 7, 10, 8, 8, 7, 8, 7, 6, 11, 19, 10, 8, 7, 8, 7, 7, 7, 6, 9, 7, 9, 7, 7, 8, 7, 12, 9, 7, 7, 7, 8, 7, 7, 8, 7, 7, 7, 9, 8, 7, 7, 7, 6, 7, 7, 16, 7, 7, 7, 8, 8, 9, 8, 7, 9, 8, 7, 8, 7, 7, 6, 7, 7, 7, 7, 12, 7, 9, 9, 7, 7, 7, 7, 9, 8, 7, 8, 11, 8.
Let's run it again:
Stored procedures: 763 ms. Details: 11, 8, 10, 8, 8, 14, 10, 6, 7, 7, 6, 7, 7, 9, 6, 6, 6, 8, 6, 6, 7, 6, 8, 7, 16, 8, 7, 8, 9, 7, 7, 8, 7, 7, 11, 10, 7, 6, 7, 8, 7, 7, 7, 7, 7, 7, 10, 9, 9, 7, 6, 7, 6, 7, 7, 6, 6, 6, 6, 6, 10, 9, 10, 7, 6, 6, 6, 6, 6, 8, 7, 6, 6, 7, 8, 9, 7, 8, 7, 10, 7, 7, 7, 6, 7, 6, 7, 11, 13, 8, 7, 10, 9, 8, 8, 7, 8, 7, 7, 7.
Queries: 752 ms. Details: 25, 10, 8, 8, 12, 8, 7, 9, 9, 8, 6, 7, 7, 6, 8, 6, 7, 7, 8, 9, 7, 7, 7, 7, 6, 10, 8, 7, 7, 7, 7, 7, 7, 7, 8, 9, 7, 6, 6, 6, 7, 13, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 6, 10, 7, 7, 8, 9, 8, 7, 6, 6, 7, 7, 9, 7, 8, 6, 9, 7, 7, 8, 7, 6, 6, 7, 7, 7, 7, 6, 7, 7, 8, 7, 7, 6, 7, 9, 8, 7, 7, 7, 7, 6, 7, 6, 6, 9, 7, 7.
It seems that the performance is very close between stored procedures and direct queries. Running the code a dozen times, I notice that stored procedures seem to be slightly fast, but the gap is very narrow. Possibly passing around the whole query creates this additional cost, which may increase if SQL Server is hosted on a dedicated machine with a slow LAN between it and the application server.
Let's now turn execution plan caching on and see what happens. To do this, I remove option (recompile) and with recompile from the code. Here's the new output:
Stored procedures: 26 ms. Details: 23, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.
Queries: 15 ms. Details: 14, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.
It becomes clear that caching has exactly the same effect for both direct queries and stored procedures. In both cases, it reduces the time to nearly zero milliseconds, and the most expensive query is the first one—the one which runs after the removal of cached execution plans.
Running the same code again shows a similar pattern. Sometimes, queries are faster, and sometimes stored procedures are. But every time, the first query is the most expensive one, and all others are close to zero milliseconds.
If the SQL connection is opened for every query, such as in this slightly modified code:
long RunQuery(string connectionString, int minPrice)
{
const string Query = @"
select top 1 [p].[Name], [p].[ProductNumber], [ph].[ListPrice]
from [Production].[Product] p
inner join [Production].[ProductListPriceHistory] ph
on [p].[ProductID] = ph.[ProductID]
and ph.[StartDate] =
(
select top 1 [ph2].[StartDate]
from [Production].[ProductListPriceHistory] ph2
where [ph2].[ProductID] = [p].[ProductID]
order by [ph2].[StartDate] desc
)
where [p].[ListPrice] > @minPrice
option (recompile)";
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var command = new SqlCommand(Query, connection))
{
command.Parameters.AddWithValue("@minPrice", minPrice);
var stopwatch = Stopwatch.StartNew();
command.ExecuteNonQuery();
stopwatch.Stop();
return stopwatch.ElapsedMilliseconds;
}
}
}
long RunStoredProcedure(string connectionString, int minPrice)
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var command = new SqlCommand("exec Demo @minPrice with recompile", connection))
{
command.Parameters.AddWithValue("@minPrice", minPrice);
var stopwatch = Stopwatch.StartNew();
command.ExecuteNonQuery();
stopwatch.Stop();
return stopwatch.ElapsedMilliseconds;
}
}
}
ICollection<long> Execute(Func<string, int, long> action)
{
var connectionString = "Server=.;Database=AdventureWorks2014;Trusted_Connection=True;";
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var command = new SqlCommand("DBCC FreeProcCache; DBCC DropCleanbuffers;", connection))
{
command.ExecuteNonQuery();
}
}
return Enumerable.Range(0, 100).Select(i => action(connectionString, i)).ToList();
}
void Main()
{
var queries = Execute(RunQuery);
var storedProcedures = Execute(RunStoredProcedure);
Console.WriteLine("Stored procedures: {0} ms. Details: {1}.", storedProcedures.Sum(), string.Join(", ", storedProcedures));
Console.WriteLine("Queries: {0} ms. Details: {1}.", queries.Sum(), string.Join(", ", queries));
}
the observed metrics are very similar:
Stored procedures: 748 ms. Details: 11, 8, 6, 6, 8, 9, 9, 8, 8, 7, 6, 8, 7, 9, 6, 6, 6, 6, 6, 6, 7, 7, 6, 9, 6, 6, 7, 6, 6, 7, 8, 6, 7, 7, 7, 13, 7, 7, 8, 7, 8, 8, 7, 7, 7, 7, 6, 7, 8, 8, 8, 9, 7, 6, 8, 7, 6, 7, 6, 6, 6, 6, 8, 12, 7, 9, 9, 6, 7, 7, 7, 8, 10, 12, 8, 7, 6, 9, 8, 7, 6, 6, 7, 8, 6, 6, 12, 7, 8, 10, 10, 7, 8, 7, 8, 10, 8, 7, 8, 7.
Queries: 761 ms. Details: 31, 9, 7, 6, 6, 8, 7, 7, 7, 7, 7, 6, 8, 7, 6, 6, 7, 10, 8, 10, 9, 7, 7, 7, 7, 10, 13, 7, 10, 7, 6, 6, 6, 8, 7, 7, 7, 7, 7, 7, 7, 9, 7, 7, 7, 6, 6, 6, 9, 7, 7, 7, 7, 7, 6, 8, 10, 7, 7, 7, 7, 7, 7, 7, 8, 6, 10, 10, 7, 8, 8, 7, 7, 7, 7, 7, 6, 6, 7, 6, 8, 7, 7, 7, 7, 7, 7, 7, 8, 7, 8, 7, 9, 7, 6, 6, 12, 10, 7, 6.
with option (recompile) and with recompile and:
Stored procedures: 15 ms. Details: 14, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.
Queries: 32 ms. Details: 26, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0.
without.
Let's see what happens under the hood. The following query shows cached execution plans:
select usecounts, size_in_bytes, cacheobjtype, objtype, text
from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
where cacheobjtype = 'Compiled Plan'
order by usecounts desc
When running this query after executing the stored procedures one hundred times, the result of the query looks like this:
usecounts size_in_bytes cacheobjtype objtype text
----------- ------------- -------------------------------------------------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
100 90112 Compiled Plan Proc create procedure Demo
@minPrice int
as
begin
set nocount on;
select top 1 [p].[Name], [p].[ProductNumber], [ph].[ListPrice]
from [Production].[Product] p
inner join [Production].[ProductListPriceHistory] ph
on [p].[ProductID] = ph.[Product
100 16384 Compiled Plan Prepared (@minPrice int)exec Demo @minPrice --with recompile
1 49152 Compiled Plan Adhoc --DBCC FreeProcCache
--DBCC DropCleanbuffers
select usecounts, size_in_bytes, cacheobjtype, objtype, text
from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
where cacheobjtype = 'Compiled Plan'
order by usecounts desc
(3 row(s) affected)
When running the query directly one hundred times, the result is:
usecounts size_in_bytes cacheobjtype objtype text
----------- ------------- -------------------------------------------------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
100 73728 Compiled Plan Prepared (@minPrice int)
select top 1 [p].[Name], [p].[ProductNumber], [ph].[ListPrice]
from [Production].[Product] p
inner join [Production].[ProductListPriceHistory] ph
on [p].[ProductID] = ph.[ProductID]
and ph.[StartDate] =
(
select top 1 [ph2].[
1 49152 Compiled Plan Adhoc --DBCC FreeProcCache
--DBCC DropCleanbuffers
select usecounts, size_in_bytes, cacheobjtype, objtype, text
from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
where cacheobjtype = 'Compiled Plan'
order by usecounts desc
(2 row(s) affected)
The execution plan is cached for stored procedures and direct queries.
The performance between stored procedures and direct queries is very similar when the SQL Server and the application are hosted on the same machine. When SQL Server is hosted on a dedicated server accessed through LAN, using stored procedures may result in better performance.
The question does not provide execution plans or a full reproduction script, but based on the information given, you should use a FAST 1 hint. If you can, you should also consider converting the scalar function to the inline table-valued type.
Download the Microsoft sample database here.
CREATE TYPE dbo.PrimaryKeys AS TABLE
(
PK integer PRIMARY KEY
);
You could try various combinations of INNER LOOP JOIN (with the written join order reversed, and FORCE ORDER added to suppress the warning), FORCESEEK(index_name(columns)) (without schemabinding) and even QUERYTRACEON(8690) to prevent a performance spool; but in my experience, FAST 1 is the most reliable way to get the right plan, and simpler as well.
CREATE FUNCTION dbo.GetXML
(
@Lines dbo.PrimaryKeys READONLY
)
RETURNS xml
WITH SCHEMABINDING -- If possible
AS
BEGIN
DECLARE @x xml;
WITH XMLNAMESPACES (N'important namespace' AS pro)
SELECT @x =
(
SELECT
SOH.SalesOrderID,
SOH.OrderDate,
SOH.DueDate,
SOH.ShipDate,
SOH.[Status],
SOH.PurchaseOrderNumber,
SOH.AccountNumber,
SOH.CustomerID,
SOH.TotalDue
FROM @Lines AS L
JOIN Sales.SalesOrderHeader AS SOH
ON SOH.SalesOrderID = L.PK
ORDER BY
SOH.SalesOrderID
FOR XML PATH(N'pro:Import'), TYPE
)
OPTION (FAST 1);
RETURN @x;
END;
In addition to all the usual reasons to avoid scalar functions, they do make plan analysis harder. First, you need to capture actual plans using Profiler or Extended Events - they do not appear in query tools like SSMS. Second, not all hints work as you would expect, for example RECOMPILE due to scoping and the separate plans.
DECLARE @Lines dbo.PrimaryKeys;
INSERT @Lines (PK)
SELECT TOP (300) SOH.SalesOrderID
FROM Sales.SalesOrderHeader AS SOH;
SELECT dbo.GetXML(@Lines);
The code above reliably produces the desired plan for me regardless of the number of rows in the table variable.
Without the FAST 1 hint in the function, the optimizer chooses a merge join and scan of both tables. This plan is cached and reused for a later function execution where TOP (300)is changed to TOP (1), reproducing the core of the issue.
Unless there are absolutely compelling reasons to stick with the scalar function, you should probably rewrite it to inline table-valued form as follows:
CREATE FUNCTION dbo.GetXMLTable
(
@Lines dbo.PrimaryKeys READONLY
)
RETURNS TABLE
AS
RETURN
WITH XMLNAMESPACES (N'important namespace' AS pro)
SELECT
X.xml_result
FROM
(
SELECT
SOH.SalesOrderID,
SOH.OrderDate,
SOH.DueDate,
SOH.ShipDate,
SOH.[Status],
SOH.PurchaseOrderNumber,
SOH.AccountNumber,
SOH.CustomerID,
SOH.TotalDue
FROM @Lines AS L
CROSS APPLY
(
SELECT TOP (1) SOH.*
FROM Sales.SalesOrderHeader AS SOH
WITH (FORCESEEK(PK_SalesOrderHeader_SalesOrderID(SalesOrderID)))
WHERE
SOH.SalesOrderID = L.PK
ORDER BY
SOH.SalesOrderID
) AS SOH
ORDER BY
SOH.SalesOrderID
FOR XML PATH(N'pro:Import'), TYPE
) AS X (xml_result);
The TOP (1) in the cross apply prevents the optimizer choosing anything other than nested loops join. The FORCESEEK hint is not required; I have included it to illustrate how it could be used if necessary.
DECLARE @Lines dbo.PrimaryKeys;
INSERT @Lines (PK)
SELECT TOP (300) SOH.SalesOrderID
FROM Sales.SalesOrderHeader AS SOH;
SELECT GXT.xml_result
FROM dbo.GetXMLTable(@Lines) AS GXT;
The actual execution plan (without using Profiler/XE) is:
Best Answer
AFAIK, you cannot force a plan to stay in cache. However, a query can be thrown out of the cache for several reasons. Read a blog about execution plans. It states some reasons why execution plans get invalidated:
Another pitfall could be that the query changes. This can happen if the where clause changes (e.g. you filter by date) and you don't use bind
Thinking about, you haven't stated that you checked whether the execution plan is still in cache or not. You should query the cache to figure that out. However, since you say your query runs only once a day, the plan might just be expired. The blog actually mentions the formula on how sql server determines when to expire a plan:
In general, 7 seconds is not to bad if you have a big report. It takes longer to get a coffee. Does it create any other issue than 6 seconds of wasted productivity for an end user?