大家好,这是我在博客园写的第一篇博文,之所以要开这个博客,是我对MS
SQL技术学习的一个兴趣记录。

SQL
Server方面的博客文章也陆陆续续的写了不少了,顺便也将这些知识点整理、归纳一下下。方便自己和他人查看。

 本文是对 SQL Server 查询性能优化——堆表、碎片与索引(一)的一些总结。

     
作为计算机专业毕业的人,自己对技术的掌握总是觉得很肤浅,博而不专,到现在我才发现自己的兴趣所在,于是我通过网络找了各种MS
SQL技术的相关文档,总觉得讲得比较干涩,没有一个系统性,今年3月底我无意浏览到一个网站提供免费的性能调优的半年培训(
SERVER的理解已经开始深入,所以我觉得应该把这些材料共享给大家,顺便对自己学习MS
SQL技术一个记录。

 

 第一:先对 SQL Server 查询性能优化——堆表、碎片与索引(一)中的例一的SET STATISTICS IO之后出现的关键信息如下

    
因为我觉得,很多东西只有自己对别人讲解一遍,才会加深自己的理解,顺便提升自己。

MS
SQL 数据类型

表 ‘T_EPZ_INOUT_ENTRY_DETAIL’。扫描计数 1,逻辑读 4825 次,物理读 6 次,预读 19672 次。

     以下开始是我的学习材料分享,这个是第0篇,总论:

 

这句解释一下。(有些内容来自网络,有些内部是自己的理解。)

  • 第1个月:数据库基础

三大数据库对比研究系列——数据类型

 

              在第1个月我们会通过讨论在SQL SERVER中的基本概念,建立SQL
SERVER的基础,。在第一个4星期里我们会学到:

 

SQL SERVER 数据库引擎当遇到一个查询语句时,SQL SERVER数据库引擎会分别生成执行计划(占用CPU和内存资源),同时存储引擎读取 IAM 以生成必须要读取的磁盘地址排序列表。这使 SQL Server 得以将其 I/O 优化为大型有序读取,根据它们在磁盘上的位置按顺序完成。磁盘中取得需要取的数据(占用I/O资源,这就是预读),注 意,两个步骤是并行的,SQL SERVER通过这种方式可以让计算和 I/O 重叠进行,从而充分利用 CPU 和磁盘,从而提高性能。

    • 第1周 SQL SERVER 如何执行一个查询
    • 第2周 _SQL Server 中数据存储的基本单位
    • 第3周 _SQL Server中管理空间的基本单位
    • 第4周 页面限制8060 bytes
  • 第2个月:索引

  • 第3个月:执行计划
  • 第4个月:统计信息
  • 第5个月:锁,阻塞,死锁
  • 第6个月:性能监控与故障排除

MS
SQL 表和视图

 

 

 

扫描计数:查询数据时对涉及到的表被 访问次数或涉及到的索引的扫描次数。在我们的例子中,不管是表扫描(例一与例二)还是索引扫描(例三)其中的表或索引都只被访问了1次,由于查询中不包括连接命令,这一信息并不是十分有用,但如果查询中包含有一个或多个连接,则这一信息是十分有用的。
  一个循环外部的表的扫描计数值为1,但对于一个循环内的表而言,其值为循环的次数。可以想象得到,对于一个循环内的表而言,其 扫描计数值越小,它所使用的资源越少,查询的性能也就越高。因此在调节一个带连接的查询的性能时,需要关注扫描计数的值,在进行调节 时,注意观察它是增加还是减少了。

数据库表的基本信息,你知道吗?

逻辑读: 这是SET STATISTICS IO或SET STATISTICS TIME命令提供的最有用的数据。我们知道,SQL Server在对任何数据进行操作前,必须首先从磁盘中读取数据所在的数据页或索引页,并把数据页或索引页存到数据缓冲区高速缓存中。
  那么逻辑读的意义是什么呢?逻辑读是指SQL Server为得到查询中的结果而必须从数据缓冲区高速缓存读取的页数。在执行查询时,SQL Server不会读取比实际需求多或少的数据, 因此,当在相同的数据集上执行同一个查询,得到的逻辑读的数字总是相同的。
  为什么说在调节查询性能中知道SQL Server执行查询时的逻辑读值是很重要的呢?因为在每次执行同一查询时,这个数值是不会变化的。因此,在进行查询性能的调节时,这是一个可以用来衡量你的调节措施是否成功的一个很好的标准。
  在对查询的性能进行调节时,如果逻辑读值下降,就表明查询使用的服务器资源减少,查询的性能有所提高。如果逻辑读值增加,则表示调节措施降低了查询的性能。在其他条件不变的情况下,一个查询使用的逻辑读越少,其效率就越高,查询的速度就越快。

数据查询表,列名对比

物理读:在这里我要说的的东西可能初听起来有点自相矛盾,但只要反复思考,就会明白其中的真正含意。
  物理读指的是,在执行真正的查询操作前,SQL Server必须从磁盘上向数据缓冲区高速缓存中读取它所需要的数据。在SQL Server开始执行查询前,它要作的第一件事就是检查它所需要的数据是否在数据缓冲区高速缓存中,如果在,就从中读取,如果不在,SQL Server必须首先将它需要的数据从磁盘上读到数据缓冲区高速缓存中。
  我们可以想象得到,SQL Server在执行物理读时比执行逻辑读需要更多的服务器资源。因此,在理想情况下,我们应当尽量避免物理读操作。
  下面的这一部分听起来让人容易感到糊涂了。在对查询的性能进行调节时,可以忽略物理读而只专注于逻辑读。你一定会纳闷儿,刚才不是还说物理读比逻辑读需要更多的服务器资源吗?
  情况确实是这样,SQL Server在执行查询时所需要的物理读次数不可能通过性能调节而减少的。减少物理读的次数是DBA的一项重要工作,但它涉及到整个服务器性能的调节,而不仅仅是查询性能的调节。在进行查询性能调节时,我们不能控制数据缓冲区高速缓存的大小或服务器的忙碌程度以及完成查询所需要的数据是在数据缓冲区中还是在磁盘上,唯一我们能够控制的数据是得到查询结果所需要执行的逻辑读的次数。
  因此,在查询性能的调节中,我们可以心安理得地不理会SET STATISTICS IO命令提供的物理读的值。(减少物理读次数、加快SQL Server运行速度的一种方式是确保服务器的物理内存足够多。)

MS SQL 建表SQL的脚本

预读:与 物理读一样,这个值在查询性能调节中也没有什么用处。预读表示SQL Server在执行预读机制时从磁盘上读取的数据页或索引页。为了优化其性能,SQL Server数据引擎首先预测执行查询执行计划所需的数据和索引页,然后在查询实际使用这些页之前将它们读入缓冲区高速缓存。根据SQL Server对数据 需求预测的准确程度,预读的数据页可能有用,也可能没用。

查看数据库、表、索引的物理存储情况

备注:一个缓冲区就是一个 8KB 大小的内存页

慎用SELECT INTO复制表

 

 

以上文字大部分来源自网络,本人对其中部分不认同处,或有自己的理解处,做了少量修改。

MS
SQL 索引约束

 

[翻译] 聚集索引表 VS
堆表

 

SQL SERVER
中is null 和 is not null 将会导致索引失效吗?

 

SQL
SERVER中什么情况会导致索引查找变成索引扫描

第二:关于碎片对于性能影响的结论:

 

SQL Server 中数据存储的基本单位是页,一页包含8KB数据。磁盘 I/O 操作在页级执行。也就是说,SQL Server 读取或写入的基本单位是数据页。连续的8个页面组成一个区(extent)。数据的insert和update操作可以引起页面分割产生碎片。如果分割后的两个页面在同一个区内,这种碎片称为内部碎片,如果分割后的两个页面处于不同的区内,这种碎片称为外部碎片。

MS
SQL 死锁阻塞

一、内部碎片和外部碎片对数据检索性能都有负面影响。

 

1. 内部碎片的产生使数据稀疏的分布在大量的页面中,这增加了读取页面到内存中所需的磁盘I/O操作,增加了从内存中检索数据的逻辑读取数量。

SQL Server
中WITH (NOLOCK)浅析

2. 外部碎片导致磁盘上的索引页面不连续,新的叶子页面和原始叶子页面离得很远,物理顺序与逻辑顺序不同。为了更好的性能,首选顺序I/O,因为这能在一个磁盘I/O读取整个区(8个8KB页面)。非连续的页面需要非顺序或者随机I/O操作来从磁盘读取数据,一个随机I/O只能读取一个页面(8KB)。

SQL
SERVER错误:已超过了锁请求超时时段。 (Microsoft
SQL Server,错误:
1222)

 

 

二、在堆表中,当删除数据链中间的记录行时,会出现空页,随着空页的累积,区的利用率也会下降,从而出现内部碎片与外部碎片。带索引的表也有可能出现外部碎片,如在现有的聚集索引中插入一行,这行正好导致现有的页空间无法满足容纳新的行,从而导致了分页,如果分页后的两个页面正好分布在两个区,就是外部碎片。当有外部碎片存在,会出现以下问题:对表进行处理时,常常出现死锁;利用较大的I/O操作或增加I/O缓冲区的大小也无法改变较慢的I/O速度;行操作的争用。

MS
SQL 的触发器

 

MS
SQL 函数介绍

三、带有索引的表会由于插人记录而导致分页,但当删除记录后,页会获得释放.从而形成跨几个区的数据.而要访问该数据就必须遍历几个区,这将导致增加I/O操作,查询记录的时间大大延长,开始时数据库的性能虽然较高,但使用一段时间后就会发生性能下降等问题。实际上,数据在存储空间上排列得越紧密有序,SQL Server访问的速度就越抉,消除碎片有助于提高系统的性能和更有效地利用数据存储空间。(如例二,例二的访问速度就比例一要快)。

 

 

SQL SERVER
的模糊查询
LIKE

四、对于扫描部分或全部表的查询,这些表碎片会导致额外的页读取,这将防碍数据的并行扫描。

ROW_NUMBER ()
与 PARTITION 组合妙用

DATEDIFF函数小问题

重温SQL——行转列,列转行

SQL 2000自定义函数调用GETDATE()报错

SQL
SERVER特殊行转列案列一则

 

MS
SQL 存储过程

SQL
Server——存储过程

 

MS
SQL 动态SQL

 

动态SQL应用小列子

SQL Server
——动态SQL

 

MS
SQL 链接服务器

 

MS SQL 错误:The operation could not
be performed because OLE DB provider “SQLNCLI10” for linked server
“test” was unable to begin a distributed
transaction.

Linked Server: EXECUTE permission denied on object
‘xp_prop_oledb_provider’, database ‘master’, owner
‘dbo’

SQL SERVER
2012链接到SQL SERVER 2000的问题解决案例

sp_addlinkedserver ‘(null)’ is an invalid product
name

SQL SERVER 2012/2014
链接到 SQL SERVER 2000的各种坑

SQL
SERVER使用ODBC 驱动建立的链接服务器调用存储过程时参数不能为NULL值

 

MS
SQL Reporting Serverices

 

迁移Reporting Services的方法与WMI错误

SQL SERVER 2008
Reporting Services 的一些小问题集合

SQL Server Reporting
Services:无法检索应用程序文件。部署中的文件已损坏

Reporting Service
服务启动时报错The service did not respond to
the start or control request in a timely
fashion

SQL Server 2008
标准版不支持Reporting
Services的数据驱动订阅

SQL Server 2012 The report server cannot open a
connection to the report server
database

数据库服务器改名导致Reporting
Service不可用的案例

SSRS Reports
2008性能优化案例

Reporting Services
错误案例一则

SSRS ReportServer
Database 的Blocking问题

SSRS Reports
2008性能优化案例二

Reporting Service
告警”w WARN: Thread pool pressure. Using
current thread for a work
item”

SQL Server 2008 R2
升级到 Service Pack 3后Report
Builder启动不了

SSRS 2008
ReportServerTempDB增长异常分析

 

MS
SQL 作业管理

 

SQL SERVER
作业浅析

SQL
SERVER作业的Schedules浅析

SQL SERVER
2005删除维护作业报错:The DELETE statement
conflicted with the REFERENCE constraint
“FK_subplan_job_id”

作业配置规范文档[MS
SQL]

Unable to determine if the owner (Domain\UserName) of
job JOB_NAME has server
access

SQL SERVER
中如何用脚本管理作业

TCP Provider The semaphore timeout period has
expired

 

MS
SQL 连接会话

Provider:SSL
Provider,error:0-等待的操作过时

 

MS
SQL 命名规范

 

MS SQL开发命名规则

T-SQL 编码标准【转帖】

 

MS
SQL 脚本美化

 

相关文章