SQL Server 2008中SQL应用系列--目录索引
SQL Server的系统查询过程负责在SELECT查询执行时候产生查询执行计划。SQL Server会“智能”地选择一个高效计划来取代低效的一个。大多数时候,SQL Server会把这份工作干得很棒。但正如有些人所担忧的,SQL Server并不是万能的,有时候,我们通过查询执行计划、表统计信息、支撑的索引及其他因素,研究性能之后,发现查询优化器选择的执行计划没有达到预期的效果,或者说,查询优化器做出了错误的选择。此时我们可能需要使用Hints(提示)来覆盖SQL Server查询优化器决定的过程。
看MSDN(http://msdn.microsoft.com/en-us/library/ms187713%28v=sql.105%29.aspx),Hints(提示)是指定的强制选项或策略,由 SQL Server 查询处理器针对 SELECT、INSERT、UPDATE 或 DELETE 语句执行。提示将覆盖查询优化器可能为查询选择的任何执行计划。
使用Hints之前,我有义务提醒一下各位读者:
■SQL Server绝大多数情况下会做出正确的选择,即便使用的Hints短期内有效,但随着数据库内容的更改,使用的查询计划反而可能更高效,但此时因为Hints更霸道,SQL Server并不会“自作主张”地使用优化器。
■SQL Server发布补丁后,有效的Hints也可能会改变。
本文将要的介绍的内容包括:SQL Server三种不同类型的Hints:联接提示(Join Hints)、查询提示(Query Hints)、表提示(Table Hints),包括SQL Server2008中引入的提示ForceSeek,可以用它将索引查找来替换索引扫描。
1、使用联接提示(Join Hints)
官方解释:联接提示用于指定查询优化器在两个表之间强制执行联接策略。(http://msdn.microsoft.com/zh-cn/library/ms173815%28v=sql.105%29.aspx)
用法:<join_hint> ::= { LOOP | HASH | MERGE | REMOTE }
联结提示会强制查询优化器来使用你命令的方式联结表,通过内部JOIN操作来实现。可用的联结提示:
代码演示:
/********* 使用Hints联结 ***************/
/********* 3w@live.cn 邀月 ************/
USE TestDb2
GO
IF NOT OBJECT_ID('HintsDemo','U') IS NULL
DROP TABLE HintsDemo
GO
IF NOT OBJECT_ID('HintsDemo2','U') IS NULL
DROP TABLE HintsDemo2
GO
----创建测试数据表
CREATE TABLE dbo.HintsDemo (HID int ,HTitle Nvarchar(50))
GO
CREATE TABLE dbo.HintsDemo2 (HID2 int ,HID int)
GO
----插入20条数据
INSERT HintsDemo(HID,HTitle)
VALUES ( cast(rand()*10 AS INT),replicate('X',cast(rand()*25 AS INT)) )
GO 20--重复该语句20次,邀月注
INSERT HintsDemo2
SELECT TOP 10 cast(rand()*10 AS INT), HID from HintsDemo
GO 2--重复该语句2次,邀月注
--此时两个表各有20条记录
SELECT * FROM HintsDemo
SELECT * FROM HintsDemo2
SET SHOWPLAN_XML ON
GO
SELECT h.HID,h.HTitle,d.HID2
FROM HintsDemo h
INNER JOIN HintsDemo2 d ON
h.HID = d.HID
GO
SET SHOWPLAN_XML OFF
GO
下面使用Hash Join
SET SHOWPLAN_XML ON
GO
SELECT h.HID,h.HTitle,d.HID2
FROM HintsDemo h
Inner HASH JOIN HintsDemo2 d ON
h.HID = d.HID
GO
SET SHOWPLAN_XML OFF
GO
2、使用查询提示(Query Hints)
官方解释:在查询语句的持续时间内,查询提示优先于查询优化器的默认行为。您可以使用查询提示指定受影响的表的锁定方法、一个或多个索引、一个查询处理操作(如表扫描或索引查找)或其他选项。查询提示应用于整个查询。(http://msdn.microsoft.com/zh-cn/library/ms181714%28v=sql.105%29.aspx)
它的参数比较复杂:
<query_hint > ::=
{ { HASH | ORDER } GROUP | { CONCAT | HASH | MERGE } UNION | { LOOP | MERGE | HASH } JOIN | EXPAND VIEWS | FAST number_rows | FORCE ORDER | IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX | KEEP PLAN | KEEPFIXED PLAN | MAXDOP number_of_processors | MAXRECURSION number
| OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )
| OPTIMIZE FOR UNKNOWN
| PARAMETERIZATION { SIMPLE | FORCED }
| RECOMPILE | ROBUST PLAN | USE PLAN N'xml_plan' | TABLE HINT ( exposed_object_name [ , <table_hint> [ [, ]...n ] ] )
}
<table_hint> ::=
[ NOEXPAND ] { INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value ) | FORCESEEK [( index_value ( index_column_name [,... ] ) ) ] | FORCESCAN | HOLDLOCK | NOLOCK
| NOWAIT | PAGLOCK | READCOMMITTED | READCOMMITTEDLOCK | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE
| SPATIAL_WINDOW_MAX_CELLS = integer | TABLOCK | TABLOCKX | UPDLOCK | XLOCK
}
一般情况下, 我们可以在存储过程中使用ReCompile查询提示,这样可以实现SQL语句级的重编译,而不是整个存储过程(批处理语句)的重编译。我们通过一个示例来说明。
/********* 使用查询提示 ***************/
/********* 3w@live.cn 邀月 ************/
DECLARE @HintsTitleDemo nvarchar(50) = '0E2FAB59-9A22-4E14-B7BE-33AB500E3B9E'
SELECT HID,HTitle
FROM HintsDemo
WHERE HTitle = @HintsTitleDemo
ORDER BY HID
/*
HID HTitle
6 0E2FAB59-9A22-4E14-B7BE-33AB500E3B9E
*/
--使用DMV查询查看内存中的统计计划是否可以重用
SELECT cacheobjtype, objtype, usecounts
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text LIKE 'DECLARE @HintsTitleDemo%'
--先清除过程缓存,请不要在生产环境中使用下句
DBCC FREEPROCCACHE
/* DBCC execution completed. If DBCC printed error messages, contact your system administrator.*/
DECLARE @HintsTitleDemo nvarchar(50) = '0E2FAB59-9A22-4E14-B7BE-33AB500E3B9E'
SELECT HID,HTitle
FROM HintsDemo
WHERE HTitle = @HintsTitleDemo
ORDER BY HID
OPTION (RECOMPILE)--强制重新编译
再次友情提醒,绝大多数情况下,SQL Server 会做出较优的选择,极端情况下,我们才需要干预它的查询计划,以覆盖SQL Server的选择。
3、表提示(Table Hints)
官方解释:通过指定锁定方法、一个或多个索引、查询处理操作(如表扫描或索引查找)或其他选项,表提示可在数据操作语言 (DML) 语句执行期间覆盖查询优化器的默认行为。表提示在 DML 语句的 FROM 子句中指定,仅影响在该子句中引用的表或视图。(http://msdn.microsoft.com/zh-cn/library/ms187373%28v=sql.105%29.aspx)
表提示与查询提示类似,用于覆盖Select、INSERT、UPDATE和DELETE的默认行为。可以为一个表设置多个表提示,并使用逗号分开,只要它们不改属于同个分组即可。
WITH ( <table_hint> [ [, ]...n ] )
<table_hint> ::= [ NOEXPAND ] { INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value ) | FORCESEEK [( index_value ( index_column_name [ ,... ] ) ) ] | FORCESCAN | FORCESEEK | HOLDLOCK | NOLOCK
| NOWAIT | PAGLOCK | READCOMMITTED | READCOMMITTEDLOCK | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE
| SPATIAL_WINDOW_MAX_CELLS = integer | TABLOCK | TABLOCKX | UPDLOCK | XLOCK
}
<table_hint_limited> ::=
{ KEEPIDENTITY | KEEPDEFAULTS | HOLDLOCK | IGNORE_CONSTRAINTS | IGNORE_TRIGGERS | NOLOCK | NOWAIT | PAGLOCK | READCOMMITTED | READCOMMITTEDLOCK | READPAST | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | TABLOCK | TABLOCKX | UPDLOCK | XLOCK
}
这里就不举例了,看MSDN(http://msdn.microsoft.com/zh-cn/library/ms187373%28v=sql.105%29.aspx)即可。
值得注意的是NOlOCK选项,如下语句:
--不锁定执行查询
SELECT HID,HTitle
FROM HintsDemo
WITH (NOLOCK)
WHERE HID = 4
/*
HID HTitle
4 E6DA3DB2-3D41-47B4-B4E3-DDA90918434C
4 1C4C9211-EB1C-42B5-A08A-558DC73462B4
4 667C9985-3B0A-4767-AED9-82FEE623433D
*/
NOLOCK表提示让查询在不在被影响的行或数据是放置共享锁--允许你在不被阻塞或不阻塞其他查询的情况下读取(但会遇到“脏读”问题)。
最后,我们来介绍一个SQL Server 2008引入的FORCESEEK表提示,它可以用来将索引扫描替换为索引查找。会有一些原因导致SQL Server产生不良的查询计划。例如表数据经常变化并且信息忆不再准确,或带有拙劣where子句的查询没有为查询优化器过程提供有用的或足够的信息。
如果为了单独查找指定数据,而在检索一行之前对整个非常大的表进行了整表扫描,由此带来的I/O开销当然是不能接受的。
/********* 使用ForceSeek强制覆盖SCAN ***************/
/********* 3w@live.cn 邀月 ************/
--创建一个测试主键
ALTER table dbo.HintsDemo
add PKID int Primary key Identity(101,1)
GO
--此时,SQL Server为我们自动创建一个聚集索引[PK__HintsDem__5E0282723D61619B]
SELECT * FROM dbo.HintsDemo
--创建一个非聚集索引
CREATE NONClustered Index idx_ForceSeekDemo
ON Testdb2.dbo.HintsDemo(HTitle)
----select *,Row_Number() over(Partition By [HID] Order by [HID] Asc) As RowID
---- from HintsDemo;
SET SHOWPLAN_XML ON
GO
SELECT DISTINCT HTitle from HintsDemo
WHERE HID BETWEEN 8 AND 10 and HTitle ='141466E4-E8CC-4219-A9AF-7C0D2B86A668'
GO
SET SHOWPLAN_XML OFF
注意本例使用With(ForceSeek)未必最优,只是提供了一个修改系统访问数据的方式,邀月注
SET SHOWPLAN_XML ON
GO
--注意本例使用With(ForceSeek)未必最优,只是提供了一个修改系统访问数据的方式,邀月注
SELECT DISTINCT HTitle from HintsDemo WITH (FORCESEEK)
WHERE HID BETWEEN 8 AND 10 and HTitle ='141466E4-E8CC-4219-A9AF-7C0D2B86A668'
GO
SET SHOWPLAN_XML OFF
你也可以更进一步指定使用哪个索引
SELECT DISTINCT HTitle from HintsDemo WITH (FORCESEEK,INDEX(idx_ForceSeekDemo))
WHERE HID BETWEEN 8 AND 10 and HTitle ='141466E4-E8CC-4219-A9AF-7C0D2B86A668'
GO
对于本例,如果你需要得更好的性能,可以考虑使用SQL Server引入的指定行集索引功能,比如你只关注某房价表中均价在5000-6000元的楼盘信息,那么可以专门为这个区间建立索引。这个属于题外话,不在本文讨论之列。
小结:本文简要介绍SQL Server三种不同类型的联接提示(Join Hints)、查询提示(Query Hints)、表提示(Table Hints),包括SQL Server2008中引入的提示ForceSeek,可以用它将索引查找来替换索引扫描。再次提醒一下读者:提示有风险,使用需谨慎!
邀月注:本文版权由邀月和CSDN共同所有,转载请注明出处。
助人等于自助! 3w@live.cn
分享到:
相关推荐
Chapter 1, Introduction to SQL Server 2016, very covers briefly the most important features and enhancements, not only those for developers. We want to show the whole picture and point where things ...
1. The Evolution of Microsoft SQL Server: 1989 to 2000 [加入我的離線書架] . SQL Server: The Early Years . Ron's Story . Kalen's Story . Microsoft SQL Server Ships . Development Roles Evolve . OS...
鎖定提示(Hints)與追蹤旗標(Trace Flags) . 總結 第四部分 效能與調整篇 14. 查詢效能最佳化 [加入我的離線書架] . 開發小組 . 應用程式與資料庫設計 . 規劃尖峰使用量 . 了解互動系統的回應時間 . ...
dbForge Studio for SQL Server is a powerful IDE for SQL Server management, administration, development, data reporting and analysis. The tool will help SQL developers to manage databases, speed up ...
Expert cube development with microsoft SQL server 2008 analysis services 很好的SQL 2008 BI教程 Design and implement fast, scalable and maintainable cubes - A real-world guide to designing cubes ...
A real-world guide to designing cubes with Analysis Services 2008 Model dimensions and measure groups in BI Development Studio Implement security, drill-through, and MDX calculations Learn how to ...
oracle sql 暗示 描述了oracle sql 语句的所有暗示
Oracle SQL Hints比较详细的一个中文手册,便于使用时查询
hints 使用的好的話很好用。在进行sql执行过程中,由于有时候系统自动优化的方式并不是最优的。需要我们手工添加hint来提高查询效率。
Oracle Hints 用法大全,对于优化 SQL 很有帮助!
Hints优化.pdf
方便地设置Hints,自动生成不同的优化场景,随意地检查和比较SQL语句的执行,并通过这种交互,解释和优化存储过程与应用程序中的SQL代码。 SQL Tuning上下文相关的专家建议,帮助检查和修改有问题的SQL
Oracle的hints调整机制介绍 Oracle的hints调整机制介绍 Oracle的hints调整机制介绍
oracle hints的详细介绍,包括各种优化算法等。
oracle HINTS用法,自己平时整理的一些关于ORACLE HINTS的用法
An intent lock indicates that SQL Server wants to acquire a shared (S) lock or exclusive (X) lock on some of the resources lower down in the hierarchy. For example, a shared intent lock placed at the ...
Spec for Extended Window Manager Hints. version 1.4 draft2
101 Helpful Hints for IELTS
"Data Analysis Using SQL and Excel, 2nd Edition" 2016 | ISBN-10: 111902143X | 936 pages | true PDF | 28 MB Data Analysis Using SQL and Excel, 2nd Edition shows you how to leverage the two most ...
maven-hints-3.1.4-sources.jar