SQLServer执行SQL语句时内存占用特点

众所周知，SQL Server在执行SQL语句时的性能准则主要是IO读取的大小。在不违背这一原则的情况下，本文分析了一些SQL语句执行时SQL Server内存的变化。

首先简单描述一下SQL Server内存占用的特点。SQL Server占用的内存除了程序(即SQL Server引擎)之外，主要包括缓存数据(缓冲区)和执行计划(缓存)。SQL Server将数据存储在8KB的页面中。此SQL Server数据在磁盘上的存储页面大小相同。当SQL Server执行SQL语句时，如果所需数据已经在其内存中，则直接从内存缓冲区中读取，执行必要的操作，然后输出执行结果。如果数据不在内存中，首先将数据从磁盘读入内存缓冲区。但是我们通常评价SQL性能指标中的IO逻辑读取数对应的是从内存缓冲区读取的页数，而IO物理读取数对应的是从磁盘读取的页数。

注意:下面这个实验也可以在多人共享的开发测试服务器上进行，因为你可以单独实际看到某个表占用的内存。但是为了方便，作者在单独的数据库上做了这个实验，证实了没有其他并发任务，所以他看到的内存变化是由每次执行的SQL语句引起的。

我们先来看一个简单的例子。创建下表:

下面是引用的片段:
create table p _ user
(usermobilestatus int not null，
mobile no int not null，
la stop time datetime not null
)

然后为该表插入一些数据:

以下是一段引用片段:
declare @ I int
set @ I = 28000
while @ I begin
insert into p _ user
select @ I % 2，@ I，getutcdate () [

然后我们首先在查询分析器中执行:

以下是引用的片段:
Set Statistics IO ON

并按Ctrl+M显示实际的执行计划。

至此，我们可以开始我们的实验了。为了准确观察每条SQL语句的变化，在执行第一条SQL语句之前，我们先清空空SQL Server占用的数据内存:

以下是引用片段:
check point
go
dbcc dropcleanbuffers

这将清除空SQL Server占用的数据缓冲区(在生产服务器上应谨慎使用此语句，因为它会在一段时间内降低后续SQL语句的执行速度)。
测试1:在没有索引的表上执行SQL语句

1.1执行全表选择或低选择性选择。

Select * From P_User

从SQL执行计划中可以看出，将生成表扫描，因为此时表中没有索引。和IO统计结果如下:

(受影响的1000行)

表' P_User '。扫描1次，逻辑读取4次，物理读取4次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。

让我们来看看数据库内存的情况。

首先，查询我们正在操作的数据库的database_id:

以下是一段引号:
select database _ id from sys . databases其中name =' testgdb '

然后使用这个database_id从表中查看内存:

以下是引用片段:
select * from sys . DM _ OS _ buffer _ descriptors BD
其中database _ id = 5
order by allocation _ unit _ id，page _ id。

结果如下:

从获得的结果可以看出，除了必要的管理页面(一个PFS_Page和一个IAM_Page)之外，内存中还有四个Data_Page页面。这和刚才在IO统计中看到的结果是一样的:逻辑读数是4，物理读数是4。因为是全表读取，所以表示P_User表中所有数据占用的数据页数正好是4。将这4个数据页的row_count数相加也可以验证总数据行数=1000。

在上面的例子中，如果空数据缓冲区没有清空，再次执行SQL，可以看到内存没有变化，逻辑读取也没有变化，只是物理读取变成了0，因为不再需要从磁盘读取数据。

1.2执行高选择性选择。

此外，如果将上面的示例修改为:

以下是报价片段:
Select Top 1 * From P_Order或select * from p _ order其中mobile no = 28502。

如您所见，系统还必须将所有数据页读入内存。

如果从移动编号= 28502的p _ order中选择top 1 *，则只能将一些数据页读入内存。但是如果没有索引，数据实际上是无序的存储在堆上，所以结果很不稳定，可能会出现所有数据页都读入内存的情况。

2.测试:聚簇索引建立后，执行SQL语句
2.1执行全表选择或低选择性选择。

修改表结构并在MobileNo字段上建立聚集索引。然后再次执行刚才的SQL语句。生成的执行计划成为聚集索引扫描。IO统计消息是:

(受影响的1000行)

表' P_User '。扫描1次，逻辑读取6次，物理读取1次，提前读取4次，lob逻辑读取0次，lob物理读取0次，lob提前读取0次。

这里的逻辑读数变成了6倍。

记忆如下:

内存的变化是添加了一个非叶级聚集索引页，而叶级聚集索引将与数据放在一起。

此外，您可以查看表索引的级别:

以下为引用片段:
select database _ id，object _ id，index _ id，index _ level，page _ count，record _ count
FROM sys . DM _ DB _ index _ physical _ stats
(DB _ ID(N ' test gdb ')，OBJECT_ID(N'dbo.P_User ')，NULL，NULL，' DETAILED ')；

从结果中可以看出，该表的聚集索引分为2级。

因此，逻辑读取增加了2——(由于聚集索引扫描，除了位于根级别的聚集索引页占用一次之外，位于根级别到叶级别的聚集索引也会额外占用一次逻辑读取)。

另一个变化是只有一次物理读取，即读取根级别的聚集索引页，其他四个数据页通过预读取而不是物理读取从磁盘加载到内存缓冲区。当存在聚集索引时，这使得执行SQL的直接成本实际上更低。

2.2进行高选择性选择。

在构建聚集索引的情况下，对性能有益的更改包括:

对于Select Top 1 * From P_Order或Select * From P _ Order WHERE MOBILE NO = 28702这样的语句，如果有聚集索引，则只有最后一条记录所在的页才会被读入内存。

3.测试:建立非聚集索引时执行SQL语句。

3.1执行全表选择或低选择性选择。

如果将表中同一字段的聚集索引更改为非聚集索引，可以看到以下特征:

执行全表扫描将类似于没有任何索引的情况，所有数据页都将被读入内存。此时，SQL Server的查询引擎实际上不能使用非聚集索引。

3.2执行高选择性选择。

只有最终数据所在的页面会被读入内存。根据查询计划，SQL Server对非聚集索引使用索引查找，然后通过查找获取实际数据行(索引覆盖的情况除外，因为不需要定位实际数据行)。

测试:执行嵌套循环连接

测试前，我们再准备一份表格和数据。

下面是引用的片段:
create table p _ order
(用户状态int not null，
移动no int not null，
sid int not null，
最新日期时间
)

插入数据:

下面是一个引用片段:
declare @ I int
set @ I = 20000
while @ I begin
insert into p _ order
select @ I % 2，@ I，@ I-19999

可以看出，在全表扫描的情况下，该表10000条数据共占用38个内存数据页。

4.1执行全表选择或低选择性选择。

以下是引用片段:
select * from p _ order a
内循环join p _ user b on a . mobile no = b . mobile no

对于这种高度选择性的选择，默认情况下，SQL Server不会执行循环联接。因此，使用了强制连接提示。

在两个表都没有索引的情况下，您可以看到:

两个表的所有数据页都将被加载到内存中。逻辑读取代价高达60000次——P _ Order表中的每一条记录都会在P_User表中被遍历。

当其中一个表有聚集索引时，尽管逻辑读取次数与之前60，000次相比已经大大减少，但仍然达到20，000次。此外，连接顺序对查询性能有很大的影响。因为实际执行是用SQL语句中前面的表作为join的外部输入，后面的表作为join的内部输入。

相比较而言，当两个表都有聚集索引时，逻辑读取的次数仍然达到数千次(取决于最终输出数据的大小)，但已经有了很大的改进。此外，只有表中最终需要输出的那部分数据会被读入内存缓冲区。
4.2执行高选择性选择

执行以下SQL语句:

下面是引用的片段:
select * from p _ order a
inner merge join p _ user b on a . mobile no = b . mobile no
其中a. mobileNo = 28913。

当两个表都没有索引时，两个表都将执行全表扫描。将所有数据页读入内存。总的逻辑读数取决于两个表中的数据页数。

当一个表有聚集索引或非聚集索引时，该表将执行索引搜索，而另一个表将进行全表扫描。在内存缓冲区中，一个表将只读取最终数据所在的数据页，一个表将读取所有数据页。逻辑读数取决于连接中表的顺序和未编制索引的表的数据页数。

当两个表都有聚集索引时，逻辑读取是最小的，每个表只有2或3次。并且只有实际需要输出的数据才会被读入内存页面。当两个表都有非聚集索引时，消耗的逻辑读取和内存资源是相似的。

测试:执行合并联接

5.1执行全表选择或低选择性选择。

执行SQL:

以下是引用片段:
select * from p _ order a
inner merge join p _ user b on a . mobile no = b . mobile no

如果两个表都没有索引，则应该用整个表扫描这两个表。所有数据都必须读入内存页面。

逻辑读数大约等于两个表的数据页之和。SQL Server处理中将使用临时表。

类似于只有一个表有聚集索引的情况，临时表将在SQL Server的处理中使用。并将所有数据页读入内存。

如果两个表都有聚集索引，虽然两个表的数据都会读入内存页，但是逻辑读取已经大大减少了，等于一个表的总数据内存页加上最终输出的数据页。此外，在SQL Server处理中将不需要临时表。

5.2执行高选择性选择。

对于这种高选择性的SQL语句，SQL Server会提示无法生成执行计划。

下面是引用的片段:
select * from p _ order a
inner merge join p _ user b on a . mobile no = b . mobile no
其中a. mobileNo = 28913。

但是您可以执行:

下面是一个引号片段:
select * from p _ order a
inner merge join p _ user b on a . mobile no = b . mobile no
其中a. mobileNo
是一个选择性低但最终结果很少的语句。如前所述，在这种情况下，使用net阶梯式循环连接可能更有效。
测试6:执行散列连接

6.1执行全表选择或低选择性选择。

对于两个表的联接，如果两个表都没有索引并且没有指定联接提示，默认情况下，SQL Server将使用哈希联接。对于两个表的联接，如果两个表都有聚集索引，默认情况下，SQL Server将使用合并联接。

执行SQL:

以下是引用片段:
select * from p _ order a
inner hash join p _ user b on a . mobile no = b . mobile no

在哈希联接的情况下，无论两个表是否有索引，所有数据页都将被读入内存，SQL Server将使用临时表进行处理。逻辑读数大约等于两个表的数据页之和。

6.2执行高选择性选择。

与merge join执行的高选择性选择类似，它不能直接执行:

下面是引用的片段:
select * from p _ order a
inner merge join p _ user b on a . mobile no = b . mobile no
其中a. mobileNo = 28913。

但是，执行这样一个低选择性的脚本可能不会产生什么结果:

下面是引用片段:
select * from p _ order a
inner merge join p _ user b on a . mobile no = b . mobile no
where a . mobile no
但是在这种情况下，使用netsted循环进行连接效率更高。

测试总结

这个测试的主要意义在于，通过分析具体的内存变化结合执行计划、IO读取等信息，可以更清晰的了解SQL Server执行SQL语句的过程。

此外，它还验证了通过分析SQL语句的IO读取和执行计划获得的一些经验:

(1)执行单表查询时，如果是高度选择的查询，应该建立非聚集索引或聚集索引(建议建立非聚集索引，非聚集索引独立于数据存储)。如果是低选择性查询，则需要建立聚集索引。

(2)执行连接查询时，如果最终输出结果较少，则宜使用嵌套循环连接；如果有许多输出结果，通过构建聚集索引和合并连接查询可以获得良好的性能。对于低性能的散列连接，通过转换为合并连接或嵌套循环连接来提高查询性能。