1、數(shù)據(jù)庫存儲緩沖區(qū)管理：替換策略LRU(最近最少使用)如果必須替換一個塊，則替換最近最少使用的塊MRU(最近最常使用)如果必須替換一個塊，則替換最近最常使用的塊緩沖區(qū)管理訪問內(nèi)存頁面為快速找到頁面，內(nèi)存頁面地址被散列給定dbid-標(biāo)識(數(shù)據(jù)庫ID, 文件號、頁面號的組合)，計算其hash地址Lazywriter(緩沖池管理器) 使用時鐘算法每個緩沖池有一個計數(shù)器，當(dāng)有進(jìn)程訪問該緩沖池時，計數(shù)器加一當(dāng)需要Lazywriter掃描每個緩沖池，如果其計數(shù)器為0，則釋放該空間（如果發(fā)生修改，則寫回磁盤）；如果計數(shù)器不為0，則計數(shù)器減一存儲分配文件文件組頁面區(qū)間文件一個數(shù)據(jù)庫是操作系統(tǒng)文件的集合數(shù)據(jù)庫之間

2、不能進(jìn)行文件共享一個數(shù)據(jù)庫至少包括一個數(shù)據(jù)文件和一個日志文件每個數(shù)據(jù)庫最多32,767個文件文件類型主數(shù)據(jù)文件 - .mdf 每個數(shù)據(jù)庫一個目錄表sysfiles1必須完全包含在此文件中輔助數(shù)據(jù)文件 - .ndf 零個或多個日志文件 - .ldf 一個或多個包含事務(wù)日志文件文件初始大小、最大尺寸、最小尺寸、以及增長大小可以指定文件可以自動增長-按指定大小或者當(dāng)前大小的百分比文件可以使用DBCC命令來減小尺寸驅(qū)動器不能是壓縮的數(shù)據(jù)和日志文件不能放在同一驅(qū)動器上USE UserDB DBCC SHRINKFILE (DataFil1, 7)DBCC SHRINKDATABASE (UserDB,

3、10) 文件組許多文件可以形成一個命名的文件組進(jìn)行統(tǒng)一的分配和管理，提供了靈活性組號1是主組號每個數(shù)據(jù)庫中都存在包含數(shù)據(jù)庫的系統(tǒng)表日志文件不加入文件組每個數(shù)據(jù)庫最多256個文件組一個文件組包含多個文件，一個文件只屬于一個文件組一個文件組包含多個對象，一個對象只屬于一個文件組文件組CREATE DATABASE SalesON PRIMARY ( NAME = SPri1_dat, = c:SPri1dat.mdf,SIZE = 10, MAXSIZE = 50, = 15% ),( NAME = SPri2_dat, = c:SPri2dt.ndf,SIZE = 10, MAXSIZE = 5

4、0, = 15% ), SalesGroup1( NAME = SGrp1Fi1_dat, = c:SG1Fi1dt.ndf, SIZE = 10,MAXSIZE = 50, = 5 ),( NAME = SGrp1Fi2_dat, = c:SG1Fi2dt.ndf,SIZE = 10, MAXSIZE = 50, = 5 ), SalesGroup2( NAME = SGrp2Fi1_dat, = c:SG2Fi1dt.ndf,SIZE = 10, MAXSIZE = 50, = 5 ), ( NAME = SGrp2Fi2_dat, = c:SG2Fi2dt.ndf,SIZE = 10,

5、MAXSIZE = 50, = 5 )LOG ON( NAME = Sales_log, = c:salelog.ldf,SIZE = 5MB, MAXSIZE = 25MB, = 5MB )通過CREATE DATABASE和ALTER DATABASE來創(chuàng)建文件組頁面數(shù)據(jù)文件被劃分成8k的頁面每個文件中的頁面號都以0開始頁面號的形式為(#)，如(3:124).mdf總是文件1，第一個日志文件是文件2區(qū)間8個連續(xù)頁面構(gòu)成一個區(qū)間64k總是從能被8整除的頁面開始第一個區(qū)間區(qū)間0位于文件1，包含頁面0到7第二個區(qū)間區(qū)間8，包含頁面8到15存儲分配總是按照區(qū)間為單位進(jìn)行對象每次增長1個區(qū)間I/O可

6、以按頁面(8 KB)或者區(qū)間(64 KB)來進(jìn)行區(qū)間單一區(qū)間：區(qū)間被單一對象擁有混合區(qū)間：區(qū)間被多個對象共享(最多8個)對象的頭8個頁面從混合區(qū)間種分配An 8K pageExtent (8 pages = 64K)T1T2T1Mixed ExtentT3T40816243240T3T4區(qū)間12 個空閑區(qū)間4個空閑區(qū)間區(qū)間按比例從所有文件中分配(Round Robin )保證文件組中所有文件按相同比例填充GAM 全局分配位圖記錄文件當(dāng)中哪些區(qū)間已經(jīng)被分配的頁面可以看成是一個8000個字節(jié)的位圖每個位代表一個區(qū)間位0代表區(qū)間0，位1代表區(qū)間8，位2代表區(qū)間160：被使用1：未被使用差不多64,0

7、00位，所以可以表示 64,000個區(qū)間表達(dá)4 GB (3,994 MBs)數(shù)據(jù)空間如果文件大于4 GB ，可以增加新的GAM頁第一個GAM位于文件的第三個頁面(page2)下一個GAM位于第511,232個頁面GAM總是位于它所控制范圍的第一個頁面SGAM 共享全局分配位圖用于表示文件中哪些區(qū)間被作為共享區(qū)間分配并且仍然有空閑區(qū)間可供分配結(jié)構(gòu)與GAM相同每一位代表一個區(qū)間位0代表區(qū)間0，位1代表區(qū)間8，位2代表區(qū)間160：區(qū)間要么不是共享區(qū)間，要么共享區(qū)間沒有空閑頁1：區(qū)間是共享區(qū)間，并且至少有一個空閑頁表達(dá)4 GB數(shù)據(jù)空間，與對應(yīng)的GAM表達(dá)范圍相同如果文件大于4 GB ，可以增加新的SG

8、AM頁第一個SGAM位于文件的第四個頁面(page3)下一個SGAM位于第511,233個頁面GAM與SGAMCurrent Use of ExtentGAM Bit SettingSGAM Bit SettingFree, not in use10Uniform extent, or full mixed extent00Mixed extent with free pages01如果需要一個新的、完全沒有使用的區(qū)間，可以從GAM中尋找位為1的區(qū)間如果需要一個有空閑頁的混合區(qū)間，可以尋找在GAM中位為0、在SGAM中位為1的區(qū)間PFS 空閑頁空間記錄文件中每個頁面是否已經(jīng)被分配以及有多少空閑

9、空間每一個頁面在PFS頁中有一個字節(jié)對應(yīng)每個PFS覆蓋8088個連續(xù)頁面(64 MB)頁面充滿度：1-50%, 51-80%, 81-95%, 96-100%第一個PFS位于文件的第二個頁面(page1)以后每8088都是一個PSF頁IAM 索引分配位圖如何發(fā)現(xiàn)一個特定對象的區(qū)間或頁面？每個表/索引都至少有一個IAM，記錄該對象擁有哪些區(qū)間一個IAM包含一個頭、8個頁面指針槽、一個表示文件內(nèi)區(qū)間范圍的位集合頭表示該IAM所映射的頭一個區(qū)間的地址8個頁面指針槽表示在一個混合區(qū)間內(nèi)哪些頁面屬于該對象IAM覆蓋的范圍與GAM相同，如果位為1，說明該區(qū)間被分配給該對象，如果位為0，說明該區(qū)間未被分配給

10、該對象如，說明第一、二個區(qū)間被分配給該對象一個對象每占據(jù)文件的4G空間，就需要一個IAM一個對象的IAM被隨機(jī)安放在文件中對象的第一個IAM記錄在sysindexes.firstIAM中對象的所有IAM構(gòu)成一個雙向鏈表DCM- 差異變化位圖記錄自從上次完全數(shù)據(jù)庫備份之后，哪些區(qū)間發(fā)生了變化DCM位于文件的第7個頁面(page6)DCM與GAM結(jié)構(gòu)和表達(dá)范圍相同每次進(jìn)行完完全數(shù)據(jù)庫備份，DCM所有位被置為0，以后如果區(qū)間的任何頁面發(fā)生修改， DCM對應(yīng)的位置為1當(dāng)進(jìn)行差異數(shù)據(jù)庫備份時，將對應(yīng)DCM中位為1的區(qū)間備份出去BCM 批量變化位圖記錄當(dāng)發(fā)生批量更新操作(BULK INSERT, bcp,

11、 select into, create index等)時，哪些區(qū)間發(fā)生了變化， BCM對應(yīng)的位置為1BCM位于文件的第8個頁面(page7) BCM與GAM結(jié)構(gòu)和表達(dá)范圍相同每次進(jìn)行完完全數(shù)據(jù)庫備份獲日志備份，BCM所有位被置為0當(dāng)進(jìn)行批量日志恢復(fù)時，將對應(yīng)BCM中位為1的區(qū)間恢復(fù)數(shù)據(jù)庫信息：sysdatabasesColumnInformationnameName of the database.dbidUnique database ID; can be reused when the database is dropped.sidSystem ID of the database cr

12、eator.modeLocking mode; used internally while a database is being created.statusBit mask that shows whether a database is read-only, off line, designated for use by a single user only, and so on. Some of the bits can be set by a database owner using the ALTER DATABASE command; others are set interna

13、lly. (The SQL Server documentation shows most of the possible bit-mask values.)status2Another bit mask-like status, with bits indicating additional database options.crdateFor user databases, the date when the database was created. For tempdb, this is the date and time that SQL Server was last starte

14、d. For other system databases, this date is not really useful. Depending on decisions made during installation, it could be the date Microsoft originally created the database prior to shipping the code, or it could be the date that you installed SQL Server.reservedReserved for future use.categoryAno

15、ther bit mask-like status. Contains information about whether the database is involved with replication.cmptlevelCompatibility level for the database. (Ill discuss this concept briefly at the end of the chapter.)Operating system path and name of the primary file.versionInternal version of SQL Server

16、 that was used to create the database查找數(shù)據(jù)表無聚集索引聚集索引非聚集索引沒有聚集索引的表HeapExtent 160Extent 168Extent 176010203SmithOtaJones.01020304AkhtarFunkSmithMartin.010203RuddWhiteBarr.010203ConFunkWhite.010203SmithOtaJones.01020304AkhtarFunkSmithMartin.010203RuddWhiteBarr.0102030405ConFunkWhiteDurkinLang.010203Smit

17、hOtaJones.01020304AkhtarFunkSmithMartin.010203RuddWhiteBarr.010203ConFunkWhite.010203SmithOtaJones.01020304AkhtarFunkSmithMartin.010203RuddWhiteBarr.0102030405DunnRandallOtaSlichterLaBrie.010203SmithOtaJones.01020304AkhtarFunkSmithMartin.010203RuddWhiteBarr.010203ConFunkWhite.010203SmithOtaJones.010

18、20304AkhtarFunkSmithMartin.010203RuddWhiteBarr.010203SmithOtaJones.01020304AkhtarFunkSmithMartin.010203RuddWhiteBarr.010203ConFunkWhite.010203SmithOtaJones.01020304AkhtarFunkSmithMartin.010203RuddWhiteBarr.010203GraffBaconKoch.ExtenteattleParisTokyoAtlanta.IAM160 1168 1176 0184 1Extent

19、Bit Mapidindid = 0First IAMsysindexes一般索引結(jié)構(gòu)Page 140 - RootPage 141Page 145AkhtarGanioAkhtarMartinMartinSmithNode Pagesidindid = xrootsysindexesPage 100 Page 120 Page 130 AkhtarBarrConFunkFunk.23345678253413341534.MartinMartinOtaPhuaRudd.12347778587878786078.SmithSmithSmithWhiteWhite.1434577879782234

20、1634.Page 110 GanioHallJonesJonesJones.76788078243459782634.LeafPages聚集索引葉結(jié)點是數(shù)據(jù)本身在索引上碼是唯一的，如果聚集索引不是唯一的索引，SQL Server 2000將添加在內(nèi)部生成的值以使重復(fù)的鍵唯一聚集索引Clustered IndexPage 140 - RootPage 100 Page 120 Page 130 Page 141Page 145AkhtarBarrConFunkFunk.23345678253413341534.MartinMartinOtaPhuaRudd.123477785878787860

21、78.SmithSmithSmithWhiteWhite.14345778797822341634.AkhtarGanioAkhtarMartinMartinSmithPage 110 GanioHallJonesJonesJones.76788078243459782634.SELECT lastname, firstnameFROM memberWHERE lastname = OtaClustered IndexPage 140 - RootPage 100 Page 120 Page 130 Page 141Page 145AkhtarBarrConFunkFunk.233456782

22、53413341534.MartinMartinOtaPhuaRudd.12347778587878786078.SmithSmithSmithWhiteWhite.14345778797822341634.AkhtarGanioAkhtarMartinMartinSmithPage 110 GanioHallJonesJonesJones.76788078243459782634.MartinOta5878.Martinidindid = 1rootsysindexes非聚集索引葉結(jié)點包含索引碼到數(shù)據(jù)行的標(biāo)識標(biāo)識可以是：RID (File #, Page #, Slot #)唯一的聚集索引碼

23、堆上的非聚集索引HeapPage 707 Page 808Page 70901020304.AkhtarFunkSmithMatey.Page 704 Page 705 Page 706 010203.ConnFunkWhite.010203.RuddWhiteBarr.010203.SmithOtaJones.01020304.MartinPhuaJonesSmith.010203.GanioJonesHall. #4HeapPage 707 Page 808Page 70901020304.AkhtarFunkSmithMatey.Page 704 Page 705 Page 706 01

24、0203.ConnFunkWhite.010203.RuddWhiteBarr.010203.SmithOtaJones.01020304.MartinPhuaJonesSmith.010203.GanioJonesHall. #404.Matey02.Phua01.Rudd02.OtaSELECT lastname, firstnameFROM memberWHERE lastnameBETWEEN Masters AND Rudd NonclusteredIndexLeaf Level(Key Value)Page 41Page 51 Page 61 Page 71 AkhtarBarrC

25、onFunkFunk4:706:014:705:034:704:014:706:024:704:02SmithSmithSmithWhiteWhite4:706:034:708:044:707:014:704:034:705:02GanioHallJonesJonesJones4:709:014:709:044:709:024:708:034:707:03MartinMateyOtaPhuaRudd4:708:014:706:044:707:024:708:024:705:01Leaf Level(Key Value)Page 41Page 51 Page 61 Page 71 AkhtarB

26、arrConFunkFunk4:706:014:705:034:704:014:706:024:704:02SmithSmithSmithWhiteWhite4:706:034:708:044:707:014:704:034:705:02GanioHallJonesJonesJones4:709:014:709:044:709:024:708:034:707:03MartinMateyOtaPhuaRudd4:708:014:706:044:707:024:708:024:705:01Matey4:706:04Phua4:708:02Rudd4:705:01Ota4:707:02Non-Lea

27、fLevelPage 28Page 28Non-LeafLevelPage 12 - RootPage 37Akhtar.MartinMartinSmith.AkhtarGanio.Page 12 - RootPage 37Akhtar.MartinMartinSmith.AkhtarGanio.MartinMartinidindid = 2rootsysindexes聚集索引表上的非聚集索引Clustered IndexOn Last NameNonclusteredIndex onFirst NameNon-LeafLevelLeaf Level(ClusteredKey Value)Aa

28、ronDeannaAaron.JoseJoseNinaDeannaDonDougDaumHallHamptonAaronAdamAmieConBarrBaldwinJoseJudyMikeLugoKaethlerNashBarrAdamCoxDaumArletteDeannaKimKobaraLaBrieShaneLindaRyanNagataNashNixonSusanneMikeTobyBarrKimNagataOMeliaClustered IndexOn Last NameNonclusteredIndex onFirst NameNon-LeafLevelLeaf Level(Clu

29、steredKey Value)AaronDeannaAaron.JoseJoseNinaDeannaDonDougDaumHallHamptonAaronAdamAmieConBarrBaldwinJoseJudyMikeLugoKaethlerNashBarrAdamCoxDaumArletteDeannaKimKobaraLaBrieShaneLindaRyanNagataNashNixonSusanneMikeTobyBarrKimNagataOMeliaidindid = 2rootsysindexesMikeNashNagataNashMikeSELECT lastname, fi

30、rstname, phoneFROM memberWHERE firstname = Mike 表信息存儲Sysobjectsname, object ID, ownerSysindexespointers to the storage space, tables size Indid0：表定義中沒有主碼或唯一性約束1：聚集索引2：非聚集索引Syscolumnscolumn name, datatype, length Sysreferences表信息存儲sysconstraintsSELECTOBJECT_NAME(constid) Constraint name,constid Const

31、raint ID,CASE (status & 0 xF)WHEN 1 THEN Primary keyWHEN 2 THEN UniqueWHEN 3 THEN Foreign KeyWHEN 4 THEN CheckWHEN 5 THEN defaultend Constraint Type,CASE (status & 0 x30)WHEN 0 x10 THEN ColumnWHEN 0 x20 THEN Tableelse NAend levelfrom sysconstraintswhere id=OBJECT_ID(SC)表信息存儲基本頁結(jié)構(gòu)所有頁面包括頁面頭、頁面體、頁面槽頁面頭

32、96 字節(jié)(64字節(jié)已用,32字節(jié)保留)頁面體存儲數(shù)據(jù)行頁面槽每行在頁面內(nèi)的偏移(每行兩個字節(jié))8,096字節(jié)基本頁結(jié)構(gòu)：頁頭字段含義Field What It ContainspageID and page number of this page in the databasenextPage and page number of the next page if this page is in a page chainprevPage and page number of the previous page if this page is in a page chainobjIDID of

33、 the object to which this page belongslsnLog sequence number (LSN) value used for changes and updates to this pageslotCntTotal number of slots (rows) used on this pagelevelLevel of this page in an index (always 0 for leaf pages)indexIdIndex ID of this page (always 0 for data pages)freeDataByte offse

34、t of the first free space on this pagepminlenNumber of bytes in fixed-length portion of rowsfreeCntNumber of free bytes on pagereservedCntNumber of bytes reserved by all transactionsxactreservedNumber of bytes reserved by the most recently started transactiontornBits1 bit per sector for detecting to

35、rn page writes flagBits2-byte bitmap that contains additional information about the page檢視數(shù)據(jù)頁ParameterDescriptiondbidID of the database containing the pagedbnameName of the database containing the page containing the pagepagenumPage number within the fileprintoptOptional print option; takes one of t

36、hese values: 0: Default; prints the buffer header and page header1: Prints the buffer header, page header, each row separately, and the row offset table2: Prints the buffer and page headers, the page as a whole, and the offset table3: Prints the buffer header, page header, each row separately, and t

37、he row offset table; each row is followed by each of its column values listed separatelyDBCC PAGE (dbid | dbname, , pagenum, printopt)檢視數(shù)據(jù)頁dbcc traceon(3604)godbcc page(5,1,88,1)goDBCC PAGE的輸出劃分為四部分 ：BUFFER, PAGE HEADER, DATA, OFFSET TABLE數(shù)據(jù)行結(jié)構(gòu)定長元組的存儲create table fixed(col1 char(5) not null,col2 int

38、 not null,col3 char(3) null,col4 char(6) not null,col5 float not null)insert into fixed values(ABCDE,123,NULL,CCCC,4567.8)ConvertPageNumsdbcc traceon(3604)dbcc page(chenlj,1,109,3)變長元組的存儲create table variable(col1 char(3) not null,col2 varchar(250)not null,col3 varchar(5) null,col4 varchar(20)not nu

39、ll,col5 smallint null)insert into variablevalues(AAA,REPLICATE(X,250),NULL,ABC,123)ConvertPageNumsdbcc traceon(3604)dbcc page(chenlj,1,112,3)DBCC EXTENTINFO (Northwind,Orders,1)BLOB的存儲BLOB的存儲create table HasText(col1 char(3) not null,col2 textnot null,col3 varchar(5) null,col4 varchar(20)not null,co

40、l5 smallint null)insert into HasTextvalues(AAA,REPLICATE(X,250),NULL,ABC,123)ConvertPageNumsdbcc traceon(3604)dbcc page(chenlj,1,116,3)BLOB的存儲將文本放入元組EXEC sp_tableoption HasText,text in row,500update HasTextset col2 = REPLICATE(Z,250)元組包含文本指針元組包含文本數(shù)據(jù)索引存儲create table Clustered_Dupes(col1 char(5) not n

41、ull,col2 int not null,col3 char(3) null,col4 char(6) not null,col5 float not null)create clustered index Cl_dupes_col1 on Clustered_Dupes(col1)insert into Clustered_Dupes values(ABCDE,123,NULL,CCCC,4567.8)insert into Clustered_Dupes values(ABCDE,456,NULL,DDDD,4567.8)insert into Clustered_Dupes value

42、s(ABCDE,64,NULL,EEEE,4567.8)dbcc traceon(3604)dbcc page(chenlj,1,119,1)建立聚集索引時如果沒有指定UNIQUE，對于重復(fù)碼，生成4個字節(jié)的唯一標(biāo)識符索引存儲：索引行結(jié)構(gòu)InformationMnemonicSizeStatus Bits ATagA Some of the relevant bits are:Bits 1 through 3: Taken as a 3-bit value, 0 indicates a primary record, 3 indicates an index record, and 5 ind

43、icates a ghost index record. Bit 4: Indicates that a NULL bitmap exists. Bit 5: Indicates that variable-length columns exist in the row. 1 byteFixed-length dataFdatapminlen - 1Number of columnsNcol2 bytesNULL bitmap (1 bit for each column in the table; a 1 indicates that the corresponding column is

44、NULL)NullbitsCeiling (Ncol / 8)Number of variable-length columns; only present if 0VarCount2 bytesVariable column offset array; only present if VarCount 0VarOffset2 * VarCountVariable-length data, if anyVarData聚集索引CREATE TABLE clustered_nodupes ( id int NOT NULL , str1 char (5) NOT NULL , str2 char

45、(600) NULL ) GOCREATE CLUSTERED INDEX idxCL ON clustered_nodupes(str1) GOSET NOCOUNT ONGODECLARE i intSET i = 1240WHILE i 13000 BEGIN INSERT INTO clustered_nodupes select i, cast(i AS char), cast(i AS char) SET i = i + 1 END GOSELECT first, root, id, indid FROM sysindexesWHERE id = object_id(cluster

46、ed_nodupes)聚集索引的根頁面聚集索引的中間頁面非聚集索引CREATE TABLE nc_heap_nodupes ( id int NOT NULL , str1 char (5) NOT NULL , str2 char (600) NULL ) GOCREATE UNIQUE INDEX idxNC_heap ON nc_heap_nodupes(str1) GOSET NOCOUNT ONGODECLARE i intSET i = 1240WHILE i 13000 BEGIN INSERT INTO nc_heap_nodupes select i, cast(i AS char), cast(i AS char) SET i = i + 1 END GOSELECT first, root, id, indid FROM sysindexesWHERE id = object_id(nc_heap_nodup