Microsoft SQL Server 6.5 技术内幕笔记

第一部分

ORACLE

内存分配（SGA和PGA）

SGA：是用于存储数据库信息的内存区，该信息为数据库进程所共享。它包含Oracle 服务器的数据和控制信息,它是在Oracle服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写。

每个线程55KB的内存

ole:object linking and embeding

SQL Executive就是现在的SQL Agent

网络输出缓冲区跟传输的包大小相同：4096字节

oracle规则优化器

sql2005之前用的规范化，sql2005开始使用代数化

（ISAM——索引顺序访问方法）它是一种索引机制，用于高效访问文件中的数据行。

（ISAM——索引顺序访问方法）是Indexed Sequential Access Method(索引顺序存取方法)的缩写是IBM公司发展起来的一个文件操作系统，可以连续地（按照他们进入的顺序）

或者任意地（根据索引）记录任何访问。

每个索引定义了一次不同排列的记录。一个职工数据库基于搜寻的信息可以有几个索引。

例如，按照职工所属科室的部门索引中，同时还有按照职工姓氏字母顺序排名的名字索引。每个索引中的关键词都是制定的。

对于职工名字字母顺序索引，姓就是指定的关键词。　　
ISAM的开发早于VSAM（虚位存储通道法）。

逻辑页码

以前磁盘小，所以数据页占用2KB

SQLSERVER6.5只实现了3个隔离级别：不包括repeatable read

SQLSERVER 更新数据行的三种方式：

原位更新：行大小跟原来一样

页内更新：行大小跟原来不一样，但行数据还保留在原数据页

完全更新：行大小跟原来不一样，行数据已经在另一个数据页，要修改整个索引

数据页面的预分配

如果是堆表就不会重新利用删除了的行的空间

rubbing：类似于mysql的 gap锁

索引B TREE长高，树长高的原理，索引的分裂，树长高

--重建索引的时候改变填充因子
ALTER INDEX [ix_counttb_id] ON [dbo].[counttb] REBUILD WITH (FILLFACTOR = 80)

SQLSERVER6.5的时候，缓冲区管理器也叫日志写入器，管理buffer pool和log write (LGWR)

SHUTDOWN WITH NOWAIT

sysindexes表

DBCC PINTABLE(DB_ID('test'),OBJECT_ID('test.dbo.test'))
--SELECT DB_ID('test')
--SELECT OBJECT_ID('test.dbo.test')
--* DBCC PINTABLE (db_id,object_id)
--将表数据驻留在内存中

DBCC PINTABLE会不会把表放到LRU 列表中 LRU LIST

mysql也是使用hash来快速访问内存中的页面的

EXEC sys.[sp_configure] @configname = 'advanced options', -- varchar(35)
    @configvalue = 1 -- int
RECONFIGURE WITH OVERRIDE
--HASH buckets
--DBCC BUFCOUNT 
--DBCC SQLPERF(HASHSTATS)

sp_repldone [ @xactid = ] xactid
    , [ @xact_seqno = ] xact_seqno
    [ , [ @numtrans = ] numtrans ]
    [ , [ @time = ] time
    [ , [ @reset = ] reset ]

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 1
注意  可以在紧急情况下使用此过程，以允许在有事务挂起复制时截断事务日志。使用此过程可防止 Microsoft® SQL Server™ 2000 在未发布和重新发布数据库之前复制数据库。

iocompletion 端口跟iocompletion等待不一样，iocompletion等待是物理I/O等待

charset这些工具在sqlserver2012里是没有的

二进制排序，bin

SELECT * FROM sysdevices
SELECT * FROM  sys.backup_devices
EXEC sys.[sp_helpdevice] @devname =  -- sysname

USE master        
GO        
sp_configure 'show advanced options', 1 
GO    
sp_configure 'remote admin connections', 1 
GO        
RECONFIGURE WITH OVERRIDE    
GO
 
 
SELECT *  FROM sys.configurations where name = 'remote admin connections'

现在sqlserver2012新建库是3.75MB大小

master数据库包含31个系统表，其中18个表在用户库中也有，另外13个表则用于跟踪服务器信息。其中的三个表（syslocks、syscurconfigs、sysprocesses）

并不在物理上存在于master数据库中，他们是随着用户的每一次查询他们而动态建立的。存在内存中

master数据库还包含10个称为“伪系统表”的表。这些表的名字以“spt-”开头。这些表用作各个系统存储过程的存储区，但他们不是真正的系统表。

你永远不要直接修改他们，因为这样做会打断某些系统存储过程，但删除他们不会破坏数据库的基本完整性。如果实际的系统表被修改了，

数据库的基本完整性就会被破坏

[dbo].[spt_fallback_db]
[dbo].[spt_fallback_dev]
[dbo].[spt_fallback_usg]
[dbo].[spt_monitor]
[dbo].[spt_values]

spt：system procedure table

sysdatabases表的含义

logptr：指向该数据库中事务日志第一个页的指针

系统表sysusers，限制哪些用户可以访问数据库，跟mysql一样

在sqlserver2008r2里封装为视图

对gz的帐号进行权限判断

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
-- Note: gid (max group id) not maintained, shiloh logic not correct anyway
--
CREATE VIEW sys.sysusers AS
    SELECT uid = convert(smallint, u.id),
        status = convert(smallint, case u.type
            when 'U' then 12 when 'G' then 4 when 'A' then 32
            when 'L' then 16 + (u.status & 1)*12 else 0 end),
        u.name, u.sid,
        roles = convert(varbinary(2048), null),
        createdate = u.created,
        updatedate = u.modified,
        altuid = convert(smallint, r.indepid),
        password = convert(varbinary(256), null),
        gid = convert(smallint, case u.type when 'R' then u.id else 0 end),
        environ = convert(varchar(255), null),
        hasdbaccess = convert(int, case when p.state in ('G','W') then 1 else 0 end),
        islogin = convert(int, case u.type when 'A' then 0 when 'R' then 0 else 1 end),
        isntname = convert(int, case u.type when 'U' then 1 when 'G' then 1
            when 'L' then (u.status & 1) else 0 end),    -- USR_SID_EXTERNAL
        isntgroup = convert(int, case u.type when 'G' then 1 else 0 end),
        isntuser = convert(int, case u.type when 'U' then 1 when 'L' then (u.status & 1) else 0 end),    -- USR_SID_EXTERNAL
        issqluser = convert(int, case u.type when 'S' then 1 else 0 end),
        isaliased = convert(int, case u.type when 'L' then 1 else 0 end),
        issqlrole = convert(int, case u.type when 'R' then 1 else 0 end),
        isapprole = convert(int, case u.type when 'A' then 1 else 0 end)
    FROM sys.sysowners u
    LEFT JOIN sys.sysprivs p ON p.class = 0 AND p.id = 0 AND p.subid = 0 AND p.grantee = u.id AND p.grantor = 1 AND p.type = 'CO'
    LEFT JOIN sys.syssingleobjrefs r ON r.depid = u.id AND r.depsubid = 0 AND r.class = case u.type when 'R' then 51 when 'L' then 72 end -- SRC_ROLEOWNER, SRC_ALIASUSER
    WHERE has_access('US', u.id) = 1
GO

CREATE TABLE [sys].[sysowners]
(
[id] [int] NOT NULL,
[name] [sys].[sysname] NOT NULL,
[type] [char] (1) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL,
[sid] [varbinary] (85) NULL,
[password] [varbinary] (256) NULL,
[dfltsch] [sys].[sysname] NULL,
[status] [int] NOT NULL,
[created] [datetime] NOT NULL,
[modified] [datetime] NOT NULL
) ON [PRIMARY]
GO

在邀月的方法三里：

http://www.cnblogs.com/downmoon/archive/2009/12/13/1623004.html

use DB_NAME
sp_dboption DB_NAME, "trunc. log on chkpt.", true
checkpoint
sp_dboption DB_NAME, "autoshrink", true

trunc. log on chkpt.

exec sp_dboption

pub数据库，图书管理系统示例库

EXEC [sys].[sp_helpdb] -- = NULL -- sysname

worktable ：group by ，order by，union，游标都用到tempdb

tempdb初始大小为2MB

错误号1105表明tempdb已满

sqlserver重启的时候，tempdb沿用上次的大小，而不是总是2mb

http://www.cnblogs.com/stswordman/p/3358496.html

Tempdb initial size和dbcc shrinkfile

对master数据库进行备份

SET ANSI_PADDING ON

数据类型和范围值

BLOB使用一个16字节的指针，外加存储实际长度所需数目的2K页面

文本和图形不能共享页面

在文本或图像列中输入的单个字节也要占用2KB页面

避免使用null，定义的时候应该是not null

2048字节-2016字节=32字节

现在的sqlserver页头96字节=32*3 多了3倍

内部存储细节

内部系统表

sysobjects(系统对象)

sysindexes（系统索引）

syscolumns（系统列）

sysreferences（系统引用，外键）

sysconstraints（系统约束）

sysobjects(系统对象)：表名，对象id，该表的所有者名字

sysindexes（系统索引）：指向该表的第一个数据页面和最后一个数据页面，表大小，区数extent，数据页面数量，当前表行数

syscolumns（系统列）：表列名，数据类型，列长度，列id

first 页和root 页

http://technet.microsoft.com/zh-tw/library/ms190283(SQL.90).aspx

sys.sysindexes (Transact-SQL)

traverse：横越；旋转；来回移动

数据页，页眉，数据行细节

变长列

buffer缓冲区，page header 页头，data数据，offset table偏移表

sysindexes.minlen

minlen，maxlen

对于只有定长列的表，sysindexes.minlen和sysindexes.maxlen是相等的

对于含变长列的表，minlen和maxlen的值是不相等的

minlen的值等于该表所有定长列之和加2字节

maxlen等于定长列和变长列（syscolumns.length）之和再加上一定数量的字节

--SQL2005 DBCC PAGE
  13 PAGE HEADER:
  14 
  15 
  16 Page @0x1A096000
  17 
  18 m_pageId = (1:14517)                 m_headerVersion = 1                  m_type = 1
  19 m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x200
  20 m_objId (AllocUnitId.idObj) = 317    m_indexId (AllocUnitId.idInd) = 256  
  21 Metadata: AllocUnitId = 72057594058702848                                 
  22 Metadata: PartitionId = 72057594049462272                                 Metadata: IndexId = 1
  23 Metadata: ObjectId = 1022626686      m_prevPage = (1:14514)               m_nextPage = (1:14518)
  24 pminlen = 16                         m_slotCnt = 102                      m_freeCnt = 38
  25 m_freeData = 7950                    m_reservedCnt = 0                    m_lsn = (2568:5252:8)
  26 m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
  27 m_tornBits = -1007571449             
  28 
  29 Allocation Status
  30 
  31 GAM (1:2) = ALLOCATED                SGAM (1:3) = NOT ALLOCATED           
  32 PFS (1:8088) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                      DIFF (1:6) = NOT CHANGED
  33 ML (1:7) = NOT MIN_LOGGED    

Page @0x1A096000 436822016

     @0x0A64DEC1 174382785

Slot 101 Offset 0x1ec1 Length 77
3672 
3673 Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
3674 
3675 Memory Dump @0x0A64DEC1
3676 
3677 00000000:   30001000 cf000000 db2e7c01 fea10000 †0.........|.....         
3678 00000010:   0600c004 0021003b 0047004d 00ce0000 †.....!.;.G.M....         
3679 00000020:   002d4efd 56604f7d 59096750 966c51f8 †.-N.V`O}Y.gP.lQ.         
3680 00000030:   53580058 0006526c 51f85300 952e55e8 †SX.X..RlQ.S...U.         
3681 00000040:   90320030 00370000 952e55c4 7e††††††††.2.0.7....U.~            
3682 
3683 Slot 101 Column 0 Offset 0x1d Length 4
3684 
3685 UNIQUIFIER = 206                     
3686 
3687 Slot 101 Column 1 Offset 0x21 Length 26
3688 
3689 Company = 中国你好有限公司XX分公司

sysindexes.root，sysindexes.first

distribution page分布页 统计信息，分布页是一个单独的页，用作一个直方图（是索引中键值的抽样图）

DBCC DBREINDEX

PAD_INDEX

IGNORE_DUP_KEY 忽略重复键

用户定义数据类型UDDT

规范化阶段和优化阶段，优化阶段才检查UDDT

bcp命令使用-E参数保留标识值

sql2012重启之后会出现自增列值跳跃增长

http://social.technet.microsoft.com/Forums/zh-CN/65ff4cb5-0a33-4d6b-b332-2ac9a728dcac/sql?forum=sqlserverzhchs

对添加数据，FILLFACTOR 填充因子不起作用

http://www.cnblogs.com/CareySon/archive/2012/01/06/2313897.html

不要在real类型和float类型上建立唯一索引或者primary key

alter table drop constraint

references

利用alter table的时候也可以指定fillfactor 填充因子

两表互相引用发生自展问题bootstrap问题

延迟约束

先利用alter table tablename nocheck constraint暂时停用约束，然后导入数据之后再开alter table tablename check constraint

这样sqlserver不检查在禁用约束时所添加的数据

添加约束的时候不检查约束，这样就可以越过重复值

--数据中心有重复值我估计是因为使用禁用约束的方式来建立约束

--建立约束的时候先指定nocheck，不检查当前表的重复或null值
ALTER TABLE [testidentity] WITH NOCHECK
ADD CONSTRAINT [CK_NOTNULL] CHECK (name IS NOT NULL)


--建立约束的时候默认是检查当前表的重复或null值
ALTER TABLE [testidentity] WITH CHECK
ADD CONSTRAINT [CK_NOTNULL] CHECK (name IS NOT NULL)

--建立完约束之后再开启约束，这样即使表里有重复值或者null值依然也可以建立约束，但是这个约束对建立约束之前的数据无效
ALTER TABLE [testidentity] CHECK CONSTRAINT [CK_NOTNULL]

--禁用约束
ALTER TABLE [testidentity] NOCHECK CONSTRAINT [CK_NOTNULL]

--开启了约束的话就会报错
消息 547，级别 16，状态 0，第 1 行
INSERT 语句与 CHECK 约束"CK_NOTNULL"冲突。该冲突发生于数据库"sss"，表"dbo.testidentity", column 'name'。
语句已终止。

正则表达式

insert 表 default values

默认约束名字的由来

各种类型的列级约束都采用这种命名方法【[PK__customer__E84D41E808EE5046]】

NULL和NOT NULL不作为一种约束对象，所以在sysobjects系统表中不会记录

--PK:PRIMARY KEY
--UN:UNIQUE
--FK:FOREIGN KEY
--DF:DEFAULT

书中68E79C55是四个字节

sql2008r2中E84D41E808EE5046是八个字节

--E84D41E808EE5046就是该约束的对象ID。这一数值用作系统表sysobjects的id列
--也用作系统表sysconstraints的constid列
SELECT OBJECT_NAME(0xE84D41E808EE5046) --无结果
SELECT OBJECT_ID('[dbo].[customer]')--117835732
SELECT * FROM [sys].[sysconstraints] WHERE [constid]=0xE84D41E808EE5046 --无结果
SELECT * FROM [sys].[objects] WHERE [object_id]=0xE84D41E808EE5046 --无结果

SELECT  [constr].* ,
        [obj].[name] ,
        [obj].[object_id]
FROM    [sys].[sysconstraints] AS constr
        INNER JOIN [sys].[objects] AS obj ON [constr].[id] = [obj].[object_id]
WHERE [obj].[object_id]=OBJECT_ID('customer')

就算中间出错也会继续插入到数据库中

USE [sss]

CREATE TABLE show_error
    (
      col1 SMALLINT NOT NULL
                    PRIMARY KEY ,
      col2 SMALLINT NOT NULL
    )

BEGIN TRAN
INSERT  show_error
VALUES  ( 1, 1 )
INSERT  show_error
VALUES  ( 1, 2 )
INSERT  show_error
VALUES  ( 2, 3 )
COMMIT TRAN

SELECT  *
FROM    show_error

set xact_abort on

update 操作的原子性

临时表

WHERE STATE IN('CA',NULL)

IN,NOT IN ,ALL ,ANY

with check option

like 用法

USE [sss]
CREATE TABLE testspace(id INT ,name VARCHAR(20))
INSERT INTO testspace
SELECT 1, 'a'

SELECT DATALENGTH(name) FROM testspace
SELECT CAST([name] AS VARBINARY(10)) FROM testspace
--十六进制 0x61  转换为十进制97

--当遇到乱码的时候可以先用cast函数转换为16进制VARBINARY，然后转换为其他形式，例如转换为int再用char函数显示出字符
SELECT char(CAST(CAST([name] AS VARBINARY(10)) AS INT)) FROM testspace

set ansi_padding的由来，尾部空格的争论

与0x20进行按位与运算，查看是否有尾部空格

使用sysindexes来获取表的总行数

NULLIF()函数

OLAP系统

CUBE关键字和ROLLUP关键字

compute by 和cube

union 查询的各个列的数据类型和列数必须要相同或者数据类型可以转换

union all

insert 表 values( default)

insert 表 default values

insert select方式插入表数据

insert exec

CREATE TABLE #config_out
(NAME VARCHAR(50),
minval INT ,
maxval INT,
configval INT,
runval INT)

INSERT [#config_out]
EXEC [sys].[sp_configure] 

SELECT * FROM [#config_out]

allow updates

backup buffer size

backup threads

hash buckets

logwrite sleep(ms)

smp concurrency

INSERT #config_out EXEC dogfood.MASTER.dbo.spconfigure

create table权限和select权限 select into

bcp命令的-E选项保留标识值

check 约束和foreign key约束、触发器不起作用

LOAD TABLE有点类似于MYSQL的LOAD DATA INFILE语句

load table dump database 不正交

load table、 dump table

update 语句高级示例

truncate table

对于有复制和外键引用的表是truncate不了的

truncate之后可以收缩，因为页面已释放，更快更干净

BEGIN TRAN
SELECT COUNT(*) FROM config_out

TRUNCATE TABLE config_out

SELECT COUNT(*) FROM config_out

ROLLBACK TRAN

SELECT COUNT(*) FROM config_out

with check option

ALTER TABLE [dbo].[aaa] NOCHECK CONSTRAINT ALL

sqlserver内部细节行定位，空间管理

为什麽非聚集索引么有行溢出页面，因为非聚集索引的键列总大小不能超过900字节（mysql 1000字节）

sql2005引进了include关键字，非聚集索引也会有行溢出页面和lob页面

USE [sss]
GO
CREATE TABLE testrowoverflownonclu(id INT ,name NVARCHAR(4000),name2 NVARCHAR(2000))

CREATE NONCLUSTERED INDEX ncix_testrowoverflownonclu ON [testrowoverflownonclu]([id]) INCLUDE([name],[name2])
CREATE CLUSTERED INDEX cix_testrowoverflownonclu ON [testrowoverflownonclu]([id]) 

DROP INDEX ncix_testrowoverflownonclu ON [testrowoverflownonclu]


INSERT INTO [testrowoverflownonclu]
        ( [id], [name], [name2] )
SELECT 4,REPLICATE('aa',2000),REPLICATE('aa',1000)

SELECT * FROM [testrowoverflownonclu]

TRUNCATE TABLE [DBCCResult]
INSERT INTO DBCCResult EXEC ('DBCC IND(sss,testrowoverflownonclu,-1) ')
SELECT * FROM [DBCCResult]

而聚集索引就是表本身，所以长度不能控制

并视图从他将与之连接的其他页的同一个分配单元(allocation unit )中分配该区extent

SELECT * FROM sys.[allocation_units]

262144 1 IN_ROW_DATA 262144 1 8 8 6
327680 1 IN_ROW_DATA 327680 1 2 2 1
458752 1 IN_ROW_DATA 458752 1 2 2 1
524288 1 IN_ROW_DATA 524288 1 2 2 1
851968 1 IN_ROW_DATA 851968 1 7 7 5
983040 1 IN_ROW_DATA 983040 1 2 2 1
281474978349056 1 IN_ROW_DATA 281474978349056 1 0 0 0
281474978414592 1 IN_ROW_DATA 281474978414592 1 2 2 1
281474978480128 1 IN_ROW_DATA 281474978480128 1 2 2 1
281474978611200 1 IN_ROW_DATA 281474978611200 1 2 2 1
281474978938880 1 IN_ROW_DATA 281474978938880 1 2 2 1
281474979397632 1 IN_ROW_DATA 281474979397632 1 17 10 8
281474979594240 1 IN_ROW_DATA 281474979594240 1 2 2 1
281474979987456 1 IN_ROW_DATA 281474979987456 1 2 2 1
281474980052992 1 IN_ROW_DATA 281474980052992 1 0 0 0
281474980249600 1 IN_ROW_DATA 281474980249600 1 4 4 2
281474980315136 1 IN_ROW_DATA 281474980315136 1 5 5 3
281474980642816 1 IN_ROW_DATA 281474980642816 1 33 29 27
281474980904960 1 IN_ROW_DATA 281474980904960 1 2 2 1
281474980970496 1 IN_ROW_DATA 281474980970496 1 0 0 0
281474981560320 1 IN_ROW_DATA 281474981560320 1 2 2 1
281474981625856 1 IN_ROW_DATA 281474981625856 1 2 2 1
281474981691392 1 IN_ROW_DATA 281474981691392 1 2 2 1
562949956763648 1 IN_ROW_DATA 562949956763648 1 0 0 0
562949958270976 1 IN_ROW_DATA 562949958270976 1 2 2 1
562949958336512 1 IN_ROW_DATA 562949958336512 1 2 2 1
844424931901440 1 IN_ROW_DATA 844424931901440 1 2 2 1
1125899909070848 1 IN_ROW_DATA 1125899909070848 1 2 2 1
71776119065149440 2 LOB_DATA 281474980642816 1 3 3 0
72057594037993472 1 IN_ROW_DATA 281474980511744 1 2 2 1
72057594038059008 1 IN_ROW_DATA 562949957222400 1 2 2 1
72057594038190080 1 IN_ROW_DATA 281474983067648 1 0 0 0
72057594038255616 1 IN_ROW_DATA 562949959778304 1 0 0 0
72057594038386688 1 IN_ROW_DATA 281474981822464 1 2 2 1
72057594038452224 1 IN_ROW_DATA 562949958533120 1 2 2 1
72057594038583296 1 IN_ROW_DATA 281474982608896 1 2 2 1
72057594038648832 1 IN_ROW_DATA 562949959319552 1 2 2 1
72057594038779904 1 IN_ROW_DATA 281474982674432 1 2 2 1
72057594038845440 1 IN_ROW_DATA 562949959385088 1 2 2 1
72057594038976512 1 IN_ROW_DATA 281474982739968 1 2 2 1
72057594039107584 1 IN_ROW_DATA 281474982805504 1 2 2 1
72057594039173120 1 IN_ROW_DATA 562949959516160 1 2 2 1
72057594039304192 1 IN_ROW_DATA 281474981101568 1 0 0 0
72057594039435264 1 IN_ROW_DATA 844424934522880 1 0 0 0
72057594039566336 1 IN_ROW_DATA 281474981363712 1 0 0 0
72057594039697408 1 IN_ROW_DATA 281474981232640 1 2 2 1
72057594039762944 1 IN_ROW_DATA 562949957943296 1 2 2 1
72057594039959552 2 LOB_DATA 281474981167104 1 0 0 0
72057594040025088 1 IN_ROW_DATA 281474981167104 1 0 0 0
72057594040156160 1 IN_ROW_DATA 281474981429248 1 0 0 0
72057594040287232 1 IN_ROW_DATA 281474981494784 1 0 0 0
72057594040418304 2 LOB_DATA 281474982871040 1 0 0 0
72057594040483840 1 IN_ROW_DATA 281474982871040 1 0 0 0
72057594040614912 2 LOB_DATA 281474979725312 1 0 0 0
72057594040680448 1 IN_ROW_DATA 281474979725312 1 0 0 0
72057594040811520 1 IN_ROW_DATA 844424933146624 1 0 0 0
72057594040877056 1 IN_ROW_DATA 1125899909857280 1 0 0 0
72057594041008128 2 LOB_DATA 281474982936576 1 0 0 0
72057594041073664 1 IN_ROW_DATA 281474982936576 1 0 0 0
72057594041204736 1 IN_ROW_DATA 844424936357888 1 0 0 0
72057594041335808 1 IN_ROW_DATA 281474983002112 1 0 0 0
72057594041466880 1 IN_ROW_DATA 844424936423424 1 0 0 0
72057594041532416 1 IN_ROW_DATA 72057594037927936 1 0 0 0
72057594041597952 3 ROW_OVERFLOW_DATA 72057594037927936 1 0 0 0
72057594041663488 2 LOB_DATA 72057594037927936 1 0 0 0
72057594041729024 1 IN_ROW_DATA 72057594037993472 1 0 0 0
72057594041794560 1 IN_ROW_DATA 72057594038059008 1 0 0 0
72057594041860096 3 ROW_OVERFLOW_DATA 72057594038059008 1 0 0 0
72057594041925632 2 LOB_DATA 72057594038059008 1 0 0 0
72057594041991168 1 IN_ROW_DATA 72057594038124544 1 0 0 0
72057594042056704 1 IN_ROW_DATA 72057594038190080 1 0 0 0
72057594042122240 3 ROW_OVERFLOW_DATA 72057594038190080 1 0 0 0
72057594042187776 2 LOB_DATA 72057594038190080 1 0 0 0
72057594042253312 1 IN_ROW_DATA 72057594038255616 1 0 0 0
72057594042318848 1 IN_ROW_DATA 562949955190784 1 2 2 1
72057594042384384 1 IN_ROW_DATA 562949955649536 1 2 2 1
72057594042449920 1 IN_ROW_DATA 844424932360192 1 2 2 1
72057594042515456 1 IN_ROW_DATA 562949956108288 1 5 5 3
72057594042580992 1 IN_ROW_DATA 562949956304896 1 2 2 1
72057594042646528 1 IN_ROW_DATA 562949956435968 1 0 0 0
72057594042712064 1 IN_ROW_DATA 281474979921920 1 0 0 0
72057594042777600 1 IN_ROW_DATA 562949956698112 1 2 2 1
72057594042843136 1 IN_ROW_DATA 844424933408768 1 2 2 1
72057594042908672 1 IN_ROW_DATA 562949956960256 1 2 2 1
72057594042974208 1 IN_ROW_DATA 562949957615616 1 2 2 1
72057594043039744 1 IN_ROW_DATA 562949957812224 1 0 0 0
72057594043105280 1 IN_ROW_DATA 844424934653952 1 2 2 1
72057594043170816 1 IN_ROW_DATA 562949959647232 1 0 0 0
72057594043236352 1 IN_ROW_DATA 562949959712768 1 0 0 0
72057594043432960 1 IN_ROW_DATA 72057594038452224 1 2 2 1
72057594043564032 1 IN_ROW_DATA 72057594038583296 1 2 2 1
72057594043629568 1 IN_ROW_DATA 72057594038648832 1 2 2 1
72057594043760640 1 IN_ROW_DATA 72057594038779904 1 2 2 1
72057594043826176 1 IN_ROW_DATA 72057594038845440 1 2 2 1
72057594043891712 1 IN_ROW_DATA 72057594038910976 1 2 2 1
72057594044219392 1 IN_ROW_DATA 72057594039238656 1 2 2 1
72057594044284928 1 IN_ROW_DATA 72057594039304192 1 2 2 1
72057594044350464 1 IN_ROW_DATA 72057594039369728 2 2 2 1
72057594044416000 1 IN_ROW_DATA 72057594039435264 1 2 2 1

延迟更新

before image ，after image

触发器显示不了执行计划

--
--create trigger tgr_config_out_insert
--on config_out
--    for insert --插入触发
--as
--    --定义变量
--    declare  @name varchar(100)
--    --在inserted表中查询已经插入记录信息
--    select @name = name from inserted;
--    PRINT @name
--go

--插入数据
insert into config_out([name]) values('5班');

行更新的奥秘，新映像和旧映像

延迟更新的日志记录ldf

直接更新，日志记录要考虑直接更新和延迟更新的情况

直接更新包括:原位更新（update-in-place）、直接字段更新（direct-field-update）、页上删除/插入更新(on-page delete/insert)、完全删除/插入更新(full delete/insert)

如果某列被设置为等于其原来的值，则不产生modify记录

所有延迟更新都是完全删除/插入操作，这类操作要执行前面描述的两组日志进程
先写DNOOP/INOOP

如果一个直接更新也是一个完全删除/插入操作，则他不需要额外的NOOP日志步骤

延迟更新总是一种删除/插入操作

在命令行输入

net start MSSQLSERVER   /T323

使用跟踪标志323还是没有办法输出更新的类型

--SQL6.5中的syslogs表
--opcode 事务日志中的操作代码

0 beginxact --start transaction
1 not used
2 not used
3 not used
4 insert --insert row
5 delete --delete row
6 insind --deferred update step2 insert record 延迟更新
7 iinsert --nc index insert 非聚集索引插入
8 idelete --nc index delete
9 modify -- modify row
10 noop --noop
11 inoop --deferred update step 1 insert record 延迟更新步骤1
12 dnoop --deferred update step 1 delete record
13 alloc --allocation
14 dbnextid -- extent allocation 区分配
15 extent -- extent allocation 区分配
16 split --page split 页拆分
17 checkpoint --checkpoint
18 savexact --savepoint 现在为save tran
19 cmd
20 dextent --deallocate extent 释放区
21 dealloc --deallocate page 释放页面
22 dropexts --delete all extents on alloc page 删除已经分配了页面的那些区
23 aextent --alloc extent-mark all pages used 标记在哪些区里分配了页面
24 salloc --alloc new pages for split (split allocate) 页拆分分配页面
25 not used
26 not used
27 sort --sort allocations
28 sodealloc --related to sort allocations
29 altdb --alter database record
30 endxact --end transaction
31 sortts --related to sort allocations
32 text --log record of direct text insert
33 inooptext --log record for deferred text insert
34 dnooptext --log record for deferred text delete
35 insindtext --indirect insert log record
36 textdelete --delete text log record
37 sortedsplit --used for sorted splits
38 chgindstat --incremental sysindexes stat changes
39 chgindpg --direct change to sysindexes
40 txtptr --info log row when retrieving textptr
41 textinfo --info log for writetext/updatetext
42 resetident --used when a trucate table resets an identity value
43 undo --compensating log record for insert only row locking(IORL)补偿日志记录
44 insert_IORL --insert with row locking record
45 insind_IORL --insind with IORL
46 insert IORL --iindex with IORL
47 split IORL --page split with IORL
48 salloc IORL --alloc new page for split with IORL
49 alloc IORL -- allocation with IORL
50 prealloclog --preallocate log space for CLRs
unknow

前面已经提到，进行原位更新时，对值有变化的每一行、每一列都有一个MODIFY记录

这种写法不会进行循环的

页拆分会记录

瘦客户端和胖客户端

SAP R3系统，使用存储过程

sql05可以了

DECLARE @@a INT
SET @@a=1
SELECT @@a

syslogs表译码表

case语句的变形：coalesce,nullif,isnull

在64K的包大小中，注释也包括在内，所以在TSQL代码中不应该包含太多注释

位运算符

位屏蔽

标量的定义：是单值的另一种叫法，你也可以把单个行中的单个列中的值看作一个标量。

SQL92 规范

目前SQLSERVER没有分离的日期和时间数据类型

数据类型转换表

ASCII()和CHAR()函数

在6.0之前，对于一个确切模式匹配的查询来说，上面的CHARINDEX()比like快得都，现在情况不是这样了，他们二者的性能相同

SELECT SOUNDEX()
SELECT DIFFERENCE()

index_col:被进行索引的列的名字

计算某个表的平均列长度，不是列所占用空间

无参函数

current_timestamp

SET XACT_ABORT ON的用词不当

使用@@error的原因

DB2的read committed隔离级别叫CURSOR STABLITY

增加重试逻辑

嵌套事务

save tran 保存点

保存点不确认数据库的任何变化,只有committ语句能够确认

sqlserver接收到一个 sql batch的时候要处理的步骤

命令分析器分析sql生成查询树

对查询树生成执行计划

每次数据修改后,触发器的执行计划不需要重新编译(存储过程执行计划)

存储过程存储些什么

查询树和tsql语句被缓存了

如果plan cache不够了,就报:没有足够内存来运行该过程

你预期有10个不同的连接同时执行一个存储过程,那么内存中就应该有该仔细计划的10个拷贝(只要你有足够的plan cache空间即可)

sp_recompile

with recompile

存储过程的字符数限制

加密了的存储过程syscomment输出都为null

临时存储过程

CREATE PROC #aa
AS
SELECT 1

4022

Skips launching automatic stored procedures when SQL Server starts. This is equivalent to setting “scan for startup procs” configuration option to 0.

Article

http://www.cnblogs.com/lyhabc/articles/3816529.html

跟踪标志4022关闭自启动存储过程

sqlserver系统存储过程

类目存储过程

sql agent存储过程，要在msdb里执行

复制相关存储过程

扩展存储过程

扩展存储过程不是由sqlserver派生的独立存储过程，他们直接运行在sqlserver的地址空间中，这些DLL利用open data services API建立（sqlserver也使用open data services API）

用C语言编写扩展存储过程

master数据库里的扩展存储过程，不是所有扩展存储过程都是xp开头的，也有sp开头的，例如

EXEC [sys].[xp_sprintf] 

EXEC sys.[xp_sscanf]

EXEC [sys].[xp_sprintf] 

EXEC sys.[xp_sscanf]

通用扩展存储过程 xp_cmdshell

sql邮件有关扩展存储过程，管理和监测有关的扩展存储过程，集成安全性有关的扩展存储过程

select into和bcp不触发触发器，因为不记录日志

利用日志在内存中构造inserted表和deleted表

级联触发器，最大嵌套16层

sp_sdidebug的代码是sqlserver内部，不是一个dll

pss结构 process status structure

动态sql

16个字节存放文本指针

readtext和updatetext都允许每次操作text列中的一个文本块，而writetext语句不允许处理文本块只允许处理整列

对于text列，writetext也可以，insert into 也可以

--SELECT TEXTVALID()
ALTER TABLE [aa] ADD txt TEXT

INSERT INTO [aa]
        ( [NAME], [ni], [txt] )
VALUES  ( '1', -- NAME - char(400)
          1, -- ni - int
          'nihao'  -- txt - text
          )
DECLARE @mytextptr VARBINARY(16)
SELECT @mytextptr=TEXTPTR(txt) FROM [aa] WHERE ni=1
--SELECT TEXTVALID(TEXTPTR(txt)) FROM [aa] WHERE ni=1
--消息 174，级别 15，状态 1，第 1 行
--textvalid 函数要求有 2 个参数。
PRINT @mytextptr
--0xFEFF112700000000FD00000001000000

--writetext根据16字节的指针直接修改text列的值 将nihao修改为abc
WRITETEXT aa.[txt] @mytextptr WITH LOG 'abc'
SELECT txt FROM [aa] WHERE ni=1

SELECT @@TEXTSIZE
2147483647byte=2GB

6.0的时候引入updatetext

DECLARE @mytextptr VARBINARY(16)
SELECT @mytextptr=TEXTPTR(txt) FROM [aa] WHERE ni=1
IF @mytextptr IS NOT NULL 
BEGIN
UPDATETEXT [aa].[txt] @mytextptr NULL NULL WITH LOG 'mary'
END

--追加
SELECT txt FROM [aa] WHERE ni=1

DECLARE @mytextptr  VARBINARY(16),@pat_offset int,@oldstring varchar(200),@newstring varchar(200),
@sizeold INT

SELECT @oldstring='mary',@newstring='tom'
SELECT @sizeold=DATALENGTH(@oldstring),@pat_offset=PATINDEX('%'+@oldstring+'%',txt)-1,
@mytextptr=TEXTPTR(txt) FROM aa WHERE ni=1
PRINT @pat_offset --3 偏移
PRINT @sizeold  --4 长度

UPDATETEXT [aa].[txt] @mytextptr @pat_offset @sizeold WITH LOG @newstring

--查找和替换
SELECT txt FROM [aa] WHERE ni=1

游标

sqlserver专门为集合操作设计，不应用游标

《inside ODBC》一书

ODBC之父：kyle geiger

EXEC [sys].[sp_cursor]

sqlserver本质上以集合为基础的

游标结合动态sql

他们为sqlserver开发了ODBC驱动程序

游标使用到tempdb

EXEC SYS.SP_MAKEWEBTASK

消息 2812，级别 16，状态 62，第 1 行
找不到存储过程 'SYS.SP_MAKEWEBTASK'。

select 统计

sqlserver发邮件

EXEC [master]..[xp_sendmail]
EXEC [master]..[xp_readmail]

EXEC [sys].[sp_sqlexec] @p1 = '' -- text

[sys].[sp_sqlexec]是open data services的一个专用的内部函数

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
create procedure sys.sp_sqlexec @p1 text as
    declare @execstr nvarchar(max)
    set @execstr = CONVERT(nvarchar(max),@p1)
    exec(@execstr)
GO

分布式查询

没有openquery方便

DROP TABLE #tmptb
CREATE TABLE #tmptb
            (
              [SERVERNAME] NVARCHAR(MAX) ,
            )
            
--执行之前要开启MSDTC
INSERT INTO #tmptb
        ( [SERVERNAME]
        )
EXEC [1XX.XX.XX.XX,XXX].[XXXXX.Archixx].[sys].[sp_sqlexec] @p1 = 'SELECT @@SERVERNAME '


--链接服务器"[1XX.XX.XX.XX,XXX]"的 OLE DB 访问接口 "SQLNCLI10" 返回了消息 "该伙伴事务管理器已经禁止了它对远程/网络事务的支持。"。
--消息 7391，级别 16，状态 2，第 1 行
--无法执行该操作，因为链接服务器 "[1XX.XX.XX.XX,XXX]" 的 OLE DB 访问接口 "SQLNCLI10" 无法启动分布式事务。

sql6.5不支持用户自定义函数

使用VB5和VC5创建OLE 自动化存储过程

EXEC [sys].[sp_OACreate]
EXEC [sys].[sp_OAMethod]
EXEC [sys].[sp_OAGetErrorInfo]
EXEC [sys].[sp_OADestroy]

为解决没有用户自定义函数的问题退出OLE 自动化[sp_OACreate]

锁

自旋锁避免死锁

锁内存结构大约28字节

锁请求者的进程id

数据库id

对象id

行号

锁类型

被锁资源也要一个大约32字节的资源锁

EXEC [sys].[sp_lock] @spid1 = 0, -- int
    @spid2 = 0 -- int

区锁 ext

约束连接

EXEC [sys].[sp_getbindtoken]
--消息 201，级别 16，状态 9，过程 sp_getbindtoken，第 1 行
--过程或函数 'sp_getbindtoken' 需要参数 '@out_token'，但未提供该参数。

EXEC [sys].[sp_bindsession]

共享锁不阻塞的原因使用约束连接共享锁空间

数据插入中的hotpage

插入行加锁（Insert row-level locking ---IRL）

3种情况

1、堆表，最后一页成为hotpage

2、聚集索引表，主键在自增列，最后一页成为hotpage

3、在自增列上建立非聚集索引，最后一页成为hotpage

插入页锁记录正被使用的行号或行槽 slot，使得两个具有IX_PAGE的进程不在同一个行槽（用来放置该行）上操作

第一个探测到当前页是满的那个进程将得到一个LN_PAGE链接页锁

sql2012

在32位系统上，每个锁块需要64个字节，每个锁拥有者需要32个字节。在64位系统上

，每个锁块需要96个字节。每个锁拥有者块需要56个字节。

32位操作系统：每个锁块需要64个字节，每个锁拥有者需要32个字节 0.3MB
64位操作系统：每个锁块需要96个字节。每个锁拥有者块需要56个字节 0.5MB

tps ,每秒事务处理数评估

sqlserver提供了一个优化器提示：fastfirstrow

用这个参数告诉sqlserver，对于你的应用程序来说，快速返回第一行比节省返回全部行的总时间重要

这个总时间即一般情况下查询计划所花费的时间

http://www.microsoft.com/en-us/server-cloud/products/sql-server/

谨慎使用游标

尽量使用存储过程

moden 调制解调器 28kb/s

SET NOCOUNT ON

禁用DONE IN PROC

network monitor

sqlserver内部应该有一个内部时钟，这个时钟跟操作系统时间无关，主要用于生成时间戳，跟单片机

页拆分引起的死锁

调整填充因子 fillfactor

系统中的锁将报告到伪系统表syslocks和master库中

DBCC INPUTBUFFER(spid)

SELECT [name],
pagetype=
CASE
WHEN [indid]=0 THEN 'data'
WHEN [indid]=1 THEN 'clust index'
WHEN [indid] BETWEEN 2 AND 254 THEN 'nonclust index'
WHEN [indid]=255 THEN 'text/image'
ELSE 'not found in sysindexes'
END
FROM [sys].[sysindexes]
WHERE id= AND [indid]=

1206、1204、1208、1200、1205

SARG

索引统计信息

sqlserver确定一个分布页中能放下多少元素，然后对整个表按等距抽样，以获得指定个数的元素，每个这样的元素称为步 step 步长

分布页也包含了关于他所遇到数据的唯一性的统计信息，数据的唯一性，这种唯一性又成为密度，他提供了关于该索引选择能力的一种指标

引导列即前导列 mysql ，sqlserver

SELECT distribution
FROM [sys].[sysindexes]

消息 207，级别 16，状态 1，第 1 行
列名 'distribution' 无效。

DBCC SHOW_STATISTICS([aa],[PK_aa])

统计信息步长的计算

查询优化的正确叫法应该叫概率优化

分布页也是2KB大小，页头也是32字节

优化器估算最低的逻辑I/O

缓冲区管理器-》高速缓存

优化器根据分布页中的密度和步数值评估索引，估计命中率

无统计信息的情况

改写格式策略，解决大表join问题，使用到tempdb的工作表

join多个表使用修剪技术将决策树维持在一个便于管理的规模上

物理读命中率

跟踪标志：302、310

查看优化器工作原理

RECONFIGURE WITH OVERRIDE

缺页中断

虚拟内存

page faults/sec(缺页/秒)

默认情况下，Windows NT给每一个线程的栈分配1MB内存，如果内存紧张，并且必须使用大量的worker

你可以直接在sqlserver上使用EDITBIN.EXE工具软件（由Windows NT提供）把栈大小减少到大约64KB

栈大小最低可以减少到16KB，不过如果这个值设置得太低，worker线程就会因为栈溢出异常而被强行终止

力伟

http://space.cnblogs.com/message/868908/

http://space.cnblogs.com/message/881397/
每一个worker在创建的时候，自己需要申请多少mb内存空间
32 bit机器是0.5mb
x64 bit机器是 2mb
ia64 机器是4mb

查了下，thead大小是个windows范畴，sql server没有特别的控制

http://space.cnblogs.com/message/869115/
SQL会为进程内的每个线程分配0.5MB的内存，以存放线程的数据结构和相关信息

我个人觉得0.5MB会不会太小了，如果线程所需要的内存超过0.5MB怎麽办？

0.5是指分配给thread自身的，如果上面运行语句，那是单独的,不算在这0.5里

DBCC memusage
DBCC PROCCACHE

DBCC TRACEON(3604,-1)
DBCC TRACEON(3635,-1)

DBCC PROCCACHE

sqlserver为每个用户连接保存27KB内存

open databases：每个已配置的数据库需要的内存大概1MB

open objects 默认值500个

历史遗留问题，默认一张表达到200个页锁后，sqlserver即把页锁升级到表锁

最大排序页，默认每个连接有128KB内存进行排序，64个内存页=128KB

MYSQL的

sort_buffer_size：系统中对数据进行排序的时候使用的 Buffer；
Sort Buffer 同样是针对单个 Thread 的，所以当多个 Thread 同时进行排序的时候，系统中就会出现
多个 Sort Buffer。一般我们可以通过增大 Sort Buffer 的大小来提高 ORDER BY 或者是 GROUP BY
的处理性能。系统默认大小为 2MB，最大限制和 Join Buffer 一样

在sqlserver6.5里，worker的处理方式从队列改为使用IO-COMPLETION端口

sqlserver把页散列以便快速访问高速缓存中的页面 hash bucket配置用于设置存储桶数目

DBCC bfcount

消息 2526，级别 16，状态 3，第 1 行
DBCC 语句错误。请查阅文档以了解正确的 DBCC 语法和选项。

sqlserver条用win32 api setProcessworkingsetsize()

预写日志要求同步I/O，以保证可恢复性，所以异步I/O从不用来进行写日志操作

提升sqlserver的优先级（priority boost 优先级提升）
使sqlserver在较高Windows NT调度优先级上运行
该选项默认值为0，sqlserver在SMP机器上，他以15以上的优先级运行。Windows NT优先级值范围从1（空闲）-到31（realtime time critical）

默认情况下，Windows NT会临时提升使用 IO -COMPLETION端口的线程（sqlserver的worker）的优先级
优先反转问题
设置该选项不能使sqlserver以最高的Windows NT优先级 31 来运行
realtime time critical

network packet size网络包大小

tpcc测试程序，基于sqlserver4.2版本

SMP并发

logLRU buffers缓冲区，日志也是以页的形式存在在内存中，事务日志缓冲区，在6.5版本以前，默认情况下，所有日志页都经过常规的数据页

所用的同一缓冲区管理器。

从6.5版本开始，利用logLRU设置可为日志保留一小部分高速缓存，用这种办法可防止日志页污染普通数据页，意思是说把数据页也刷写到磁盘

就像mysql的备份 http://www.orczhou.com/index.php/2010/05/innodb-plugin-make-buffer-cache-scan-resistant/

InnoDB Plugin特性介绍：让Buffer Pool不被污染
1. Buffer Pool中LRU缺点、细节

这样LRU算法的一个缺点是，如果有某一个查询做了一次全表扫描（例如备份，临时DDL等），都可能会导致整个Buffer Pool中LRU链表中的数据块都被替换了，

甚至很多热点数据也会被替换，而这些新进的数据块可能在这一次查询之后就再也不会被读到了。我们也称这种情况为“Buffer Pool被污染”

In-Flight日志

http://www.cnblogs.com/CareySon/p/3195276.html#2763836

物理日志I/O 顺序的

DBCC SQLPERF(LRUSTATS2)
消息 2526，级别 16，状态 12，第 1 行
DBCC 语句错误。请查阅文档以了解正确的 DBCC 语法和选项。

group committe 成组确认事务

waitforsingleobject()

SMP（Symmetric Multi-Processing），对称多处理结构的简称

对称多处理器

设置cpu处理器关联掩码，Linux上的客户要求的

自旋锁和时间片

USE [sss]

EXEC sys.[sp_tableoption] @TableNamePattern = N'[aaa]', -- nvarchar(776)
    @OptionName = 'insert row lock', -- varchar(35)
    @OptionValue = 'true' -- varchar(12)


消息 15600，级别 15，状态 1，过程 sp_tableoption，第 50 行
为过程 'sys.sp_tableoption' 指定的参数或选项无效。