sql-server - SQL Server 计划缓存中的即席查询和准备好的查询有什么区别？

Question

我正在尝试了解 SQL Server 的计划缓存内容。

所以我的问题是:
1.临时计划和准备好的计划有什么区别？
2.尝试优化sql server计划缓存时我应该了解什么？

Answer 1

What is the difference between ad hoc and prepared plans?

即席查询:

select * from t1

准备好的查询:
用占位符代替实际值的查询称为准备语句。

一些示例:

select * from t1 where id=@id

另一个来自维基百科的示例:

command.CommandText = "SELECT * FROM users WHERE USERNAME = @username AND ROOM = @room";

    command.Parameters.AddWithValue("@username", username);
    command.Parameters.AddWithValue("@room", room);

What should I know about it when trying to optimize the sql server plan cache?

有一些关于如何优化计划缓存的白皮书。所以我会尽量减少它......

通常，当针对 SQL 执行查询时，SQL 会编译计划并将其存储在计划缓存中。该计划缓存是从缓冲池中获取的内存，不同版本对 how much amount of memory will be used 有不同的限制。

您知道内存是宝贵的资源，如果出现泄漏，再多的硬件也不够..

假设您只提交一次或两次查询，并且您倾向于多次提交这样的查询。SQL 会将这些查询的计划存储在计划缓存中，该缓存通常会膨胀PlanCache这很糟糕

有不同的 DMVS 可以帮助您挖掘计划缓存..

在计划缓存中查找不同类型对象的查询:

select 
objtype,count(*) as countt,sum(size_in_bytes)*1024.0 as memoryinkb
 from   sys.dm_exec_cached_plans a
 group by objtype

即席、准备好的查询会导致计划缓存膨胀并且仅使用一次:

select q.query_hash, 
    q.number_of_entries, 
    t.text as sample_query, 
    p.query_plan as sample_plan
from (select top 20 query_hash, 
            count(*) as number_of_entries, 
            min(sql_handle) as sample_sql_handle, 
            min(plan_handle) as sample_plan_handle
        from sys.dm_exec_query_stats
        group by query_hash
        having count(*) > 1
        order by count(*) desc) as q
    cross apply sys.dm_exec_sql_text(q.sample_sql_handle) as t
    cross apply sys.dm_exec_query_plan(q.sample_plan_handle) as p

删除计划缓存膨胀的语句:

DECLARE @MB decimal(19,3)
        , @Count bigint
        , @StrMB nvarchar(20)


SELECT @MB = sum(cast((CASE WHEN usecounts = 1 AND objtype IN ('Adhoc', 'Prepared') THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024 
        , @Count = sum(CASE WHEN usecounts = 1 AND objtype IN ('Adhoc', 'Prepared') THEN 1 ELSE 0 END)
        , @StrMB = convert(nvarchar(20), @MB)
FROM sys.dm_exec_cached_plans


IF @MB > 10
        BEGIN
                DBCC FREESYSTEMCACHE('SQL Plans') 
                RAISERROR ('%s MB was allocated to single-use plan cache. Single-use plans have been cleared.', 10, 1, @StrMB)
        END
ELSE
        BEGIN
                RAISERROR ('Only %s MB is allocated to single-use plan cache – no need to clear cache now.', 10, 1, @StrMB)
                — Note: this is only a warning message and not an actual error.
        END
go

以上内容应该让您了解从哪里开始，以下是必须阅读的主题和引用资料:

1. http://www.sqlskills.com/blogs/kimberly/category/plan-cache/

2. http://sqlblog.com/blogs/kalen_delaney/archive/2007/11/04/did-you-know-sp2-does-not-limit-the-amount-of-plan-cache-you-can-have.aspx

3. https://technet.microsoft.com/en-us/library/dd672789(v=sql.100).aspx

4. Must read article By SQLCAT on issues customer faced while using Prepare Statements

在上面引用的文章中，kimberely 建议启用“针对临时工作负载优化”选项，但我建议先对其进行测试。这里是 interesting thread在 DBA.SE 上