Sql 2005 数据库优化（摘录）

1，  创建聚集索引

CREATE UNIQUE CLUSTERED INDEX CLIDX_OrderDetails

ON dbo.OrderDetails(SalesOrderID)

2，  创建聚集复合索引

CREATE UNIQUE CLUSTERED INDEX CLIDX_OrderDetails

 

     ON dbo.OrderDetails(SalesOrderID,SalesOrderDetailID)

3，  创建非聚集索引

4，  使用包含性索引

DROP INDEX dbo.OrderDetails.NCLIX_OrderDetails_ProductID

 

CREATE INDEX NCLIX_OrderDetails_ProductID

 

    ON dbo.OrderDetails(ProductID)

 

INCLUDE (CarrierTrackingNumber)

5，  计算列上的索引

 

注意     在计算列上建立索引是有一些限制的。最主要的限制是，计算列必须具有确定性，且必须精确。有关这些限制的更多信息，可以参见SQL Server Books Online 主题“Creating Indexes on Computed Columns”。

添加需要计算的列，建立索引

6，  使用select * 不是好习惯，只需要取所需的数据就可以了

7，  XML列上的索引

1，创建并使用主XML索引

CREATE PRIMARY XML INDEX index_name

 

    ON <object> ( xml_column_name )

Xml列的查询：

以下语句使用一个XQuery方法来获取在XML文档中具有特定路径的XML数据

WITH XMLNAMESPACES

 

('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works

 

/ProductModelDescription' AS "PD")

 

 

 

SELECT ProductID, CatalogDescription

 

FROM dbo.Products

 

WHERE CatalogDescription.exist ('/PD:ProductDescription/PD:Features') = 1

2，辅助XML索引

PATH辅助XML索引  使用.exist方法时，这种索引可用于判断一个特定的路径是否存在。

 

VALUE辅助XML索引  在完整路径未知或包含通配符时，可以用此索引执行一个基于值的查询。

 

PROPERTY辅助XML索引  在通向值的路径未知的时候，此索引可用于获取值。

 

创建辅助XML索引的一般语法是：

 

CREATE XML INDEX index_name

 

    ON <object> ( xml_column_name )

 

    USING XML INDEX xml_index_name

 

    FOR { VALUE | PATH | PROPERTY }

以下语句在XML列CatalogDescription创建一个辅助path, value和property索引:

CREATE XML INDEX IXML_Products_CatalogDesc_Path

 

     ON dbo.Products (CatalogDescription)

 

     USING XML INDEX PRXML_Products_CatalogDesc FOR PATH

 

 

 

CREATE XML INDEX IXML_Products_CatalogDesc_Value

 

     ON dbo.Products (CatalogDescription)

 

     USING XML INDEX PRXML_Products_CatalogDesc FOR VALUE

 

 

 

CREATE XML INDEX IXML_Products_CatalogDesc_Property

 

     ON dbo.Products (CatalogDescription)

 

     USING XML INDEX PRXML_Products_CatalogDesc FOR PROPERTY

以下使用XQuery方法的语句

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/

 

ProductModelDescription' AS "PD")

 

SELECT *

 

     FROM dbo.Products

 

     WHERE CatalogDescription.exist

 

     ('/PD:ProductDescription/@ProductModelID[.="19"]') = 1;

 

 

 

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/

 

ProductModelDescription' AS "PD")

 

SELECT *

 

     FROM dbo.Products

 

     WHERE CatalogDescription.exist ('//PD:*/@ProductModelID[.="19"]') = 1;

 

 

 

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/

 

ProductModelDescription' AS "PD")

 

SELECT

 

     CatalogDescription.value('(/PD:ProductDescription/@ProductModelID)[1]',

 

     'int') as PID

 

FROM dbo.Products

 

WHERE

 

     CatalogDescription.exist ('/PD:ProductDescription/@ProductModelID') = 1;

8，  创建一个索引视图

         1.    使用SCHEMABINDING子句来创建一个视图。这个视图必须符合许多要求。例如，它只能引用同一数据库中的基表。所有引用的函数必须是确定性的；行集函数、派生表和子查询都不能在索引视图中使用。创建索引视图的完整要求列表可以在SQL Server Books Online 的主题“Creating Indexed Views”中找到。

 

2.    在视图上创建一个惟一索引。这个索引的叶子级由视图的完整结果集组成。

 

3.    在聚集索引的基础上根据需求创建非聚集索引。非聚集索引可以按平常的方式创建

CREATE VIEW dbo.vOrderDetails

 

     WITH SCHEMABINDING

 

AS

 

     SELECT DATEPART(yy,Orderdate) as Year,

 

         DATEPART(mm,Orderdate) as Month,

 

         SUM(LineTotal) as OrderTotal,

 

         COUNT_BIG(*) as LineCount

 

     FROM dbo.Orders o INNER JOIN dbo.OrderDetails od

 

         ON o.SalesOrderID = od.SalesOrderID

 

     GROUP BY DATEPART(yy,Orderdate),

 

         DATEPART(mm,Orderdate)

9，  为视图创建索引

CREATE UNIQUE CLUSTERED INDEX CLIDX_vOrderDetails_Year_Month

 

ON dbo.vOrderDetails(Year,Month)

10，联接操作用于将表或者中间结果联接在一起。SQL Server使用三种类型的联接操作：

 

1，嵌套循环联接  这种联接使用一个联接输入作为内部输入表，另一个联接输入作为外部输入表。嵌套循环会在每一个内部输入行进行一次并在外部输入中搜索相应的行。当外部输入联接条件的列上有索引的时候，SQL Server可以使用索引查找在外部输入中查找行。如果不存在索引，SQL Server就只好根据内部输入的每一个行，使用扫描操作在外部输入中找出匹配的行。嵌套循环常用于内部输入的行数较少的情况，因为此时这种联接方式最高效。

 

2，合并联接  这种联接适用于联接输入在其联接列上已排好序时。在合并联接操作中，SQL Server对已排序的输入进行一次扫描，然后将数据合并在一起。合并联接非常高效，但数据必须被提前排序，这意味着在联接列上必须有索引。如果联接列上没有索引，SQL Server会将输入行先排序。但是这种情况并不经常发生，因为排序数据工作的效率通常都较低。

 

3，哈希联接  这种联接用于海量、未排序、没有索引的输入。哈希联接在联接列上使用哈希操作来将输入联接在一起。为了计算并存储哈希操作的结果，SQL Server所需要的内存和CPU时间都比其它联接操作多。

11，使用“数据库引擎优化顾问”

1.    打开SQL Server Management Studio。打开“新建查询”窗口并更改数据库上下文为“AdventureWorks”。

2.    键入并执行以下语句，将使用这些语句来通过“数据库引擎优化顾问”优化数据库。以下代码包含在示例文件UsingDatabaseEngineTuningAdvisor.sql中。

 

USE AdventureWorks;

 

SELECT o.SalesOrderID, o.OrderDate, od.ProductID

     FROM dbo.Orders o INNER JOIN dbo.OrderDetails od

     ON o.SalesOrderID = od.SalesOrderID

     WHERE o.SalesOrderID = 43659;

 

SELECT o.SalesOrderID, o.OrderDate, od.ProductID

     FROM dbo.Orders o INNER JOIN dbo.OrderDetails od

     ON o.SalesOrderID = od.SalesOrderID

     WHERE o.SalesOrderID BETWEEN 43659 AND 44000;

 

3.    为了将这段脚本存为一个工作负荷文件，需要打开“文件”菜单并选择“另存为”并指定文件名称为dta.sql。

4.    在SQL Server Management Studio中，在“工具”菜单上选择“数据库引擎优化顾问”。连接到SQL Server实例。

5.    选择在步骤3存储的文件作为工作负荷文件并选择数据库“AdventureWorks”作为要优化的数据库。如图6.20所示。

图6.20  选择要优化的数据库

6.    按工具条上的“开始分析”按钮。

7.    在分析完成之后，窗口中将显示建议，如图6.21所示。

8.    “数据库引擎优化顾问”建议创建两个索引。可以在“操作”菜单中选择“保存建议”来保存生成索引的脚本。

9.    关闭“数据库引擎优化顾问”。

索引类型

索引类型	描    述
聚集索引	在索引的叶子级存储表行的数据。基于索引键提供快速排序和数据范围访问的功能。一个表只能有一个聚集索引
非聚集索引	基于索引键提供了快速索引查找操作并可以创建为包含性索引。一个表可以存在249个这样的索引
计算列索引	存储了计算列并在计算列作为搜索条件时提供快速的数据访问
XML列索引	提供对XML列的快速XQuery访问。
索引视图	存储视图结果并对其提供快速访问。在视图经常被查询尤其是具有聚合函数的时候非常有用
查看估计的查询执行计划	按“Ctrl+L”或在“查询”菜单上选择“显示估计的执行计划”
查看实际的查询执行计划	按“Ctrl+M”或在“查询”菜单上选择“包括实际的执行计划”。实际的执行计划显示在“执行计划”选项卡中
创建一个聚集索引	CREATE UNIQUE CLUSTERED INDEX <index_name>      ON <table>(<column>)
创建一个非聚集索引	CREATE [ UNIQUE ] NONCLUSTERED INDEX index_name      ON <object> ( column [ ASC | DESC ] [ ,...n ] )
创建一个主XML索引	CREATE PRIMARY XML INDEX index_name      ON <object> ( xml_column_name )
创建一个辅助XML索引	CREATE XML INDEX index_name      ON <object> ( xml_column_name )      USING XML INDEX xml_index_name      FOR { VALUE | PATH | PROPERTY }
查看数据分布	查询sys.stats和sys.stats_columns视图。对特定的列使用以下语句：   DBCC SHOW_STATISTICS(<table>, <column>)
获取碎片信息	使用行集函数 sys.dm_db_physical_stats
重建索引并对索引进行碎片整理	ALTER INDEX <index> ON <table>.<column> REBUILD
使用“数据库引擎优化顾问”	在SQL Server Management Studio中，在“工具”菜单上选择“数据库引擎优化顾问”

二、SQL语句编写注意问题

　　下面就某些SQL语句的where子句编写中需要注意的问题作详细介绍。在这些where子句中，即使某些列存在索引，但是由于编写了劣质的SQL，系统在运行该SQL语句时也不能使用该索引，而同样使用全表扫描，这就造成了响应速度的极大降低。

　　

　　1. IS NULL 与 IS NOT NULL

　　不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。

　　

　　任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

　　

　　2. 联接列

　　

　　对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。我们一起来看一个例子，假定有一个职工表（employee），对于一个职工的姓和名分成两列存放（FIRST_NAME和LAST_NAME），现在要查询一个叫比尔.克林顿（Bill Cliton）的职工。

　　

　　下面是一个采用联接查询的SQL语句，

　　

　　select * from employss

　　where

　　first_name　　''　　last_name ='Beill Cliton';

　　

　　上面这条语句完全可以查询出是否有Bill Cliton这个员工，但是这里需要注意，系统优化器对基于last_name创建的索引没有使用。

　　

　　当采用下面这种SQL语句的编写，Oracle系统就可以采用基于last_name创建的索引。

　　

　　Select * from employee

　　where

　　first_name ='Beill' and last_name ='Cliton';

　　

　　遇到下面这种情况又如何处理呢？如果一个变量（name）中存放着Bill Cliton这个员工的姓名，对于这种情况我们又如何避免全程遍历，使用索引呢？可以使用一个函数，将变量name中的姓和名分开就可以了，但是有一点需要注意，这个函数是不能作用在索引列上。下面是SQL查询脚本：

　　

　　select * from employee

　　where

　　first_name = SUBSTR('&&name',1,INSTR('&&name',' ')-1)

　　and

　　last_name = SUBSTR('&&name',INSTR('&&name’,' ')+1)

　　

　　3. 带通配符（%）的like语句

　　

　　同样以上面的例子来看这种情况。目前的需求是这样的，要求在职工表中查询名字中包含cliton的人。可以采用如下的查询SQL语句：

　　

　　select * from employee where last_name like '%cliton%';

　　

　　这里由于通配符（%）在搜寻词首出现，所以Oracle系统不使用last_name的索引。在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用：

　　

　　select * from employee where last_name like 'c%';

　　

　　4. Order by语句

　　

　　ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

　　

　　仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。

　　

　　5. NOT

　　

　　我们在查询时经常在where子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用and（与）、or（或）以及not（非）。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子：

　　

　　... where not (status ='VALID')

　　

　　如果要使用NOT，则应在取反的短语前面加上括号，并在短语前面加上NOT运算符。NOT运算符包含在另外一个逻辑运算符中，这就是不等于（<>）运算符。换句话说，即使不在查询where子句中显式地加入NOT词，NOT仍在运算符中，见下例：

　　

　　... where status <>'INVALID';

　　

　　再看下面这个例子：

　　

　　select * from employee where salary<>3000;

　　

　　对这个查询，可以改写为不使用NOT：

　　

　　select * from employee where salary<3000 or salary>3000;

　　

　　虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。

　　

　　6. IN和EXISTS

　　

　　有时候会将一列和一系列值相比较。最简单的办法就是在where子句中使用子查询。在where子句中可以使用两种格式的子查询。

　　

　　第一种格式是使用IN操作符：

　　

　　... where column in(select * from ... where ...);

　　

　　第二种格式是使用EXIST操作符：

　　

　　... where exists (select 'X' from ...where ...);

　　

　　我相信绝大多数人会使用第一种格式，因为它比较容易编写，而实际上第二种格式要远比第一种格式的效率高。在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。

　　

　　第二种格式中，子查询以‘select 'X'开始。运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作（这里假定在where语句中使用的列存在索引）。相对于IN子句来说，EXISTS使用相连子查询，构造起来要比IN子查询困难一些。

　　

　　通过使用EXIST，Oracle系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间。Oracle系统在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因。

　　

　　同时应尽可能使用NOT EXISTS来代替NOT IN，尽管二者都使用了NOT（不能使用索引而降低速度），NOT EXISTS要比NOT IN查询效率更高。

 

sql server2005如果语句不符合SARG会引起大量性能损失（实例）

sql server2005如果语句不符合SARG（扫描参数）会引起大量性能损失，特别是在数据量较大的时候。
关于SARG的定义：用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值得范围内的匹配或者两个以上条件的AND连接。
原来的sql语句：

SET   STATISTICS   IO    ON   
select top (10) a.Guid,a.lon,a.lat,a.Name,a.Address,a.Tel,ROW_NUMBER()
 OVER(ORDER BY b.DirectoryID) as 'rownumber'  FROM LBS_Poi 
a left join LBS_Poi_Extend b on a.Guid = b.Guid 
  where  a.Name like + '%武汉%'  and  b.Auditing = 1

a和b表是一一对应的关系，有7w多条数据的时候执行竟然需要30s左右。

改进后的sql语句

SET   STATISTICS   IO    ON   
select top (10) a.Guid,a.lon,a.lat,a.Name,a.Address,a.Tel,ROW_NUMBER()
 OVER(ORDER BY b.DirectoryID) as 'rownumber'  FROM LBS_Poi 
a left join LBS_Poi_Extend b on a.Guid = b.Guid  and  b.Auditing = 1
  where  a.Name like + '%武汉%' 

只是把b.Auditing = 1移动到前面的连接语句中，执行速度<1s。真是质的飞跃啊。

分析下原因就是前面的where语句不是一个SARG（扫描参数）。
如果一个表达式不能满足SARG的形式，那它就无法限制搜索的范围了，也就是SQL SERVER必须对每一行都判断它是否满足where子句中的所有条件。所以一个索引对于不满足SARG形式的表达式来说是无用的。所以执行的那么慢了。

RowLock & NoLock

NOLOCK的使用

NOLOCK可以忽略锁，直接从数据库读取数据。这意味着可以避开锁，从而提高性能和扩展性。但同时也意味着代码出错的可能性存在。你可能会读取到运行事务正在处理的无须验证的未递交数据。这种风险可以量化。

如果是金融方面的代码或者一些非常规的总计(你想绝对保证安全性)，你应该小心行事并且不使用这种技术。 但是我认为使用该技术会比你90%应用系统性能要好，当用户(或者是交互代码)发现一个未递交的修改时，使用技术会保证不会像未使用该技术那样引起大麻烦。实际上，你可能发现你的大多数数据很少或者甚至不进行修改的，这样我们就不会因为这些数据被锁住而浪费大量的时间。

例如，如果你想统计在2000年6月份到8月份之间加入Streamload.com的所有用户，就没有理由去锁住任何记录： 2000年9月1号一到来，这个用户数就是确定的。又例如要列举在Streamload.com的文件列表：这种结果即使 不是100%的正确，也不是大问题。因为你要么不拥有该文件，当然也无所谓你是否能找到它，或者你确实拥有该文件，这种情况下你当然知道你是否修改了该文件，以及该文件是否已经上传完毕了。

但是，如果这些数据的修改，对数据库来说是基础性的修改，或者这些数据对于用户来说，必须是百分之百保证是修改正确的(例如帐单或者余额数据)，那么你不要使用该技术。

ROWLOCK的使用

ROWLOCK告诉SQL Server只使用行级锁。ROWLOCK语法可以使用在SELECT,UPDATE和DELETE语句中，不过 我习惯仅仅在UPDATE和DELETE语句中使用。如果在UPDATE语句中有指定的主键，那么就总是会引发行级锁的。但是当SQL Server对几个这种UPDATE进行批处理时，某些数据正好在同一个页面(page)，这种情况在当前情况下 是很有可能发生的，这就象在一个目录中，创建文件需要较长的时间，而同时你又在更新这些文件。当页面锁引发后，事情就开始变得糟糕了。而如果在UPDATE或者DELETE时，没有指定主键，数据库当然认为很多数据会收到影响，那样就会直接引发页面锁，事情同样变得糟糕。

通过指定使用行级锁，这种情况可以得到避免。但是需要小心的是，如果你错误地使用在过多行上，数据库并不会聪明到自动将行级锁升级到页面锁，服务器也会因为行级锁的开销而消耗大量的内存和CPU，直至无法响应。尤其主要留意的是 企业管理器中"管理/当前活动"(Management/Current Activity)这一项。该项会花较长的时间来载入锁的信息。这些信息时十分有用的，当你使用行级锁后，你如果在"锁/处理"(Locks/Processes)下看到几百个锁，一点都不奇怪，而恰恰应该庆幸锁超时和死锁的问题减少了。