SQL Server:检测非参数化查询

Question

我有一个客户端，它已经由过去的开发人员执行了编程。他们的代码最近变得可疑，我想知道他们是否在使用参数化查询。我希望我可以通过SQL Server检测非参数化请求，但我还没有找到这样做的方法。我知道并不是所有的查询都需要参数化，因为查询可能类似于

select count(*) from Customers

但是，如果SQL Server能够以编程方式告诉我哪些查询具有任何字符串常量输入而不是参数输入，那就太好了。顺便说一句，记录所有SQL查询并删除所有带有@符号的行是很接近的，但下面的查询将被认为是合法的：

select * from Users where Username='user' and Password=@Password

因此，我确实需要SQL Server读取命令的内容，并确定是否所有输入都是参数化的。非常感谢。

Answer 1

实际上，您正在搜索adhoc查询（不是存储过程，也不是sp_executesql）。

你可以先看看sys.dm_exec_cached_plans DMV:

SELECT
    CP.usecounts
    , CP.cacheobjtype
    , ST.text as sql_Statement    
    , QP.query_plan
FROM
    sys.dm_exec_cached_plans CP
    CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) ST
    CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
WHERE
    ST.dbid = DB_ID()
    AND CP.objtype IN ( 'Adhoc')
ORDER BY
    CP.usecounts DESC

只需注意，关系引擎可以参数化简单的查询（称为简单参数化的功能），所以您可能有相同的行到

如果您想通过不缓存即席查询的计划来提高性能，那么有一个名为optimize for ad hoc workloads的服务器选项会在执行查询时向关系引擎发出信号=>以存储计划的较轻版本（计划存根）。

计划缓存污染也可能是降低数据库性能的一个原因。检查此查询以检测是否也是您的情况。

Answer 2

Mihai将指向当前系统对象dm_exec_cached_plans。（当我查找您问题的答案时，我发现[syscacheobjects]实际上已被弃用。）因此，我确实投票支持他的答案。

然而，我仍然认为在WHERE之后检查单个报价可以节省您的时间。使用dm_exec_cached_plans，这里有一个类似的查询，使用WHERE，就像我之前给你的一样：

SELECT usecounts, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans 
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE [text] LIKE '%WHERE%''%'
----AND [text] NOT LIKE '%sp_executesql%'  ----queries probably ok, with sp_executesql
----WHERE usecounts > 1   ----un-commenting this might also be interesting to check out. 
ORDER BY usecounts DESC;

或者，只需将其添加到Mihai的查询中即可：

SELECT
    CP.usecounts
    , CP.cacheobjtype
    , ST.text as sql_Statement    
    , QP.query_plan
FROM
    sys.dm_exec_cached_plans CP
    CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) ST
    CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
WHERE
    CP.objtype = 'Adhoc'
    AND ST.dbid = DB_ID()
    AND ST.text LIKE '%WHERE%''%'
ORDER BY
    CP.usecounts DESC

请注意ORDER BY，它建立在这样一种思想之上，即常见的查询会导致频繁的暴露。但你可能已经意识到了。。。如果可疑代码正在构建查询字符串本身，那么唯一值（如行号、客户ID或订单号等）可能会生成唯一查询，（如果它们是在不使用sp_executesql的情况下构建的）实际上可能是最重要的查询，即使它们有usecounts = 1。

就你的问题的答案而言，（"SQL查询什么时候从这个表中删除？"）：一旦缓存中有足够的查询导致"内存压力"，旧查询就会随着新查询的到来而从缓存中删除……当SQL重新启动时，所有查询都会被清除。（请参见http://www.sqlservercentral.com/Forums/Topic1375781-391-1.aspx和http://technet.microsoft.com/en-us/library/ms181055%28v=sql.105%29.aspx)

希望能有所帮助。。。