Oracle Linux 7.1中安装MariaDB

在Oracle Linux 7.1中安装Mariadb 10.0.38,因为Mariadb是Mysql的分支,安装操作基本类似。
1.首先下载安装介质mariadb-10.0.38-linux-glibc_214-x86_64.tar.gz 并将其上传到服务器的/soft目录

2.创建用户与用户组

[root@cs1 soft]# groupadd mysql

[root@cs1 soft]# useradd -g mysql mysql

3.将介质解压到/usr/local/目录下

[root@cs1 soft]# cd /usr/local

[root@cs1 local]# gunzip < /soft/mariadb-10.0.38-linux-glibc_214-x86_64.tar.gz | tar xvf -
mariadb-10.0.38-linux-glibc_214-x86_64/
mariadb-10.0.38-linux-glibc_214-x86_64/man/
mariadb-10.0.38-linux-glibc_214-x86_64/man/man8/
mariadb-10.0.38-linux-glibc_214-x86_64/man/man8/mysqld.8
........省略..........

[root@cs1 local]# ls -lrt
总用量 4
drwxr-xr-x.  2 root root    6 5月   8 2014 src
drwxr-xr-x.  2 root root    6 5月   8 2014 sbin
drwxr-xr-x.  2 root root    6 5月   8 2014 libexec
drwxr-xr-x.  2 root root    6 5月   8 2014 lib64
drwxr-xr-x.  2 root root    6 5月   8 2014 lib
drwxr-xr-x.  2 root root    6 5月   8 2014 include
drwxr-xr-x.  2 root root    6 5月   8 2014 games
drwxr-xr-x.  2 root root    6 5月   8 2014 etc
drwxr-xr-x.  5 root root   46 10月 12 2017 share
drwxr-xr-x.  2 root root   46 3月   9 2018 bin
drwxrwxr-x  13 1021 1004 4096 1月  31 02:40 mariadb-10.0.38-linux-glibc_214-x86_64

4.创建软链接

[root@cs1 local]# ln -s mariadb-10.0.38-linux-glibc_214-x86_64 mysql
[root@cs1 local]# ls -lrt
总用量 4
drwxr-xr-x.  2 root root    6 5月   8 2014 src
drwxr-xr-x.  2 root root    6 5月   8 2014 sbin
drwxr-xr-x.  2 root root    6 5月   8 2014 libexec
drwxr-xr-x.  2 root root    6 5月   8 2014 lib64
drwxr-xr-x.  2 root root    6 5月   8 2014 lib
drwxr-xr-x.  2 root root    6 5月   8 2014 include
drwxr-xr-x.  2 root root    6 5月   8 2014 games
drwxr-xr-x.  2 root root    6 5月   8 2014 etc
drwxr-xr-x.  5 root root   46 10月 12 2017 share
drwxr-xr-x.  2 root root   46 3月   9 2018 bin
drwxrwxr-x  13 1021 1004 4096 1月  31 02:40 mariadb-10.0.38-linux-glibc_214-x86_64
lrwxrwxrwx   1 root root   38 6月   4 14:43 mysql -> mariadb-10.0.38-linux-glibc_214-x86_64

5.确保目录mysql能够被用户mysql访问

[root@cs1 local]# cd mysql
[root@cs1 mysql]# chown -R mysql .
[root@cs1 mysql]# chgrp -R mysql .

6.在安装Mariadb之前,必须创建Mariadb存放数据的目录并初始化grant表,执行mysql_install_db命令来安装Mariadb,如果使用root用户来执行,必须包含–user选项来指定用户,如果是mysql用户来执行可以忽略–user选项。使用 –basedir=path 选项指定Mariadb安装目录的路径,–datadir=path选项指定Mariadb数据目录的路径。如果没有指定–basedir与–datadir选项它们在/usr/local/mysql目录下创建一个data目录。

[root@cs1 mysql]# scripts/mysql_install_db --user=mysql
Installing MariaDB/MySQL system tables in './data' ...
190604 14:44:44 [Note] ./bin/mysqld (mysqld 10.0.38-MariaDB) starting as process 19627 ...
190604 14:44:45 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up to 20MB.

190604 14:44:45 [Note] InnoDB: Using mutexes to ref count buffer pool pages
190604 14:44:45 [Note] InnoDB: The InnoDB memory heap is disabled
190604 14:44:45 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
190604 14:44:45 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
190604 14:44:45 [Note] InnoDB: Compressed tables use zlib 1.2.11
190604 14:44:45 [Note] InnoDB: Using Linux native AIO
190604 14:44:45 [Note] InnoDB: Using CPU crc32 instructions
190604 14:44:45 [Note] InnoDB: Initializing buffer pool, size = 128.0M
190604 14:44:45 [Note] InnoDB: Completed initialization of buffer pool
190604 14:44:45 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
190604 14:44:45 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
190604 14:44:45 [Note] InnoDB: Database physically writes the file full: wait...
190604 14:44:45 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
190604 14:44:45 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
190604 14:44:46 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
190604 14:44:46 [Warning] InnoDB: New log files created, LSN=45781
190604 14:44:46 [Note] InnoDB: Doublewrite buffer not found: creating new
190604 14:44:46 [Note] InnoDB: Doublewrite buffer created
190604 14:44:46 [Note] InnoDB: 128 rollback segment(s) are active.
190604 14:44:46 [Warning] InnoDB: Creating foreign key constraint system tables.
190604 14:44:46 [Note] InnoDB: Foreign key constraint system tables created
190604 14:44:46 [Note] InnoDB: Creating tablespace and datafile system tables.
190604 14:44:46 [Note] InnoDB: Tablespace and datafile system tables created.
190604 14:44:46 [Note] InnoDB: Waiting for purge to start
190604 14:44:46 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.42-84.2 started; log sequence number 0
190604 14:44:48 [Note] InnoDB: FTS optimize thread exiting.
190604 14:44:48 [Note] InnoDB: Starting shutdown...
190604 14:44:48 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool
190604 14:44:50 [Note] InnoDB: Shutdown completed; log sequence number 1616697
OK
Filling help tables...
190604 14:44:50 [Note] ./bin/mysqld (mysqld 10.0.38-MariaDB) starting as process 19661 ...
190604 14:44:50 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up to 20MB.

190604 14:44:50 [Note] InnoDB: Using mutexes to ref count buffer pool pages
190604 14:44:50 [Note] InnoDB: The InnoDB memory heap is disabled
190604 14:44:50 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
190604 14:44:50 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
190604 14:44:50 [Note] InnoDB: Compressed tables use zlib 1.2.11
190604 14:44:50 [Note] InnoDB: Using Linux native AIO
190604 14:44:50 [Note] InnoDB: Using CPU crc32 instructions
190604 14:44:50 [Note] InnoDB: Initializing buffer pool, size = 128.0M
190604 14:44:50 [Note] InnoDB: Completed initialization of buffer pool
190604 14:44:50 [Note] InnoDB: Highest supported file format is Barracuda.
190604 14:44:50 [Note] InnoDB: 128 rollback segment(s) are active.
190604 14:44:50 [Note] InnoDB: Waiting for purge to start
190604 14:44:50 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.42-84.2 started; log sequence number 1616697
190604 14:44:50 [Note] InnoDB: FTS optimize thread exiting.
190604 14:44:50 [Note] InnoDB: Starting shutdown...
190604 14:44:51 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool
190604 14:44:53 [Note] InnoDB: Shutdown completed; log sequence number 1616707
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:

'./bin/mysqladmin' -u root password 'new-password'
'./bin/mysqladmin' -u root -h cs1.jy.net password 'new-password'

Alternatively you can run:
'./bin/mysql_secure_installation'

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd '.' ; ./bin/mysqld_safe --datadir='./data'

You can test the MariaDB daemon with mysql-test-run.pl
cd './mysql-test' ; perl mysql-test-run.pl

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
Get Involved

7.因为安装Mariadb的用户是root用户,而data目录必须要被mysql用户访问,因此修改data目录的用户与组权限

[root@cs1 mysql]# chown -R root .
[root@cs1 mysql]# chown -R mysql data

8.在安装完成后来测试Mariadb的启动

[root@cs1 mysql]# bin/mysqld_safe --user=mysql &
[1] 19770
[root@cs1 mysql]# 190604 14:45:25 mysqld_safe Logging to '/usr/local/mysql/data/cs1.jy.net.err'.
190604 14:45:25 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

mysqld_safe是服务端工具,用于启动mysqld,并且是mysqld的守护进程,mysqld_safe加&在后台运行$BASEDIR/bin/mysqld_safe &。因为mysqld_safe是mysqld的守护进程,所以mysqld_safe脚本会在启动MySQL服务器后继续监控其运行情况,并在其死机时重新启动它。直接使用mysqld_safe启动mysqld时,mysqld_safe可以使用参数选项见mysqld_safe –help,此时可以使用其他配置文件,相当于mysqld_safe把参数传递给mysqld。mysql.server脚本其实也是调用mysqld_safe脚本去启动MySQL服务器的,但此时mysqld_safe不能使用参数选项即不能mysqld_safe –defaults-file这样的模式。

9.将启动Mariadb的脚本配置成服务通过service name start命令来启用

[root@cs1 mysql]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@cs1 mysql]# cat /etc/init.d/mysqld

[root@cs1 Packages]# service mysqld status
 SUCCESS! MariaDB running (19839)
[root@cs1 Packages]# service mysqld stop
Shutting down MariaDB... SUCCESS! 
[root@cs1 Packages]# service mysqld start
Starting MariaDB.190604 18:26:04 mysqld_safe Logging to '/usr/local/mysql/data/cs1.jy.net.err'.
190604 18:26:05 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
 SUCCESS! 

10.修改root用户密码

-bash-4.2$ mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.0.38-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [mysql]> set password=password("123456");
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> exit

-bash-4.2$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 10.0.38-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

11.使用mytop工具来监控Mariadb,mytop在Mariadb安装时也会自动安装,而mysql中需要单独安装

-bash-4.2$ mytop --prompt -u root -d mysql
Password: 
install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at (eval 13) line 3,  line 1.
Perhaps the DBD::mysql perl module hasn't been fully installed,
or perhaps the capitalisation of 'mysql' isn't right.
Available drivers: DBM, ExampleP, File, Gofer, Proxy, SQLite, Sponge.
 at /usr/local/mysql//bin/mytop line 253.

错误信息说明没有安装perl-DBD-MySQL工具包

12.安装perl-DBD-MySQ工具包

[root@cs1 bin]# yum install perl-DBD-MySQL
已加载插件:langpacks
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
file:///run/media/jy/OL-7.1%20Server.x86_64/repodata/repomd.xml: [Errno 14] curl#37 - "Couldn't open file /run/media/jy/OL-7.1%20Server.x86_64/repodata/repomd.xml"
正在尝试其它镜像。
正在解决依赖关系
--> 正在检查事务
---> 软件包 perl-DBD-MySQL.x86_64.0.4.023-5.0.1.el7 将被 安装
--> 正在处理依赖关系 libmysqlclient.so.18(libmysqlclient_18)(64bit),它被软件包 perl-DBD-MySQL-4.023-5.0.1.el7.x86_64 需要
--> 正在处理依赖关系 libmysqlclient.so.18()(64bit),它被软件包 perl-DBD-MySQL-4.023-5.0.1.el7.x86_64 需要
--> 正在检查事务
---> 软件包 mysql-community-libs.x86_64.0.5.6.23-3.el7 将被 安装
--> 正在处理依赖关系 mysql-community-common(x86-64) = 5.6.23-3.el7,它被软件包 mysql-community-libs-5.6.23-3.el7.x86_64 需要
--> 正在检查事务
---> 软件包 mysql-community-common.x86_64.0.5.6.23-3.el7 将被 安装
--> 解决依赖关系完成

依赖关系解决

============================================================================================================================================================================================================================================
 Package                                                            架构                                               版本                                                         源                                                 大小
============================================================================================================================================================================================================================================
正在安装:
 perl-DBD-MySQL                                                     x86_64                                             4.023-5.0.1.el7                                              local                                             140 k
为依赖而安装:
 mysql-community-common                                             x86_64                                             5.6.23-3.el7                                                 local                                             256 k
 mysql-community-libs                                               x86_64                                             5.6.23-3.el7                                                 local                                             2.0 M

事务概要
============================================================================================================================================================================================================================================
安装  1 软件包 (+2 依赖软件包)

总下载量:2.4 M
安装大小:12 M
Is this ok [y/d/N]: y
Downloading packages:


Error downloading packages:
  mysql-community-common-5.6.23-3.el7.x86_64: [Errno 256] No more mirrors to try.
  perl-DBD-MySQL-4.023-5.0.1.el7.x86_64: [Errno 256] No more mirrors to try.
  mysql-community-libs-5.6.23-3.el7.x86_64: [Errno 256] No more mirrors to try.

出现不能下载相关工具包的错误

13.将操作系统ISO文件挂载到系统中并进入Packages目录执行rpm命令来安装

root@cs1 Packages]# ls -lrt mysql*
-rw-rw-r-- 1 root root  1313664 5月  14 2014 mysql-connector-java-5.1.25-3.el7.noarch.rpm
-rw-rw-r-- 1 root root   145144 8月   6 2014 mysql-connector-odbc-5.2.5-6.0.1.el7.x86_64.rpm
-rw-r--r-- 1 root root   420364 2月  19 2015 mysql-community-bench-5.6.23-3.el7.x86_64.rpm
-rw-r--r-- 1 root root 19378376 2月  19 2015 mysql-community-client-5.6.23-3.el7.i686.rpm
-rw-r--r-- 1 root root  3435828 2月  19 2015 mysql-community-devel-5.6.23-3.el7.i686.rpm
-rw-r--r-- 1 root root   262464 2月  19 2015 mysql-community-common-5.6.23-3.el7.x86_64.rpm
-rw-r--r-- 1 root root   262516 2月  19 2015 mysql-community-common-5.6.23-3.el7.i686.rpm
-rw-r--r-- 1 root root 19979168 2月  19 2015 mysql-community-client-5.6.23-3.el7.x86_64.rpm
-rw-r--r-- 1 root root  3543424 2月  19 2015 mysql-community-devel-5.6.23-3.el7.x86_64.rpm
-rw-r--r-- 1 root root 23060740 2月  19 2015 mysql-community-embedded-5.6.23-3.el7.i686.rpm
-rw-r--r-- 1 root root 23733580 2月  19 2015 mysql-community-embedded-5.6.23-3.el7.x86_64.rpm
-rw-r--r-- 1 root root 64644916 2月  19 2015 mysql-community-embedded-devel-5.6.23-3.el7.i686.rpm
-rw-r--r-- 1 root root  2104968 2月  19 2015 mysql-community-libs-5.6.23-3.el7.x86_64.rpm
-rw-r--r-- 1 root root  2040356 2月  19 2015 mysql-community-libs-5.6.23-3.el7.i686.rpm
-rw-r--r-- 1 root root 68058508 2月  19 2015 mysql-community-embedded-devel-5.6.23-3.el7.x86_64.rpm
-rw-r--r-- 1 root root 60224180 2月  19 2015 mysql-community-server-5.6.23-3.el7.x86_64.rpm
-rw-r--r-- 1 root root 59280136 2月  19 2015 mysql-community-test-5.6.23-3.el7.x86_64.rpm

[root@cs1 Packages]# rpm -ihv mysql-community-libs-5.6.23-3.el7.x86_64.rpm
警告:mysql-community-libs-5.6.23-3.el7.x86_64.rpm: 头V3 RSA/SHA256 Signature, 密钥 ID ec551f03: NOKEY
错误:依赖检测失败:
        mysql-community-common(x86-64) = 5.6.23-3.el7 被 mysql-community-libs-5.6.23-3.el7.x86_64 需要
[root@cs1 Packages]# rpm -ivh mysql-community-common-5.6.23-3.el7.x86_64.rpm
警告:mysql-community-common-5.6.23-3.el7.x86_64.rpm: 头V3 RSA/SHA256 Signature, 密钥 ID ec551f03: NOKEY
准备中...                             ################################# [100%]
正在升级/安装...
   1:mysql-community-common-5.6.23-3.e################################# [100%]
[root@cs1 Packages]# rpm -ihv mysql-community-libs-5.6.23-3.el7.x86_64.rpm
警告:mysql-community-libs-5.6.23-3.el7.x86_64.rpm: 头V3 RSA/SHA256 Signature, 密钥 ID ec551f03: NOKEY
准备中...                             ################################# [100%]
正在升级/安装...
   1:mysql-community-libs-5.6.23-3.el7################################# [100%]

[root@cs1 Packages]# ls -lrt perl-DBD-MySQL
ls: 无法访问perl-DBD-MySQL: 没有那个文件或目录
[root@cs1 Packages]# ls -lrt perl-DBD-MySQL*
-rw-rw-r-- 1 root root 143092 8月   6 2014 perl-DBD-MySQL-4.023-5.0.1.el7.x86_64.rpm
[root@cs1 Packages]# rpm -ivh perl-DBD-MySQL-4.023-5.0.1.el7.x86_64.rpm
警告:perl-DBD-MySQL-4.023-5.0.1.el7.x86_64.rpm: 头V3 RSA/SHA256 Signature, 密钥 ID ec551f03: NOKEY
准备中...                             ################################# [100%]
正在升级/安装...
   1:perl-DBD-MySQL-4.023-5.0.1.el7   ################################# [100%]

14.执行mytop命令来监控Mariadb

-bash-4.2$ mytop -u root -p 123456
MariaDB on localhost (10.0.38-MariaDB)                                                                                                                                                                  up 0+01:04:21 [15:49:46]
 Queries: 170.0   qps:    0 Slow:     0.0         Se/In/Up/De(%):    06/00/00/00
 Sorts:     0 qps now:    1 Slow qps: 0.0  Threads:    2 (   1/   0) 00/00/00/00
 Handler: (R/W/U/D)     0/    0/    0/    0        Tmp: R/W/U:    89/   89/    0
 ISAM Key Efficiency: 0.0%  Bps in/out:   1.4/ 66.6   Now in/out:  22.6/ 2.9k

       Id     User         Host/IP        DB   Time     %    Cmd           State Query
       --     ----         -------        --   ----     -    ---           ----- ----------
       12     root       localhost     mysql     10   0.0  Sleep                                                                                                                                                                          
       10     root       localhost      test      0   0.0  Query            init show full processlist   

到此安装也就完成了。

Linux 7 安装Mysql 5.7

在Oracle Linux 7.1中安装MySql 5.7 。mysql安装位置:/mysqlsoft/mysql,数据库文件数据位置:/mysqldata/mysql。
1.首先下载安装介质

mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

2. 在根目录下创建文件夹mysqlsoft和数据库数据文件/mysqldata/mysql

[root@cs2 /]# mkdir -p /mysqlsoft
[root@cs2 /]# mkdir -p /mysqldata/mysql
[root@cs2 /]# ls -lrt /mysqldata/
total 0
drwxr-xr-x 2 root root 6 May 31 11:58 mysql

3.上传介质mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz到/mysqlsoft目录中并解压

[root@cs2 /]# cd mysqlsoft
[root@cs2 mysqlsoft]# ls -lrt
total 628704
-rw-r--r-- 1 root root 643790848 Apr 20  2018 mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

[root@cs2 mysqlsoft]# tar -zxvf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

[root@cs2 mysqlsoft]# ls -lrt
total 628704
-rw-r--r-- 1 root root 643790848 Apr 20  2018 mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
drwxr-xr-x 9 root root       120 May 31 12:11 mysql-5.7.22-linux-glibc2.12-x86_64

建议一般不要修改默认文件名,通过软连接来完成

[root@cs2 mysqlsoft]# mv mysql-5.7.22-linux-glibc2.12-x86_64  mysql

[root@cs2 mysqlsoft]# ls -lrt
total 628704
-rw-r--r-- 1 root root 643790848 Apr 20  2018 mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
drwxr-xr-x 9 root root       120 May 31 13:28 mysql-5.7.22-linux-glibc2.12-x86_64
lrwxrwxrwx 1 root root        35 May 31 13:33 mysql -> mysql-5.7.22-linux-glibc2.12-x86_64
[root@cs2 mysqlsoft]# cd mysql
[root@cs2 mysql]# ls
bin  COPYING  docs  include  lib  man  README  share  support-files

4. 创建mysql用户与用户组

[root@cs2 mysql]# groupadd mysql
[root@cs2 mysql]# useradd -r -g mysql -s /bin/false mysql

因为用户只用于所有权目的,而不是登录目的,useradd命令使用-r与-s /bin/false选项来创建一个用户没有登录服务器主机的权限。

5.修改/mysqlsoft/mysql与/mysqldata/mysql目录权限

[root@cs2 /]# chown -R mysql:mysql /mysqlsoft/mysql
[root@cs2 /]# chown -R mysql:mysql /mysqldata/mysql
[root@cs2 /]# chmod -R 775 /mysqlsoft/mysql
[root@cs2 /]# chmod -R 775 /mysqldata/mysql

6. MySQL对于libaio库有依赖性。台果这个libaio库没有安装那么数据目录初始化与后续的数据库服务启动将会失败,安装libaio库执行以下操作:
查询是否安装了libaio库

[root@cs2 local]# yum search libaio
Loaded plugins: langpacks
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
=========================================================================================================== N/S matched: libaio ============================================================================================================
libaio.i686 : Linux-native asynchronous I/O access library
libaio.x86_64 : Linux-native asynchronous I/O access library
libaio-devel.i686 : Development files for Linux-native asynchronous I/O access
libaio-devel.x86_64 : Development files for Linux-native asynchronous I/O access

  Name and summary matches only, use "search all" for everything.

如果没有安装,可以执行下面的命令来安装

[root@cs2 local]# yum install libaio
Loaded plugins: langpacks
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
file:///run/media/jy/OL-7.1%20Server.x86_64/repodata/repomd.xml: [Errno 14] curl#37 - "Couldn't open file /run/media/jy/OL-7.1%20Server.x86_64/repodata/repomd.xml"
Trying other mirror.
Package libaio-0.3.109-12.el7.x86_64 already installed and latest version
Nothing to do

7.配置mysql参数
只是设置几个简单的mysql运行参数

[root@cs2 ~]# vi /mysqlsoft/mysql/my.cnf 
[mysqld]
basedir=/mysqlsoft/mysql
datadir=/mysqldata/mysql
bind-address=0.0.0.0
user=mysql
port=3306
log-error=/mysqldata/mysql/mysql.err
pid-file=/mysqldata/mysql/mysqld.pid
socket = /mysqldata/mysql/mysql.sock
character-set-server=utf8
default-storage-engine=INNODB
explicit_defaults_for_timestamp = true
"/mysqlsoft/mysql/my.cnf" [New] 67L, 1642C written

注意:log-error 一定要配置,因为如果mysql启动错误,可以从日志文件中找到错误原因。其次bind—address配置0.0.0.0是为了监听所有的连接。还有就是socket参数所指定的mysql.sock文件的路径最好设置为/tmp/mysql.sock,因为unix socket文件的缺省位置在/tmp目录中。

8.初始化mysql

[root@cs2 /]# cd /mysqlsoft/mysql/bin
[root@cs2 bin]# ./mysqld --user=mysql  --defaults-file=/mysqlsoft/mysql/my.cnf --basedir=/mysqlsoft/mysql --datadir=/mysqldata/mysql --initialize


[root@cs2 mysql]# cat mysql.err
2019-05-31T06:01:50.260643Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2019-05-31T06:01:50.260731Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
 100
 100
2019-05-31T06:01:53.795162Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-05-31T06:01:54.049268Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-05-31T06:01:54.102171Z 0 [ERROR] unknown variable 'defaults-file=/mysqlsoft/mysql/my.cnf'
2019-05-31T06:01:54.102193Z 0 [ERROR] Aborting

网上有人说是文件权限的问题,然后重新授权chmod 664 my.cnf 但并没有解决,有人建议调整一下参数顺序就好!!!最后调整了一下参数顺序果然有效:

[root@cs2 bin]# ./mysqld  --defaults-file=/mysqlsoft/mysql/my.cnf --initialize --basedir=/mysqlsoft/mysql --datadir=/mysqldata/mysql --user=mysql

[root@cs2 mysql]# cat mysql.err
2019-05-31T06:05:06.362925Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2019-05-31T06:05:06.362994Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
 100
 100
2019-05-31T06:05:09.779913Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-05-31T06:05:10.026707Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-05-31T06:05:10.094462Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 0be6983a-836a-11e9-a341-005056a092af.
2019-05-31T06:05:10.109209Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-05-31T06:05:10.110107Z 1 [Note] A temporary password is generated for root@localhost: ,;pm93qnL%-j
2019-05-31T06:05:14.966324Z 1 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
2019-05-31T06:05:14.966373Z 1 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
2019-05-31T06:05:14.966391Z 1 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
2019-05-31T06:05:14.966419Z 1 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
2019-05-31T06:05:14.966428Z 1 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
2019-05-31T06:05:14.966441Z 1 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
2019-05-31T06:05:14.966493Z 1 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
2019-05-31T06:05:14.966508Z 1 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.

其中[Note] A temporary password is generated for root@localhost: ,;pm93qnL%-j的root@localhost: 后面跟的是mysql数据库登录的临时密码,各人安装生成的临时密码不一样。可以看到到日志文件没有报错,而且有了临时密码,表示初始化成功。

9. 如果想服务能够部署自动支持安全连接,使用mysql_ssl_rsa_setup工具来创建缺省SSL与RSA文件

[root@cs2 bin]# ./mysql_ssl_rsa_setup --datadir=/mysqldata/mysql
Generating a 2048 bit RSA private key
......................................................................+++
..............................................................+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
.............+++
..............+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
.....................................+++
................................................+++
writing new private key to 'client-key.pem'
-----

9.启动mysql服务

[root@cs2 /]# sh /mysqlsoft/mysql/support-files/mysql.server start
/mysqlsoft/mysql/support-files/mysql.server: line 239: my_print_defaults: command not found
/mysqlsoft/mysql/support-files/mysql.server: line 259: cd: /usr/local/mysql: No such file or directory
Starting MySQL ERROR! Couldn't find MySQL server (/usr/local/mysql/bin/mysqld_safe)

启动mysql服务命令会报错,因为没有修改mysql的配置文件
修改Mysql配置文件,修改前为以下内容

if test -z "$basedir"
then
  basedir=/usr/local/mysql
  bindir=/usr/local/mysql/bin
  if test -z "$datadir"
  then
    datadir=/usr/local/mysql/data
  fi
  sbindir=/usr/local/mysql/bin
  libexecdir=/usr/local/mysql/bin
else
  bindir="$basedir/bin"
  if test -z "$datadir"
  then
    datadir="$basedir/data"
  fi
  sbindir="$basedir/sbin"
  libexecdir="$basedir/libexec"
fi

修改后的内容如下

[root@cs2 ~]# vi /mysqlsoft/mysql/support-files/mysql.server
if test -z "$basedir"
then
  basedir=/mysqlsoft/mysql
  bindir=/mysqlsoft/mysql/bin
  if test -z "$datadir"
  then
    datadir=/mysqldata/mysql
  fi
  sbindir=/mysqlsoft/mysql/bin
  libexecdir=/mysqlsoft/mysql/bin
else
  bindir="$basedir/bin"
  if test -z "$datadir"
  then
    datadir="$basedir/data"
  fi
  sbindir="$basedir/sbin"
  libexecdir="$basedir/libexec"
fi


[root@cs2 ~]# cp /mysqlsoft/mysql/support-files/mysql.server  /etc/init.d/mysqld

[root@cs2 ~]# chmod 755 /etc/init.d/mysqld

10.启动mysql

[root@cs2 ~]# service mysqld start
Starting MySQL.. SUCCESS!

11.配置环境变量

[root@cs2 ~]# vi /etc/profile
# /etc/profile

# System wide environment and startup programs, for login setup
# Functions and aliases go in /etc/bashrc

# It's NOT a good idea to change this file unless you know what you
# are doing. It's much better to create a custom.sh shell script in
# /etc/profile.d/ to make custom changes to your environment, as this
# will prevent the need for merging in future updates.

export MYSQL_HOME=/mysqlsoft/mysql/
export PATH=$PATH:$MYSQL_HOME/bin

11.登录Mysql
初始化成功后,查看初始化密码

[root@cs2 ~]# cat /mysqldata/mysql/mysql.err | grep password
2019-05-31T06:05:10.110107Z 1 [Note] A temporary password is generated for root@localhost: ,;pm93qnL%-j

并输入刚刚复制的密码,但是 却提示不能通过mysql.sock文件实现连接

[root@cs2 bin]# ./mysqladmin -u root -p password
Enter password: 
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/mysqlsoft/mysql/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/mysqlsoft/mysql/mysql.sock' exists!

这里就奇怪了,因为在my.cnf文件中设置的socket文件路径为/mysqldata/mysql/mysql.sock,但mysql所使用的文件不是启动服务所生成的。

[root@cs2 ~]# cat /mysqlsoft/mysql/my.cnf
[mysqld]
basedir=/mysqlsoft/mysql
datadir=/mysqldata/mysql
bind-address=0.0.0.0
user=mysql
port=3306
log-error=/mysqldata/mysql/mysql.err
pid-file=/mysqldata/mysql/mysqld.pid
socket = /mysqldata/mysql/mysql.sock
character-set-server=utf8
default-storage-engine=INNODB
explicit_defaults_for_timestamp = true

[root@cs2 mysql]# ls -lrt
total 286896
-rw-r----- 1 mysql mysql 134217728 May 31 14:05 ib_logfile1
-rw-r----- 1 mysql mysql        56 May 31 14:05 auto.cnf
drwxr-x--- 2 mysql mysql      8192 May 31 14:05 performance_schema
drwxr-x--- 2 mysql mysql      4096 May 31 14:05 mysql
drwxr-x--- 2 mysql mysql      8192 May 31 14:05 sys
-rw------- 1 mysql mysql      1679 May 31 14:25 ca-key.pem
-rw-r--r-- 1 mysql mysql      1107 May 31 14:25 ca.pem
-rw------- 1 mysql mysql      1679 May 31 14:25 server-key.pem
-rw-r--r-- 1 mysql mysql      1107 May 31 14:25 server-cert.pem
-rw------- 1 mysql mysql      1679 May 31 14:25 client-key.pem
-rw-r--r-- 1 mysql mysql      1107 May 31 14:25 client-cert.pem
-rw------- 1 mysql mysql      1679 May 31 14:25 private_key.pem
-rw-r--r-- 1 mysql mysql       451 May 31 14:25 public_key.pem
-rw-r----- 1 mysql mysql       291 May 31 18:05 ib_buffer_pool
-rw------- 1 mysql mysql         6 May 31 18:08 mysql.sock.lock
srwxrwxrwx 1 mysql mysql         0 May 31 18:08 mysql.sock
-rw-r----- 1 mysql mysql         6 May 31 18:08 mysqld.pid
-rw-r----- 1 mysql mysql    101396 May 31 18:08 mysql.err
-rw-r----- 1 mysql mysql  12582912 May 31 18:08 ibtmp1
-rw-r----- 1 mysql mysql  12582912 May 31 18:08 ibdata1
-rw-r----- 1 mysql mysql 134217728 May 31 18:08 ib_logfile0

可以看到在/var/lib/mysql目录中的mysql.sock是指向/mysqlsoft/mysql/mysql.sock文件的。

[mysql@cs2 ~]$ ls -lrt /var/lib/mysql 
总用量 110604
-rw-rw----. 1   27   27 50331648 10月 11 2017 ib_logfile1
drwx------. 2   27   27     4096 10月 11 2017 performance_schema
drwx------. 2   27   27     4096 10月 11 2017 mysql
-rw-rw----. 1   27   27       56 10月 11 2017 auto.cnf
-rw-rw----. 1   27   27 50331648 5月  30 18:32 ib_logfile0
-rw-rw----. 1   27   27 12582912 5月  30 18:32 ibdata1
lrwxrwxrwx  1 root root       27 5月  31 15:44 mysql.sock -> /mysqlsoft/mysql/mysql.sock

如果使用-S选项来指定生成的mysql.sock文件进行登录是可以成功登录的

[mysql@cs2 mysql]$  mysql -S /mysqldata/mysql/mysql.sock -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> quit

修改socket文件路径为/mysqlsoft/mysql/mysql.sock

[root@cs2 ~]# cat /mysqlsoft/mysql/my.cnf
[mysqld]
basedir=/mysqlsoft/mysql
datadir=/mysqldata/mysql
bind-address=0.0.0.0
user=mysql
port=3306
log-error=/mysqldata/mysql/mysql.err
pid-file=/mysqldata/mysql/mysqld.pid
socket = /mysqlsoft/mysql/mysql.sock
character-set-server=utf8
default-storage-engine=INNODB
explicit_defaults_for_timestamp = true

再重启mysql服务

[root@cs2 ~]# service mysqld stop
Shutting down MySQL.. SUCCESS! 
[root@cs2 ~]# service mysqld start
Starting MySQL.. SUCCESS!

[mysql@cs2 ~]$ mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

这就可以登录了。

12.重置root用户密码

[mysql@cs2 ~]$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set password=password("123456");
Query OK, 0 rows affected, 1 warning (0.00 sec)

13.设置允许远程登录mysql
如果要远程访问数据库,只需要把拥有全部权限的root账号对应的记录的Host字段改为%就可以了

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to root@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

测试远程登录

-bash-4.2$ mysql -h 10.11.13.19 -P 3306 -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT DISTINCT CONCAT('User: [', user, '''@''', host, '];') AS USER_HOST FROM user; 
+------------------------------------+
| USER_HOST                          |
+------------------------------------+
| User: [root'@'%];                  |
| User: [mysql.session'@'localhost]; |
| User: [mysql.sys'@'localhost];     |
+------------------------------------+
3 rows in set (0.05 sec)

到此所有的安装步骤就完成,安装还是相当简单的。

当用户无限制使用表空间配额且表空间有足够空间时在执行DML操作时出现超出表空间的空间限额

朋友生产数据库在向特定的一张表插入数据时报超出表空间data的空间限额如是执行下查看用户所用的表空间配额信息,查看用户所使用表空间的配额发现没有限制,因为max_bytes为 -1

SQL>  select * from dba_ts_quotas where username='data';

TABLESPACE_NAME                USERNAME                            BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DROPPED
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- -------
data                           data                           8825732464         -1  107735992         -1 NO

 1 rows selected

SQL>
SQL> select
  2    fs.tablespace_name            "Tablespace",
  3    (df.totalspace-fs.freespace)  "Used MB",
  4    fs.freespace                  "Free MB",
  5    df.totalspace                 "Total MB",
  6    round(100*(fs.freespace/df.totalspace)) "Pct. Free"
  7  from
  8    (select tablespace_name,round(sum(bytes)/1024/1024) TotalSpace
  9    from dba_data_files group by  tablespace_name) df,
 10    (select tablespace_name,round(sum(bytes)/1024/1024) FreeSpace
 11    from dba_free_space group by tablespace_name) fs
 12  where df.tablespace_name=fs.tablespace_name;

Tablespace                        Used MB    Free MB   Total MB  Pct. Free
------------------------------ ---------- ---------- ---------- ----------
SYSTEM                               7207       3033      10240         30
TEST                                 6790      34170      40960         83
USERS                                 173      25427      25600         99
UNDOTBS2                              227      24013      24240         99
DATA                               990119     176281    1166400         15
SYSAUX                               3925       1195       5120         23
UNDOTBS1                            12898      28062      40960         69

7 rows selected

查看表lv_data的依赖对象

SQL> select NAME,TYPE from dba_dependencies where REFERENCED_NAME='LV_DATA';

NAME                           TYPE
------------------------------ ------------------
LV_DATA                        VIEW
FC_UPDATE_CORPFUND             PROCEDURE
FC_UPDATE_MY                   PROCEDURE
FC_UPDATE_KY                   PROCEDURE
FC_UPDATE_FACTPAY              PROCEDURE
FC_UPDATE_CALCPAY              PROCEDURE
FC_UPDATE_KY                   PROCEDURE
......
LV_DATA                        SYNONYM
LV_DATA                        VIEW
LV_DATA                        SYNONYM
LV_DATA                        SYNONYM

139 rows selected

查看所有依赖对象的所有者

SQL> select  distinct owner from dba_objects where OBJECT_NAME in(select NAME from dba_dependencies where REFERENCED_NAME='LV_DATA');

OWNER
------------------------------
SY
SY_BK
WEB
CX
DATA
OLD
TEST
XC
CZ
OSY
BACKUP
TJ

12 rows selected

对所有依赖对象所有者授权可以无限制使用表空间

SQL> grant unlimited tablespace to OSY;

Grant succeeded

SQL> grant unlimited tablespace to SBK;

Grant succeeded

SQL> grant unlimited tablespace to WEB;

Grant succeeded

SQL> grant unlimited tablespace to CX;

Grant succeeded

SQL> grant unlimited tablespace to DATA;

Grant succeeded

SQL> grant unlimited tablespace to OLD;

Grant succeeded

SQL> grant unlimited tablespace to TEST;

Grant succeeded

SQL> grant unlimited tablespace to XC;

Grant succeeded

SQL> grant unlimited tablespace to CZ;

Grant succeeded

SQL> grant unlimited tablespace to SY;

Grant succeeded

SQL> grant unlimited tablespace to BACKUP;

Grant succeeded

SQL> grant unlimited tablespace to TJ;

Grant succeeded

再向表lv_data插入数据时恢复正常

sql server 2005 数据修改的内部原理

当插入新的记录到数据表中时,SQL Server必须决定在哪里插入记录。如果数据表没有聚集索引—也就是说,数据表还是堆表的话–新的记录总是插到表中任何有可用空间的地方。如果空间中没有可用的分页,SQL Server会尝试从已经分配给对象的现有的统一扩展中寻找未分配的分页。如果这样的分页也不存在,SQL Server就必须为数据表分配一个全新的扩展。

聚集索引根据新记录的聚集索引键列的值将插入导向到特定的分页。当新的记录是插入语句的直接结果或者是由“删除+插入”策略所执行的更新语句的结果时就会发生插入。SQL Server将新的记录插入到聚集索引中的合适位置,如果当前分页没有可用空间的话,会通过页拆分(page split)在分页中进行接合。如果不将聚集索引 声明为唯一性并插入具有重复键值的记录时,SQL Server会为所有后续的具有相同键值的记录自动生成一个唯一标识符(uniqueifier)。于是,SQL Server在内部将所有的聚集索引键都看作是唯一的。

页拆分(Splitting Pages)
SQL Server找到新的可用分页以后,原来的分页就要被拆分:行的一半留在原来的分页上,而另一半则被移到新的分页上。在某些情况下,由于变长字段的长度可能会变得远远大于分页上任何现有的记录,SQL Server会发现即使在页拆分以后仍然没有可用空间来存放新的记录。拆分后,一条或多条记录会被提升到父级分页中。如果只需要一次拆分,那么就提升一条记录。然而,如果在一次拆分后仍然无法容纳新的记录,那么就可能会潜在地产生多个新的分页并产生多次到父级分页的提升。举例来说,比如一个具有32行数据的分页。假设SQL Server尝试插入一条8000个字节长的新记录。SQL Server会对该分页进行一次拆分,但是无法丙容纳一条800个字节长的新记录。即使在第二次拆分后,仍然无法容纳新的记录。最终,SQL Server会意识到如果分页中有任何其他记录存在的话是无法容纳这条新记录的,因此它会分配一个新的分页来容纳这条唯一的新记录。于是,会发生几次页拆分,产生许多新的分页以及父级分页中的许多新的行。

SQL Server总是从根节点向下搜索索引树的,因此在插入操作的过程中,分页是在下层中进行的。这意味着当插入操作搜索索引的时候,需要将索引保护起来防止可能的更新操作。这种保护机制使用闩锁。当分页被读取或者被写入磁盘的时候,SQL Server会获取闩锁来保护分页内容的物理完整性。父节点(非叶节点)被闩锁锁住直到得知子节点拥有自己的闩锁来保护时为止。然后,父级的闩锁就可以安全地被释放掉了。

在父级节点上的闩锁被释放以前,SQL Server会判断分页是否能够容纳另两行数据。如果不能的话,就进行页拆分。最终目的是确保父级分页总是有足够的空间容纳记录或者由子分页拆分所得到的记录(有时候这会导致无需进行页拆分–至少尚未拆分。这将带来性能上的优化)。拆分的类型取决于被拆分分页的类型:索引的根分页,中间级的索引分页以及数据分页。

拆分索引的根分页
如果索引的根分页需要为插入一条新的索引也被拆分的话,SQL Server会为此索引分配两个新的分页。根分页中的所有记录在这两个新分页中被拆分,而新的索引行也被插入到这些分页之中的某个合适的位置。原来的根分页仍然是根,但是现在上面只有两条记录,指向新分配的两个分页。根的页拆分会在索引中创建一个新的级别。由于索引通常只有几层的深度,这种类型的拆分并不会经常发生。

拆分中间级索引分页
中间级索引分页的拆分是通过定位分页上索引键的中点,分配新的分页并将旧的索引分页中较低的一半复制到新的分页来实现的。同样地,尽管这比根分页的拆分要常见一些,但这种情况也并不会经常发生。

拆分数据分页
数据分页的拆分是最值得关注也是最可能发生的情况,并且这也可能是开发者唯一应该关注的拆分类型。数据分页的拆分只有在表上存在聚集索引且进行插入操作时才会发生。如果不存在聚集索引,SQL Server会根据PFS分页在任何可以容纳新记录的分页上完成插入。尽管只有插入行为才会引起拆分,但这种插入行为也可能是update语句产生的结果,而不仅仅针对insert语句。正如下面将要介绍的,如果行不能在原处被更新或者至少在同一个分页中被更新的话,更新操作实际上就被分解为先删除原始行再插入行的一个新版本。当然,插入新的记录还会造成页拆分。

拆分数据分页是一个复杂的操作。与拆分中间级索引分页非常类似的是,SQL Server也是通过定位分页上的索引键的中点,分配新的分页并将旧的索引分页中较低的一半复制到新的分页来实现的。这需要索引管理器决定在哪个分页上放置新的记录并且能够处理原业的旧分页和新分页都不能容纳的大的行记录。当数据分页被拆分时,聚集索引键值不会改变,因而非聚集索引也不会受到影响。

下面来看看当发生拆分时分页上的情况。下面的脚本新建了一个具有较大行记录的数据表bigrows—相当大,实际上一个分页上只能容纳五行数据。一旦表被创建并插入五行记录以后,我们可以通过如下步骤找到该表的第一个分页(在本例中也是唯一的分页):将dbcc ind的输出结果插入到sp_table_pages表中,寻找没有前一页的数据分页的信息然后使用dbcc page来查看分页的内容。由于没有必要查看分页上所有的8020个字节的数据,我们只需要查看分页末尾的行偏移数组并观察当插入第六条记录时分页上的情况。

/*首先创建数据表bigrows */
use adventureworks;
GO

drop table bigrows;
GO

create table bigrows
(
a int primary key,
b varchar(1600)
);
GO

/* 向表中插入五行数据 */
insert into bigrows values(5,replicate('a',1600));
insert into bigrows values(10,replicate('b',1600));
insert into bigrows values(15,replicate('c',1600));
insert into bigrows values(20,replicate('d',1600));
insert into bigrows values(25,replicate('e',1600));
GO

truncate table sp_table_pages;
insert into sp_table_pages exec('dbcc ind(adventureworks,bigrows,-1)');
select pagefid,pagepid from sp_table_pages where pagetype=1;

DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。

(2 行受影响)
pagefid pagepid
------- -----------
1 20996

(1 行受影响)

dbcc traceon(3604);
GO
dbcc page(adventureworks,1,20996,1);

DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。

PAGE: (1:20996)

BUFFER:

BUF @0x0000000083FD7F80

bpage = 0x00000000835FE000 bhash = 0x0000000000000000 bpageno = (1:20996)
bdbid = 6 breferences = 0 bUse1 = 686
bstat = 0xc0010b blog = 0x212121bb bnext = 0x0000000000000000

PAGE HEADER:

Page @0x00000000835FE000

m_pageId = (1:20996) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 346 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594060603392
Metadata: PartitionId = 72057594054311936 Metadata: IndexId = 1
Metadata: ObjectId = 743673697 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 8 m_slotCnt = 5 m_freeCnt = 11
m_freeData = 8171 m_reservedCnt = 0 m_lsn = (134:408:2)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:16176) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:

Slot 0, Offset 0x60, Length 1615, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x000000000E22C060

0000000000000000: 30000800 05000000 0200fc01 004f0661 ?0............O.a
0000000000000010: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000020: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000030: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000040: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000050: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000060: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000070: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000080: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000090: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000000A0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000000B0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000000C0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000000D0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000000E0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000000F0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000100: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000110: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000120: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000130: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000140: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000150: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000160: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000170: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000180: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000190: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000001A0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000001B0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000001C0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000001D0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000001E0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000001F0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000200: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000210: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000220: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000230: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000240: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000250: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000260: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000270: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000280: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000290: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000002A0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000002B0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000002C0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000002D0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000002E0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000002F0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000300: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000310: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000320: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000330: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000340: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000350: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000360: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000370: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000380: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000390: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000003A0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000003B0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000003C0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000003D0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000003E0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000003F0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000400: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000410: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000420: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000430: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000440: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000450: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000460: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000470: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000480: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000490: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000004A0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000004B0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000004C0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000004D0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000004E0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000004F0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000500: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000510: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000520: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000530: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000540: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000550: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000560: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000570: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000580: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000590: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000005A0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000005B0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000005C0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000005D0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000005E0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000005F0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000600: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000610: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000620: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000630: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000640: 61616161 61616161 61616161 616161????aaaaaaaaaaaaaaa

Slot 1, Offset 0x6af, Length 1615, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x000000000E22C6AF

0000000000000000: 30000800 0a000000 0200fc01 004f0662 ?0............O.b
0000000000000010: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000020: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000030: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000040: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000050: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000060: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000070: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000080: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000090: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000000A0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000000B0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000000C0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000000D0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000000E0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000000F0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000100: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000110: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000120: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000130: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000140: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000150: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000160: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000170: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000180: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000190: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000001A0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000001B0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000001C0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000001D0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000001E0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000001F0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000200: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000210: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000220: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000230: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000240: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000250: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000260: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000270: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000280: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000290: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000002A0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000002B0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000002C0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000002D0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000002E0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000002F0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000300: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000310: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000320: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000330: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000340: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000350: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000360: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000370: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000380: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000390: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000003A0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000003B0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000003C0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000003D0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000003E0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000003F0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000400: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000410: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000420: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000430: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000440: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000450: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000460: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000470: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000480: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000490: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000004A0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000004B0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000004C0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000004D0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000004E0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000004F0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000500: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000510: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000520: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000530: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000540: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000550: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000560: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000570: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000580: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000590: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000005A0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000005B0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000005C0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000005D0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000005E0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000005F0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000600: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000610: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000620: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000630: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000640: 62626262 62626262 62626262 626262????bbbbbbbbbbbbbbb

Slot 2, Offset 0xcfe, Length 1615, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x000000000E22CCFE

0000000000000000: 30000800 0f000000 0200fc01 004f0663 ?0............O.c
0000000000000010: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000020: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000030: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000040: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000050: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000060: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000070: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000080: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000090: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000000A0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000000B0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000000C0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000000D0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000000E0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000000F0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000100: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000110: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000120: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000130: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000140: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000150: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000160: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000170: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000180: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000190: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000001A0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000001B0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000001C0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000001D0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000001E0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000001F0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000200: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000210: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000220: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000230: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000240: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000250: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000260: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000270: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000280: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000290: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000002A0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000002B0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000002C0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000002D0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000002E0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000002F0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000300: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000310: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000320: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000330: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000340: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000350: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000360: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000370: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000380: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000390: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000003A0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000003B0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000003C0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000003D0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000003E0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000003F0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000400: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000410: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000420: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000430: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000440: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000450: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000460: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000470: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000480: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000490: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000004A0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000004B0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000004C0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000004D0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000004E0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000004F0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000500: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000510: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000520: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000530: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000540: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000550: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000560: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000570: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000580: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000590: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000005A0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000005B0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000005C0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000005D0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000005E0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000005F0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000600: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000610: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000620: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000630: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000640: 63636363 63636363 63636363 636363????ccccccccccccccc

Slot 3, Offset 0x134d, Length 1615, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x000000000E22D34D

0000000000000000: 30000800 14000000 0200fc01 004f0664 ?0............O.d
0000000000000010: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000020: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000030: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000040: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000050: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000060: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000070: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000080: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000090: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000000A0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000000B0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000000C0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000000D0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000000E0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000000F0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000100: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000110: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000120: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000130: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000140: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000150: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000160: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000170: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000180: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000190: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000001A0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000001B0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000001C0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000001D0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000001E0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000001F0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000200: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000210: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000220: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000230: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000240: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000250: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000260: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000270: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000280: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000290: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000002A0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000002B0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000002C0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000002D0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000002E0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000002F0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000300: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000310: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000320: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000330: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000340: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000350: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000360: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000370: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000380: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000390: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000003A0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000003B0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000003C0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000003D0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000003E0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000003F0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000400: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000410: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000420: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000430: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000440: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000450: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000460: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000470: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000480: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000490: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000004A0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000004B0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000004C0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000004D0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000004E0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000004F0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000500: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000510: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000520: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000530: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000540: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000550: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000560: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000570: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000580: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000590: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000005A0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000005B0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000005C0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000005D0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000005E0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000005F0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000600: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000610: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000620: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000630: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000640: 64646464 64646464 64646464 646464????ddddddddddddddd

Slot 4, Offset 0x199c, Length 1615, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x000000000E22D99C

0000000000000000: 30000800 19000000 0200fc01 004f0665 ?0............O.e
0000000000000010: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000020: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000030: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000040: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000050: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000060: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000070: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000080: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000090: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000000A0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000000B0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000000C0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000000D0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000000E0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000000F0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000100: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000110: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000120: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000130: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000140: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000150: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000160: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000170: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000180: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000190: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000001A0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000001B0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000001C0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000001D0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000001E0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000001F0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000200: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000210: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000220: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000230: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000240: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000250: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000260: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000270: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000280: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000290: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000002A0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000002B0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000002C0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000002D0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000002E0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000002F0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000300: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000310: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000320: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000330: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000340: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000350: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000360: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000370: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000380: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000390: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000003A0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000003B0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000003C0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000003D0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000003E0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000003F0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000400: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000410: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000420: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000430: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000440: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000450: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000460: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000470: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000480: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000490: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000004A0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000004B0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000004C0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000004D0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000004E0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000004F0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000500: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000510: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000520: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000530: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000540: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000550: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000560: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000570: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000580: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000590: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000005A0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000005B0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000005C0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000005D0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000005E0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000005F0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000600: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000610: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000620: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000630: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000640: 65656565 65656565 65656565 656565????eeeeeeeeeeeeeee

下面是从dbcc page输出结果中得到的行偏移数组:

OFFSET TABLE:

Row - Offset
4 (0x4) - 6556 (0x199c)
3 (0x3) - 4941 (0x134d)
2 (0x2) - 3326 (0xcfe)
1 (0x1) - 1711 (0x6af)
0 (0x0) - 96 (0x60)

DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。

下面插入一行新的记录并再次查看行偏移数组:

use adventureworks;
GO
insert into bigrows values(22,replicate('x',1600));
GO

从m_nextPage = (1:20999)结果可知现在有两个数据分页,下一个分页号为20999。

PAGE: (1:20996)

BUFFER:

BUF @0x0000000083FD7F80

bpage = 0x00000000835FE000 bhash = 0x0000000000000000 bpageno = (1:20996)
bdbid = 6 breferences = 0 bUse1 = 4322
bstat = 0xc0010b blog = 0x212121bb bnext = 0x0000000000000000

PAGE HEADER:

Page @0x00000000835FE000

m_pageId = (1:20996) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 346 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594060603392
Metadata: PartitionId = 72057594054311936 Metadata: IndexId = 1
Metadata: ObjectId = 743673697 m_prevPage = (0:0) m_nextPage = (1:20999)
pminlen = 8 m_slotCnt = 3 m_freeCnt = 3245
m_freeData = 8171 m_reservedCnt = 0 m_lsn = (134:416:17)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:16176) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:

Slot 0, Offset 0x60, Length 1615, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x0000000003A8C060

0000000000000000: 30000800 05000000 0200fc01 004f0661 ?0............O.a
0000000000000010: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000020: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000030: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000040: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000050: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000060: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000070: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000080: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000090: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000000A0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000000B0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000000C0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000000D0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000000E0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000000F0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000100: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000110: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000120: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000130: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000140: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000150: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000160: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000170: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000180: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000190: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000001A0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000001B0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000001C0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000001D0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000001E0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000001F0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000200: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000210: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000220: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000230: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000240: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000250: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000260: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000270: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000280: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000290: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000002A0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000002B0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000002C0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000002D0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000002E0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000002F0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000300: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000310: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000320: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000330: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000340: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000350: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000360: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000370: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000380: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000390: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000003A0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000003B0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000003C0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000003D0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000003E0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000003F0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000400: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000410: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000420: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000430: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000440: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000450: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000460: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000470: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000480: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000490: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000004A0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000004B0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000004C0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000004D0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000004E0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000004F0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000500: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000510: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000520: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000530: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000540: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000550: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000560: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000570: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000580: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000590: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000005A0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000005B0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000005C0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000005D0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000005E0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000005F0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000600: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000610: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000620: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000630: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000640: 61616161 61616161 61616161 616161????aaaaaaaaaaaaaaa

Slot 1, Offset 0x6af, Length 1615, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x0000000003A8C6AF

0000000000000000: 30000800 0a000000 0200fc01 004f0662 ?0............O.b
0000000000000010: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000020: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000030: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000040: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000050: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000060: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000070: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000080: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000090: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000000A0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000000B0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000000C0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000000D0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000000E0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000000F0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000100: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000110: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000120: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000130: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000140: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000150: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000160: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000170: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000180: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000190: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000001A0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000001B0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000001C0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000001D0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000001E0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000001F0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000200: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000210: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000220: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000230: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000240: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000250: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000260: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000270: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000280: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000290: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000002A0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000002B0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000002C0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000002D0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000002E0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000002F0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000300: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000310: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000320: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000330: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000340: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000350: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000360: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000370: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000380: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000390: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000003A0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000003B0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000003C0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000003D0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000003E0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000003F0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000400: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000410: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000420: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000430: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000440: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000450: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000460: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000470: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000480: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000490: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000004A0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000004B0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000004C0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000004D0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000004E0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000004F0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000500: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000510: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000520: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000530: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000540: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000550: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000560: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000570: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000580: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000590: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000005A0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000005B0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000005C0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000005D0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000005E0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000005F0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000600: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000610: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000620: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000630: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000640: 62626262 62626262 62626262 626262????bbbbbbbbbbbbbbb

Slot 2, Offset 0xcfe, Length 1615, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x0000000003A8CCFE

0000000000000000: 30000800 0f000000 0200fc01 004f0663 ?0............O.c
0000000000000010: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000020: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000030: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000040: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000050: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000060: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000070: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000080: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000090: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000000A0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000000B0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000000C0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000000D0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000000E0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000000F0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000100: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000110: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000120: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000130: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000140: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000150: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000160: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000170: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000180: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000190: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000001A0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000001B0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000001C0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000001D0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000001E0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000001F0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000200: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000210: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000220: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000230: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000240: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000250: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000260: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000270: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000280: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000290: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000002A0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000002B0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000002C0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000002D0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000002E0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000002F0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000300: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000310: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000320: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000330: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000340: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000350: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000360: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000370: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000380: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000390: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000003A0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000003B0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000003C0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000003D0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000003E0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000003F0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000400: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000410: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000420: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000430: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000440: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000450: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000460: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000470: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000480: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000490: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000004A0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000004B0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000004C0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000004D0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000004E0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000004F0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000500: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000510: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000520: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000530: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000540: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000550: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000560: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000570: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000580: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000590: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000005A0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000005B0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000005C0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000005D0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000005E0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000005F0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000600: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000610: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000620: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000630: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000640: 63636363 63636363 63636363 636363????ccccccccccccccc

OFFSET TABLE:

Row - Offset
2 (0x2) - 3326 (0xcfe)
1 (0x1) - 1711 (0x6af)
0 (0x0) - 96 (0x60)

DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。

在拆分后检查原来的数据分页发现它既有可能包含原始分页的上半部分的行记录也可能是下半部分。SQL Server通常会移动记录因而新插入的记录会移动到新的分页上去。因为这些行记录无论如何都会移动,因此调整它们的位置来容纳新插入的记录就更具有意义了。在这个例子中,新的记录,其聚集键值为22,会被插入到分页的下半部分中去。因此当发生页拆分时,前三行仍然留在原始分页20996中。可以检查页头来找到下一个包含新记录的分页位置。其中,m_nextPage域指明了页号。该值以十进制的格式表示为文件号;分页号对,因此可以很容易地将其运用在dbcc page命令中。当我运行该查询时,得到的m_nextPage的值为1:20999,因此要执行如下命令:

use adventureworks;
GO
dbcc traceon(3604);
GO
dbcc page(adventureworks,1,20999,1)

DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。

PAGE: (1:20999)

BUFFER:

BUF @0x0000000083FF9C80

bpage = 0x0000000083E72000 bhash = 0x0000000000000000 bpageno = (1:20999)
bdbid = 6 breferences = 0 bUse1 = 7865
bstat = 0xc0010b blog = 0x212121bb bnext = 0x0000000000000000

PAGE HEADER:

Page @0x0000000083E72000

m_pageId = (1:20999) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 346 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594060603392
Metadata: PartitionId = 72057594054311936 Metadata: IndexId = 1
Metadata: ObjectId = 743673697 m_prevPage = (1:20996) m_nextPage = (0:0)
pminlen = 8 m_slotCnt = 3 m_freeCnt = 3245
m_freeData = 4941 m_reservedCnt = 0 m_lsn = (134:416:20)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:16176) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:

Slot 0, Offset 0x60, Length 1615, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x0000000003A8C060

0000000000000000: 30000800 14000000 0200fc01 004f0664 ?0............O.d
0000000000000010: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000020: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000030: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000040: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000050: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000060: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000070: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000080: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000090: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000000A0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000000B0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000000C0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000000D0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000000E0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000000F0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000100: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000110: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000120: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000130: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000140: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000150: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000160: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000170: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000180: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000190: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000001A0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000001B0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000001C0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000001D0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000001E0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000001F0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000200: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000210: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000220: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000230: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000240: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000250: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000260: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000270: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000280: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000290: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000002A0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000002B0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000002C0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000002D0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000002E0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000002F0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000300: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000310: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000320: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000330: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000340: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000350: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000360: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000370: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000380: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000390: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000003A0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000003B0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000003C0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000003D0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000003E0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000003F0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000400: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000410: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000420: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000430: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000440: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000450: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000460: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000470: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000480: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000490: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000004A0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000004B0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000004C0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000004D0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000004E0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000004F0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000500: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000510: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000520: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000530: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000540: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000550: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000560: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000570: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000580: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000590: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000005A0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000005B0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000005C0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000005D0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000005E0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
00000000000005F0: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000600: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000610: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000620: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000630: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000640: 64646464 64646464 64646464 646464????ddddddddddddddd

Slot 1, Offset 0xcfe, Length 1615, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x0000000003A8CCFE

0000000000000000: 30000800 16000000 0200fc01 004f0678 ?0............O.x
0000000000000010: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000020: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000030: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000040: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000050: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000060: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000070: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000080: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000090: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000000A0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000000B0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000000C0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000000D0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000000E0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000000F0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000100: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000110: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000120: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000130: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000140: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000150: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000160: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000170: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000180: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000190: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000001A0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000001B0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000001C0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000001D0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000001E0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000001F0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000200: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000210: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000220: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000230: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000240: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000250: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000260: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000270: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000280: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000290: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000002A0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000002B0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000002C0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000002D0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000002E0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000002F0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000300: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000310: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000320: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000330: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000340: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000350: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000360: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000370: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000380: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000390: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000003A0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000003B0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000003C0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000003D0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000003E0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000003F0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000400: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000410: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000420: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000430: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000440: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000450: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000460: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000470: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000480: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000490: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000004A0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000004B0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000004C0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000004D0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000004E0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000004F0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000500: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000510: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000520: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000530: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000540: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000550: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000560: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000570: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000580: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000590: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000005A0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000005B0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000005C0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000005D0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000005E0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000005F0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000600: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000610: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000620: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000630: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000640: 78787878 78787878 78787878 787878????xxxxxxxxxxxxxxx

Slot 2, Offset 0x6af, Length 1615, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x0000000003A8C6AF

0000000000000000: 30000800 19000000 0200fc01 004f0665 ?0............O.e
0000000000000010: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000020: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000030: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000040: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000050: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000060: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000070: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000080: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000090: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000000A0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000000B0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000000C0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000000D0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000000E0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000000F0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000100: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000110: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000120: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000130: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000140: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000150: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000160: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000170: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000180: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000190: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000001A0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000001B0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000001C0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000001D0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000001E0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000001F0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000200: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000210: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000220: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000230: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000240: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000250: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000260: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000270: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000280: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000290: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000002A0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000002B0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000002C0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000002D0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000002E0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000002F0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000300: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000310: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000320: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000330: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000340: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000350: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000360: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000370: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000380: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000390: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000003A0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000003B0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000003C0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000003D0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000003E0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000003F0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000400: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000410: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000420: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000430: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000440: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000450: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000460: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000470: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000480: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000490: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000004A0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000004B0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000004C0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000004D0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000004E0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000004F0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000500: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000510: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000520: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000530: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000540: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000550: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000560: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000570: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000580: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000590: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000005A0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000005B0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000005C0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000005D0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000005E0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
00000000000005F0: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000600: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000610: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000620: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000630: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000640: 65656565 65656565 65656565 656565????eeeeeeeeeeeeeee

OFFSET TABLE:

下面是插入以后第二个分页上的行偏移数组:

Row - Offset
2 (0x2) - 1711 (0x6af)
1 (0x1) - 3326 (0xcfe)
0 (0x0) - 96 (0x60)

DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。

注意,在页拆分以后,此分页上有三行记录:原始分页上的后两条记录(键值为20和25),以及值为22的新记录。如果检查分页上的实际数据的话,会注意到新的记录在槽号(slot)为1的地方,即使该记录本身的确是分页上物理顺序中的最后条记录。槽1(键值为22)从偏移量3326处开始,而槽2(键值为25)则从偏移量1711处开始。行的聚集索引键序是由行的槽号指明的,而不是在分页上的物理位置。如果数据表包含聚集索引,那么在槽1处的记录的键值总是小于槽2处记录的键值而大于槽0处记录的键值。

尽管典型的页拆分的代价并不十分高昂,但是用户还是希望能够尽可能降低生产系统中的页拆分频率,至少在使用高峰的时刻会如此。一次页拆分开锁很低,但成千上百的拆分就不是了。可以通过在现有数据上创建聚集索引时使用filefactor子句在分页上预留一些空间来避免高峰时刻可能的系统崩溃。可以在系统运营最不繁忙的时雄姿英发定期使用期望的填充因子重建索引对系统进行优化。按照这种方式,在高峰时刻依然有额外的空间可以使用,并且可以因此节省拆分带来的开锁。如果系统不存在“不活跃”的时间,可以使用alter index对索引进行重组并调整填充因子,而不必使整张表都陷入不可使用的状态。注意:带有reorganize的alter index语句只能通过压缩数据和转移分页来调整填充因子,它不会通过添加新的分页来重置填充因子。利用SQL Server代理可以很轻松地在系统不繁忙时安排索引的重建或重组。

删除数据
当从一张数据表中删除记录时,必须同时考虑数据分页和索引分页上的变化。谨记聚集索引的叶级别实际上就是数据本身,而从带有聚集索引的表中删除数据的方式与从非聚集索引 的叶级别删除记录完全一样。从堆表中删除记录则是另一种处理方式,类似于从索引的结点分页中进行删除。

从堆表中删除记录
SQL Server 2005不会在行被删除时自动压缩分页上的空间。考虑到性能优化,在分页需要额外的连续空间来插入新的记录以前都不会进行数据压缩。下面的盒子从分页中间删除一行数据然后使用dbcc page来查看分页。

use adventureworks;
GO

create table smallrows
(
a int identity,
b char(10)
);
GO

insert into smallrows values('row 1');
insert into smallrows values('row 2');
insert into smallrows values('row 3');
insert into smallrows values('row 4');
insert into smallrows values('row 5');

truncate table sp_table_pages;

insert into sp_table_pages exec('dbcc ind(adventureworks,smallrows,-1)');

select pagefid,pagepid from sp_table_pages where pagetype=1;
结果:
pagefid pagepid
------- -----------
1 21920

(1 行受影响)

use adventureworks;
GO
dbcc traceon(3604);
GO
dbcc page(adventureworks,1,21920,1)

下面是dbcc page的输出结果

PAGE: (1:21920)

BUFFER:

BUF @0x0000000083FF9480

bpage = 0x0000000083E52000 bhash = 0x0000000000000000 bpageno = (1:21920)
bdbid = 6 breferences = 0 bUse1 = 3478
bstat = 0xc0010b blog = 0x212121bb bnext = 0x0000000000000000

PAGE HEADER:

Page @0x0000000083E52000

m_pageId = (1:21920) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 347 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594060668928
Metadata: PartitionId = 72057594054377472 Metadata: IndexId = 0
Metadata: ObjectId = 775673811 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 18 m_slotCnt = 5 m_freeCnt = 7981
m_freeData = 201 m_reservedCnt = 0 m_lsn = (134:472:3)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:16176) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:

Slot 0, Offset 0x60, Length 21, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x000000000E22C060

0000000000000000: 10001200 01000000 726f7720 31202020 ?........row 1
0000000000000010: 20200200 fc?????????????????????????? ...

Slot 1, Offset 0x75, Length 21, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x000000000E22C075

0000000000000000: 10001200 02000000 726f7720 32202020 ?........row 2
0000000000000010: 20200200 fc?????????????????????????? ...

Slot 2, Offset 0x8a, Length 21, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x000000000E22C08A

0000000000000000: 10001200 03000000 726f7720 33202020 ?........row 3
0000000000000010: 20200200 fc?????????????????????????? ...

Slot 3, Offset 0x9f, Length 21, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x000000000E22C09F

0000000000000000: 10001200 04000000 726f7720 34202020 ?........row 4
0000000000000010: 20200200 fc?????????????????????????? ...

Slot 4, Offset 0xb4, Length 21, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x000000000E22C0B4

0000000000000000: 10001200 05000000 726f7720 35202020 ?........row 5
0000000000000010: 20200200 fc?????????????????????????? ...

OFFSET TABLE:

Row - Offset
4 (0x4) - 180 (0xb4)
3 (0x3) - 159 (0x9f)
2 (0x2) - 138 (0x8a)
1 (0x1) - 117 (0x75)
0 (0x0) - 96 (0x60)

现在删除中间一行数据(where a=3)并再次查看分页的内容

use adventureworks;
GO
delete from smallrows where a=3;
GO
dbcc traceon(3604);
GO
dbcc page(adventureworks,1,21920,1)

下面是第二次执行dbcc page的输出结果

PAGE: (1:21920)

BUFFER:

BUF @0x0000000083FF9480

bpage = 0x0000000083E52000 bhash = 0x0000000000000000 bpageno = (1:21920)
bdbid = 6 breferences = 3 bUse1 = 3693
bstat = 0xc0010b blog = 0x212121bb bnext = 0x0000000000000000

PAGE HEADER:

Page @0x0000000083E52000

m_pageId = (1:21920) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8008
m_objId (AllocUnitId.idObj) = 347 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594060668928
Metadata: PartitionId = 72057594054377472 Metadata: IndexId = 0
Metadata: ObjectId = 775673811 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 18 m_slotCnt = 5 m_freeCnt = 8002
m_freeData = 201 m_reservedCnt = 21 m_lsn = (134:488:2)
m_xactReserved = 21 m_xdesId = (0:18192) m_ghostRecCnt = 0
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:16176) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:

Slot 0, Offset 0x60, Length 21, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x000000000E22C060

0000000000000000: 10001200 01000000 726f7720 31202020 ?........row 1
0000000000000010: 20200200 fc?????????????????????????? ...

Slot 1, Offset 0x75, Length 21, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x000000000E22C075

0000000000000000: 10001200 02000000 726f7720 32202020 ?........row 2
0000000000000010: 20200200 fc?????????????????????????? ...

Slot 3, Offset 0x9f, Length 21, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x000000000E22C09F

0000000000000000: 10001200 04000000 726f7720 34202020 ?........row 4
0000000000000010: 20200200 fc?????????????????????????? ...

Slot 4, Offset 0xb4, Length 21, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x000000000E22C0B4

0000000000000000: 10001200 05000000 726f7720 35202020 ?........row 5
0000000000000010: 20200200 fc?????????????????????????? ...

OFFSET TABLE:

Row - Offset
4 (0x4) - 180 (0xb4)
3 (0x3) - 159 (0x9f)
2 (0x2) - 0 (0x0)
1 (0x1) - 117 (0x75)
0 (0x0) - 96 (0x60)

注意在堆表中,这行数据就不会出现在分页中了。分页底部的行偏移数组显示第三行数据(槽2)现在位于偏移量0处(这意味着现在没有行使用槽2),而占用槽3的行仍然在位于和删除前相同的偏移量处。分页上的数据并没有被压缩。

除了分页上的空间不被回收之外,堆表中的空闲分页也经常无法被回收。即使从堆表中删除了全部记录。SQL Server不会将空闲分页标记成未分配状态,因此这部分空间无法供其他对象使用。目录视图sys.dm_db_partition_stats会显示这块空间仍然属于堆表。

从B树中删除记录
在索引的叶级别(无论聚集索引还是非聚集索引),当行记录被删除时,SQL Server会将其标记为虚影记录。这意味着这行记录仍然保留在分页上但是SQL Server会修改记录头中的一个比特位用来表示这行数据实际上是一个虚影。页头中也会反映出分页上的虚影记录数量。虚影记录有多种用途。它们可以使用回滚操作具有更高的效率;如果这行记录还没有被物理地移除掉,那么SQL Server要回滚删除操作的话就只需修改表示行为虚影的那个比特位即可。同样,虚影记录也是一种针对键范围锁定以及其他锁定模式的并发优化。虚影记录也被用来支持行版本控制。

虚影记录迟早还是会被清除掉的,取决于系统的负荷情况,而且有时SQL Server会在你有机会查看到以前就将其清除掉了。在下面所示的代码中,如果执行delete操作并等待一会儿再运行dbcc page的话,虚影记录可能就真的消失了。这就是为什么要在运行delete以前察看表中的分页数量,这样就可以在查询窗口中一次点击执行delete和dbcc page命令。为了确保虚影不被清除掉,可以将delete放到一个用户事务中并且在检查分页之前不提交或回滚该事务。清理线程不会清除属于活动事务的虚影记录。另外,可以使用未记载的跟踪标记661禁用虚影清理以确保像这个脚本一样运行浿时能够有一致的结果。通常,请记住未被记载的跟踪标记并不能保证在任何后续版本或者service pack中继续工作,并且不被微软支持。还要明确在完成测试以后必须关闭跟踪标记。

下面的例子新建了与前面delete例子中相同的数据表,但这次声明了一个主键,这意味着SQL Server会创建一个聚集索引。聚集索引的叶级别就是实际的数据,因此当记录被移除时,会被标记为虚影。

use adventureworks;
GO

drop table smallrows;
GO

create table smallrows
(
a int identity primary key,
b char(10)
);
GO

insert into smallrows values('row 1');
insert into smallrows values('row 2');
insert into smallrows values('row 3');
insert into smallrows values('row 4');
insert into smallrows values('row 5');

truncate table sp_table_pages;

insert into sp_table_pages exec('dbcc ind(adventureworks,smallrows,-1)');

select pagefid,pagepid from sp_table_pages where pagetype=1;
结果:
pagefid pagepid
------- -----------
1 21928

use adventureworks;
GO
delete from smallrows where a=3;
GO
dbcc traceon(3604);
GO
dbcc page(adventureworks,1,21928,1)

下面是dbcc page的输出结果:

PAGE: (1:21928)

BUFFER:

BUF @0x0000000084FF6000

bpage = 0x0000000084D80000 bhash = 0x0000000000000000 bpageno = (1:21928)
bdbid = 6 breferences = 3 bUse1 = 5368
bstat = 0xc0010b blog = 0x212121bb bnext = 0x0000000000000000

PAGE HEADER:

Page @0x0000000084D80000

m_pageId = (1:21928) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 348 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594060734464
Metadata: PartitionId = 72057594054443008 Metadata: IndexId = 1
Metadata: ObjectId = 791673868 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 18 m_slotCnt = 5 m_freeCnt = 7981
m_freeData = 201 m_reservedCnt = 0 m_lsn = (134:576:2)
m_xactReserved = 0 m_xdesId = (0:18204) m_ghostRecCnt = 1
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:16176) = 0x68 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:

Slot 0, Offset 0x60, Length 21, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x000000000E22C060

0000000000000000: 10001200 01000000 726f7720 31202020 ?........row 1
0000000000000010: 20200200 fc?????????????????????????? ...

Slot 1, Offset 0x75, Length 21, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x000000000E22C075

0000000000000000: 10001200 02000000 726f7720 32202020 ?........row 2
0000000000000010: 20200200 fc?????????????????????????? ...

Slot 2, Offset 0x8a, Length 21, DumpStyle BYTE

Record Type = GHOST_DATA_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x000000000E22C08A

0000000000000000: 1c001200 03000000 726f7720 33202020 ?........row 3
0000000000000010: 20200200 fc?????????????????????????? ...

Slot 3, Offset 0x9f, Length 21, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x000000000E22C09F

0000000000000000: 10001200 04000000 726f7720 34202020 ?........row 4
0000000000000010: 20200200 fc?????????????????????????? ...

Slot 4, Offset 0xb4, Length 21, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x000000000E22C0B4

0000000000000000: 10001200 05000000 726f7720 35202020 ?........row 5
0000000000000010: 20200200 fc?????????????????????????? ...

OFFSET TABLE:

Row - Offset
4 (0x4) - 180 (0xb4)
3 (0x3) - 159 (0x9f)
2 (0x2) - 138 (0x8a)
1 (0x1) - 117 (0x75)
0 (0x0) - 96 (0x60)

注意,这行记录仍然会出现在分页中,因为数据表带有聚集索引。行的头信息表明这的确是一条虚影记录。分页末尾的行偏移数组显示槽2中的记录仍然在相同的偏移量处而且所有记录都和删除以前所在的位置相同。另外,页头还提供了分页中虚影记录的数量(m_ghostRecCnt = 1)。可以通过sys.dm_db_index_physical_stats看到数据表中虚影记录的总数

删除索引结点级中的记录
当从表中删除一行数据时,SQL Server必须维护所有的非聚集索引,因为每个非聚集索引都有一个指针指向现在消失了的那条记录。索引结点分页中的记录在被删除以后并不是虚影记录,但是与堆表分页一样,直到新的索引行需要该分页中的空间时,SQL Server才会压缩空间。

回收分页
当数据分页中的最后一条记录都被删除时,整个分页就被回收。例外发生在表是堆表结构的时候,正如之前讨论过的(如果此分页是表中剩下的唯一分页的话是不会被回收的。一张数据表总是包含至少一个分页,即使该分页是空的)。数据分页的回收将导致SQL Server删除索引分页中指向被回收的数据分页的那行记录。如果索引行被删除的话,那么索引分页就会被回收(同样,可能在删除/插入/更新策略时发生),只在索引分页中保留一个条目。该条目会被移到相邻的分页中,然后SQL Server将回收这个空闲分页。

到目前为止,所有的讨论都集中在删除一行记录所必需的分页操作上。如果一个删除操作删除掉多行记录,用户必须注意一些其他事项。由于在单个查询中修改多条记录对于插入,更新以及删除操作来说都是相同的。

更新行
SQL Server有多种方式可以更新行记录,它会自动隐式地为特定的操作选择最快速的更新策略。在决定策略的过程中,SQL Server会估计受到影响的记录数,按照怎样的方式访问记录(通过扫描,或者一次索引和索引获取)以及是否会对索引键进行修改。更新可以由查询处理器或者存储引擎来控制。在本小节中,我们只研究更新究竟按照原位替换的方式进行还是SQL Server将其按照两个独立的操作来处理;删除旧行并插入新行。更新是由哪个组件控制的问题;查询处理器还是存储引擎,实际上与所有数据修改操作都有关联(并不只与更新相关)。

移动行
如果表中的一条记录必须移到新的位置时又会怎么样呢?在SQL Server 2005中,当一条带有变长字段的记录被更新了一个新的较大的值,使得原始分页无法再容纳此记录时,会发生移动行的情况。这也可能在改变聚集索引的字段时发生,因为数据行是根据聚集键进行逻辑排序的。举例来说,如果在lastname字段上有一个聚集索引,那么一条lastname值为Abbot的记录会存储到接近表头的位置。如果接着将lastname值更新为Zappa,那么就必须将这行数据移到靠近表尾的地方。

前面部分曾经了解了索引的结构并知道非聚集索引的叶级别的每个结点都包含一个行定位符(或者书签)指向数据表中的每行记录。如果表上有聚集索引,那么行定位符就是该行数据的聚集键。因此,当且仅当聚集索引键被更新时,才需要在每个非聚集索引中进行相应的修改。请在决定要在哪些字段上创建聚集索引的时候牢记这点。最好在非易失性的字段上创建聚集索引。

如果行的移动是因为原来的分页不能再容纳这行数据。它依然会保持相同的行定位符(换而言之,该行数据的聚集键保持不变),并且没有非聚集索引需要被修改。

在索引内部机理的讨论中也了解了如果数据表上没有聚集索引(换而言之,仍然是堆表结构),那么存在非聚集索引中的行定位符实际上就是行的物理位置。在SQL Server 2005中,如果堆中的一行记录要移到新的分页上去,该记录会在原来的位置留下一个“前转指针”(forwarding pointer)。SQL Server无需改变非聚集索引,它们依旧指向原来的位置,并从原位出发指向新的位置。

下面来看一个例子。首先新建一张表,与之前演示插入操作的那张表十分相似,但是该表还有第三个变长字段。当向这张表插入五条数据以后,会填满这个分页,然后更新其中一行记录使第三个字段变得相当长。原始分页就无法再容纳这行数据,因此必须被移动。接着可以将dbcc ind的输出结果导入sp_table_pages数据表中,以得到该表所使用的分页数目。

use adventureworks;
GO

drop table bigrows;
GO

create table bigrows
(
a int identity,
b varchar(1600),
c varchar(1600)
);
GO

insert into bigrows values(replicate('a',1600),'');
insert into bigrows values(replicate('b',1600),'');
insert into bigrows values(replicate('c',1600),'');
insert into bigrows values(replicate('d',1600),'');
insert into bigrows values(replicate('e',1600),'');
GO

update bigrows set c=replicate('x',1600) where a=3;
GO

truncate table sp_table_pages;

insert into sp_table_pages exec('dbcc ind(adventureworks,bigrows,-1)');

select pagefid,pagepid from sp_table_pages where pagetype=1;

查看输出结果:

pagefid pagepid
------- -----------
1 20996
1 20997

use adventureworks;
GO
dbcc traceon(3604);
GO
dbcc page(adventureworks,1,20996,1)
PAGE: (1:20996)

输出结果如下:

BUFFER:

BUF @0x0000000083FD7F80

bpage = 0x00000000835FE000 bhash = 0x0000000000000000 bpageno = (1:20996)
bdbid = 6 breferences = 0 bUse1 = 42333
bstat = 0xc0010b blog = 0x2121bbbb bnext = 0x0000000000000000

PAGE HEADER:

Page @0x00000000835FE000

m_pageId = (1:20996) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8
m_objId (AllocUnitId.idObj) = 349 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594060800000
Metadata: PartitionId = 72057594054508544 Metadata: IndexId = 0
Metadata: ObjectId = 823673982 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 8 m_slotCnt = 5 m_freeCnt = 1617
m_freeData = 8180 m_reservedCnt = 1606 m_lsn = (134:664:13)
m_xactReserved = 1606 m_xdesId = (0:18215) m_ghostRecCnt = 0
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:16176) = 0x63 MIXED_EXT ALLOCATED 95_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:

Slot 0, Offset 0x60, Length 1615, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x000000000E9DC060

0000000000000000: 30000800 01000000 0300f801 004f0661 ?0............O.a
0000000000000010: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000020: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000030: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000040: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000050: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000060: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000070: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000080: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000090: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000000A0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000000B0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000000C0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000000D0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000000E0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000000F0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000100: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000110: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000120: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000130: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000140: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000150: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000160: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000170: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000180: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000190: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000001A0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000001B0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000001C0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000001D0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000001E0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000001F0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000200: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000210: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000220: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000230: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000240: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000250: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000260: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000270: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000280: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000290: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000002A0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000002B0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000002C0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000002D0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000002E0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000002F0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000300: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000310: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000320: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000330: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000340: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000350: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000360: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000370: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000380: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000390: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000003A0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000003B0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000003C0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000003D0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000003E0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000003F0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000400: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000410: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000420: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000430: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000440: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000450: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000460: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000470: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000480: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000490: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000004A0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000004B0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000004C0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000004D0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000004E0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000004F0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000500: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000510: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000520: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000530: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000540: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000550: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000560: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000570: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000580: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000590: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000005A0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000005B0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000005C0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000005D0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000005E0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
00000000000005F0: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000600: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000610: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000620: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000630: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000640: 61616161 61616161 61616161 616161????aaaaaaaaaaaaaaa

Slot 1, Offset 0x6af, Length 1615, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x000000000E9DC6AF

0000000000000000: 30000800 02000000 0300f801 004f0662 ?0............O.b
0000000000000010: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000020: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000030: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000040: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000050: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000060: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000070: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000080: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000090: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000000A0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000000B0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000000C0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000000D0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000000E0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000000F0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000100: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000110: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000120: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000130: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000140: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000150: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000160: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000170: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000180: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000190: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000001A0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000001B0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000001C0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000001D0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000001E0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000001F0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000200: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000210: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000220: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000230: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000240: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000250: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000260: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000270: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000280: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000290: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000002A0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000002B0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000002C0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000002D0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000002E0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000002F0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000300: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000310: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000320: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000330: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000340: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000350: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000360: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000370: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000380: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000390: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000003A0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000003B0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000003C0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000003D0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000003E0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000003F0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000400: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000410: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000420: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000430: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000440: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000450: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000460: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000470: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000480: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000490: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000004A0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000004B0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000004C0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000004D0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000004E0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000004F0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000500: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000510: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000520: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000530: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000540: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000550: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000560: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000570: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000580: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000590: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000005A0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000005B0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000005C0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000005D0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000005E0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
00000000000005F0: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000600: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000610: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000620: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000630: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000640: 62626262 62626262 62626262 626262????bbbbbbbbbbbbbbb

a=3的记录所在槽2的内容如下:

Slot 2, Offset 0x1feb, Length 9, DumpStyle BYTE

Record Type = FORWARDING_STUB Record Attributes =
Memory Dump @0x000000000E9DDFEB

0000000000000000: 04055200 00010000 00?????????????????..R......

第一个字节的值为4(04)意味着这只是一个前转部分。后三个字节005205是记录移动的目的分页号。由于只是一个十六进制的值,需要将其转化成十进制值20997。下一组四个字节表明分页在文件号为1,槽号为0的地方。如果接着使用dbcc page来查看分页20997,可以看到前转记录的模样。

select CONVERT(bigint, CAST(0x005205 As varbinary));
--------------------
20997

(1 行受影响)

use adventureworks;
GO
dbcc traceon(3604);
GO
dbcc page(adventureworks,1,20997,1)

输出结果如下:

PAGE: (1:20997)

BUFFER:

BUF @0x0000000080FF9180

bpage = 0x0000000080E46000 bhash = 0x0000000000000000 bpageno = (1:20997)
bdbid = 6 breferences = 0 bUse1 = 44564
bstat = 0xc0010b blog = 0x12121bbb bnext = 0x0000000000000000

PAGE HEADER:

Page @0x0000000080E46000

m_pageId = (1:20997) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 349 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594060800000
Metadata: PartitionId = 72057594054508544 Metadata: IndexId = 0
Metadata: ObjectId = 823673982 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 8 m_slotCnt = 1 m_freeCnt = 4865
m_freeData = 4952 m_reservedCnt = 0 m_lsn = (134:664:10)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:16176) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:

Slot 0, Offset 0x6bb, Length 3229, DumpStyle BYTE

Record Type = FORWARDED_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x0000000010F1C6BB

0000000000000000: 32000800 03000000 0300f803 00530693 ?2............S..
0000000000000010: 0c9d8c63 63636363 63636363 63636363 ?...ccccccccccccc
0000000000000020: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000030: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000040: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000050: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000060: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000070: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000080: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000090: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000000A0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000000B0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000000C0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000000D0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000000E0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000000F0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000100: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000110: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000120: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000130: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000140: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000150: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000160: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000170: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000180: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000190: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000001A0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000001B0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000001C0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000001D0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000001E0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000001F0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000200: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000210: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000220: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000230: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000240: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000250: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000260: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000270: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000280: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000290: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000002A0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000002B0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000002C0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000002D0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000002E0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000002F0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000300: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000310: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000320: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000330: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000340: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000350: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000360: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000370: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000380: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000390: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000003A0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000003B0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000003C0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000003D0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000003E0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000003F0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000400: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000410: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000420: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000430: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000440: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000450: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000460: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000470: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000480: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000490: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000004A0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000004B0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000004C0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000004D0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000004E0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000004F0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000500: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000510: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000520: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000530: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000540: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000550: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000560: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000570: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000580: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000590: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000005A0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000005B0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000005C0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000005D0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000005E0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
00000000000005F0: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000600: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000610: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000620: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000630: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000640: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000650: 63636378 78787878 78787878 78787878 ?cccxxxxxxxxxxxxx
0000000000000660: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000670: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000680: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000690: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000006A0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000006B0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000006C0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000006D0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000006E0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000006F0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000700: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000710: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000720: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000730: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000740: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000750: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000760: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000770: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000780: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000790: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000007A0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000007B0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000007C0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000007D0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000007E0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000007F0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000800: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000810: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000820: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000830: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000840: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000850: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000860: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000870: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000880: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000890: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000008A0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000008B0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000008C0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000008D0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000008E0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000008F0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000900: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000910: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000920: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000930: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000940: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000950: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000960: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000970: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000980: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000990: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000009A0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000009B0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000009C0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000009D0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000009E0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
00000000000009F0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000A00: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000A10: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000A20: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000A30: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000A40: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000A50: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000A60: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000A70: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000A80: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000A90: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000AA0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000AB0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000AC0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000AD0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000AE0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000AF0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000B00: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000B10: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000B20: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000B30: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000B40: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000B50: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000B60: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000B70: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000B80: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000B90: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000BA0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000BB0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000BC0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000BD0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000BE0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000BF0: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000C00: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000C10: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000C20: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000C30: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000C40: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000C50: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000C60: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000C70: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000C80: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000C90: 78787800 04045200 00010002 00????????xxx...R......

OFFSET TABLE:

Row - Offset
0 (0x0) - 1723 (0x6bb)

前转指针
前转指针允许用户在堆中修改数据而无需担心对非聚集索引带来较严重的影响。如果一条已经被前转的记录必须再次移动,最初的前转指针就被更新并指向新的位置。如果还要继续移动,那么前转指针就会不断地指向另一个前转指针。另外,如果前转记录收缩到其原始位置足以能够容纳时,这条记录会被移回到原始位置,如果分页上还有可用空间的话,前转指针就会被删除。

SQL Server的后续版本可能会包括一些机制对堆表中的数据进行物理的重组,这将消除前转指针的作用。注意前转指针只存在于堆表中,并且alter table的重组选项对于堆表来说是不起作用的。可以对堆表上的非聚集索引进行碎片整理但是不能对表本身。目前来说,当前转指针创建完以后就永远呆在原地—除了个别例外情况。第一种例外已经提到过,行会收缩并返回其初始位置。第二种例外是数据库收缩时。当文件收缩时,SQL Server实际上会为书签进行再分配。收缩进程不会产生前转指针。对于因为收缩进程而被移除的分页来说,它们包含的任何前转指针或者残余部分实际上都处于“未前转”的状态。前转指针被移除的其他情况十分明显,譬如前转记录被删除或者在表上创建聚集索引从而使其不再是堆表结构。

Oracle Shared Pool Memory Management

Oracle在管理共享池内存方面面临着难以置信的挑战。多年来的所有改变、bug、补丁和各种性能问题都说明了这一点。虽然这可能会引起一些同情,但当面对与内存管理相关的棘手问题时,同情很快就会转化为愤怒。在本节中,我将解释如何管理共享池内存、多年来的管理进展、如何分配和释放内存、如何处理可能出现的4031错误,以及最后如何解决共享池锁存器争用。

From Hashing to Subpools
在Oracle 7和Oracle 8i中,共享池管理是在一种有趣的哈希结构帮助下执行的。如果还记得我们关于cache buffer 哈希链与library cache哈希链,那么这将非常有意义,但这里存在一种苦恼。当一个进程需要共享池中的内存时,它产生的哈希和链与所请求的内存大小相关。链也通常被称作heap,它是可用内存块链表。因此,从概念上讲,前几个链与大约1KB的内存块相关,后几个链与大约2KB的内存块相关,以此类推。虽然这确实很巧妙,但是经过一段时间对大小不一致的内存进行分配和释放之后,链实际上可以变成几千个节点长。请记住,哈希缓冲区链的大小平均在0到1之间。所以一个由几千个节点组成的链是巨大的。更糟的是,只有一个共享池latch锁来覆盖所有哈希链!清洗共享池帮助很大,因为链将减少到一个可观的规模。但这无法操作大型生产数据库,因此Oracle不得不进行更改。

Oracle9i引入了子池,这自然会导致多个共享池锁存器。基于哈希的策略被多个子池替换,每个子池包含一个在标准LRU策略上操作的堆。Oracle也开始标准化内存需求大小,这增加了找到可接受内存大小块的可能性。子池、多个共享池锁latch和LRU策略极大地减少了共享池内存管理问题。如果您同时管理过Oracle8i和Oracle9i系统,您可能会经历这种变化,并注意到有很大的不同。

数据库系统中共享池子池的数量可以通过查看实例参数_kghdsidx_count或通过计算x$kghlu视图中的行数来判断。

下面的查询显示了与共享池子池相关的一系列SQL语句。在这个例子中,一个大小为800MB的共享池存在三个子池。x$ksmss查询对于每个子池返回一行记录并且如果存在java pool还会另外加一行记录。设置子池数量的实例参数_kghdsidx_count不能被动态修改。如果你想影响Oracle调用一个子池号发生改变,你必须设置实例参数并回收实例。

SQL> @spspinfo
SQL> select sum(bytes/1024/1024) sp_size
2 from v$sgastat
3 where pool='shared pool';
SP Size (MB)
------------
         800
SQL> select count(*) no_sp from x$kghlu;
Num of SPs
----------
         4
SQL> select INST_ID, KSMDSIDX, KSMSSLEN
2 from x$ksmss
3 where ksmssnam='free memory';
INST_ID    KSMDSIDX   KSMSSLEN
---------- ---------- -----------
1          0          301989888
1          1          18818468
1          2          12659340
1          3          7697300
1          4          20482152
SQL> select i.ksppinm param,v.ksppstvl value
2 from x$ksppi i, x$ksppcv v
3 where v.indx=i.indx
4 and v.inst_id=i.inst_id
5 and i.ksppinm='_kghdsidx_count';
PARAM                VALUE
-------------------- -----
_kghdsidx_count      4

Oracle对子池的数量设置了严格的限制。在Oracle 11g中,可以使用7个共享池子池来启动实例,但有8个子池,该实例没有启动——实际上,在重新启动之前需要关闭实例。

有趣的是,Oracle不必遵从子池号的意愿。实际上,在一个类似于上面查询结果的Oracle数据库11.1g的示例中,实例参数被设置为2,实例重新启动,但是Oracle创建了三个子池。在Oracle数据库11.2g中,实例参数再次被设置为2,实例重新启动,并且按照指定的Oracle创建了两个子池。在没有手动设置实例参数的情况下运行Oracle数据库11.1g和11.2g, Oracle只创建了一个子池。因此,尽管你可以影响甲骨文,它仍然保留做出改变的权利。

内存分配与回收
内存分配是相当简单的。它遵循标准的LRU算法并与pinning与locking一起使用。当一个Oracle进程(服务器或后台进程)请求内存时,Oracle内核中的一部分称作为heap manager(堆管理器)的会被执行。虽然细节不断变化,但概念算法基本相同。

Oracle进程需要特定数量的内存,这些内存被转换为多个特定大小内存块的请求。堆管理器搜索与每个请求匹配的单个大小的内存块。多个内存块(认为是非连续的)是不行的。如果进程请求4KB内存,堆管理器必须从共享池内存中返回4KB内存块的地址。

在Oracle9i中,Oracle进程获得子池latch,并将在放弃之前搜索子池至多5次。因为内存的情况可能会发生急剧且快速的变化,允许多次传递会增加找到内存的可能性。然而在五次搜索之后,当持有各自共享池latch时,如果合适的大小的内存块没有找到,Oracle将会放弃,并posts错误代码4031,“out of mmemory”信息,并且会话将会停止处理。对于每个Oracle DBA来说,这在生产系统中将是不可接受的。

在Oracle Database 10g中,Oracle进程对内存的要求更加强烈。如果在五次搜索之后在当前共享池中没有找到合适的内存,进程将移动到另一个子池。这一过程将继续进行到所有定义的子池被搜索完为止。如果在这时,没有找到合适的内存,就像以前一样,Oracle将会放弃并posts 4031错误并且停止处理。Oracle在这个版本中所做的是消耗更多CPU和更长时间地持有共享池latch来减少返回错误消息的机会。从数据库操作的角度来看,性能较慢总比没有性能好。在我们解决性能问题时,至少可以执行工作。

当内存不足时,Oracle将回收不被频繁访问的内存块。可能在尝试检索SQL语句的文本时遇到过这种情况,并且它不再缓存在共享池中。幸运地是,Oracle不会回收内存供其它对象使用。例如,如果一个游标被pinned(固定)了,Oracle将不会回收相关的内存,不管该内存是不是被频繁访问。事实上,即使清除共享池也不会删除被固定的游标。如果真的想清空共享池并且想从头开始,可以重启实例。

共享池latch竞争识别与解决
共享池latch被用来序列化共享池内存的管理。这意味着像搜索内存,LRU活动,分配内存与回收内存请求共享池latch这样的操作。因为从Oracle 9i开始存在多个子池,并且每个子池有它自己的共享池latch,只要使用这个版本或之后的版本就可以大大减少共享池lat这个特别的解决方案非常简洁,因为它只需要很少的工作,而且我们不是在玩弄Oracle的共享池LRU算法。但是,请记住,更多的子池可能需要更多的共享池内存,需要重新启动实例才能使实例参数更改生效,Oracle保留不尊重您的建议的权利ch竞争的可能性。但有些时候这些仍然不够。下面有些方案可能减少latch获取时间latch持有时间或者两者都减少。

固定大且频繁使用的对象
此策略用来确保对象成功进入缓存,不用管内存活动或对象大小。任何包第一次被调用时,整个包被加载到内存中。操作中在激活共享池后如果需要触发,将强制执行大量的内存管理活动,这将导致对象不能被加载而触发4031错误。即使如果对象被成功加载,用户可能会注意到应用程序的延迟。

有些时候可能想要固定小对象。例如,假设一个对象有一种高强度活动模式,长时间的暂停导致对象的内存被释放,然后是另一段高强度活动。为了确保没有应用程序延迟且为了减少内存管理,我们可以简单固定对象。

大多数大型Oracle应用程序都提供一个脚本,其中包含要固定在共享池中的对象,并且它们将建议在实例启动后立即运行该脚本。重要的是要知道,即使您的应用程序供应商提供了这样一个列表,您也可以通过了解您的组织实际使用对象的方式来细化这个列表。供应商应用程序开发人员通常会创建固定列表。然而大多数应用程序开发商认为他们的对象是最重要的并且应该总是被固定。但实际上,很多时候,在应用程序在生产环境中运行之前,没有人真正知道您的组织将如何使用它。因此如果出现4031错误,这对于修改固定列表来说是一个好消息。

想要确保对象总是固定在共享池中有四个简单步骤要操作。关键词pin常被使用,dbms_shared_pool包的keep函数被用来确保对象保留在共享池中。缺省情况下当创建数据库时这个包不会被加载,因此第一步就是要加载它。下面的代码就是用来创建这个过程。

[oracle@jytest1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon May 6 14:30:28 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @$ORACLE_HOME/rdbms/admin/dbmspool.sql

Session altered.


Package created.


Grant succeeded.


Session altered.

下一步骤是找到大的或频繁的对象。Oracle保持对共享池对象使用进行跟踪并且可以通过v$db_object_cache视图来查看这些信息。下面是使用OSM脚本dboc.sql来识别潜在的对象。您可能会看到一组比其他包大得多的包,以及执行得比其他包频繁得多的包。还可能有一些对象,您个人知道它们具有不同寻常的执行配置文件,而您希望缓存它们。

一旦有了要保存的对象列表,下一步就是确定如何将它们放入缓存中。keep函数用于固定对象,或者更好地说,用于将对象保存在共享池中。

SQL> @dboc 10 20
old   9: where  a.sharable_mem >= &min_size
new   9: where  a.sharable_mem >= 20
old  10:   and  a.executions >= &min_exec
new  10:   and  a.executions >= 10

DB/Inst: jy/jy2                                                   07-May 08:26am
Report:   dboc.sql             OSM by OraPub, Inc.                Page         1
                      Oracle Database Object Cache Summary

                                                 Obj          Exe  Size
Owner        Obj Name                            Type Loads   (k)  (KB) Kept?
------------ ----------------------------------- ---- ----- ----- ----- -----
SYS          DBMS_STATS_INTERNAL                 PBDY     0    32   492 NO
SYS          PLITBLM                             PKG      0     8     8 NO
SYS          DBMS_ASSERT                         PBDY     0     6    16 NO
SYS          STANDARD                            PBDY     0     3    32 NO
SYS          DBMS_STATS_INTERNAL                 PKG      0     1   122 NO
SYS          DBMS_SQLDIAG                        PBDY     0     1    40 NO
SYS          DBMS_SQLTUNE_UTIL0                  PBDY     0     1    16 NO
SYS          DBMS_AUTO_TASK                      PBDY     0     0    24 NO
SYS          DBMS_AUTO_TASK                      PKG      0     0    28 NO
SYS          DBMS_STANDARD                       PKG      0     0    44 NO
SYS          DBMS_ADVISOR                        PBDY     0     0    69 NO
SYS          DBMS_SQLTUNE_UTIL2                  PBDY     0     0    20 NO
SYS          DBMS_UTILITY                        PKG      0     0    12 NO
SYS          PRVT_ADVISOR                        PBDY     0     0   176 NO
SYS          DBMS_SQLTUNE_UTIL1                  PBDY     0     0    57 NO
SYS          DBMS_STATS_ADVISOR                  PBDY     0     0   167 NO
SYS          DBMS_SYS_ERROR                      PBDY     0     0     8 NO
SYS          DBMS_OUTPUT                         PBDY     0     0    12 NO
SYS          DBMS_UTILITY                        PBDY     0     0    57 NO
SYS          DBMS_PDB                            PBDY     0     0    12 NO
SYS          DBMS_STATS_ADVISOR                  PKG      0     0    24 NO
SYS          DBMS_SQLTUNE_INTERNAL               PBDY     0     0   532 NO

22 rows selected.


SQL> l
  1  select a.owner ownerx,
  2         a.name  namex,
  3         decode(a.type,'PACKAGE','PKG','PACKAGE BODY','PBDY','FUNCTION','FNC','PROCEDURE','PRC') typex,
  4         a.loads/1000 loadsx,
  5         a.executions/1000 execsx,
  6         a.sharable_mem/1024 sizex,
  7         a.kept keptx
  8  from   v$db_object_cache a
  9  where  a.sharable_mem >= &min_size
 10    and  a.executions >= &min_exec
 11    and  a.type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
 12* order by executions desc, sharable_mem desc, name

为了将一个游标保存在共享池中,从v$sql,v$sqlarea或者v$open_cursor中收集它的地址与哈希值。下面的代码显示地址(6877c238)和哈希值(1356456286)在它们之间使用逗号进行连接作为一个参数输入,第二个参数是C,因为我们要保存一个游标。对于保存触发器参数为T,对于序列,使用Q,对于包,过程与函数,参数为P。

SQL> exec dbms_shared_pool.keep('6877C238,1356456286','C');
PL/SQL procedure successfully completed.

上面的代码片段可以用于编程结构,但是大多数人发现下面的选项最容易使用。下面的代码用来保存jy方案中的TuoMi过程。

SQL> exec dbms_shared_pool.keep('jy.TuoMi');

PL/SQL procedure successfully completed.

最后,在发出上述代码片段之后,您可以轻松地进行检查,以确保确实保存了对象。从下面的输出结果可以看到jy.TUOMI过程对象的Kept列被设置为YES。

SQL> @dboc 0 0
old   9: where  a.sharable_mem >= &min_size
new   9: where  a.sharable_mem >= 0
old  10:   and  a.executions >= &min_exec
new  10:   and  a.executions >= 0

DB/Inst: jy/jy2                                                   07-May 08:48am
Report:   dboc.sql             OSM by OraPub, Inc.                Page         1
                      Oracle Database Object Cache Summary

                                                 Obj          Exe  Size
Owner        Obj Name                            Type Loads   (k)  (KB) Kept?
------------ ----------------------------------- ---- ----- ----- ----- -----
SYS          DBMS_STATS_INTERNAL                 PBDY     0    32   492 NO
SYS          PLITBLM                             PKG      0     8     8 NO
SYS          DBMS_ASSERT                         PBDY     0     6    16 NO
..............
SYS          DBMS_SQLTUNE_INTERNAL               PKG      0     0    71 NO
JY           TUOMI                               PRC      0     0    36 YES
SYS          DBMS_SMB_INTERNAL                   PBDY     0     0    32 NO
SYS          DBMS_SQLTUNE                        PKG      0     0    32 NO
..............
99 rows selected.

经常有人问我,多长时间修改一次固定列表。就我个人而言,除非有很好的理由,否则我不喜欢调用任何数据库更改。改进固定列表的一个很好的理由是,如果系统突然开始出现共享池latch争用,或者遇到4031个错误。这一点非常重要:如果添加了应用程序功能、发生了应用程序升级或应用程序使用发生了显著变化,则从更主动的角度细化固定列表。

清空共享
虽然不在任何列表的最上面,但是只要刷新共享池就可以立即缓解共享池latch争用。对于oracle9i之前的系统尤其如此,那时还不存在子池。这很明显不是一个最优解决方案,因为每个对象都没有固定在共享池中将被删除并且它们的内存会被回收。初始结果可能会适得其反,因为它可能会导致立即进行大量的硬解析,正如我们所知,这会消耗大量CPU资源,并强制执行非自然数量的锁。然而,这种不幸的情况很快就会平息下来。

有时,共享池大小的组合,数据库版本(Oracle 9i)与应用程序的使用将使DBA别无选择,只能计划定期共享池刷新。这就是现实情况。

如下面的代码片段所示,刷新共享池非常简单,但效果确实显著

SQL> alter system flush shared_pool;

System altered.

增加子池数量
最简单、最强大和最合适的共享池latch解决方案之一是简单地添加子池,增加子池也将增加共享池latch。前面的“从哈希到子池”小节详细介绍了这个过程。这个特别的解决方案非常简洁,因为它只需要很少的工作,而且我们不是在玩弄Oracle的共享池LRU算法。但是,请记住,更多的子池可能需要更多的共享池内存,需要重新启动实例才能使实例参数更改生效,Oracle保留不尊重您的建议的权利。

减少共享池大小
这听起来可能很奇怪,在子池存在之前,增加共享池大小最终可能导致共享池latch争用。每一种算法的性能都是有限的,都是针对特定情况而设计的。当情况发生变化时,算法可能无法按预期执行。不要忘记,增加缓存来支持更多的活动几乎总是需要更多的CPU资源来管理。因此,可能会有一个收益递减点。Oracle最初的共享池内存管理算法在大约600MB的共享池中运行得相当好,但是当它达到750MB左右时,dba开始看到大量的共享池latch争用是非常常见的。一旦引入了子池,特别是与我概述的其他解决方案相结合,共享池latch争用就可以成功地解决。

4031错误解决方案
Oracle在决定什么时候放弃,什么时候继续使用CPU和保持latches之间有一个微妙的平衡。多年来,Oracle耗尽共享池内存的可能性已经降低,但是4031错误的几率仍然高度依赖于Oracle共享池内存的数量和应用程序。下面是一个实际的4031错误消息

ORA-04031: unable to allocate 4192 bytes of shared memory ("shared
pool","SELECT * F...","sql area (6,0)","kafco :
qkacol"):4031:375:2008:ocicon.c

上面的信息显示,4KB内存正尝试在子池6中进行分配,但是由于某些原因,不能完成分配。幸运地是有一些方法来减少收到4031错误的机会。

清空共享
与解决共享池latch争用一样,4031错误的一个解决方案也是清空共享池。虽然没有DBA愿意承认定期清空共享池,但这仍然有效。根据Oracle版本、分配的共享池内存的数量以及应用程序独特的内存使用模式,这可能是您的最佳选择。对于oracle 9i之前的系统尤其如此。

增加共享池大小
从概念上讲,增加共享池内存为Oracle提供了更大的灵活性来满足内存请求。然而,除了好处之外,在转移计算资源时也总是有成本的。在大多数情况下,收益实际上大于成本,因此,如果操作系统有可用内存,或者可以将内存从其他Oracle缓存转移到共享池,增加共享池内存很可能减少4031个错误。

请记住,每当您要求Oracle管理更多内存时,都需要更多的CPU来管理这些内存。在oracle9i之前的系统中尤其如此,因为可能存在非常长的内存链堆。如果链有数千个块长,而4031个错误可能会消失,那么在试图获取共享池latch和扫描长链时,这种情况可能会表现为严重的共享池latch争用和大量CPU消耗——所以要小心。

如Oracle文档所述,如果您通过自动内存管理获得解放,您可能需要设置最小的共享池大小。在增加缓冲区缓存的过程中,Oracle会自动减少共享池的大小,以至于开始出现4031个错误。

增加共享池保留大小
当一个较大的包最初被加载到一个已经非常活跃的共享池中时,就会出现一个常见的内存分配挑战。共享池越活跃,特别是当它很小并且对象大小非常不同时,就越有可能找不到所需的内存。

假设我们的服务器进程需要内存来存储一个大游标。当Oracle搜索共享池内存时,如果对象大小大于阈值,Oracle首先搜索保留区域。如果在保留区域中没有找到内存,Oracle将到非保留区域搜索。这种策略有助于将较小的对象排除在保留区域之外,从而将其保留给较大的对象。

有三个实例参数可以组合使用:
.shared_pool_reserved_size被用来直接设置共享池保留大小以字节为单位
.隐藏参数_shared_pool_reserved_pct,它的缺省值为5(5%),可以被用来代替shared_pool_reserved_size。
.一个相对大的对象是由实例参数_shared_pool_reserved_min_alloc来定义的,它的缺省值为4400字节。有趣的是缺省值4400字节仅仅比常见的单个块请求4096字节大。因此,在默认情况下,Oracle表示任何大于一个典型大小的内存块请求都被认为是大的,因此应该从保留的大小中获得内存。

前两个参数中的任何一个都可以用来为相对较大的对象设置共享池保留内存大小。如果您设置其中一个参数,Oracle将计算另一个参数。通过仔细调整这些参数,性能分析人员可以增加进程找到大量内存的可能性,同时仍然维护大量内存给相对较小对象使用。虽然这些参数通常不会调整,但如果发生4031错误,它们的小心调整可能会修复问题。

最小化游标固定时间
当执行游标时,游标也被固定。毕竟,您不希望SQL语句在执行期间突然消失!这是好消息。潜在的坏消息是,当执行完成时,固定游标被释放。如果没有其他进程固定游标,Oracle可以随意销毁,即释放关联的内存。现在假设有人想重新执行游标。如果它已被释放,将执行硬解析,因为整个游标将被重建!每个应用程序使用模式都是独特的;因此,当与更小的共享池或许多独特的SQL语句(或者两者都有)结合使用时,内存管理和库缓存活动可能会变得异常紧张。减少硬解析的一种方法是固定游标,使它们不能被释放。

Oracle提供了一个特殊的实例参数,该参数将保持所有会话的所有游标固定到关闭游标为止。但是,这种好处是以增加共享池内存消耗为代价的,因此,增加了接收4031错误的可能性。Oracle非常清楚这一点,所以为了鼓励回收释放内存并降低发生4031个错误的可能性,cursor_space_for_time实例参数默认设置为false。

如果系统正经历4031错误,你应该要检查cursor_space_for_time参数值。如果你的系统在过去某个时间点已经经历了严重的共享池latch急用,那么可以理解有人将cursor_space_for_time设置为true了。虽然你可能不会决定设置cursor_space_for_time参数为false。但这是一个有效选项应该被考虑。

减小保留对象的内存消耗
如果有太多对象通过执行dbms_shared_pool.keep过程被强制保留在共享池中,它们可能会消耗大量的内存Oracle可能无法成功地管理剩下的内存。此外,如果没有将大型对象保存在共享池中,则实例已经运行了一段时间,然后引用该对象,当强制加载该对象时,内存可能不可用。关键是不要随意地将对象保存在共享池中。

升级数据库版本到10gr2
当然,4031个错误不是升级的惟一原因,而是从Oracle数据库10gr2开始将内存标准化为4KB块。虽然我永远不会仅仅因为这个改进就建议升级到这个版本,但是这可能是升级的一部分原因。

就像对段区大小进行标准化一样,拥有标准的内存块大小可以提高快速找到合适内存的可能性。可以找到的内存越快,消耗的CPU周期就越少,必须持有共享池latch的时间就越短,存在大量浪费的小内存块的可能性就越小(增加4031错误的可能性)。

Proudly powered by WordPress | Indrajeet by Sus Hill.