哈尔滨工业大学计算机学院
数据库研究中心
[email protected]
如何在SQL Server 2000中显示查询计划。
使用 SQL 查询分析器以文本方式显示执行计划
SHOWPLAN_TEXT and SHOWPLAN_ALL
The two SET options SHOWPLAN_TEXT and SHOWPLAN_ALL let you see the estimated query plan without actually executing the query. Both options also automatically enable the SET NOEXEC option, so you don't see any results from your query—you see only the way that SQL Server has determined is the best method for processing the query. Turning NOEXEC ON can be a good thing while tuning a query. For example, if you have a query that takes 20 minutes to execute, you might try to create an index that will allow it to run faster. However, immediately after creating a new index, you might just want to know whether the query optimizer will even choose to use that index. If you were actually executing the query every time you looked at its plan, it would take you 20 minutes for every tuning attempt. Setting NOEXEC ON along with the show plan option will allow you to see the plan without actually executing all the statements.
WARNING
Since turning on SHOWPLAN_TEXT or SHOWPLAN_ALL implies that NOEXEC is also on, you must set the SHOWPLAN option to OFF before you do anything else. For example, you must set SHOWPLAN_TEXT to OFF before setting SHOWPLAN_ALL to ON.
使用 SQL 查询分析器以图形方式显示执行计划
如何使用DBCC PAGE命令来查看数据页格式
例子：
use test
go
select * from stu
go
结果：
首先来看sysindexes关系的内容。
数据库中的每个索引和表在表中各占一行。该表存储在每个数据库中。
列名 数据类型 描述
Id Int 表 ID（如果 indid = 0 或 255）。否则为索引所属表的 ID。
Status Int 内部系统状态信息。
First binary(6) 指向第一页或根页的指针。
Indid Smallint 索引 ID：
0 = 该表为新表，对应的表是一个堆
1 = 聚集索引
>1 = 非聚集
255 = 具有 text 或 image 数据的表条目
root binary(6) 如果 indid >= 1 和 < 255，root 是指向根页的指针。如果 indid = 0 或 indid = 255，root 是指向最后一页的指针。
minlen Smallint 最小行大小。
keycnt Smallint 键的数目。
groupid Smallint 在其上创建对象的文件组 ID。
dpages Int 如果 indid = 0 或 indid = 1，dpages 是已用数据页的计数。如果 indid = 255，其设置为 0。否则是已用索引页的计数。
reserved Int 如果 indid = 0 或 indid = 1，reserved 是分配给所有索引和表数据的页计数。如果 indid = 255，reserved 是分配给 text 或 image 数据的页计数。否则是分配给索引的页计数。
used Int 如果 indid = 0 或 indid = 1，used 是用于所有索引和表数据的总页数。如果 indid = 255，used 是用于 text 或 image 数据的页计数。否则是用于索引的页计数。
rowcnt Bigint 基于 indid = 0 和 indid = 1 的数据级行计数。如果 indid = 255，rowcnt 设置为 0。
rowmodctr Int 对自上次更新表的统计后插入、删除或更新行的总数进行计数。
xmaxlen Smallint 最大行大小。
maxirow Smallint 最大非叶索引行大小。
OrigFillFactor Tinyint 创建索引时使用的起始填充因子值。不保留该值；然而，如果需要重新创建索引但记不住当初使用的填充因子，则该值可能很有帮助。
reserved1 Tinyint 保留。
reserved2 Int 保留。
FirstIAM binary(6) 保留。
impid Smallint 保留。索引实现标志。
lockflags Smallint 用于约束经过考虑的索引锁粒度。例如，对于本质上是只读的查找表，可以将其设置为仅进行表级锁定以使锁定成本减到最小。
pgmodctr Int 保留。
keys varbinary(816) 组成索引键的列 ID 列表。
name Sysname 表名（如果 indid = 0 或 255）。否则为索引的名称。
statblob Image 统计 BLOB。
maxlen Int 保留。
rows Int 基于 indid = 0 和 indid = 1的数据级行数，该值对于 indid >1 重复。如果 indid = 255，rows 设置为 0。提供该列是为了向后兼容。
select id ,first,indid from sysindexes where id =object_id('stu') and indid in (0,1)
DBCC的格式为:
DBCC PAGE ({dbid | dbname},filenum,pagenum[,printopt])
为了获得filenum，filenum，执行下面的语句：
select id ,first,indid from sysindexes where id =object_id('stu') and indid in (0,1)
id first indid
453576654 0x190000000100 0
然后运行 DBCC
dbcc traceon (3604)
go
dbcc page('test',1,25,1)
go
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
PAGE: (1:25)
------------
BUFFER:
-------
BUF @0x18ED5A00
---------------
bpage = 0x19620000 bhash = 0x00000000 bpageno = (1:25)
bdbid = 7 breferences = 24 bstat = 0x9
bspin = 0 bnext = 0x00000000
PAGE HEADER:
------------
Page @0x19620000
----------------
m_pageId = (1:25) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8008
m_objId = 453576654 m_indexId = 0 m_prevPage = (0:0)
m_nextPage = (0:0) pminlen = 8 m_slotCnt = 8
m_freeCnt = 7938 m_freeData = 1316 m_reservedCnt = 0
m_lsn = (6:166:2) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 2
Allocation Status
-----------------
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:1) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
DATA:
-----
Slot 0, Offset 0x60
-------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
19620060: 00080030 0000000c 01000002 7a001300 0..............z
19620070: 6f6168 hao
Slot 1, Offset 0x73
-------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
19620073: 00080030 00000002 01000002 6b001200 0..............k
19620083: 6961 ai
Slot 2, Offset 0x85
-------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
19620085: 00080030 0000002d 01000002 77001300 0...-..........w
19620095: 676e61 ang
Slot 3, Offset 0x98
-------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
19620098: 00080030 0000000f 01000002 66001300 0..............f
196200a8: 676e65 eng
Slot 4, Offset 0xab
-------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
196200ab: 00080030 0000004c 01000002 78001100 0...L..........x
196200bb: 78 x
Slot 5, Offset 0xbc
-------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
196200bc: 00080030 0000005a 01000002 79001100 0...Z..........y
196200cc: 79 y
Slot 6, Offset 0xcd
-------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP
196200cd: 00080010 000000c8 020002 ...........
Slot 7, Offset 0x50e
--------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1962050e: 00080030 000004d2 01000002 7a001600 0..............z
1962051e: 6b6f6168 6961 haokai
OFFSET TABLE:
-------------
Row - Offset
7 (0x7) - 1294 (0x50e)
6 (0x6) - 205 (0xcd)
5 (0x5) - 188 (0xbc)
4 (0x4) - 171 (0xab)
3 (0x3) - 152 (0x98)
2 (0x2) - 133 (0x85)
1 (0x1) - 115 (0x73)
0 (0x0) - 96 (0x60)
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
分析：数据行的格式
19620060: 00080030 0000000c 01000002 7a001300 0..............z
19620070: 6f6168 hao
id = 12
name = zhao
30 00 0800 0c000000 0200 00 0100 1300 7a68616f
30 00 0800 0c000000 0200 00 0100 1300 7a68616f
状态位 未使用 找到第一 列的位置
第八个字节开始 定长第一列的值为 c=12 总列数为2 Null标志
均可以为非空 变长列数
为1列 第一个变长结束的位置
在0x13=19结束 第一个变长列
zhao
OBJECT_ID
返回数据库对象标识号。
语法
OBJECT_ID ( 'object' )
参数
'object'
要使用的对象。object 的数据类型为 char 或 nchar。如果 object 的数据类型是 char，那么隐性将其转换成 nchar。
返回类型
int
DBCC EXTENTINFO
利用DBCC EXTENTINFO得到属于一个对象的所有盘区的列表。
其语法为：
DBCC EXTENTINFO(dbname,tablename,indexid)
sql 会把聚集索引的数据页用链表连接起来，但对于堆不需要。
例如有
以及
其数据行如下（选例）：
19918060: 00080030 0a0a0001 01000004 4e002b00 0............+.N
19918070: 48207765 20657269 6f4a202d 6f6e2062 ew Hire - Job no
19918080: 70732074 66696365 646569 t specified
job_id = 1
job_desc = New Hire - Job not specified
min_lvl = 10
max_lvl = 10
00080030 0a0a0001 01000004 4e002b00 48207765 20657269 6f4a202d 6f6e2062 70732074 66696365 646569
30 00 0800 0100 0a
列数开始的位置 Small int
第一列数据 Tiny int
第三列数据
0a 0400 00 01 002b
Tiny int
第四列数据 列数为4 均可空 变长列数为1 第一个变长列结束的位置
剩下的为字符串
REPLICATE
以指定的次数重复字符表达式。
语法
REPLICATE ( character_expression , integer_expression )
参数
character_expression
由字符数据组成的字母数字表达式。character_expression 可以是常量或变量，也可以是字符列或二进制数据列。
integer_expression
是正整数。如果 integer_expression 为负，则返回空字符串。
返回类型
varchar
character_expression 必须为可隐性转换为 varchar 的数据类型。否则，使用 CAST 函数显式转换 character_expression。
For example:
replicate(cast(1 as varchar(1)),250)
or
replicate(‘1’,250) and they are the same.
如果是cast(1 as char)则，类型转化后的1所占的空间为32个字节
如果是cast(1 as varchar(1)),则1所占的空间为1个字节。
Primary key unique
在SQL中primary key 和unique的处理是一样的。系统会自动在primary key 和unique所约束的列上建立聚集索引。查询优化器基于位于索引的存在而不是基于一个烈被声明为主码的事实来作出决策。例如：
在建立it表后，系统自动为it建立聚集索引，同时上图表明了sysobjects,syscolumns和sysindexes的关系。
待插入两行后，结果如下：
当我们插入
Insert into it values(3,null)
分析文件格式
1990408d: 00080010 00000003 020002 ...........
id = 3
name = [NULL]
10 00 0800 03000000 0200 02
10 00 0800 03000000 0200 02
第一列数据在第八个字节处 一个列数据为3 共有两列 Null = 10 第二列为空，结束
建立索引是如不指名是clustered或nonclustered,则系统默认为非簇集的，即indid > 1
syslogins中保留了所有登陆用户的信息
sp_dboption
显示或更改数据库选项。不能在 master 或 tempdb 数据库上使用 sp_dboption。向后兼容性支持 sp_dboption。使用 ALTER DATABASE 设置数据库选项。
语法
sp_dboption [ [ @dbname = ] 'database' ]
[ , [ @optname = ] 'option_name' ]
[ , [ @optvalue = ] 'value' ]
参数
[@dbname =] 'database'
在其中设置指定选项的数据库的名称。database 的数据类型为 sysname，默认值为 NULL。
[@optname =] 'option_name'
要设置的选项的名称。没有必要输入完整的选项名称。Microsoft? SQL Server? 可识别名称中任何独有的部分。如果选项名称包含空格或者关键字，请将选项名称用引号引起来。如果省略此参数，sp_dboption 将列出处于打开状态的选项。option_name 的数据类型为 varchar(35)，默认值为 NULL。
[@value =] 'value'
option_name 的新设置。如果省略此参数，sp_dboption 将返回当前设置。value 可以是 true、false、on 或 off。value 的数据类型为 varchar(10)，默认值为 NULL。
返回代码值
0（成功）或 1（失败）
可以用sp_dboption 来设置批量insert
select into/bulkcopy 当为 true 时，允许使用 SELECT INTO 语句和快速大容量复制。
累积求和问题－T－SQL系列
累积求和问题
有一表： col1 value running_tot
1 10 0 （10）
2 15 0 （25）
3 50 0 （75）
若要将value列累积求和的结果放到running_tot 列，可以这样：
declare @i int
set @i=0
update table
set @i=running_tot=@i+value
或者
declare @i int
set @i=0
update table
set @i=running=@i+values
from table
本文地址：http://com.8s8s.com/it/it19321.htm