本文属于《理解性能的奥秘——应用程序中慢,SSMS中快》系列
接上文:理解性能的奥秘——应用程序中慢,SSMS中快(3)——不总是参数嗅探的错
前面已经提到过关于存储过程在SSMS中运行很快,但在应用程序中运行很慢的可能原因:因为ARITHABORT的不同选项会导致不同的缓存词目,另外由于SQL Server使用了参数嗅探导致获得了不同的执行计划。
虽然已经说明了这个现象的原因,但是还没解释:如何定位和解决这个问题?到目前为止,大家都知道了如何快速处理,如果这个问题很紧急,可以直接使用:
EXEC sp_recompile 存储过程名
前面提到过,这个操作会刷新计划缓存。下次存储过程被调用时,会产生新的查询计划。如果通过这种方式可以解决,那么认为这个已经不是问题了。
但是如果问题依旧,那么就需要做更深入的研究,并且不适合再用sp_recompile或者类似的方式去修改存储过程。另外请一直打开显示执行计划的选项以便用于对比和检查,最起码可以获取参数的值(可以通过右键执行计划→【显示执行计划XML】的方式在XML格式的执行计划中搜索ParameterList的值)。这是本节的主题。
在开始本节之前,给个小建议:修改SSMS的默认值,以便因为ARITHABORT的默认值带来困惑。建议把这个值设为OFF。但是与应用程序相同设置确实会有一些小缺点:你可能观察不到与参数嗅探有关的性能问题。但是如果你已经养成了都校验ARITHABORT ON和OFF的结果,那么这个问题就不成问题了。
获取必要的事实:
Hoping help will come from above
But even angels there make the same mistakes
- 哪个语句慢?
- 不同的查询计划是怎样的?
- SQL Server嗅探了哪些参数?
- 表和索引的定义是怎样的?
- 统计信息的分布情况如何?实时更新吗?
哪个语句慢?
在SSMS中获取查询计划和参数:
SET ARITHABORT ON go EXEC that_very_sp 4711, 123, 1 go SET ARITHABORT OFF go EXEC that_very_sp 4711, 123, 1
从计划缓存中直接获取查询计划和参数:
查询语句:
DECLARE @dbname NVARCHAR(256),
@procname NVARCHAR(256)
SELECT @dbname = 'Northwind',
@procname = 'dbo.List_orders_11';
WITH basedata
AS (
SELECT qs.statement_start_offset / 2 AS stmt_start,
qs.statement_end_offset / 2 AS stmt_end,
est.encrypted AS isencrypted,
est.TEXT AS sqltext,
epa.value AS set_options,
qp.query_plan,
charindex('<ParameterList>', qp.query_plan) + len('<ParameterList>') AS paramstart,
charindex('</ParameterList>', qp.query_plan) AS paramend
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) est
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) qp
CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) epa
WHERE est.objectid = object_id(@procname)
AND est.dbid = db_id(@dbname)
AND epa.attribute = 'set_options'
),
next_level
AS (
SELECT stmt_start,
set_options,
query_plan,
CASE
WHEN isencrypted = 1
THEN '-- ENCRYPTED'
WHEN stmt_start >= 0
THEN substring(sqltext, stmt_start + 1, CASE stmt_end
WHEN 0
THEN datalength(sqltext)
ELSE stmt_end - stmt_start + 1
END)
END AS Statement,
CASE
WHEN paramend > paramstart
THEN CAST(substring(query_plan, paramstart, paramend - paramstart) AS XML)
END AS params
FROM basedata
)
SELECT set_options AS [SET],
n.stmt_start AS Pos,
n.Statement,
CR.c.value('@Column', 'nvarchar(128)') AS Parameter,
CR.c.value('@ParameterCompiledValue', 'nvarchar(128)') AS [Sniffed Value],
CAST(query_plan AS XML) AS [Query plan]
FROM next_level n
CROSS APPLY n.params.nodes('ColumnReference') AS CR(c)
ORDER BY n.set_options,
n.stmt_start,
Parameter
如果再次之前你从来没用过DMVs,估计对你而言会有点难懂。但是为了把注意力集中在我们的主题上。在这里需要提醒的是语句中需要制定数据库和存储过程名。
结果输出:
USE Northwind
GO
CREATE PROCEDURE List_orders_11 @fromdate DATETIME,
@custid NCHAR(5)
AS
SELECT @fromdate = dateadd(YEAR, 2, @fromdate)
SELECT *
FROM Orders
WHERE OrderDate > @fromdate
AND CustomerID = @custid
IF @custid = 'ALFKI'
CREATE INDEX test ON Orders (ShipVia)
SELECT *
FROM Orders
WHERE CustomerID = @custid
AND OrderDate > @fromdate
IF @custid = 'ALFKI'
DROP INDEX test
ON Orders
GO
SET ARITHABORT ON
EXEC List_orders_11 '19980101',
'ALFKI'
GO
SET ARITHABORT OFF
EXEC List_orders_11 '19970101',
'BERGS'
- SET——表示查询计划中set_options属性。正如前面提到的,这是一个位掩码。上图中可以看到两个值:251 为默认设置,4347为默认设置+ARITHABORT ON。如果看到其他值,可以用作者编写的一个函数反编译:setoptions 。
- Pos——表示该语句在存储过程的起始位置,从存储过程创建语句起算,包括任何CREATE PROCEDURE之前的注释。虽然大部分情况下作用不大,但是可以看到语句在存储过程中的出现顺序。
- Statement——SQL语句,但是注意对于每个参数,都会重复一次。
- Parameter——参数名,仅列出语句中出现的参数。也就是说,如果该查询语句没有使用到的参数,是不会出现在这里的。
- Sniffed Value——在编译时的参数值,也就是在嗅探优化并产生查询计划的参数值。因为这是从计划缓存中获取的信息,所以这里的值并不是实际参数值,从上图中可以看到,对于不同的语句可能会出现不同的参数值。
- Query Plan——对应的预估执行计划。
从Trace文件中获取查询计划和参数:
获取表和索引的定义:
DECLARE @tbl NVARCHAR(265)
SELECT @tbl = 'Orders'
SELECT o.NAME,
i.index_id,
i.NAME,
i.type_desc,
substring(ikey.cols, 3, len(ikey.cols)) AS key_cols,
substring(inc.cols, 3, len(inc.cols)) AS included_cols,
stats_date(o.object_id, i.index_id) AS stats_date,
i.filter_definition
FROM sys.objects o
JOIN sys.indexes i
ON i.object_id = o.object_id
CROSS APPLY (
SELECT ', ' + c.NAME + CASE ic.is_descending_key
WHEN 1
THEN ' DESC'
ELSE ''
END
FROM sys.index_columns ic
JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.is_included_column = 0
ORDER BY ic.key_ordinal
FOR XML PATH('')
) AS ikey(cols)
OUTER APPLY (
SELECT ', ' + c.NAME
FROM sys.index_columns ic
JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.is_included_column = 1
ORDER BY ic.index_column_id
FOR XML PATH('')
) AS inc(cols)
WHERE o.NAME = @tbl
AND i.type IN (
1,
2
)
ORDER BY o.NAME,
i.index_id
这个语句仅用于常规关系型索引,不适合XML索引和空间索引。
获取统计信息相关信息:
DECLARE @tbl NVARCHAR(265)
SELECT @tbl = 'Orders'
SELECT o.NAME,
s.stats_id,
s.NAME,
s.auto_created,
s.user_created,
substring(scols.cols, 3, len(scols.cols)) AS stat_cols,
stats_date(o.object_id, s.stats_id) AS stats_date,
s.filter_definition
FROM sys.objects o
JOIN sys.stats s
ON s.object_id = o.object_id
CROSS APPLY (
SELECT ', ' + c.NAME
FROM sys.stats_columns sc
JOIN sys.columns c
ON sc.object_id = c.object_id
AND sc.column_id = c.column_id
WHERE sc.object_id = s.object_id
AND sc.stats_id = s.stats_id
ORDER BY sc.stats_column_id
FOR XML PATH('')
) AS scols(cols)
WHERE o.NAME = @tbl
ORDER BY o.NAME,
s.stats_id
其中列stats_date返回统计信息最近更新时间。如果这个时间已经过去很久,那么统计信息可能已经过时。参数嗅探问题的根源通常不是统计信息过时,但是也应该检查一下。 需要记住的是,统计信息的列如果是单调递增——如ID、date列,那么会很快过时,因为语句通常获取最近插入的数据,在统计信息的直方图中,记录的却通常是旧的数据。关于直方图会在后续介绍。
UPDATE STATISTICS 表名 WITH FULLSCAN, INDEX
UPDATE STATISTICS tbl indexname WITH FULLSCAN
注意:在表名和索引名之间没有句号,只有空格。
如果是统计信息过时导致的性能问题,通常在更新统计信息之后,就可以发现应用程序的性能马上就得到提升。因为统计信息的更新会触发重编译,使得存储过程会对参数重新嗅探并产生更好的执行计划。
DBCC SHOW_STATISTICS (Orders, OrderDate)