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

解剖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
 
 
另一个也是相当强大的hex编辑器

 

 
只是使用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不到
 

 

《SQLSERVER2005存储引擎》

SQLSERVER2005存储引擎里面把cachestore翻译为 存储仓库

还有SQLOS

SQLSERVER的工作线程是映射到Windows的线程池,SQLSERVER每条工作线程内存的分配都是由Windows来分配

SQL2005引入SQLOS,开始由SQLSERVER自己来调度线程,而先前是由Windows来调度

 

《SQLSERVER2005: T-SQ程序设计》

人们总是说游标性能不好,这本书里面解释了游标实际上也有他的优势的地方

 

《SQLSERVER2008 TSQL查询》

脏读、幻读、重复读解释得特别清楚,对脏读、幻读、重复读搞不清楚的童鞋可以看一下
某些人会把重复读理解成幻读《Transaction And Lock--READ COMMITTED隔离级别下的"脏读"
分区表查询的内部原理
内部碎片和外部碎片

 

《深入解析SQLSERVER2008 》

DBCC的工作原理解释得很清楚,大部分内容跟《SQLSERVER2005存储引擎》有重叠

在最后一章DBCC 揭秘,译者把鬼影记录翻译为备份记录,搞到一头雾水

 

一边看书,一边思考

例如这篇文章《大表分批删除脚本》 作者为什么要写 DELETE TOP (5000) 呢? 有可能是5000行锁升级到表锁的原因

 

还有这一篇文章《恢复SQLSERVER被误删除的数据

大家看完书本之后,看一下存储过程的代码,自己是否理解里面代码的意思


结尾

SQLSERVER的水真的很深,我自己也没有办法精通,就例如 :一个开窗函数就可以写一本219页的书
《T-SQL性能调优秘笈——基于SQL Server 2012窗口函数》这本书的作者是《SQL2005技术内幕 TSQL查询》的作者之一:Itzik Ben-Gan   
 
 
我把这些笔记放上博客园目的只有一个“为大家提供尽可能多的笔记,让大家尽快学会SQLSERVER”
作为一位SQLSERVER MVP,我觉得自己已经尽了SQL Server MVP的义务,帮助大家迅速成长,成为数据库大牛
我高兴的是,还有很多使用SQL Server的大牛活跃在博客园里
这些SQLSERVER高手们还在不断的写文章,研究SQLSERVER,而且dudu园主也是不遗余力的将SQLSERVER方面的文章推上编辑推荐
 

 

解剖SQL Server系列目录

解剖SQLSERVER 第二篇  对数据页面头进行逆向(译)

解剖SQLSERVER 第三篇  数据类型的实现(译)

解剖SQLSERVER 第四篇  OrcaMDF里对dates类型数据的解析(译)

解剖SQLSERVER 第五篇  OrcaMDF里读取Bits类型数据(译)

解剖SQLSERVER 第六篇  对OrcaMDF的系统测试里避免regressions (译)

解剖SQLSERVER 第七篇  OrcaMDF 特性概述(译)

解剖SQLSERVER 第八篇  OrcaMDF 现在支持多数据文件的数据库(译)

解剖SQLSERVER 第九篇  OrcaMDF现在能通过系统DMVs显示元数据(译)

解剖SQLSERVER 第十篇     OrcaMDF Studio 发布+ 特性重温(译)

解剖SQLSERVER 第十一篇    对SQLSERVER的多个版本进行自动化测试(译)

解剖SQLSERVER 第十二篇   OrcaMDF 行压缩支持(译)

解剖SQLSERVER 第十三篇    Integers在行压缩和页压缩里的存储格式揭秘(译)

解剖SQLSERVER 第十四篇    Vardecimals 存储格式揭秘(译)

解剖SQLSERVER 第十五篇  SQLSERVER存储过程的源文本存放在哪里?(译)

解剖SQLSERVER 第十六篇 OrcaMDF RawDatabase --MDF文件的瑞士军刀(译)

解剖SQLSERVER 第十七篇 使用 OrcaMDF Corruptor 故意损坏数据库(译)

解剖SQLSERVER 完结篇 关于Internals Viewer源代码

 

 

由于本人精力有限而且E文水平不太好,翻译过程可能有错漏,望大家见谅

建议先看一下技术内幕的书,否则一头栽进代码你会理解不了

 

通过阅读这些译文大家可能会觉得SQLSERVER的新存储格式比较复杂,要赶上SQLSERVER的步伐不太容易,

改天微软推出一个SQLSERVER补丁包,并在补丁包里面添加新的存储格式你的软件可能又要歇菜了~

而我写这系列文章并不是要与这些数据库恢复软件商作对,而是让大家知道他们能够恢复哪些数据,有哪些数据超出了他们的数据恢复能力

 

Mark S. Rasmussen的PPT:

http://files.cnblogs.com/lyhabc/StoringCharacterDataOptimally%E6%95%B0%E6%8D%AE%E5%AD%98%E5%82%A8%E4%BC%98%E5%8C%96byorcaMDF%E5%A4%A7%E7%89%9BMarkS.Rasmussen.rar

posted @ 2014-10-16 17:18  桦仔  阅读(11633)  评论(88编辑  收藏