在SQL Server数据库中查询慢问题的解决思路及方案

数据库  /  管理员 发布于 2年前   1948

在SQL Server数据库中慢查询问题

1、没有索引或许没有用到索引 (这是查询慢最常见的问题，是程序规划的缺点)

2、I/O 吞吐量小，构成了瓶颈效应。

3、没有创立核算列导致查询不优化。

4、内存不足

5、网络速度慢

6、查询出的数据量过大 (可以选用屡次查询，其他的办法下降数据量)

7、锁或许死锁 (这也是查询慢最常见的问题，是程序规划的缺点)

8、sp_lock,sp_who，活动的用户检查，原因是读写竞赛资源。

9、过多不用要的行和列及数据

10、查询句子欠好，没有优化

可以经过以下办法来优化查询：

1、把数据、日志、索引放到不同的 I/O 设备上，添加读取速度，曾经可以将 Tempdb 应放在 RAID0 上，SQL2000 不在支撑。数据量 (尺度) 越大，进步 I/O 越重要。

2、纵向、横向切割表，削减表的尺度 (sp_spaceuse)

3、晋级硬件

4、依据查询条件，树立索引，优化索引、优化拜访办法，束缚成果集的数据量。留意填充因子要恰当 (是运用默许值 0)。索引应该尽量小，运用字节数小的列建索引好 (参照索引的创立)，不要对有限的几个值的字段建单一索引如性别字段。

5、进步网速。

6、扩展服务器的内存，Windows 2000 和 SQL Server 2000 能支撑 4-8G 的内存。

装备虚拟内存：
虚拟内存巨细应依据核算机上并发运转的服务进行装备。运转 Microsoft SQL Server 2000 时，
可考虑将虚拟内存巨细设置为核算机中装置的物理内存的 1.5 倍。
假如别的装置了全文检索功用，并方案运转 Microsoft 查找服务以便履行全文索引和查询，可考虑：
将虚拟内存巨细装备为至少是核算机中装置的物理内存的 3 倍。
将 SQL Server max server memory 服务器装备选项装备为物理内存的 1.5 倍
 (虚拟内存巨细设置的一半)。

7、添加服务器 CPU 个数；可是有必要理解并行处理串行处理更需求资源例如内存。

运用并行仍是串行程是 MSSQL 主动评价挑选的。单个使命分解成多个使命，就可以在处理器上运转。

例如 耽误查询 的排序、衔接、扫描和 GROUP BY 字句一起履行，
SQL SERVER 依据体系的负载状况决议的并行等级，
杂乱的需求耗费很多的 CPU 的查询最适合并行处理。
可是更新操作 UPDATE，INSERT，DELETE 还不能并行处理。

8、假如是运用 like 进行查询的话，简略的运用 index 是不可的，可是全文索引，耗空间。

like “a%” 运用索引 like “% a” 不运用索引证 like “% a%” 查询时，
查询耗时和字段值总长度成正比，所以不能用 CHAR 类型，而是 VARCHAR。
关于字段的值很长的建全文索引。

9、DB Server 和 APPLication Server 别离；OLTP 和 OLAP 别离

10、分布式分区视图可用于完成数据库服务器联合体。

联合体是一组分隔办理的服务器，但它们相互协作分管体系的处理负荷。
这种经过分区数据构成数据库服务器联合体的机制可以扩展一组服务器，
以支撑大型的多层 Web 站点的处理需求。
有关更多信息，拜见规划联合数据库服务器。(参照 SQL 帮助文件 “分区视图”)

a、在完成分区视图之前，有必要先水平分区表

b、 在创立成员表后，在每个成员服务器上界说一个分布式分区视图，而且每个视图具有相同的称号。

这样，引证分布式分区视图名的查询可以在任何一个成员服务器上 运转。
体系操作好像每个成员服务器上都有一个原始表的复本相同，
但其实每个服务器上只要一个成员表和一个分布式分区视图。
数据的方位对应用程序是通明的。

11、重建索引 

DBCC REINDEX ,DBCC INDEXDEFRAG，

缩短数据和日志 

DBCC SHRINKDB,DBCC SHRINKFILE

设置主动缩短日志。

关于大的数据库不要设置数据库主动添加，它会下降服务器的功用。

在 T-SQL 的写法上有很大的考究，下面列出常见的关键：

首要，DBMS 处理查询方案的进程是这样的：

1、查询句子的词法、语法检查
2、将句子提交给 DBMS 的查询优化器
3、优化器做代数优化和存取途径的优化
4、由预编译模块生成查询规划
5、然后在适宜的时刻提交给体系处理履行
6、*** 将履行成果回来给用户。

其次，看一下 SQL SERVER 的数据寄存的结构：

一个页面的巨细为 8K (8060) 字节，8 个页面为一个盘区，依照 B 树寄存。

12、Commit 和 rollback 的差异 

Rollback：
回滚一切的事物。
Commit：
提交当时的事物。
没有必要在动态 SQL 里写事物，假如要写请写在外面如：
begin tran exec (@s) commit trans 或许将动态 SQL 写成函数或许存储进程。

13、在查询 Select 句子顶用 Where 字句束缚回来的行数，防止表扫描，

假如回来不用要的数据，浪费了服务器的 I/O 资源，加剧了网络的担负下降功用。
假如表很大，在表扫描的期间将表锁住，制止其他的联接拜访表，后果严重。

14、SQL 的注释声明对履行没有任何影响

15、尽可能不运用光标，它占用很多的资源。假如需求 row-by-row 地履行，尽量选用非光标技能，如：在客户端循环，用暂时表，Table 变量，用子查询，用 Case 句子等等。

游标可以依照它所支撑的提取选项进行分类：
只进有必要依照从行到一行的次序提取行。
FETCH NEXT 是仅有答应的提取操作，也是默许办法。
可翻滚功用够在游标中任何当地随机提取恣意行。
游标的技能在 SQL2000 下变得功用很强壮，他的意图是支撑循环。
有四个并发选项 READ_ONLY：
不答应经过游标定位更新 (Update)，且在组成成果集的行中没有锁。
OPTIMISTIC WITH valueS：
达观并发操控是业务操控理论的一个规范部分。
达观并发操控用于这样的景象，即在翻开游标及更新行的距离中，
只要很小的时机让第二个用户更新某一行。
当某个游标以此选项翻开时，没有锁操控其间的行，这将有助于化其处理才能。
假如用户企图修正某一行，则此行的当时值会与一次提取此行时获取的值进行比较。
假如任何值产生改动，则服务器就会知道其他人已更新了此行，并会回来一个过错。
假如值是相同的，服务器就履行修正。
挑选这个并发选项 OPTIMISTIC WITH ROW VERSIONING: 
此达观并发操控选项依据行版别操控。运用行版别操控，其间的表有必要具有某种版别标识符，
服务器可用它来确认该行在读入游标后是否有所更改。
在 SQL Server 中，这个功用由 timestamp 数据类型供给，
它是一个二进制数字，表明数据库中更改的相对次序。
每个数据库都有一个大局当时时刻戳值：
@@DBTS。每次以任何办法更改带有 timestamp 列的行时，
SQL Server 先在时刻戳列中存储当时的 @@DBTS 值，然后添加 @@DBTS 的值。
假如某个表具有 timestamp 列，则时刻戳会被记到行级。
服务器就可以比较某行的当时时刻戳值和前次提取时所存储的时刻戳值，然后确认该行是否已更新。
服务器不用比较一切列的值，只需比较 timestamp 列即可。
假如应用程序对没有 timestamp 列的表要求依据行版别操控的达观并发，
则游标默许为依据数值的达观并发操控。
SCROLL LOCKS 这个选项完成失望并发操控。
在失望并发操控中，在把数据库的行读入游标成果集时，应用程序将企图确定数据库行。
在运用服务器游标时，将行读入游标时会在其上放置一个更新锁。
假如在业务内翻开游标，则该业务更新锁将一向坚持到业务被提交或回滚；
当提取下一行时，将除掉游标锁。假如在业务外翻开游标，则提取下一行时，锁就被丢掉。
因而，每逢用户需求彻底的失望并发操控时，游标都应在业务内翻开。
更新锁将阻挠任何其它使命获取更新锁或排它锁，然后阻挠其它使命更 新该行。
但是，更新锁并不阻挠同享锁，所以它不会阻挠其它使命读取行，
除非第二个使命也在要求带更新锁的读取。
翻滚锁依据在游标界说的 SELECT 句子中指定的锁提示，这些游标并发选项可以生成翻滚锁。
翻滚锁在提取时在每行上获取，并坚持到下次提取或许游标封闭，以先产生者为准。
下次提取时，服务器为新提取中的行获取翻滚锁，并开释前次提取中行的翻滚锁。
翻滚锁独立于业务锁，并可以坚持到一个提交或回滚操作之后。
假如提交时封闭游标的选项为关，则 COMMIT 句子并不封闭任何翻开的游标，
而且翻滚锁被保留到提交之后，以保护对所提取数据的阻隔。
所获取翻滚锁的类型取决于游标并发选项和游标 SELECT 句子中的锁提示。
锁提示 只读达观数值
* 指定 NOLOCK 提示将使指定了该提示的表在游标内是只读的。

16、用 Profiler 来盯梢查询，得到查询所需的时刻，找出 SQL 的问题所在；用索引优化器优化索引

17、留意 UNion 和 UNion all 的差异。UNION all 好

18、留意运用 DISTINCT，在没有必要时不要用，它同 UNION 相同会使查询变慢。重复的记录在查询里是没有问题的

19、查询时不要回来不需求的行、列

20、用 

sp_configure ‘query governor cost limit’

或许 

SET QUERY_GOVERNOR_COST_LIMIT 

来束缚查询耗费的资源。

当评价查询耗费的资源超出束缚时，服务器主动撤销查询，在查询之前就扼杀掉。

SET LOCKTIME 设置锁的时刻

21、用 

select top 100/10 Percent 

来束缚用户回来的行数或许 

SET ROWCOUNT 

来束缚操作的行

22、在 SQL2000 曾经，一般不要用如下的字句: 

“IS NULL”, “”, “!=”, “!>”, “!