Sql语句查询优化

zyfsky666

在存储过程中语句加参数查询慢（参数嗅探） 转载
加参数查询很慢的问题，不加的话查询就很快。
有时候是参数嗅探引起的。

什么是参数嗅探：

SQL Server对查询语句编译和缓存机制是SQL语句执行过程中非常重要的环节，也是SQLOS内存管理非常重要的一环。理由是SQL Server对查询语句编译过程是非常消耗系统性能，代价昂贵的。因为它需要从成百上千条执行路径中选择一条最优的执行计划方案。所以，查询语句可以重用执行计划的缓存，避免重复编译，以此来节约系统开销。这种编译查询语句，选择最优执行方案，缓存执行计划的机制就是参数嗅探问题产生的理论基础。

以下有几种方式避免因为参数嗅探所导致的全表扫描：

select id from t where num = @num
解决方案1

可以改为强制查询使用索引
例如：

select id from t with(index(索引名)) where num = @num
解决方案2

option(RECOMPILE) 重新编译
例如：

select * from T_order where ordertime>@date option(RECOMPILE)
或者OPTION (OPTIMIZE FOR UNKNOWN)
解决方案3
在存储过程定义变量后，as begin前加WITH RECOMPILE

文章出自：https://yq.aliyun.com/articles/67176

摘要：

1.何时更新统计信息

（1）查询执行缓慢，或者查询语句突然执行缓慢。这种场景很可能是由于统计信息没有及时更新而遭遇了参数嗅探的问题。

（2）当大量数据更新（INSERT/DELETE/UPDATE）到升序或者降序的列时，这种情况下，统计信息直方图可能没有及时更新。

（3）建议在除索引维护（当你重建、整理碎片或者重组索引时，数据分布不会改变）外的维护工作之后更新统计信息。

（4）数据库的数据更改频繁，建议最低限度每天更新一次统计信息。数据仓库可以适当降低更新统计信息的频率。

（5）当执行计划出现统计信息缺失警告时，需要手动建立统计信息

2.如何查找过期的统计信息

（1）假设定义超过30天未更新的统计信息算过期的话，那么查找过期的统计信息语句如下：USE test01GO

DECLARE
@day_before int = 30;SELECT

Object_name = OBJECT_NAME(object_id)
,Stats_Name = [name]
,Stats_Last_Updated = STATS_DATE([object_id], [stats_id])
FROM sys.stats WITH(NOLOCK)
WHERE STATS_DATE([object_id], [stats_id]) <= DATEADD(day, -@day_before, getdate())
order by Stats_Last_Updated desc;

（2）查找到过期的统计信息以后，接下来需要手动更新统计信息，我们可以从下面三个维度来达到目的：

&#61550;更新索引级别统计信息
&#61550;更新单表级别统计信息
&#61550;更新整个数据库级别统计信息



USE test01
GO
--update statistcis for a specify statistic
UPDATE STATISTICS dbo.Rose PK_Rose;
GO



--update statistcis for a specify table
UPDATE STATISTICS dbo.Rose WITH FULLSCAN;
GO



--update statistcis for a specify database
USE test01
GO
EXEC sys.sp_updatestats
GO

（3）更新实例级别统计信息

USE master
GO

DECLARE
@sql NVARCHAR(MAX)
;

SET
@sql = N'
USE [?]
IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'', ''distribution'')
BEGIN
RAISERROR(N''--------------------------------------------------------------
Search on database: ?'', 10, 1) WITH NOWAIT
EXEC sys.sp_updatestats
END
'
;

EXEC SYS.SP_MSFOREACHDB @sql,@replacechar=N'?'
历史死锁检查
SELECT
&#160; &#160; &#160; xed.value('@timestamp', 'datetime') as Creation_Date,
&#160; &#160; &#160; xed.query('.') AS Extend_Event
FROM
(
&#160; &#160; &#160; SELECT CAST([target_data] AS XML) AS Target_Data
&#160; &#160; &#160; FROM sys.dm_xe_session_targets AS xt
&#160; &#160; &#160; INNER JOIN sys.dm_xe_sessions AS xs
&#160; &#160; &#160; ON xs.address = xt.event_session_address
&#160; &#160; &#160; WHERE&#160;xs.name&#160;= N'system_health'
&#160; &#160; &#160; AND xt.target_name = N'ring_buffer'
) AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xed)
ORDER BY Creation_Date DESC

UPDATE STATISTICS dbo.tablename

白晔书生

看看，学习学习经验