SQLSERVER內(nèi)存和緩沖區(qū)管理為SQLServer提供的可用內(nèi)存越多，SQLServer就有越多的資源使用于緩沖區(qū)，即可減少服務(wù)器從磁盤讀取數(shù)據(jù)以獲取靜態(tài)信息或編譯過程計(jì)劃的次數(shù)。調(diào)整SQLServer所占內(nèi)存可通過在服務(wù)器配置文件中設(shè)置totalmemory參數(shù)進(jìn)行，該參數(shù)的值指定了SQLServer在啟動時(shí)所需的內(nèi)存總量，如若該值為10000頁，貝ijSQLServer在啟動時(shí)就將試圖獲得10000*2KB=19.5MB的內(nèi)存，若不能滿足則啟動失敗。注意：若操作系統(tǒng)在進(jìn)程的整個(gè)生命周期中支持動態(tài)內(nèi)存分配，則可在SQLServer啟動后為其分配額外的內(nèi)存。SQLServer啟動時(shí)將內(nèi)存分配給SQLServer可執(zhí)行代碼，SQLServer使用的靜態(tài)內(nèi)存，用戶可配置參數(shù)占用的內(nèi)存及不駐留在緩存上的數(shù)據(jù)結(jié)構(gòu)，剩余的內(nèi)存分配給兩種SQLServer緩沖區(qū)即：數(shù)據(jù)緩沖區(qū)和過程緩沖區(qū)。兩緩沖區(qū)的大小對整個(gè)系統(tǒng)的性能影響很大，在一個(gè)開發(fā)系統(tǒng)上可能要增加過多端程緩沖區(qū)的專用內(nèi)存，而在一生產(chǎn)系統(tǒng)上則需減少過程緩沖區(qū)大小以便獲得更大的數(shù)據(jù)緩沖區(qū)。1、確定緩沖區(qū)大小在totalmemory配置的總空間中，首先要除去可愛執(zhí)行代碼所占空間，其大小因平臺和版本不同而異，可使用sp_configure查詢executablecodesize參數(shù)的值得知，一般為3-4MB；其次需除去內(nèi)部結(jié)構(gòu)所占空間，內(nèi)部結(jié)構(gòu)分成內(nèi)核結(jié)構(gòu)和服務(wù)器結(jié)構(gòu)，亦可將此部分內(nèi)存看成靜態(tài)開銷和用戶配置參數(shù)占用兩個(gè)部分，前者大小不受用戶可配置參數(shù)的影響，-般為2-3MB，而后者即用戶可配置參數(shù)的大小則取決于配置參數(shù)的類型及其值的大小，包括numberofuserconnections,numberofopendatabases,numberofdevices,numberofopenobjects,numberoflocks等，要得到這些用戶配置參數(shù)使用內(nèi)存大小的精確估計(jì)，可調(diào)用不帶選項(xiàng)的sp_configure以顯示所有參數(shù)和每個(gè)參數(shù)使用的內(nèi)存量，其和即是用戶配置參數(shù)所占用內(nèi)存。除去上述占用，剩余內(nèi)存將以procedurecachepercent配置參數(shù)的值按比例分配給過程緩沖區(qū)和數(shù)據(jù)緩沖區(qū)，例若值為20,即表示剩余內(nèi)存的20%分配給過程緩沖區(qū)而80%分配

給數(shù)據(jù)緩沖區(qū)。確定緩沖區(qū)大小的另一種方法是檢查SQLSERVE啟動時(shí)寫入給數(shù)據(jù)緩沖區(qū)。確定緩沖區(qū)大小的另一種方法是檢查SQLSERVE啟動時(shí)寫入SQLServerServer:Numberofprocbuffersassocated:556Server:Numberofblocksleftforprocheaders:629Server:Memoryallocatedforthedefaultdatacache:4144kb前兩行為過程緩沖區(qū)信息，過程緩沖區(qū)總大小是分配給過程緩沖區(qū)的內(nèi)存（第一行）及分配給過程頭（即存放編譯對象如存儲過程的地方，根據(jù)被存儲對象的大小，可能需要一個(gè)或多個(gè)過程頭）的內(nèi)存（第二行）之和，可存儲在過程緩沖區(qū)中的編譯對象受到此二者中較小者的限制。上述信息中，第一行指明了分配給過程緩沖區(qū)的緩沖區(qū)數(shù)量為556KB，每個(gè)緩沖區(qū)大小為76B，故過程緩沖區(qū)大小即為42256B，合21頁；第二行則指明了分配給過程頭的空間為629頁，由此可以得出，過程緩沖區(qū)總大小為629+21=650頁，合1.27MB。信息中第三行則指明了分配給默認(rèn)數(shù)據(jù)庫緩沖區(qū)的空間大小。此外，若配置了命名緩沖區(qū)，則相關(guān)信息還將包含每個(gè)命名緩沖區(qū)的信息，再加上這些命名緩沖區(qū)所占內(nèi)存大小即得到總的數(shù)據(jù)緩沖區(qū)大小。欲知數(shù)據(jù)緩沖區(qū)大小還可通過sp_helpcache過程得到每個(gè)緩沖區(qū)和捆綁到這些緩沖區(qū)中的對象以及與不同緩沖區(qū)大小相關(guān)的開銷的詳細(xì)信息。2、緩沖區(qū)管理數(shù)據(jù)緩沖區(qū)保存SQLServer當(dāng)前使用和最近使用過的數(shù)據(jù)頁，索引頁和日志頁。初裝SQLServer時(shí)，會產(chǎn)生一個(gè)默認(rèn)的數(shù)據(jù)緩沖區(qū)。SQLServer允許系統(tǒng)管理員把數(shù)據(jù)緩沖區(qū)分成獨(dú)立的命名數(shù)據(jù)緩沖區(qū)，然后可把數(shù)據(jù)庫或數(shù)據(jù)庫對象捆綁到這些命名緩沖區(qū)上，以對緩沖區(qū)空間進(jìn)行更合理的組織，控制數(shù)據(jù)庫，表和索引在內(nèi)存的駐留。另外，系統(tǒng)管理員還可在緩沖區(qū)內(nèi)創(chuàng)建緩沖池用于執(zhí)行大塊的磁盤I/O,改善數(shù)據(jù)查詢性能，減少磁盤I/O。有關(guān)數(shù)據(jù)庫緩沖區(qū)的指令有：sp_cacheconfig創(chuàng)建或刪除命名緩沖區(qū)，改變緩沖區(qū)的大小或類型。sp_poolconfig創(chuàng)建或刪除I/O緩沖池，并改變其大小。sp_bindeache將數(shù)據(jù)庫或數(shù)據(jù)庫對象捆綁到緩沖區(qū)。sp_unbindcache從一個(gè)緩沖區(qū)中取消對指定對象或數(shù)據(jù)庫的捆綁。sp_unbindcache_all從一個(gè)緩沖區(qū)中取消所有對象的捆綁。sp_helpcache報(bào)告有關(guān)數(shù)據(jù)緩沖區(qū)的小結(jié)信息，并顯示捆綁于緩沖區(qū)的數(shù)據(jù)庫和對象。sp_cachestrategy報(bào)告有關(guān)為表，索引設(shè)置的緩沖區(qū)策略，禁止或重新允許先提或MRU策略。sp_logiosize為日志改變默認(rèn)的I/O大小。sp_spaceused估計(jì)表和索引大小或有關(guān)數(shù)據(jù)庫使用的空間量的信息。sp_estspace估計(jì)表和索引的大小，給出表包含的行的數(shù)目。sp_help報(bào)告表將捆綁于哪個(gè)緩沖區(qū)。sp_helpindex報(bào)告索引將捆綁于哪個(gè)緩沖區(qū)。sp_helpdb報(bào)告數(shù)據(jù)庫將捆綁于哪個(gè)緩沖區(qū)。setshowplanon報(bào)告有關(guān)用于查詢的I/O大小和緩沖區(qū)的應(yīng)用策略。setstatisticsioon報(bào)告為查詢進(jìn)行的讀操作數(shù)量。setprefetchon/off允許或禁止某個(gè)會話的預(yù)先提取。(1)查詢數(shù)據(jù)庫緩沖區(qū)的信息：查看默認(rèn)數(shù)據(jù)緩沖區(qū)的大小，可使用指令sp_cacheconfig"defaultdatacache",顯示結(jié)果如下，由兩部分構(gòu)成，上部分報(bào)告每個(gè)緩沖區(qū)中的內(nèi)存池配置信息并總計(jì)了所有配置緩沖區(qū)的大小；下部分提供了每個(gè)緩沖區(qū)中的其他詳細(xì)信息。第一部分輸出各列含義為:cachename為緩沖區(qū)名；status指示該緩沖區(qū)是否激活（值為pend/act時(shí)表示該緩沖區(qū)剛建立，重啟后將被激活；值為active時(shí)表示該緩沖區(qū)目前正處于激活狀態(tài)；值為pend/del時(shí)表示該緩沖區(qū)處于激活狀態(tài)，但在服務(wù)器重啟后將被刪除）；type指示緩沖區(qū)是否可以存儲數(shù)據(jù)（“mixed'和）日志頁（“l(fā)ogonly只有默認(rèn)緩沖區(qū)才會有“default類型，且默認(rèn)的數(shù)據(jù)緩沖區(qū)類型不能改變，其他類型緩沖區(qū)也不能改為“default。”configvalue顯示下次SQLServer重啟后緩沖區(qū)的大小，在此情況下，默認(rèn)緩沖區(qū)不能顯式配置，故信息中其大小為；runvalue顯示SQLServer目前使用的大小，對于默認(rèn)數(shù)據(jù)緩沖區(qū)，該值將作為未顯示配置到其他緩沖區(qū)中的所有數(shù)據(jù)緩沖區(qū)空間。第二部分輸出各列含義為:IOsize顯示緩沖池中的緩存大小，所有緩沖池缺省都分配2KB空間（有效空間大小為2KB，4KB，8KB，16KB）；washsize指示緩沖池的刷洗區(qū)大?。籧onfigsize和runsize顯示配置大小和目前使用的大小，該兩值在2KB的緩沖池中是不同的（因?yàn)椴荒茱@式配置其大小），而對于其他緩沖池，若試圖在緩沖池間移動空間而一些空間又不能被釋放時(shí)，上述兩值也不同。CacheNameStatusTypeConfigValueRunvaluedefaultdatacacheActiveDefault0.00MB59.36MBTotal0.00MB59.36MBCache:defaultdatacache,status:Active,Type:DefaultConfigSize:0.00MB,Runsize:59.36MBIOSizeWashSizeConfigSizeRunSize2KB512KB0.00MB59.36MB（2）配置數(shù)據(jù)緩沖區(qū):配置數(shù)據(jù)緩沖區(qū)有兩種途徑：一是使用過程sp_cacheconfig和sp_poolconfig進(jìn)行交互式配置，二是編輯配置文件中的totalmemory參數(shù)后重啟使之生效。A、交互式配置：創(chuàng)建命名緩沖區(qū)（新創(chuàng)建的命名緩沖區(qū)從缺省緩沖區(qū)defaultdatacache中分配空間）每次執(zhí)行sp_cacheconfig或sp_poolconfig時(shí),SQLServer都將新的緩沖區(qū)或緩沖池信息寫入配置文件中并將文件的舊版本拷貝到一個(gè)備份文件中，給出備份文件名的信息被送至錯(cuò)誤日志。創(chuàng)建一個(gè)新的名為pubs_cache的10MB緩沖區(qū)指令如下，大小單位除MB外,還可為P頁，KB,GB,（默認(rèn)為KB）。該指令將改變系統(tǒng)表并將新值寫入配置文件但暫不激活緩沖區(qū)，要使其生效須重啟SQLServer。sp_cacheconfigpubs_cache,"10MB"在重啟SQLServer使新值生效前，可先用sp_cacheconfig“pubs_cache查看配置信息如下，其中的status列值為pend/act說明該緩沖區(qū)的配置是掛起的，要待重啟后生效，故Configvalue（配置的值）和Runvalue（正在使用的值）不同：CacheNameStatusTypeConfigValueRunvaluepubs_cachepend/actmixed10.00MB00.00MBTotal10.00MB00.00MB重啟后再允許不帶緩沖區(qū)名參數(shù)的sp_cacheconfig,則信息改變?nèi)缦?，可見pubs_cache已被激活，默認(rèn)緩沖區(qū)從其空間中分出了10MB給該緩沖區(qū)。可利用sp_cacheconfig過程設(shè)置默認(rèn)數(shù)據(jù)緩沖區(qū)的最小值，該部分空間將被鎖定給默認(rèn)數(shù)據(jù)緩沖區(qū)。例如設(shè)置默認(rèn)數(shù)據(jù)緩沖區(qū)大小最少不低于25MB則指令為：sp_cacheconfig"defaultdatacache""25M"CacheNameStatusTypeConfigValueRunvaluedefaultdatacacheActiveDefault0.00MB49.28MBpubs_cacheActivemixed10.00MB10.00MBTotal10.00MB59.28MBCache:defaultdatacache,status:Active,Type:DefaultConfigSize:0.00MB,Runsize:49.28MBIOSizeWashSizeConfigSizeRunSize2KB512KB0.00MB49.28MBCache:pubs_cache,status:Active,Type:mixedConfigSize:10.00MB,Runsize:10.00MBIOSizeWashSizeConfigSizeRunSize2KB512KB0.00MB10.00MB若要保留一個(gè)只用于事務(wù)日志的緩沖區(qū)的類型，可以將緩沖區(qū)的類型設(shè)為“l(fā)ogonly如”創(chuàng)，建“l(fā)ogonly類型的pubs_log緩沖區(qū)為sp_cacheconfigpubs_log,“7MB,“l(fā)ogonly下示為該緩沖區(qū)在重啟前的狀態(tài)。CacheNameStatusTypeConfigValueRunvaluepubs_cachepend/actlogonly7.00MB0.00MBTotal7.00MB0.00MB創(chuàng)建命名緩沖區(qū)后一般都須將某些對象捆綁到該緩沖區(qū)上。若建立命名緩沖區(qū)后未將任何對象捆綁到該命名緩沖區(qū)上，則該命名緩沖區(qū)所占內(nèi)存將被浪費(fèi)，因?yàn)槿魏挝粗付ň彌_區(qū)的對象都將缺省使用默認(rèn)數(shù)據(jù)緩沖區(qū)defaultdatacacheo3、將數(shù)據(jù)緩沖區(qū)劃分為緩沖池建立數(shù)據(jù)緩沖區(qū)后，可將其劃分成緩沖池，各不同的緩沖池具有不同的I/O大小，當(dāng)SQLServer進(jìn)行大I/O操作時(shí)，可將許多頁一次讀入緩沖區(qū)中，故可提高I/O效率。這些頁總是被當(dāng)作一個(gè)單元進(jìn)行處理，它們共存于緩沖區(qū)中，作為一個(gè)單元被寫到磁盤中。緩沖池的頁大小可為2KB,4KB,8KB,16KB，緩沖池的總大小不能低于512KB。所有關(guān)于緩沖池的配置都是動態(tài)的，無需重啟SQLServer即可生效。SQLServer內(nèi)2KB頁緩沖池的個(gè)數(shù)必須保持一個(gè)合理的值，否則將會影響SQLServer性能，因?yàn)槟承┟钪荒苁褂?K的I/O塊如diskinit及某些dbcc命令。在pubs_cache數(shù)據(jù)緩沖區(qū)中創(chuàng)建一個(gè)每頁16KB，總空間為7MB的緩沖池，指令如下。通常，創(chuàng)建命名數(shù)據(jù)緩沖區(qū)時(shí)，其空間總是被默認(rèn)劃分為每頁2KB的緩沖池，創(chuàng)建其它頁大小的緩沖池時(shí)，該2KB頁緩沖池空間被部分分配給新緩沖池。下例中2KB頁大小的緩沖池原共有10MB空間，分配給7MB給16KB頁的新緩沖池后，2KB頁緩沖池還剩3MB空間。>sp_poolconfigpubs_cache，"7M"，"16K">go>sp_cacheconfigpubs_cacheCacheNameStatusTypeConfigValueRunvaluepubs_cacheActivemixed10.00MB10.00MBTotal10.00MB10.00MBCache:pubs_cache,status:Active,Type:mixedConfigSize:10.00MB,Runsize:10.00MBIOSizeWashSizeConfigSizeRunSize2KB512KB0.00MB3.00MB16KB1424KB7.00MB7.00MB<p<p>>在默認(rèn)數(shù)據(jù)緩沖區(qū)中也可創(chuàng)建緩沖池，如創(chuàng)建16KB緩沖池，空間大小為8MB,則指令為：sp_poolconfig"defaultdatacache","8M","16K"。若要為一個(gè)數(shù)據(jù)庫的事務(wù)日志創(chuàng)建緩沖區(qū)，應(yīng)配置緩沖區(qū)中大部分空間來匹配日志I/O的大小。默認(rèn)值為4KB,若沒有4KB的可用緩沖池，SQLServer就為日志使用2KB的I/O。日志I/O的大小可通過系統(tǒng)過程sp_logiosize來改變，每個(gè)數(shù)據(jù)庫的日志I/O大小會在SQLServer啟動后的錯(cuò)誤日志中報(bào)告，也可通過使用數(shù)據(jù)庫和執(zhí)行不帶參數(shù)的過程。sp_logiosize來檢查一個(gè)數(shù)據(jù)庫的日志I/O大小。如為pubs_log緩沖區(qū)配置4KB頁大小的緩沖池指令為sp_poolconfigpubs_log，"3MB"，"4KB"，也可在默認(rèn)數(shù)據(jù)緩沖區(qū)中創(chuàng)建一個(gè)4KB頁緩沖池，以供未被捆綁至其它緩沖區(qū)的任何數(shù)據(jù)庫的事務(wù)日志來使用：sp_poolconfig"defaultdatacache"，"2.5MB"，"4KB"。此外，可修改緩沖池大小，如從16K頁I/O的緩沖池中取出1MB空間增加到4KB頁I/O緩沖池中:sp_poolconifgpub_cache，"1MB"，"4KB"，"16KB"4、緩沖區(qū)悃綁操作系統(tǒng)管理員把數(shù)據(jù)緩沖區(qū)分成獨(dú)立的命名數(shù)據(jù)緩沖區(qū)后，可將數(shù)據(jù)庫對象捆綁到這些緩沖區(qū)上，以控制數(shù)據(jù)庫，表和索引在內(nèi)存的駐留。若建立命名緩沖區(qū)后未將任何對象捆綁到該命名緩沖區(qū)上，則該命名緩沖區(qū)所占內(nèi)存將被浪費(fèi)，因?yàn)槿魏挝粗付ň彌_區(qū)的對象都將缺省使用默認(rèn)數(shù)據(jù)緩沖區(qū)defaultdatacacheo為了將任何系統(tǒng)表，包括事務(wù)日志syslogs捆綁至緩沖區(qū)中，數(shù)據(jù)庫須處于單用戶模式。對象被捆綁后立即生效而無需重啟SQLServero注意，進(jìn)行捆綁或刪除捆綁操作時(shí)，SQLServer需鎖定相應(yīng)對象，故相應(yīng)對象上的其它操作可能有延遲。此外，可不刪除現(xiàn)有的捆綁而重新捆綁對象。另外，當(dāng)被捆綁對象上有臟的讀活動或有打開的游標(biāo)時(shí)，捆綁或刪除捆綁的操作將不能進(jìn)行。1>sp_dboptionpubs2,single,true直pubs2庫為單用戶狀態(tài)2>usepubs23>checkpoint4>go（準(zhǔn)備進(jìn)行數(shù)據(jù)庫對象的捆綁。注意，捆綁對象須在對象所在的庫中進(jìn)行）。>sp_bindcachepubs_cache,pubs2,titles/將pubs2庫中的表titles捆綁至緩沖區(qū)pubs_cache。>sp_bindcachepubs_cache,pubs2,titles。titleind/在titles上捆綁索引，并將pubs2庫中的表titles捆綁至緩沖區(qū)pubs_cache。>sp_bindcachepubs_cache,pubs2,"hj。sale_east"/將pubs2庫中用戶hj的表sale_east捆綁至緩沖區(qū)pubs_cache。>sp_bindcachepubs_log,pubs2,syslogs/將pubs2庫的事務(wù)日志syslogs捆綁至緩沖區(qū)pubs_log。>sp_bindcachepubs_cache,pubs2,au_pix,"textonly"/表的text和image列存儲在一個(gè)單獨(dú)的數(shù)據(jù)結(jié)構(gòu)中，若將此表捆綁至緩沖區(qū)，需加“text參數(shù)。1>sp_dboptionpubs3,single,true直pubs3庫為單用戶狀態(tài)2>usepubs33>checkpoiot4>usemaster行g(shù)o準(zhǔn)備進(jìn)行數(shù)據(jù)庫的捆綁，注意，捆綁數(shù)據(jù)庫須在master庫中進(jìn)>sp_bindcachetempdb_cache,tempdb將數(shù)據(jù)庫tempdb捆綁至緩沖區(qū)tempdb_cache?？赏ㄟ^sp_helpcache過程查詢所有或指定緩沖區(qū)及其中被捆綁對象的捆綁信息。如下例示,其注意，被捆綁到日志緩沖區(qū)上的對象只能是syslogs表。中status列報(bào)告緩沖區(qū)的捆綁是（“『否（“I”）。若數(shù)據(jù)庫或?qū)ο蟊焕壷辆彌_池而該緩沖區(qū)已被刪除，則捆綁信息仍保留在系統(tǒng)表中但緩沖區(qū)捆綁被標(biāo)記無效，所有無效的捆綁對象均使用默認(rèn)的數(shù)據(jù)庫緩沖區(qū)。若隨后用與被刪除的緩沖區(qū)相同的名字創(chuàng)建了另一個(gè)緩沖區(qū)，則當(dāng)該緩沖區(qū)通過重啟SQLServer而被激活時(shí)，上述捆綁又將變?yōu)橛行?。刪除緩沖區(qū)捆綁可用兩個(gè)過程:sp_unbindcache用于刪除緩沖區(qū)中對一個(gè)實(shí)體的捆綁；sp_unbindcache_all則用于刪除緩沖區(qū)中對所有對象的捆綁，但若捆綁至緩沖區(qū)的數(shù)據(jù)庫超過8個(gè)，或數(shù)據(jù)庫的對象超過8個(gè)，就不能使用sp_unbindcache_alI,此情況下，必須使用sp_unbindcache刪除單個(gè)的數(shù)據(jù)庫或?qū)ο?，使捆綁的?shù)據(jù)庫不超過8個(gè)。此外，當(dāng)刪除緩沖區(qū)對一個(gè)對象的捆綁時(shí)，內(nèi)存中所有當(dāng)前的頁都被從緩沖區(qū)中清除。>sp_unbindcachepubs2/刪除對數(shù)據(jù)庫pubs2的捆綁。>sp_unbindcachepubs2,titles/刪除對pubs2庫中對titles表的捆綁。>sp_unbindcachepubs2,titles,titleidind/鏟除對pubs2庫中titles表上titleidind索引的捆綁?？赏ㄟ^sp_cacheconfig指定一個(gè)新的空間以增加或減少指定緩沖區(qū)的大小，所有新增空間都將從缺省數(shù)據(jù)緩沖區(qū)defaultdatacache內(nèi)的2KB緩沖池中被加到指定緩沖區(qū)內(nèi)的2K頁緩沖池中，所有減少的空間亦從指定緩沖區(qū)內(nèi)的2KB頁緩沖池中被釋放到缺省數(shù)據(jù)庫緩沖區(qū)defaultdatacache內(nèi)的2KB緩沖池中。>sp_cacheconfigpubs_cacheCacheNameStatusTypeConfigValueRunvaluepubs_cacheActivemixed10.00MB10.00MBTotal10.00MB10.00MBCache:pubs_cache,status:Active,Type:mixedConfigSize:10.00MB,Runsize:10.00MBIOSizeWashSizeConfigSizeRunSize2KB720KB0.00MB3.00MB4KB1024KB4.00MB4.00MB16KB1424KB3.00MB3.00MB現(xiàn)以sp_cacheconfigpubs_cache,“20MB后重啟，則改變?nèi)缦?gt;sp_cacheconfigpubs_cacheCacheNameStatusTypeConfigValueRunvaluepubs_cacheActivemixed20.00MB20.00MBTotal20.00MB20.00MBCache:pubs_cache,status:Active,