sqlserver性能的优化

1、分享内容利用索引优化查询利用索引优化查询使用数据库分区表提高程序检索效率使用数据库分区表提高程序检索效率提高数据库查询效率的实用方法提高数据库查询效率的实用方法SQL数据进行排序、分组、统计技巧数据进行排序、分组、统计技巧SQL Server查询速度慢的原因查询速度慢的原因1、没有索引或者没有用到索引(这是查询慢最常见的问题)2、I/O吞吐量小，形成了瓶颈效应。3、内存不足4、网络速度慢5、查询出的数据量过大(可以采用表分区)6、锁或者死锁(这也是查询慢最常见的问题)7、返回了不必要的行和列8、查询语句不好，没有优化提高程序检索效率的解决方案提高程序检索效率的解决方案（1）存储过程代替普通的S

2、QL语句（2）对一些经常查询的表建立索引（3）使用数据库分区表（4）优化查询语句一一.存储过程代替普通的存储过程代替普通的SQL语句语句使用使用存储过程好处：存储过程好处：存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。当对数据库进行复杂操作时(如对多个表进行Update,Insert,Select,Delete时)，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。存储过程可以重复使用,可减少数据库开发人员的工作量安全性高,可设定只有某些用户才具有对指定存储过程的使用权二.理解理解

3、sqlserver索引结构索引结构索引是什么可以把索引理解为一种特殊的目录。SQLSERVER提供了两种索引：聚集索引（clusteredindex，也称聚类索引、簇集索引）和非聚集索引（nonclusteredindex，也称非聚类索引、非簇集索引）。举例来说明一下聚集索引和非聚集索引的区别:（汉语字典为例）1.汉语字典的正文本身就是一个聚集索引2.把目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。1、何时使用聚集索引或非聚集索引、何时使用聚集索引或非聚集索引下面的表总结了何时使用聚集索引或非聚集索引（很重要）：我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如：返回

4、某范围内的数据一项。比如您的某个表有一个时间列，恰好您把聚合索引建立在了该列，这时您查询2003年1月1日至2006年12月1日之间的全部数据时，这个速度就将是很快的，因为您的这本字典正文是按日期进行排序的，聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可；而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。2、结合实际，谈索引使用的误区、结合实际，谈索引使用的误区1、主键就是聚集索引通常，我们会在每个表中都建立一个ID列，以区分每条数据，并且这个ID列是自动增大的，步长一般1,把它设为主键，SQL SERVER会将此列默认为聚集索引，这样做有好处，

5、就是可以让您的数据在数据库中按照ID进行物理排序，但是这样做的意义不大。 从我们前面谈到的聚集索引的定义我们可以看出，使用聚集索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。在实际应用中，因为ID号是自动生成的，我们并不知道每条记录的ID号，所以我们很难在实践中用ID号来进行查询。这就使让ID号这个主键作为聚集索引成为一种资源浪费。2、只要建立索引就能显著提高查询速度事实上，我们可以发现上面的例子中，第2、3条语句完全相同，且建立索引的字段也相同；不同的仅是前者在OrderDate字段上建立的是非聚合索引，后者在此字段上建立的是聚合索引，但查询速度却有着天壤之别。所以，并非

6、是在任何字段上简单地建立索引就能提高查询速度。 3、其他注意事项“水可载舟，亦可覆舟”，索引也一样。索引有助于提高检索性能，但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引，数据库就要做更多的工作。过多的索引甚至会导致索引碎片三三.使用数据库分区表使用数据库分区表什么是分表区什么是分表区答：表分区分为水平分区和垂直分区；水平分区将表分为多个表。每个表包含的列数相同，但是行更少；垂直分区将原始表分成多个只包含较少列的表；水平分区是最常用分区方式，后面我们以水平分区来介绍具体实现方法。什么情况下需要分区什么情况下需要分区:答：数据库中某个表中的数据量大并且数据是分段式(比如一个表中

7、的数据是以时间段分隔)的情况下使用分区。1.如何创建分区表如何创建分区表创建分区表必须要经过下面五个步骤：1）创建文件组2）创建文件3）创建分区函数4）创建分区方案5）创建分区表（1）创建文件组创建文件组，有两种方案，一种是通过手动添加，另外一种就是通过SQL脚本进行添加。方案一：通过手动添加方案一：通过手动添加打开SQLServerManagementStudio，找到分区表所在的数据库，右键单击选择“属性”，选择“文件组”选项，单击下面的“添加”按钮，添加X个文件组，如下图所示：方案二方案二:通过通过SQL脚本进行添加脚本进行添加ALTERDATABASEPerformanceADDFIL

8、EGROUPPERFG2002ALTERDATABASEPerformanceADDFILEGROUPPERFG2003.（2）创建数据库文件有两种方案，一种是通过手动添加，另外一种就是通过SQL脚本进行添加。方案一：通过手动添加方案一：通过手动添加打开SQLServerManagementStudio，找到分区表所在的数据库，右键单击选择“属性”，选择“文件组”选项方案二方案二:通过通过SQL脚本进行添加脚本进行添加ALTERDATABASEPerformanceADDFILE(NAME=NSellLogDetail2010,FILENAME=NF:学习SqlServer分享DBPerLog

9、DetailSellLogDetail2010.ndf,SIZE=3072KB,FILEGROWTH=1024KB)TOFILEGROUPPERFG2002.在添加文件的时候要注意以下几点：在添加文件的时候要注意以下几点：1、不要忘记将不同的文件放在文件组中。当然一个文件组中也可以包含多个不同的文件。2、如果可以的话，将不同的文件放在不同的硬盘分区里，最好是放在不同的独立硬盘里。要知道IQ的速度往往是影响SQLServer运行速度的重要条件之一。将不同的文件放在不同的硬盘上，可以加快SQLServer的运行速度。(3）创建分区函数创建分区函数的目的是告诉SQLServer以什么条件对分区表进行

10、分区，这一步必须要什么SQL脚本来完成：CREATEPARTITIONFUNCTIONPartFun_per(datetime)ASRANGERIGHTFORVALUES(2002,2003,2004,2005,2006)1、CREATEPARTITIONFUNCTION意思是创建一个分区函数。2、PartFun_per为分区函数名称。3、ASRANGERIGHT为设置分区范围的方式为Right，也就是右置方式。4、FORVALUES(2002,2003,2004,2005,)为按这些个值来分区，Values中的值就是分区的条件（4）创建分区方案分区方案的作用是将分区函数生成的分区映射到文件组

11、中去。分区函数的作用是告诉SQLServer，如何将数据进行分区，而分区方案的作用则是告诉SQLServer将已分区的数据放在哪个文件组中。-4.创建分区方案CREATEPARTITIONSCHEMEPartsch_perASPARTITIONPartFun_perTO(PERFG2002,PERFG2003,PERFG2004,PERFG2005,PERFG2006,PERFG2006)1、CREATEPARTITIONSCHEME意思是创建一个分区方案。2、Partsch_per为分区方案名称。3、ASPARTITIONpartfun_CX说明该分区方案所使用的数据划分条件（也就是所使用的

12、分区函数）为PartFun_per。4、TO后面的内容是指PartFun_per分区函数划分出来的数据对应存放的文件组。到此为止，分区函数和分区方案就创建完毕了。创建后的分区函数和分区方案在数据库的“存储”中可以看到（5）创建分区表创建分区表，创建方式和创建普遍表类似，如下所示：-5.创建分区表(新表)CREATETABLEOrders_tn(OrderIDintIDENTITY(1,1)NOTNULL,CustIDchar(11)NOTNULL,EmpIDINTNOTNULL,ShipperIDvarchar(5)NOTNULL,OrderDateDATETIMENOTNULL,Filler

13、CHAR(155)NOTnull)ONPartsch_per(OrderDate)注：ONPartsch_per(OrderDate)是：将创建的表引用分区方案四四.提高数据库查询效率的实用方法提高数据库查询效率的实用方法1.对查询进行优化，应尽量避免全表扫描，首先应考虑在where及orderby涉及的列上建立索引2.应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如:selectidfromtwherenumisnull可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：selectidfromtwherenum=03.

14、应尽量避免在where子句中使用!=或操作符，否则将引擎放弃使用索引而进行全表扫描4.应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：selectidfromtwherenum=10ornum=20可以这样查询：selectidfromtwherenum=10unionallselectidfromtwherenum=205.in和notin也要慎用，否则会导致全表扫描，如：selectidfromtwherenumin(1,2,3)对于连续的数值，能用between就不要用in了：selectidfromtwherenumbetween1and3

15、6.下面的查询也将导致全表扫描：selectidfromtwherenamelike%abc%7.应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：selectidfromtwherenum/2=1008.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描9.任何地方都不要使用select*fromt，用具体的字段列表代替“*”，不要返回用不到的任何字段。10.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。11.在所有的存储过程和触发器的开始处设置SETNOCOUNTON，在结束时设置SETNOCOUNTOFF。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC消息。12.在使用索引字段作为条件时，如果