ooutwire's questions

SQL 服务器版本：

选择@@版本；

/*
微软 SQL Server 2012 - 11.0.2100.60 (X64)
Windows NT 6.1（内部版本 7601：Service Pack 1）上的开发人员版（64 位）
*/

问题：

所以，我在玩最大内存，我不小心将最大内存设置为 128MB，现在，我无法打开与 SQL Server 的连接。当我试图打开一个会话并修复错误时，我得到了这个美妙的弹出窗口。

为了自救，我决定在 SQL Server 中添加 -f 参数，并反弹服务。

在此处查看更多信息：

服务器内存服务器配置选项

和这里：

数据库引擎服务启动选项

重新启动服务后，我再次尝试连接。我收到以下消息：

根据我掌握的关于 -f 标志的信息，这是预料之中的。然后，我能够使用专用管理员连接 (DAC) 打开连接。然后我能够运行 sp_configure 并更改最大内存设置。这是我在进行更改之前在 sp_configure 中看到的内容。运行值似乎又是无穷大（或足够接近）。

我运行此更新以将最大内存增加到 3GB：

使用大师；
去
sp_configure
      '最大服务器内存 (MB)'
    , 3072;
去
重新配置；
去

然后我删除了 -f 参数并重新启动了 SQL Server。那时，服务器可以在多用户模式下访问，我的最大内存值等于 3GB。这不是我遇到过的最优雅的解决方案；但是，唉，它奏效了；所以我决定在这里分享。

有人有更快更清洁的解决方案吗？

我的一个朋友今天告诉我，我可以简单地分离然后重新附加数据库，而不是弹回 SQL Server，这个操作将从缓存中清除给定数据库的页面和计划。我不同意并在下面提供我的证据。如果你不同意我的观点或有更好的反驳，那就一定要提供。

我在这个版本的 SQL Server 上使用 AdventureWorks2012：

选择@@版本；
微软 SQL Server 2012 - 11.0.2100.60 (X64)
Windows NT 6.1（内部版本 7601：Service Pack 1）上的开发人员版（64 位）

加载数据库后，我运行以下查询：

首先，运行 Jonathan K 在此处找到的 AW 增肥脚本：

AW 发胖

--------------------------
-- 第 1 步：Bpool 的东西？
--------------------------
使用 [AdventureWorks2012]；
去

选择
     OBJECT_NAME(p.object_id) AS [ObjectName]
   , p.object_id
   , p.index_id
   , COUNT(*) / 128 AS [缓冲区大小(MB)]
   , COUNT(*) AS [buffer_count]
从
     sys.allocation_units 作为一个
     INNER JOIN sys.dm_os_buffer_descriptors AS b
           ON a.allocation_unit_id = b.allocation_unit_id
     INNER JOIN sys.partitions AS p
           ON a.container_id = p.hobt_id
在哪里
     b.database_id = DB_ID()
     和 p.object_id > 100
通过...分组
     p.object_id
   , p.index_id
订购方式
     buffer_count DESC；

结果显示在这里：

分离并重新附加数据库，然后重新运行查询。

--------------------------
-- 第 2 步：分离/附加
--------------------------
-- 分离
使用 [主]
去
EXEC master.dbo.sp_detach_db @dbname = N'AdventureWorks2012'
去

-  附
使用[主]；
去

创建数据库 [AdventureWorks2012] 开启
(
    文件名 = N'C:\sql server\files\AdventureWorks2012_Data.mdf'
)
    ,
(
    文件名 = N'C:\sql server\files\AdventureWorks2012_Log.ldf'
)
 附上；
去

现在 bpool 里有什么？

--------------------------
-- 第 3 步：Bpool 的东西？
--------------------------
使用 [AdventureWorks2012]；
去

选择
     OBJECT_NAME(p.object_id) AS [ObjectName]
   , p.object_id
   , p.index_id
   , COUNT(*) / 128 AS [缓冲区大小(MB)]
   , COUNT(*) AS [buffer_count]
从
     sys.allocation_units 作为一个
     INNER JOIN sys.dm_os_buffer_descriptors AS b
           ON a.allocation_unit_id = b.allocation_unit_id
     INNER JOIN sys.partitions AS p
           ON a.container_id = p.hobt_id
在哪里
     b.database_id = DB_ID()
     和 p.object_id > 100
通过...分组
     p.object_id
   , p.index_id
订购方式
     buffer_count DESC；

结果：

此时所有的读取都合乎逻辑吗？

--------------------------------
-- 第四步：逻辑只读？
--------------------------------
使用 [AdventureWorks2012]；
去

设置统计 IO 开启；   
    选择 * 从数据库日志；
    去
设置统计 IO 关闭；  

/*
（1597 行受影响）
表“数据库日志”。扫描计数 1，逻辑读取 782，物理读取 0，预读读取 768，lob 逻辑读取 94，lob 物理读取 4，lob 预读读取 24。
*/  

我们可以看到缓冲池并没有被分离/附加完全吹走。看来我朋友说错了。有没有人不同意或有更好的论点？

另一种选择是将数据库脱机然后联机。让我们尝试一下。

--------------------------------
-- 第 5 步：离线/在线？
--------------------------------
ALTER DATABASE [AdventureWorks2012] 设置离线；
去
ALTER DATABASE [AdventureWorks2012] 在线设置；
去

--------------------------
-- 第 6 步：Bpool 的东西？
--------------------------
使用 [AdventureWorks2012]；
去

选择
     OBJECT_NAME(p.object_id) AS [ObjectName]
   , p.object_id
   , p.index_id
   , COUNT(*) / 128 AS [缓冲区大小(MB)]
   , COUNT(*) AS [buffer_count]
从
     sys.allocation_units 作为一个
     INNER JOIN sys.dm_os_buffer_descriptors AS b
           ON a.allocation_unit_id = b.allocation_unit_id
     INNER JOIN sys.partitions AS p
           ON a.container_id = p.hobt_id
在哪里
     b.database_id = DB_ID()
     和 p.object_id > 100
通过...分组
     p.object_id
   , p.index_id
订购方式
     buffer_count DESC；

看来离线/在线操作效果要好得多。

我和我的老老板打赌。我跟她打赌，SQL Server 在分配新范围时，总是从缓冲池中分配，并且从不检查磁盘上是否有可以存储分配的位置。本质上，她认为 SQL Server 应该在分配页面之前检查 LUN 上的可用空间。这似乎是错误的，因为我可以将我的存储放在月球上，这会导致一些严重的延迟。我觉得她真的希望 SQL Server 总是先从磁盘中读取一个页面，然后再执行 DML 任务。

这是我证明她错了的“证据”。如果您不同意我的“证明”，那么请务必以更好的方式回应！

让我们创建一个简单的数据库和表。数据库的恢复模式将被设置为 SIMPLE 并且 AUTO_CREATE_STATISTICS 将被关闭，以最小化日志记录膨胀。

在我们开始之前，让我透露一下我正在使用的 SQL Server 的版本。

选择@@版本；
---------------------------------------------- ----------------------------------
微软 SQL Server 2012 - 11.0.2100.60 (X64)
Windows NT 6.1（内部版本 7601：Service Pack 1）上的开发人员版（64 位）

现在，代码...

使用大师；
去

IF DATABASEPROPERTYEX(N'PageAllocDemo' , N'Version') > 0
      开始
            ALTER DATABASE PageAllocDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
            DROP DATABASE PageAllocDemo;
      结尾;
去

创建数据库 PageAllocDemo
去

使用 PageAllocDemo；
去
设置无计数；
去

-- 将数据库设置为 SIMPLE 并关闭日志生成 crapola
ALTER DATABASE PageAllocDemo SET RECOVERY SIMPLE;
去
改变数据库 PageAllocDemo 设置 AUTO_CREATE_STATISTICS OFF；
去

创建表 dbo.X
(
      c1 整数标识 (1,1)
) 在 [主要] 上；
去

现在，让我们检查一下分配了多少页？我怀疑是零，因为我们只创建了一个“逻辑表”，在我们的例子中是一个空堆。

-- 我们的表分配了多少页？
DBCC IND (PageAllocDemo,X,-1);
去

现在，清除日志。

-- 清除日志
检查点；
去

日志中当前有什么？

-- 现在日志中有什么？
SELECT * FROM fn_dblog(NULL,NULL);
去

/*

--------------------------------------
-- 操作 -------------- 上下文 ---
--------------------------------------
LOP_BEGIN_CKPT LCX_NULL
LOP_XACT_CKPT LCX_BOOT_PAGE_CKPT
LOP_END_CKPT LCX_NULL

*/

这是预料之中的，因为我们处于 SIMPLE 恢复模型中。我们现在将创建一个显式事务，它将在我们的表中插入一条记录；但是，在我们这样做之前，让我们打开 Process Monitor 并过滤我们的 MDF 和 LDF 文件以及 SQL Server 进程的 PID。

开始交易：

开始交易

    插入 dbo.X 默认值；
    去

Process Monitor 显示两次写入事务日志文件。

让我们检查日志记录。

-- 现在日志中有什么？
    SELECT * FROM fn_dblog(NULL,NULL);

    /*
    我省略了PFS、GAM、SGAM等的所有日志记录。
    ---------------------------------------------- --------------
    -- 操作 -------------- 上下文 ------ 事务 ID ---
    ---------------------------------------------- --------------
    LOP_BEGIN_XACT LCX_NULL 0000:0000030e
    LOP_BEGIN_XACT LCX_NULL 0000:0000030f
    LOP_FORMAT_PAGE LCX_HEAP 0000:0000030f
    LOP_COMMIT_XACT LCX_NULL 0000:0000030f
    LOP_INSERT_ROWS LCX_HEAP 0000:0000030e
    LOP_COMMIT_XACT LCX_NULL 0000:0000030e

*/

我省略了位图和 PFS 分配，我们可以看到分配了一个页面，并按预期插入了一行。

有多少页分配给我们的堆？

-- 我们的表分配了多少页？
    DBCC IND (PageAllocDemo,X,-1);
    去

    /*

    一个 IAM 页面和一个数据页面，仅此而已
    ----------------------------------
    PageFID PagePID IAMFID IAMPID      
    ------ ---------- ------ ------
    1 264 空 空        
    1 231 1 264         

    */

这是预料之中的。我们有一个 IAM 页面和一个数据页面。现在，我们的倒数第二个动作是提交交易。我预计此时会发生 512B 日志块刷新。

提交交易；

让我们用检查点操作来完成“证明”。到目前为止，没有任何内容提交到数据文件，只提交到日志文件。

检查点；
去

很酷，数据页按预期刷新到磁盘。

我的结论是，从 Process Monitor 的证据来看，SQL Server 在内存中分配，在内存中添加记录，并将页面提交到磁盘，所有这些都没有在存储级别检查任何内容。

有人反对这个假设吗？如果是，为什么？

所以，假设我有一个页面，我打算将其拆分。

 
使用大师；
去

IF DATABASEPROPERTYEX (N'Pages', N'Version') > 0
开始
    ALTER DATABASE Pages SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    删除数据库页面；
结尾
去

创建数据库页面；
去

IF EXISTS（SELECT object_id FROM sys.objects WHERE name = 'PageSplit'）
开始
    删除表 PageSplit ;
结尾


使用页面；
去

创建表 PageSplit
(
      c1 整数标识
    , c2 VARCHAR(2000) 默认复制('b' , 1000)
);
去

创建唯一的聚集索引 CIX_c1
在 dbo.PageSplit 上
(
    [c1] 升序
);
去

插入 PageSplit 默认值；
去7

DBCC IND(Pages,PageSplit,-1);
去
DBCC TRACEON(3604) ;
去
DBCC 页面（页面，1,231,3）；
去
-- m_freeCnt = 977

此时，我只需要像这样更新一行，我就会得到一个拆分：3 行到一页，4 行到另一页：

更新分页
SET c2 = REPLICATE('b', 2000)
其中 c1 = 1 ;
去

所以，我的问题是：更新何时发生：在页面拆分之前还是之后？我假设它发生在分裂之后。

我附上了 SQL Profiler 和 DBCC IND 的输出。请注意，更新后的行保留在原始页面上。

我应用了 SQL Kiwi 的建议。在这里查看结果：

SQL Server 恢复模型更改是否记录在任何地方...WMI、错误日志...等？

例如，如果我从更改FULL为SIMPLE，这是否记录在任何地方？

我继续在许多论坛和许多博客上看到一个页面的组成如下所示： 页面大小：16 x 512B = 8192B 页面标题：= 96B 最大 In_Row 行：= 8060B

这留下 (8192 - 96 - 8060)B = 36B。

好的，这是合乎逻辑且正确的。我的问题是这样的：为什么这么多人说剩下的36B是为slot数组保留的？

显然，slot 数组在页面上每行给出 2B；所以，它可以小到 2B，大到 1472B：

2B：1 行 * 2B = 2B

1472B：8096B = n*9B（带开销的最小行大小……想想单个 TINYINT 列）+ n*2B（每行插槽数组成本）=> 8096 = 11n => n = 8096 / 11 = 736。

736*2B = 1472B。

由于 14B 版本标签，这让我达到了 20。

USE master ;
GO

CREATE DATABASE test ;
GO

USE test ;
GO

ALTER DATABASE test
    SET ALLOW_SNAPSHOT_ISOLATION ON ;
GO

ALTER DATABASE test
    SET READ_COMMITTED_SNAPSHOT ON ;
GO

DROP TABLE tbl ;
GO

CREATE TABLE tbl
(
      i CHAR(8000) DEFAULT(REPLICATE('a',8000))
    , j CHAR(53)   DEFAULT(REPLICATE('a',53))
) ;

INSERT INTO tbl 
    DEFAULT VALUES ;
GO

DBCC IND (test,tbl,-1) ;
GO
DBCC TRACEON(3604) ;
GO
DBCC PAGE(test,1,272,3) ;
GO

另一个例子。如果你从 49 到 50，你会得到 VARCHAR(MAX) 到 LOB_DATA。

DROP TABLE tbl ;
GO

CREATE TABLE tbl
(
      i VARCHAR(MAX) DEFAULT(REPLICATE('a',8000))
    , j CHAR(49)   DEFAULT(REPLICATE('a',49))
) ;

sp_tableoption N'tbl', 'large value types out of row', 'OFF' ;
GO

INSERT INTO tbl 
    DEFAULT VALUES ;
GO

DBCC IND (test,tbl,-1) ;
GO
DBCC TRACEON(3604) ;
GO
DBCC PAGE(test,1,272,3) ;
GO

即使在 SQL Server 2012 中，这个问题似乎仍然存在。@SQLKiwi 指向 Kimberly Tripp 的这篇文章 - http://www.sqlskills.com/blogs/kimberly/a-simple-start-table-creation-best-practices /。

-- 示例 1：使用聚簇索引

CREATE TABLE tbl5 
(
    i VARCHAR(900) NOT NULL 
) ;
GO
CREATE CLUSTERED INDEX CIX_tbl5
    ON tbl5 (i ASC) ;
GO

INSERT      INTO tbl5
            ( i )
VALUES
            ( REPLICATE('a' , 900) )
    ,       ( REPLICATE('b' , 900) ) 
    ,       ( REPLICATE('c' , 900) )
    ,       ( REPLICATE('d' , 900) ) 
    ,       ( REPLICATE('e' , 900) ) 
    ,       ( REPLICATE('f' , 900) ) 
    ,       ( REPLICATE('g' , 900) ) 
    ,       ( REPLICATE('h' , 900) )  -- 900 x 8  + (13 x 8) = 7200 + 104 = 7304
    ,       ( REPLICATE('i' , 761) ) ;-- 7304 + 761 + 13  
GO                                    -- = 8078 + 96(Page Header) + 18 (Slot)
                                      -- = 8192
                                      -- m_freeCnt = 0

DBCC IND(tempdb,tbl5,-1) ; -- everything is IN_ROW_DATA
GO
DBCC TRACEON(3604) ;
GO
DBCC PAGE(tempdb,1,177,3) ;
GO

-- 示例 2：使用堆。当“i”列为 392 m_freeCnt = 387 时。当“i”= 393 时，我得到一个新页面。为什么我不能向“i”添加更多字节？

CREATE TABLE tbl6
(
    i VARCHAR(900) NOT NULL 
) ;
GO
INSERT      INTO tbl6
            ( i )
VALUES
            ( REPLICATE('a' , 900) ) 
    ,       ( REPLICATE('b' , 900) ) 
    ,       ( REPLICATE('c' , 900) ) 
    ,       ( REPLICATE('d' , 900) ) 
    ,       ( REPLICATE('e' , 900) ) 
    ,       ( REPLICATE('f' , 900) ) 
    ,       ( REPLICATE('g' , 900) ) 
    ,       ( REPLICATE('h' , 900) ) 
    ,       ( REPLICATE('i' , 393) ) ;
GO

DBCC IND(tempdb,tbl6,-1); 
GO
DBCC TRACEON(3604);
GO
DBCC PAGE(tempdb,1,181,3);
GO

使用临时数据库；
去

删除表表；
去

创建表表
(
    我 SQL_VARIANT NOT NULL
);
去

插入表 (i)
    价值观 (1) ;
去

从表中选择我；
去

DBCC IND ('tempdb','tbl',-1) ;
去

DBCC TRACEON (3604) ; -- 页面转储将转到控制台
去

DBCC PAGE ('tempdb',1,157,3) ;
去

• 记录大小 = 17B
• 30000400 01000001 00110038 01010000 00
• 标记 A = 0x30 = 1B
• 标签 B = 0x00 = 1B
• 空位图偏移 = 0x0004 = 2B
• 列数 = 0x0001 = 2B
• 空位图 = 0x00 = 1B
• 可变长度列数 = 0x0001 = 2B
• 可变长度列偏移数组 = 0x0011 = 2B
• 这是整数的 sql_variant 编码...我猜= 0x0138 = 2B
• 我们的整数列 = 0x00000001 = 4B

    选择
      SQL_VARIANT_PROPERTY(i , 'BaseType') AS BaseType -- SYSNAME NVARCHAR(128)
    , SQL_VARIANT_PROPERTY(i , 'Precision') AS [Precision] -- INT
    , SQL_VARIANT_PROPERTY(i , 'Scale') AS Scale -- INT
    , SQL_VARIANT_PROPERTY(i , 'TotalBytes') AS TotalBytes -- INT
    , SQL_VARIANT_PROPERTY(i , 'Collat​​ion') AS [Collat​​ion] -- SYSNAME NVARCHAR(128)
    , SQL_VARIANT_PROPERTY(i , 'MaxLength') AS MaxLength -- INT
从
      表;
去

我的问题：

1. 0x3801...那是什么
2. 我 = 328792402 嗯？这个是从哪里来的？
3. SQL_VARIANT_PROPERTY() 表示我有一个整数。为什么不选择tinyint？
4. 有谁知道在哪里可以找到 SQL_VARIANT_PROPERTY()。我必须使用 DAC 才能获得它吗？

在 SQL Server 中，为什么在行中存储 9B 的 tinyint。由于某种原因，NULL 位图掩码的末尾似乎多了一个字节。

    使用临时数据库；
    去

    创建表表
    (
        我 TINYINT 不为空
    );
    去

    插入表 (i)
        价值观 (1) ;
    去

    DBCC IND ('tempdb','tbl',-1) ;
    去

    DBCC TRACEON (3604) ; -- 页面转储将转到控制台
    去

    DBCC PAGE ('tempdb',1,168,3) ;
    去

结果（由于 DBCC PAGE 首先显示最低有效字节，我反转了字节）：

Record Size = 9B
10000500 01010000 00
TagA = 0x10 = 1B
TagB = 0x00 = 1B
Null Bitmap Offset = 0x0005 = 2B
Our integer column = 0x01 = 1B
Column Count = 0x0001 = 2B
NULL Bitmap = 0x0000 = 2B (what!?)