解剖SQLSERVER 第一篇数据库恢复软件商的黑幕（有删减版）

这一系列，我们一起来解剖SQLSERVER

在系列的第一篇文章里本人可能会得罪某些人，但是作为一位SQLSERVER MVP，在我的MVP任期内希望可以对大家作出一些贡献

在第一篇里面涉及到某些内容可能不会以详细的方式给出截图并且和大家讲解，毕竟第一篇的篇幅比较长，希望大家见谅。。

在第一篇文章开始之前，先说三个题外话

第一个题外话更新丢失

首先要做的事情是，跟大家道歉

在之前《SQLSERVER走起》的微信公众帐号里推送了一篇文章，题目是《RDS-SQLSERVER的READ COMMITTED与READ_COMMITTED_SNAPSHOT的区别及各自优缺点》

由于当时没有仔细看，就给大家推送了，文章里面的建议确实是误导了大家

文章里面这样说道

举个例子描述这个场景：

T1事务发起一个修改，读取原库存是10，需求修改库存减1，原库存应该变成9，因为是READ_COMMITTED_SNAPSHOT隔离级别，所以数据库会在tempdb里生成一个快照，但是事务未提交，在这时发起了第二事务T2，也来修改库存，因为看到事务T1未提交，所以他不能获取未提交事务修改的值9（如果获取9就是脏读了），而是他获取的是最后提交版本的库存为10，而正巧T2未提交前，T1先提交了，实际库存应该变9而不是10，但T2事务获取库存值是10，假设T2的需求是减库2，那么最后T2提交后，会覆盖T1事务所做的修改，库存变成了8（我们实际期望的是10-1-2=7），这样就造成了逻辑混乱。

实际上，这种情况不是逻辑混乱，这种情况是属于 “更新丢失”，大家随便拿起一本SQLSERVER教科书，里面都会有说到更新丢失这种现象

文章里面说的解决方案基本上是错误的

错误一：提高事务隔离级别将会造成更加多的死锁

错误二：没有对“更新丢失”进行错误处理

我的好朋友高继伟（博客园里的shanks_gao）跟这个阿里云SQLSERVER经理说过这个问题，但是最后他还是没有改过来

解决方案有两种，都是在默认隔离级别 READ COMMITTED下，不需要修改默认隔离级别

第一种：使用try catch捕获更新丢失

--示例
CREATE TABLE kucun(id INT PRIMARY KEY,qty INT,product NVARCHAR(20))

--插入一些测试数据

SELECT * FROM dbo.kucun

-----------------------------------------------
--session 1
BEGIN TRAN 
DECLARE @qty INT
SELECT @qty=qty FROM kucun WITH(UPDLOCK)  WHERE [product]='牙膏'
SELECT @qty
UPDATE kucun SET qty=@qty-1 WHERE [product]='牙膏'
COMMIT TRAN
------------------------------------------
--session 2
BEGIN TRAN 
DECLARE @qty INT
SELECT @qty=qty FROM kucun WITH(UPDLOCK)  WHERE [product]='牙膏'
--阻塞
SELECT @qty
--session 1提交之后才可以读，但是后面的update语句不会执行，这个时候更新丢失，使用try catch机制来捕获更新丢失
UPDATE kucun SET qty=@qty-1 WHERE [product]='牙膏'
COMMIT TRAN

第二种：如果使用的是SQLSERVER2008 可以使用merge语句来执行这个原子操作

--session 1

BEGIN TRAN
MERGE [dbo].[kucun] AS TGT
    USING [dbo].[kucun] AS SRC
    ON  TGT.product = SRC.product AND TGT.id = SRC.id AND  TGT.product = '牙膏' 
    WHEN MATCHED THEN
        UPDATE SET
               TGT.qty = TGT.qty - 1;

COMMIT TRAN

SELECT * FROM [dbo].[kucun]  WHERE product = '牙膏'

--session 2

--当session 1没有提交的时候就会阻塞，当session 1提交的时候 session 2 也能成功update记录 ，不会造成更新丢失

BEGIN TRAN
MERGE [dbo].[kucun] AS TGT
    USING [dbo].[kucun] AS SRC
    ON  TGT.product = SRC.product AND TGT.id = SRC.id AND  TGT.product = '牙膏' 
    WHEN MATCHED THEN
        UPDATE SET
               TGT.qty = TGT.qty - 1;

COMMIT TRAN

希望大家升级一下SQLSERVER，使用SQLSERVER2008提供的最新的merge语句，因为merge语句确实能够减少很多不必要的麻烦，而且性能也会有提升

看到这里，可能大家对这种最基础最基础的知识不以为然，但是大家试想一下，恰好这种最基础的东西就有可能带来致命的后果

例子：

比如你的银行账户里有100万，你取出来了20万，还剩下80万

但是刚好遇到更新丢失，你的账户里面可能已经取出了钱但是系统里面没有扣取你的钱又或者扣除多了、扣除少了

后果可大可小

还有库存系统，这里就不说了

大家可能觉得“桦仔想借用这个例子，趁机贬低他人来抬高自己” 。实际上，我对于这个经理也是很理解，

当你管理成千成万台服务器的时候，你的脑子里就会想到数据库架构、集群搭建、容灾、业务连续性。。。

这是数据库架构师要做的事，很难会顾及到这些基础的东西，我自己也是管理着公司很多的数据库

但是作为数据库专家，你给客户的建议应该要足够专业吧？？？

第二个题外话估计行数

某一天，群里面某位童鞋给我看了一个执行计划，他说：“扫描运算符里面的估计行数为什麽误差这麽大？”

脚本

USE [sss]
SELECT @@VERSION
--Microsoft SQL Server 2005 - 9.00.4035.00 (X64) 
--Nov 24 2008 16:17:31 
--Copyright (c) 1988-2005 Microsoft Corporation
--Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)


CREATE TABLE teststat(id INT,name NVARCHAR(20))

--首先插入5条记录
INSERT INTO teststat
SELECT 1 ,'nihao' UNION ALL
SELECT 2 ,'dajiahao' UNION ALL
SELECT 3 ,'nihao' UNION ALL
SELECT 4 ,'dajiahao' UNION ALL
SELECT 5 ,'nihao' 

--显示实际执行计划
SELECT * FROM teststat
--预估行数5条

--查看缓存的执行计划
SELECT  [cacheobjtype] ,
        [objtype] ,
        [usecounts] ,
        [sql]
FROM    sys.[syscacheobjects]
WHERE   [sql] NOT LIKE '%cache%'
        AND [sql] LIKE '%INSERT INTO%'


--再插入100条记录
INSERT INTO teststat(id,name)
SELECT 6,'dajiahao'
GO 100


--查询缓存的执行计划
SELECT  [cacheobjtype] ,
        [objtype] ,
        [usecounts] ,
        [sql]
FROM    sys.[syscacheobjects]
WHERE   [sql] NOT LIKE '%cache%'
        AND [sql] LIKE '%INSERT INTO%'

--显示实际执行计划
SELECT * FROM teststat
--预估行数还是5条




--清空编译计划
DBCC FREEPROCCACHE
GO

--显示实际执行计划
SELECT * FROM teststat
--预估行数变成105条

第一次查看缓存的执行计划

第二次查看缓存的执行计划

清空plan cache之后

实际上，清空一下执行计划缓存就可以了，那位童鞋在第一次插入1条记录，第二次插入4条记录，

那么估计行数是1，实际行数是5，他就认为SQLSERVER估计得太不准确了，而且他认为统计信息有问题，

实际上，这个时候根本没有统计信息，又怎麽会跟统计信息扯上关系呢？

注意：DBCC FREEPROCCACHE是清空实例级别的计划缓存，请不要随意在生产环境下执行

第三个题外话性能太差的SQLSERVER

某一天，开发又抱怨了：“SQLSERVER很慢，查询要差不多9秒，这个问题怎麽彻底解决！”

这个例子不是证明SQLSERVER多牛逼，只是为了说明数据量大的时候，SQLSERVER也可以应付

开发查询的是一张xxclassifyxx表，表数据1.8亿+

查询语句如下

SELECT TOP 500  * FROM  DBO.xxCLASSIFYxx with (nolock) WHERE ID>5102332830 ORDER BY ID

表情况：

聚集索引建立在ID列上

上面的查询大概需要8秒

大家看到这个SQL语句可能一开始并没有什么头绪，但大家会发现，聚集索引既然建立在ID这一列上，那么ORDER BY ID是不是有点多余呢？？？

语句修改之前

执行时间 8秒
(500 行受影响)
表 'xxClassifyxx'。扫描计数 113，逻辑读取 619665 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

语句修改之后，只是去掉了ORDER BY ID

SELECT TOP 500  * FROM  DBO.xxCLASSIFYxx with (nolock) WHERE ID>5102332830

(500 行受影响)
表 'xxClassifyxx'。扫描计数 1，逻辑读取 32 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

修改之后所用时间

在这里，加ORDER BY ID和不加ORDER BY ID对于SQLSERVER来看是不同的

注意：不要乱用（forceseek表提示），本来应该使用索引扫描的让SQLSERVER使用索引查找，使用（forceseek表提示）之前一定要先测试一下

正题

使用Visual Studio查看数据库文件数据

对于需要经常和数据打交道的那些数据恢复人可能会喜欢使用winhex这款hex编辑器软件来查看文件数据

我最喜欢的功能是数据着色，根据SQLSERVER数据页面的大小 8192字节，你可以输入8192字节为

页面大小，然后winhex会以8192的块来交替着色

指定8192字节为一个页面

winhex就会8192字节的块大小来交替着色，比如单数页面以白色为底色，双数页面以灰色为底色

输入偏移值来定位某行数据

我相信对于winhex这个软件比我还要精通的人有很多，小弟就不献丑了，对这个软件不作介绍

实际上也可以使用Visual Studio来查看mdf文件的数据，把Visual Studio看成是winhex的精简版

其实编辑文件用其他的hex编辑器也是可以的，而不局限于winhex和Visual Studio ，例如Editplus 、NotePad++

在开始之前，大家可以看一下这篇文章，张充计算页面偏移位置的方式《SQL Server 简单模式下，误删除堆表记录如何恢复（绕过页眉校验）》

张充给出的页面偏移计算公式是:逻辑页号89 *8192 就是页面89 的偏移位置

按照公式逻辑页面号 * 8192 来算出偏移位置

这个计算公式没有错，只是，如果你真的要靠这个公式来找数据页面，很多时候会找不到

例如没有考虑到逻辑碎片的问题，而且我们平时搜索数据的时候一般都不会使用这个公式

还有大家在研究的时候不要将DBCC PAGE的输出中左边的偏移值和winhex中左边的偏移值对应起来

DBCC PAGE

WINHEX

测试脚本

View Code

我们看下怎么用VS查看数据文件内容

我们使数据库脱机，然后把mdf文件拖入VS

然后使数据库联机，这时候可以继续操作数据库，但是VS跟winhex都不会自动刷新你对数据库所做的修改

在这里VS当然没有winhex强大，winhex可以搜索文本字符无论是unicode还是ASCII

教程：在Winhex中搜索文本字符时注意

另一个也是相当强大的hex编辑器

HxD hex编辑器下载：

https://files.cnblogs.com/lyhabc/HxDhex%E7%BC%96%E8%BE%91%E5%99%A8.rar

《Unicode中文编码表》

只是使用VisualStudio可能会方便大家一边看代码一边研究文件结构

还有说一下，不要随意用VS和winhex修改数据，否则你的数据库有90%机率起不来喔

用winhex修改数据，选中要修改的数据的字节区域，然后右键—》edit-》你可以选择剪切、添加字节、填充零等等

用Visual Studio修改数据更简单，选中要修改的字节区域，然后直接输入16进制数就可以了

在修改数据之前，把页面校验设置为NONE

ALTER DATABASE [sss] SET PAGE_VERIFY NONE

如果你修改数据页头的话，在修改完毕之后数据库还可以联机，但是你修改数据行的话，问题就严重了

下面是本人玩坏了的数据库的报错信息汇总

824错误

消息 824，级别 24，状态 2，第 1 行
SQL Server 检测到基于一致性的逻辑 I/O 错误 校验和不正确(应为: 0xb70c8233，但实际为: 0xb7438233)。在文件 'E:\DataBase\sss.mdf' 中、偏移量为 0x0000000009a000 的位置对数据库 ID 8 中的页 (1:77) 执行 读取 期间，发生了该错误。SQL Server 错误日志或系统事件日志中的其他消息可能提供了更详细信息。这是一个威胁数据库完整性的严重错误条件，必须立即纠正。请执行完整的数据库一致性检查(DBCC CHECKDB)。此错误可以由许多因素导致；有关详细信息，请参阅 SQL Server 联机丛书。

消息 5028，级别 16，状态 4，第 1 行 系统无法激活足够的数据库来重建日志。 sss的 DBCC 结果。 CHECKDB 在数据库 'sss' 中发现 0 个分配错误和 0 个一致性错误。 消息 7909，级别 20，状态 1，第 1 行 紧急模式修复失败。您必须从备份中还原。

消息 601，级别 12，状态 3，第 1 行 由于数据移动，无法继续以 NOLOCK 方式扫描。

消息 926，级别 14，状态 1，第 1 行 无法打开数据库 'sss'。恢复操作已将该数据库标记为 SUSPECT。有关详细信息，请参阅 SQL Server 错误日志。 消息 5069，级别 16，状态 1，第 1 行 ALTER DATABASE 语句失败。 消息 5125，级别 24，状态 2，第 1 行 文件 'E:\DataBase\sss.mdf' 似乎已被操作系统截断。其大小应为 3072 KB，但实际大小为 3064 KB。 消息 3414，级别 21，状态 1，第 1 行 恢复期间出
错，导致数据库 'sss' (数据库 ID 8)无法重新启动。请诊断并纠正这些恢复错误，或者从已知的正确备份中还原。如果无法更正错误，或者为意外错误，请与技术支持人员联系。

分享某位牛人的代码

国外某位牛人开发了一个软件，这个软件能够读取SQLSERVER的mdf文件，而且这个软件支持大部分的SQLSERVER数据存储新格式

最重要的是，这个软件是开放源代码的

这个软件是一个winform程序

他里面带了四个测试数据库，支持SQL2005，SQL2008，SQL2008 R2，SQL2012

我们运行一下这个软件，OrcaMDF.OMS才是winform程序，其他项目只是程序集

我们打开AWLT2012.mdf

无论系统表、DMV、用户表、系统存储过程、系统视图都可以读取出来

附加到SQLSERVER之后，查看数据库属性，作者使用的是微软的标准示例数据库adventureworkoltp来做的测试

这几个库的版本号是611,655,661,706

我们可以自己新建一个数据库，然后测试一下

牛人的博客地址：http://improve.dk/

项目代码已经放上去GITHUB:https://github.com/improvedk/OrcaMDF

分享SQLSERVER技术内幕系列图书笔记

本人把一些SQLSERVER技术内幕读书笔记分享出来，其实也不算是分享，因为这些笔记一直躺在我的博客里

大家可以对我做的笔记进行搜索，技术内幕系列图书最大的一个特征是：Microsoft Press 权威性不可忽视

笔记地址

《Microsoft SQL Server 6.5 技术内幕笔记》

《Microsoft SQL Server 2005技术内幕：T-SQL查询笔记》

《Microsoft SQL Server 2005技术内幕：存储引擎笔记》

《Microsoft SQL Server 2005技术内幕查询、调整和优化笔记》

《Microsoft SQL Server 2005技术内幕： T-SQ程序设计笔记》

《深入解析SQL Server 2008 笔记》

《Microsoft SQL Server 2008技术内幕：T-SQL查询笔记》

《MICROSOFT SQL SERVER 2008技术内幕：T-SQL语言基础笔记》

《精通SQL Server2008程序设计笔记》

大家平时遇到的很多问题，这些书本里面基本都有解决方案，除非是特别新的技术

对于一些教条之类的方法、技巧、规定，书本里面都给出了为什麽这麽做，里面个中的原理

先说一下《SQLSERVER6.5 技术内幕》

SQL6.5技术内幕里面介绍了：SQLSERVER开发团队刚开始开发SQLSERVER的时候采用单进程多线程的方式

而没有使用ORACLE的多进程的方式，书本里面解释了原因

执行计划修剪，buffer pool污染（mysql也有污染现象好像从5.5版本解决了），原行更新，原页更新

数据页面在那个年代还是2KB而不是现在的8KB

某些存储过程还保留着SQL6.5的代码

EXEC sys.[sp_helpconstraint] @objname = N'[dbo].[nums]', -- nvarchar(776)
    @nomsg = '' -- varchar(5)


select object_id, type, name,[parent_object_id] from sys.objects where parent_object_id = OBJECT_ID('customer')
and type in ('C ','PK','UQ','F ', 'D ') -- ONLY 6.5 sysconstraints objects

如果大家有兴趣的话可以下载SQL6.5下来玩一下，如果大家能下载下来并能安装和运行的话，安装包只有100MB不到

http://msdn.itellyou.cn/