SQL Server：检测非参数化查询

Question

我有一个客户，他们以前的开发人员为他们编写了程序。最近他们的代码变得可疑起来，我想知道他们是否在使用参数化查询。我希望能够通过SQL Server检测非参数化请求，但我还没有找到方法。我理解并不是所有的查询都需要参数化，因为查询可能是这样的：

select count(*) from Customers

但是如果SQL Server可以通过程序告诉我哪些查询具有任何字符串常量输入而不是参数输入，那将是很好的。 顺便说一下，记录所有SQL查询并删除所有带有@符号的行是接近的，但以下查询将被视为合法：

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

我需要SQL Server读取命令的内容，并确定所有输入是否已参数化。谢谢。

- user1325179

---

我了解到您无法获取源代码。这是哪个版本的SQL Server？ - abhi

如果字符串字面量是由程序员而不是用户提供的，则存在合法的字符串字面量用例。 - Joel Coehoorn

@dasblinkenlight，这可能被视为误报，但在这个练习中没关系。 - user1325179

嗯，这取决于可以接受多少个误报...你可以尝试使用 SELECT * FROM [master].[sys].[syscacheobjects] WHERE [sql] LIKE '%WHERE%''%'，它会给你执行的查询，其中在 WHERE 后面有任何单引号。显然，你可以想出各种变化，但我认为 [syscacheobjects] 系统视图中的 [sql] 列可能会对此有所帮助。 - Doug_Ivison

@Doug_Ivison，非常有帮助。 SQL查询语句何时从此表中删除？ 它们不会永远缓存在这里，是吗？ 我只是想知道这个表允许我看到多久以前的数据。谢谢。 - user1325179

Answer 1

今日免费次数已满, 请开通会员/明日再来

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

请注意，关系引擎可以参数化简单查询（称为简单参数化功能），因此您可能会得到相同的行。

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

计划缓存污染也可能是导致数据库性能下降的原因。检查此查询以确定是否也是您的情况。

- bjnr

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或订单号等）可能会产生唯一的查询，即使它们具有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）

希望这可以帮到你...

- Doug_Ivison

---

关于 usecounts=1：实际上，计划高速缓存污染与使用 usecounts=1 缓存的许多查询计划有关，因为 SQL Server 认为 adhoc 查询即使更改了参数的值也是不同的。更重要的是，adhoc 查询中的任何注释或回车都会导致生成一个新的计划（除了简单参数化情况，在生产环境中不应该经常出现！） - bjnr