sql server单表数据量与查询速度 sql server单表存储数据量

转载

云中谁寄锦书来 2024-06-10 01:07:52

文章标签 数据库 SQL Server 性能调优 文章分类 SQL Server 数据库

上周通过探讨SQL Server如何执行一个查询奠定了基础。我也在那里提到页是8kb的缓存。今天我们对页进行进一步集中探讨，从性能调优角度挖掘出更多的细节。

页是SQL Server的基础，在SQL Server里一切都与页有关。当我们想提高查询性能时，我们可以减少SQL Server指定查询所需页的读取。在第二个月当我们讨论索引时，我们发现其实索引的结构也是由页组成的。当你不知道页是什么的时候，你就不能对SQL Server进行调优和故障排除。

数据页结构

在SQL Server中页的大小始终是8kb的大小，页有不同的类型：数据页，索引页，系统页等等。今天我们对在SQL Server存储我们表数据的数据页进行更多细节的学习。一个数据页总是由三个部分组成：

页头（Page Header）
数据区（Payload）
行偏移数组（Row Offset Array）

在SQL Server中页头的始终是96 byte长（不受页的类型约束），这里存储着像Page ID，Object ID等页的大体信息。数据区是页中最有意思的部分，因为我们的记录就存在那里。SQL Server给你8192 bytes（8kb）的空间，其中8096 bytes是给数据区的。因此计算多少条记录刚好可以填满一个页是个很容易的事，直接拿8096除以记录长度即可（这里包含至少7 bytes的内部行开销）。当你把结果取整下，你就得到在一页里你可以存放多少条记录。

SQL Server中对页操作必须是整页读或写的，因此我们的目标总是希望在一页里存放尽可能多的记录。SQL Server不能从你存储里读页的一部分，或者把页的一部分写入存储。I/O操作始终最少都是在页级别完成的。

最后在页的底部你会看到被称作行偏移数组的东西。行偏移数组用2 bytes存储着每条记录在页里位置的偏移量。第一条记录始终开始与96的偏移，刚好紧接着页头。下图可以给你刚才介绍的数据页结构的概况认识。

sql server单表数据量与查询速度 sql server单表存储数据量_SQL

深入解析数据页

我们来看一个简单的表定义：

1 CREATE TABLE Customers
 2 (
 3    FirstName CHAR(50) NOT NULL,
 4    LastName CHAR(50) NOT NULL,
 5    Address CHAR(100) NOT NULL,
 6    ZipCode CHAR(5) NOT NULL,
 7    Rating INT NOT NULL,
 8    ModifiedDate DATETIME NOT NULL,
 9 )
10 GO

对于这样一个表定义我们很容易计算出在一页里我们可以存放几条记录。这里记录的大小是224 bytes长（50+50+100+5+4+8+7)。8096 / 224 = 36.14，也就是说在一页你最多能存放36条记录。那其他剩余的空间——在这里是32 bytes（8096-224*36）就浪费掉了，因为数据页只能属于一个指定的数据库对象，且不能与其他对象共享。最坏的情况，当你的表定义了一条长度为4031bytes 的记录时，在每一页你都在浪费4029 bytes的空间。当你用像VARCHAR等变长类型定义字段时，情况会发生改变，因为SQL Server允许变长列存放在不同的页。

如果你想知道在你数据库设计后，每页有多少空间浪费掉，你可以通过下列动态管理视图(DMV）查询下缓冲池：sys.dm_os_buffer_descriptors 从这个动态管理视图（DMV）显示的每条记录都代表当前在缓存池里保存的每一页，当你在有大内存的机器上查询这个动态管理视图时要注意了，这个操作很耗内存。free_space_in_bytes 列告诉你当前页有多少空间是空闲的。下面这个查询可以告诉你在SQL Server里每个数据库有多少空间被浪费：

1 SELECT
2    DB_NAME(database_id),
3    SUM(free_space_in_bytes) / 1024 AS 'Free_KB'
4 FROM sys.dm_os_buffer_descriptors
5 WHERE database_id <> 32767
6 GROUP BY database_id
7 ORDER BY SUM(free_space_in_bytes) DESC
8 GO

这个是我在系统里经常执行的查询（例如在做SQL Serve健康检查时），为了找出哪个数据库有糟糕的表设计。