【转】性能分析—查询运行慢的原因(SQLServer2008宝典)

https://www.cnblogs.com/weifb/articles/3999273.html

影响查询语句执行性能的常见因素

1.网络通信的速度较慢或者网络通信不稳定。

2.数据库服务器的内存不足，或者没有足够的内存供SQL Server使用。

3.Select的语句设计不合理。

4.数据库结构的设计不合理，导致数据库中存在大量重复数据。

5.没有创建索引或者索引的设计不合理。

6.没有创建有效的索引视图。

7.索引列上缺少有用的统计信息或者统计信息过期。

8.缺少有效的分区设计

如何定位导致查询运行慢的原因

1.首先可以判断影响性能的因素是否与查询语句相关，还是由于数据库服务器的硬件配置造成的。

2.如果确定影响性能的因素与查询语句相关，则可以使用SQL Server profile对SQL Server进行跟踪，通过对跟踪数据的分析，确定执行时间较长或占用系统资源较多的查询语句。

3.在确定了影响数据库性能的查询语句后，可以通过显示计划来了解执行查询语句的过程，通过对查询语句执行过程的分析，或者通过SQLServer数据库引擎优化顾问来分析查询语句可以优化的地方。（参照SqlServer2008学习笔记：日常维护、深入管理、性能优化.pdf的第5章）

4.判断是否使用了有效的统计信息。SQLServer可以自动统计表中各列上值的分布情况，定时根据表中数据的情况更新统计信息，有助于提高查询语句的执行效率。

5.确认表的索引设计是否合理。有效的使用和设计索引可以大大提高查询效率。（参照SqlServer2008学习笔记：日常维护、深入管理、性能优化.pdf的第5章）

6.对于数据量很大的表，可以考虑对表进行分区，将表中的数据分散在不同的分区中，从而缩小查询数据的范围，提高查询效率。（数据分区，参照第19章介绍）。

Select语句优化

在Select语句中应该尽可能不返回多余的数据，需要从行和列两方面进行优化，控制尽量少的输出数据。

分区技术的分类

可以将大型表拆分成更小的单个的表，也可以将表放置在不同的磁盘驱动器上，从而实现分区技术。

1.硬件分区：可以通过增加冗余的硬件设备，将数据库存储和查询任务分配到不同的硬件设备上，构建高效的数据库硬件体系结构。

2.水平分区：水平分区将表分成多个表，每个表中包含的数据列数相同，只是将原来的结果集以行的单位进行了拆分。例如，按照时间进行分区也是一种常用的分区方法。

3.垂直分区：垂直分区也是将表分成多个表，单每个表包含的行数相同，只是将原表的结果集以列进行拆分。对于包含列数较多的大型表而言，如果需要经常访问其中的几列数据，则可以对其进行垂直分区，从而减少分区表中数据的数量，提高查询效率。

分区表技术解析：对于大型表而言，它的索引数据量也会变得很大，仅依靠创建索引来优化查询是不够的。分区可以将数据细分到不同的区域中，使每个区域的数据更少、更易维护。在分区表上创建的索引页会体现在各个分区中，从而减少了分区索引所需要维护的数据量，更好的提高查询的效率。现在数据库服务器中通常会配置多个CPU，在分区表上执行查询语句时，每个CPU可以独立地对每个分区中的数据进行查询，然后再将结果集合并。因此，在配置多个CPU的数据库服务器上，对大型表进行分区能更好的利用系统的硬件资源，提高数据库的工作效率。

分区表采用水平分区技术，将数据按某种规则划分成不同的区域，保存在不同的文件组中。通常在同时满足下面两种情况时需要使用分区表。

1.表中已经存在大量的数据，或者预计表中将会保存大量的数据。

2.不能按预期对表中的数据执行查询或更新。例如，对当前月份的数据主要执行insert、update、delete等操作，而对以前月份的数据主要执行Select操作，此时可以按照月份创建分区表。

事例：以信用卡刷卡消费为例介绍创建和使用分区表的方法。因为银行的信用卡拥有大量的客户，他们在刷卡消费时，会产生大量的数据，保存在银行的后台数据库中，如果不对数据库进行任何优化，在对这些数据进行查询和统计时将非常的慢，同时会占用大量的系统资源。

解决方案：创建数据库Credit，然后创建表Consume2009，用于保存2009年所有信用卡刷卡消费的数据。可以在分区表上创建索引，称为分区索引。在对表或索引分区前，需要创建两个数据库对象，即分区函数和分区方案。

分区函数用于定义根据指定的列（分区依据列）的值将表或索引中的记录映射到不同的分区中。例如：在表Consume可以把ConsumeDate列作为分区依据列，将不同月份的消费记录保存在不同的分区中。分区方案将分区函数指定的每一个分区映射到文件组上。

确定分区依据列和分区数

在创建分区表和分区索引之前，要确定分区的方案，首先需要确定分区的依据和分区数量，例如，按照信用卡消费的月份对数据库Credit中的表Consume2009进行分区，可以将Consume2009表分为12个区。

Sql Server规定一张表最多只能有1000个分区。

创建文件组

在创建分区表之前，需要创建数据库的文件组。建议创建与分区数量相同的文件组，在条件允许的情况下，可以将不同的文件组放置在不同的磁盘上，从而提高查询大型表的效率。可以使用 ALTER DATABASE <数据库名> ADD FILEGROUP <文件组名> 语句创建文件组名

以上面的Credit数据库为例。

ALTER DATABASE Credit ADD FILEGROUP filegroup1

ALTER DATABASE Credit ADD FILEGROUP filegroup2

ALTER DATABASE Credit ADD FILEGROUP filegroup3

ALTER DATABASE Credit ADD FILEGROUP filegroup4

ALTER DATABASE Credit ADD FILEGROUP filegroup5

ALTER DATABASE Credit ADD FILEGROUP filegroup6

ALTER DATABASE Credit ADD FILEGROUP filegroup7

ALTER DATABASE Credit ADD FILEGROUP filegroup8

ALTER DATABASE Credit ADD FILEGROUP filegroup9

ALTER DATABASE Credit ADD FILEGROUP filegroup10

ALTER DATABASE Credit ADD FILEGROUP filegroup11

ALTER DATABASE Credit ADD FILEGROUP filegroup12

文件组创建好之后就可以向文件组中添加文件

其他文件组中添加文件依次类推。

执行完上面的脚本后，打开数据库Credit的属性对话框，在"选择页"列表中选择"文件"，可以查看到新建的12文件以及他们所属的文件组。

分区技术应用场景

在需要使用分区技术的数据库中，数据库管理员的主要任务是合理规划和创建分区表，并创建分区索引。可以使用向导创建分区表，但通常建议使用CREATE PARTITION FUNCTION 命令和CREATE PARTITION SCHEME命令来创建分区函数和分区方案，因为这样还可以日后重复使用。在维护采用分区技术的数据库时，有时需要对分区执行合并和分区操作

使用CREATE PARTITION FUNCTION语句创建分区函数

分区函数的功能是指定如何对表或索引进行分区，它可以将数据映射到一组分区上。在分区函数中需要指定分区数量、分区依据列和每个分区的分区依据列范围。分区依据只能是一列。可以使用CREATE PARTITION FUNCTION 语句创建创建分区函数，其基本语法结构如下：

语法语句解析：

AS RANGE 子句指定在对表中数据进行分区时，数据库引擎按升序从左向右排序的情况下，每个指定的分区边界值属于左侧分区还是右侧分区。

FOR VALUES子句指定分区的边界间隔值。

在CREATE PARTITION FUNCTION语句中不需要指定具体的分区列，分区列由CREATE TABLE 语句或CREATE INDEX 语句在创建分区表或分区索引时定义。换言之，分区函数只是定义了分区的方法，此方法具体应用在哪个表的哪个列上，则需要在创建表或索引时指定。例如：创建分区函数Consume2009PartitionFunction1,按销售月份对销售数据进行分区，语句内容如下

使用DROP PARTITION FUNCTION 语句删除分区函数

可以使用DROP PARTITION FUNCTION 语句删除分区函数，其基本语法结构如下：

DROP PARTITION FUNCTION <函数名>，注意只有在没有分区方案使用该分区函数时，才能对其进行删除。

使用CREATE PARTITION SCHEME 语句创建分区方案

创建分区函数后，必须将其与指定的分区方案向关联。分区方案用于指定分区对应的文件组，即使多个分区同时位于一个文件组，也需要分别为每个分区定义所属的文件组。

可以使用CREATE PARTITION SCHEME 语句创建分区方案，具体语句结构如下：

CREATE PARTITION SCHEME <分区方案名>

AS PARTITION <分区函数名>

TO(文件组名1，文件组名2……)

例如：为分区函数Consume2009PartitionFunction1指定对应的文件组，可以使用下面的语句。

CREATE PARTITINO SCHEME Consume2009PartitionScheme1

AS PARTITION Consume2009PartitionFunction1

TO (FileGroup1,FileGroup2,FileGroup3,FileGroup4,FileGroup5,FileGroup6,FileGroup7,FileGroup8,FileGroup9,FileGroup10,FileGroup11,FileGroup12)

使用DROP PARTITION SCHEME 语句删除分区方案