什么是动态SQL?
- 使用EXEC()和sp_executesql执行的SQL语句。
- 直接从客户端发到服务器的SQL语句。
- 在SQLCLR中提交的SQL语句。
SELECT @sql = 'SELECT mycol FROM tbl WHERE keycol = ' + convert(varchar, @value) EXEC(@sql)
或者在C#中:
cmd.CommandText = "SELECT mycol FROM tbl WHERE keycol = " + value.ToString();
EXEC sp_executesql N'SELECT mycol FROM dbo.tbl WHERE keycol = @value', N'@value int', @value = @value
cmd.CommandText = "SELECT mycol FROM dbo.tbl WHERE keycol = @value"; cmd.Parameters.Add("@value", SqlDbType.Int); cmd.Parameters["@value"].Value = value;
动态SQL的计划生成:
SELECT * FROM Orders WHERE OrderID = 11000
如果你提交的是下面这种方式,那么编译后就得到了上面这个语句:
EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderID = @p1', N'@p1 int', @p1 = 11000
对于参数化,也有两种模式:简单和强制。对于简单参数化,SQL Server仅参数化相当小的一部分简单查询。对于强制参数化,SQL Server会把所有参数用常量替换,这篇文章提到的情况除外:强制参数化(微软联机丛书) 默认模式是简单参数化,可以使用ALTER DATABASE命令对每个库进行修改。
动态SQL和计划缓存:
查询文本作为哈希键:
USE Northwind GO EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderDate > @orderdate', N'@orderdate datetime', '20000101' EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderDate > @orderdate', N'@orderdate datetime', '19980101' EXEC sp_executesql N'select * from Orders where OrderDate > @orderdate', N'@orderdate datetime', '19980101'
执行计划如下图,你会发现前两个执行语句使用同一个查询计划:
USE Northwind GO DBCC FREEPROCCACHE GO EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderDate > @orderdate', N'@orderdate datetime', '20000101' EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderDate > @orderdate', N'@orderdate datetime', '19980101' EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderDate > @orderdate', N'@orderdate datetime', '19980101'
这一次,三个查询计划都一样了。
默认架构的重要性:
USE Northwind GO DBCC FREEPROCCACHE GO CREATE SCHEMA Schema2 GO CREATE USER User1 WITHOUT LOGIN WITH DEFAULT_SCHEMA = dbo CREATE USER User2 WITHOUT LOGIN WITH DEFAULT_SCHEMA = Schema2 GRANT SELECT ON Orders TO User1, User2 GRANT SHOWPLAN TO User1, User2 GO EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderDate > @orderdate', N'@orderdate datetime', '20000101' GO EXECUTE AS USER = 'User1' EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderDate > @orderdate', N'@orderdate datetime', '19980101' REVERT GO EXECUTE AS USER = 'User2' EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderDate > @orderdate', N'@orderdate datetime', '19980101' REVERT GO DROP USER User1 DROP USER User2 DROP SCHEMA Schema2 GO
执行计划如前面所示,前两个执行计划一样但是第三个不一样。这个脚本创建了两个数据库用户,并授权给他们运行查询。然后语句被执行了三次。第一次由默认架构(即dbo),然后使用两个新建的账号运行。
SELECT qs.plan_handle, a.attrlist FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) est CROSS APPLY (SELECT epa.attribute + '=' + convert(nvarchar(127), epa.value) + ' ' FROM sys.dm_exec_plan_attributes(qs.plan_handle) epa WHERE epa.is_cache_key = 1 ORDER BY epa.attribute FOR XML PATH('')) AS a(attrlist) WHERE est.text LIKE '%WHERE OrderDate > @orderdate%' AND est.text NOT LIKE '%sys.dm_exec_plan_attributes%'
- 没有db_id()的限定,因为这一列仅对存储过程通用。
- 由于没有存储过程名可以匹配,所以必须使用语句文本来搜索。
- 需要额外的条件去筛选sys.dm_exec_plan_attributes。
date_first=7 date_format=1 dbid=6 objectid=158662399 set_options=251 user_id=5 date_first=7 date_format=1 dbid=6 objectid=158662399 set_options=251 user_id=1
EXEC sp_executesql N'SELECT * FROM dbo.Orders WHERE OrderDate > @orderdate', N'@orderdate datetime', '20000101'
date_first=7 date_format=1 dbid=6 objectid=549443125 set_options=251 user_id=-2
在SSMS中运行应用程序查询:
EXECUTE AS USER = 'appuser' go -- 需要执行的SQL语句 go REVERT
但是,如果这个账号访问的资源不在当前库中,就会报错。这个时候,可以使用EXECUTE AS LOGIN来替代,但是这个方案需要有服务器级别的权限。
SELECT '<' + est.text + '>' FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) est WHERE est.text LIKE '%能标识出语句特征的SQL代码%'
注意这个要运行在文本模式,默认是网格模式,SSMS会使用空格来替换换行符。其中尖括号只是为了作为分隔作用。
EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderDate > @orderdate', N'@orderdate datetime', '20000101'
即使你按这种格式来执行也无所谓:
EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderDate > @orderdate', N'@orderdate datetime', '20000101'
处理动态SQL中的参数嗅探问题:
自动参数化的影响:
SELECT * FROM Orders WHERE OrderID = 11000
Seek Keys[1]: Prefix: [Northwind].[dbo].[Orders].OrderID = Scalar Operator(CONVERT_IMPLICIT(int,[@1],0))
[@1]揭示了语句被自动参数化(auto-parameterised)
有时候这种情况是由用户发起,如:
SELECT ... FROM dbo.Orders WHERE Status = 'Delayed'
在Northwind中的这个表不存在stauts列,更不存在Delayed这个值,只是演示需要,当SQL Server参数化这个查询时,由于产生查询计划的要求必须可以覆盖所有参数,所以优化器并不会使用status上的列。
DECLARE @x int SELECT ... FROM dbo.Orders WHERE Status = 'Delayed' AND @x IS NULL
计划向导和计划冻结:
USE Northwind GO DBCC FREEPROCCACHE GO EXEC sp_executesql N'SELECT * FROM dbo.Orders WHERE OrderDate > @orderdate', N'@orderdate datetime', @orderdate = '19960101' GO EXEC sp_create_plan_guide @name = N'MyGuide', @stmt = N'SELECT * FROM dbo.Orders WHERE OrderDate > @orderdate', @type = N'SQL', @module_or_batch = NULL, @params = N'@orderdate datetime', @hints = N'OPTION (TABLE HINT (dbo.Orders , INDEX (OrderDate)))' GO EXEC sp_executesql N'SELECT * FROM dbo.Orders WHERE OrderDate > @orderdate', N'@orderdate datetime', @orderdate = '19980101' GO EXEC sp_control_plan_guide N'DROP', N'MyGuide'
最后,@hints就是重点所在。在这个例子中,指定了查询要总使用OrderDate上的索引,不管@orderdate的嗅探值是什么。还有一个在SQL 2005中不可用的查询提示OPTION(TABLE HINT),这就是为什么这个脚本只能在SQL 2008+使用。
在这个例子中,我使用了计划向导强制使用索引,但是你也可以使用其他提示,包含USE PLAN提示指定使用特定的完整的查询计划。不过这个风险有点大。
USE Northwind GO DBCC FREEPROCCACHE SET ARITHABORT ON GO EXEC sp_executesql N'SELECT * FROM dbo.Orders WHERE OrderDate > @orderdate', N'@orderdate datetime', @orderdate = '19990101' GO DECLARE @plan_handle VARBINARY(64), @rowc INT SELECT @plan_handle = plan_handle FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) est WHERE est.TEXT LIKE '%Orders WHERE OrderDate%' AND est.TEXT NOT LIKE '%dm_exec_query_stats%' SELECT @rowc = @@rowcount IF @rowc = 1 EXEC sp_create_plan_guide_from_handle 'MyFrozenPlan', @plan_handle ELSE RAISERROR ( '%d plans found in plan cache. Canno create plan guide', 16, 1, @rowc ) GO -- Test it out! SET ARITHABORT OFF GO EXEC sp_executesql N'SELECT * FROM dbo.Orders WHERE OrderDate > @orderdate', N'@orderdate datetime', @orderdate = '19960101' GO SET ARITHABORT ON EXEC sp_control_plan_guide 'DROP', 'MyFrozenPlan'
示例中,首先清空缓存,然后把ARITHABORT开启。然后执行一个能使查询使用合适查询计划的参数。然后第二个批演示了如何使用sp_create_plan_guide_from_handle。首先查询sys.dm_exec_query_stats和sys.dm_exec_sql_text去查找批的条目。然后把@@rowcount值存入本地变量。这样做可以防止从缓存中获得多个匹配或不匹配项。如果得到了准确的匹配项,就可以调用sp_create_plan_guide_from_handle,并传入两个参数:计划向导的名字和plan handle。