模板
教程
環境
性能
功能
管理
SYBASE內存和緩沖區管理
為SQLSERVER提供的可用內存越多，SQLSERVER就有越多的資源使用于緩沖區，即可減少服務器從磁盤讀取數據以獲取靜態信息或編譯過程計劃的次數。調整SQLSERVER所占內存可通過在服務器配置文件中設置totalmemory參數進行，該參數的值指定了SQLSERVER在啟動時所需的內存總量，如若該值為10000頁，則SQLSERVER在啟動時就將試圖獲得10000*2K=19.5M的內存，若不能滿足則啟動失敗。
注意，若操作系統在進程的整個生命周期中支持動態內存分配，則可在SQLSERVER啟動后為其分配額外的內存。
SQLSERVER啟動時將內存分配給SQLSERVER可執行代碼，SQLSERVER使用的靜態內存，用戶可配置參數占用的內存及不駐留在緩存上的數據結構，剩余的內存分配給兩種SQLSERVER緩沖區即：數據緩沖區和過程緩沖區。兩緩沖區的大小對整個系統的性能影響很大，在一個開發系統上可能要增加過多端程緩沖區的專用內存，而在一生產系統上則需減少過程緩沖區大小以便獲得更大的數據緩沖區。
1.確定緩沖區大小
在totalmemory配置的總空間中，首先要除去可愛執行代碼所占空間，其大小因平臺和版本不同而異，可使用sp_configure查詢executablecodesize參數的值得知，一般為3-4MB；其次需除去內部結構所占空間，內部結構分成內核結構和服務器結構，亦可將此部分內存看成靜態開銷和用戶配置參數占用兩個部分，前者大小不受用戶可配置參數的影響，一般為2-3MB，而后者即用戶可配置參數的大小則取決于配置參數的類型及其值的大小，包括numberofuserconnections, numberofopendatabases,numberofdevices,numberofopenobjects,numberoflocks等，要得到這些用戶配置參數使用內存大小的精確估計，可調用不帶選項的sp_configure以顯示所有參數和每個參數使用的內存量，其和即是用戶配置參數所占用內存。
除去上述占用，剩余內存將以procedurecachepercent配置參數的值按比例分配給過程緩沖區和數據緩沖區，例若值為20，即表示剩余內存的20%分配給過程緩沖區而80%分配給數據緩沖區。
確定緩沖區大小的另一種方法是檢查SQLSERVE啟動時寫入SQLSERVER錯誤日志的內存信息，其中準確地說明了分配給過程緩沖區和數據緩沖區的數據量，以及多少個過程或其他編譯對象能同時駐留在緩沖區中，如相關信息為：
server:Numberofprocbuffersassocated:556
server:Numberofblocksleftforprocheaders:629
server:Memoryallocatedforthedefaultdatacache:4144kb
前兩行為過程緩沖區信息，過程緩沖區總大小是分配給過程緩沖區的內存(第一行)及分配給過程頭(即存放編譯對象如存儲過程的地方，根據被存儲對象的大小，可能需要一個或多個過程頭) 的內存(第二行)之和，可存儲在過程緩沖區中的編譯對象受到此二者中較小者的限制。上述信息中，第一行指明了分配給過程緩沖區的緩沖區數量為556，每個緩沖區大小為76B，故過程緩沖區大小即為42256B，合21頁；第二行則指明了分配給過程頭的空間為629頁，由此可以得出，過程緩沖區總大小為629+21=650頁，合1.27M。
信息中第三行則指明了分配給默認數據庫緩沖區的空間大小。此外，若配置了命名緩沖區，則相關信息還將包含每個命名緩沖區的信息，再加上這些命名緩沖區所占內存大小即得到總的數據緩沖區大小。欲知數據緩沖區大小還可通過sp_helpcache過程得到每個緩沖區和捆綁到這些緩沖區中的對象以及與不同緩沖區大小相關的開銷的詳細信息。
2.緩沖區管理
數據緩沖區保存SQLSERVER當前使用和最近使用過的數據頁,索引頁和日志頁.初裝SQLSERVER時,會產生一個默認的數據緩沖區.SQLSERVER允許系統管理員把數據緩沖區分成獨立的命名數據緩沖區,然后可把數據庫或數據庫對象捆綁到這些命名緩沖區上,以對緩沖區空間進行更合理的組織,控制數據庫,表和索引在內存的駐留.另外,系統管理員還可在緩沖區內創建緩沖池,用于執行大塊的磁盤I/O,改善數據查詢性能,減少磁盤I/O.有關數據庫緩沖區的指令有：
sp_cacheconfig創建或刪除命名緩沖區,改變緩沖區的大小或類型
sp_poolconfig創建或刪除I/O緩沖池,并改變其大小
sp_bindeache將數據庫或數據庫對象捆綁到緩沖區
sp_unbindcache從一個緩沖區中取消對指定對象或數據庫的捆綁
sp_unbindcache_all從一個緩沖區中取消所有對象的捆綁
sp_helpcache報告有關數據緩沖區的小結信息,并顯示捆綁于緩沖區的數據庫和對象
sp_cachestrategy報告有關為表,索引設置的緩沖區策略,禁止或重新允許先提或MRU策略
sp_logiosize為日志改變默認的I/O大小
sp_spaceused估計表和索引大小或有關數據庫使用的空間量的信息
sp_estspace估計表和索引的大小,給出表包含的行的數目
sp_help報告表將捆綁于哪個緩沖區
sp_helpindex報告索引將捆綁于哪個緩沖區
sp_helpdb報告數據庫將捆綁于哪個緩沖區
setshowplanon報告有關用于查詢的I/O大小和緩沖區的應用策略
setstatisticsioon報告為查詢進行的讀操作數量
setprefetchon/off允許或禁止某個會話的預先提取
(1)查詢數據庫緩沖區的信息
查看默認數據緩沖區的大小,可使用指令sp_cacheconfig"defaultdatacache",顯示結果如下,由兩部分構成,上部分報告每個緩沖區中的內存池配置信息并總計了所有配置緩沖區的大小;下部分提供了每個緩沖區中的其他詳細信息。
第一部分輸出各列含義為:cachename為緩沖區名;status指示該緩沖區是否激活(值為pend/act時表示該緩沖區剛建立,重啟后將被激活;值為active時表示該緩沖區目前正處于激活狀態;值為pend/del時表示該緩沖區處于激活狀態,但在服務器重啟后將被刪除);type 指示緩沖區是否可以存儲數據("mixed")和日志頁("logonly"),只有默認緩沖區才會有"default"類型,且默認的數據緩沖區類型不能改變,其他類型緩沖區也不能改為"default"; configvalue顯示下次SQLSERVER重啟后緩沖區的大小,在此情況下,默認緩沖區不能顯式配置,故信息中其大小為0;runvalue顯示SQLSERVER目前使用的大小,對于默認數據緩沖區,該值將作為未顯示配置到其他緩沖區中的所有數據緩沖區空間。
第二部分輸出各列含義為:IOsize顯示緩沖池中的緩存大小,所有緩沖池缺省都分配2K空間(有效空間大小為2K,4K,8K,16K);washsize指示緩沖池的刷洗區大小;configsize和runsize顯示配置大小和目前使用的大小,該兩值在2K的緩沖池中是不同的(因為不能顯式配置其大小),而對于其他緩沖池,若試圖在緩沖池間移動空間而一些空間又不能被釋放時, 上述兩值也不同。
|
(2)配置數據緩沖區
配置數據緩沖區有兩種途徑:一是使用過程sp_cacheconfig和sp_poolconfig進行交互式配置,二是編輯配置文件中的totalmemory參數后重啟使之生效。
A.交互式配置
創建命名緩沖區(新創建的命名緩沖區從缺省緩沖區defaultdatacache中分配空間) ，每次執行sp_cacheconfig或sp_poolconfig時,SQLSERVER都將新的緩沖區或緩沖池信息寫入配置文件中并將文件的舊版本拷貝到一個備份文件中,給出備份文件名的信息被送至錯誤日志。
創建一個新的名為pubs_cache的10M緩沖區指令如下,大小單位除M外,還可為P頁,K,G, (默認為K).該指令將改變系統表并將新值寫入配置文件但暫不激活緩沖區,要使其生效須重啟SQLSERVER.
sp_cacheconfigpubs_cache,"10M"
在重啟SQLSERVER使新值生效前,可先用sp_cacheconfig"pubs_cache"查看配置信息。如下,其中的status列值為pend/act說明該緩沖區的配置是掛起的,要待重啟后生效,故Configvalue(配置的值)和Runvalue(正在使用的值)不同。
CacheNameStatusTypeConfigvalueRunvalue |
重啟后再允許不帶緩沖區名參數的sp_cacheconfig,則信息改變如下,可見pubs_cache 已被激活,默認緩沖區從其空間中分出了10M給該緩沖區.可利用sp_cacheconfig過程設置默認數據緩沖區的最小值,該部分空間將被鎖定給默認數據緩沖區.例如設置默認數據緩沖區大小最少不低于25M則指令為sp_cacheconfig"defaultdatacache""25M"。
|
若要保留一個只用于事務日志的緩沖區的類型,可以將緩沖區的類型設為"logonly", 如創建"logonly"類型的pubs_log緩沖區為sp_cacheconfigpubs_log,"7M","logonly", 下示為該緩沖區在重啟前的狀態。
CacheNameStatusTypeConfigvalueRunvalue
---------------------------------------------------------
pubs_cachepend/actlogonly7.00MB0.00MB
-------------------------------
Total7.00MB0.00MB
創建命名緩沖區后一般都須將某些對象捆綁到該緩沖區上。若建立命名緩沖區后未將任何對象捆綁到該命名緩沖區上，則該命名緩沖區所占內存將被浪費，因為任何未指定緩沖區的對象都將缺省使用默認數據緩沖區defaultdatacache.
>;
將數據緩沖區劃分為緩沖池建立數據緩沖區后,可將其劃分成緩沖池,各不同的緩沖池具有不同的I/O大小,當SQLSERVER進行大I/O操作時,可將許多頁一次讀入緩沖區中,故可提高I/O效率.這些頁總是被當作一個單元進行處理,它們共存于緩沖區中,作為一個單元被寫到磁盤中。
緩沖池的頁大小可為2K,4K,8K,16K,緩沖池的總大小不能低于512K.所有關于緩沖池的配置都是動態的,無需重啟SQLSERVER即可生效.SQLSERVER內2K頁緩沖池的個數必須保持一個合理的值，否則將會影響SQLSERVER性能，因為某些命令只能使用2K的I/O塊如diskinit及某些dbclearcase/" target="_blank" >cc命令。
在pubs_cache數據緩沖區中創建一個每頁16K,總空間為7MB的緩沖池,指令如下.通常,創建命名數據緩沖區時,其空間總是被默認劃分為每頁2K的緩沖池,創建其它頁大小的緩沖池時,該2K頁緩沖池空間被部分分配給新緩沖池.下例中2K頁大小的緩沖池原共有10M空間,分配給7M給16K頁的新緩沖池后,2K頁緩沖池還剩3M空間。
|
在默認數據緩沖區中也可創建緩沖池,如創建16K緩沖池,空間大小為8M,則指令為：sp_poolconfig"defaultdatacache","8M","16K".
若要為一個數據庫的事務日志創建緩沖區,應配置緩沖區中大部分空間來匹配日志I/O的大小.默認值為4K,若沒有4K的可用緩沖池,SQLSERVER就為日志使用2K的I/O.
日志I/O的大小可通過系統過程sp_logiosize來改變,每個數據庫的日志I/O大小會在SQLSERVER啟動后的錯誤日志中報告,也可通過使用數據庫和執行不帶參數的過程sp_logiosize來檢查一個數據庫的日志I/O大小.如為pubs_log緩沖區配置4K頁大小的緩沖池指令為sp_poolconfigpubs_log,"3M","4K",也可在默認數據緩沖區中創建一個4K頁緩沖池,以供未被捆綁至其它緩沖區的任何數據庫的事務日志來使用:sp_poolconfig"defaultdatacache","2.5M","4K".
此外，可修改緩沖池大小，如從16K頁I/O的緩沖池中取出1M空間增加到4K頁I/O緩沖池中:sp_poolconifgpub_cache,"1M","4K","16K"
緩沖區悃綁操作
系統管理員把數據緩沖區分成獨立的命名數據緩沖區后,可將數據庫對象捆綁到這些緩沖區上,以控制數據庫,表和索引在內存的駐留.若建立命名緩沖區后未將任何對象捆綁到該命名緩沖區上，則該命名緩沖區所占內存將被浪費，因為任何未指定緩沖區的對象都將缺省使用默認數據緩沖區defaultdatacache.為了將任何系統表, 包括事務日志syslogs捆綁至緩沖區中,數據庫須處于單用戶模式.對象被捆綁后立即生效而無需重啟SQLSERVER.注意,進行捆綁或刪除捆綁操作時,SQLSERVER需鎖定相應對象,故相應對象上的其它操作可能有延遲.此外,可不刪除現有的捆綁而重新捆綁
對象.另外,當被捆綁對象上有臟的讀活動或有打開的游標時,捆綁或刪除捆綁的操作將不能進行。
1>;sp_dboptionpubs2,single,true置pubs2庫為單用戶狀態
2>;usepubs2
3>;checkpoint
4>;go準備進行數據庫對象的捆綁。注意，捆綁對象須在對象所在的庫中進行。
>;sp_bindcachepubs_cache,pubs2,titles
將pubs2庫中的表titles捆綁至緩沖區pubs_cache.
>;sp_bindcachepubs_cache,pubs2,titles.titleind
在titles上捆綁索引,并將pubs2庫中的表titles捆綁至緩沖區pubs_cache.
>;sp_bindcachepubs_cache,pubs2,"hj.sale_east"
將pubs2庫中用戶hj的表sale_east捆綁至緩沖區pubs_cache.
>;sp_bindcachepubs_log,pubs2,syslogs
將pubs2庫的事務日志syslogs捆綁至緩沖區pubs_log.
>;sp_bindcachepubs_cache,pubs2,au_pix,"textonly"
表的text和image列存儲在一個單獨的數據結構中,若將此表捆綁至緩沖區,需加"text"參數.
1>;sp_dboptionpubs3,single,true置pubs3庫為單用戶狀態
2>;usepubs3
3>;checkpoiot
4>;usemaster
5>;go準備進行數據庫的捆綁，注意，捆綁數據庫須在master庫中進行。
>;sp_bindcachetempdb_cache,tempdb
將數據庫tempdb捆綁至緩沖區tempdb_cache。注意，被捆綁到日志緩沖區上的對象只能是syslogs表?？赏ㄟ^sp_helpcache過程查詢所有或指定緩沖區及其中被捆綁對象的捆綁信息.如下例示,其中status列報告緩沖區的捆綁是("V")否("I")有效.若數據庫或對象被捆綁至緩沖池而該緩沖區已被刪除,則捆綁信息仍保留在系統表中但緩沖區捆綁被標記無效,所有無效的捆綁對象均使用默認的數據庫緩沖區.若隨后用與被刪除的緩沖區相同的名字創建了另一個緩沖區,則當該緩沖區通過重啟SQLSERVER而被激活時,上述捆綁又將變為有效。
刪除緩沖區捆綁可用兩個過程:sp_unbindcache用于刪除緩沖區中對一個實體的捆綁; sp_unbindcache_all則用于刪除緩沖區中對所有對象的捆綁,但若捆綁至緩沖區的數據庫超過8個,或數據庫的對象超過8個,就不能使用p_unbindcache_all,此情況下,必須使用sp_unbindcache刪除單個的數據庫或對象,使捆綁的數據庫不超過8個.此外,當刪除緩沖區對一個對象的捆綁時,內存中所有當前的頁都被從緩沖區中清除.
>;sp_unbindcachepubs2
刪除對數據庫pubs2的捆綁
>;sp_unbindcachepubs2,titles
刪除對pubs2庫中對titles表的捆綁
>;sp_unbindcachepubs2,titles,titleidind
鏟除對pubs2庫中titles表上titleidind索引的捆綁.
>;改變命名數據緩沖區大小
可通過sp_cacheconfig指定一個新的空間以增加或減少指定緩沖區的大小,所有新增空間都將從缺省數據緩沖區defaultdatacache內的2K緩沖池中被加到指定緩沖區內的2K頁緩沖池中,所有減少的空間亦從指定緩沖區內的2K頁緩沖池中被釋放到缺省數據庫緩沖區defaultdatacache內的2K緩沖池中。
|
>;sp_cacheconfigpubs_cache |
返回pubs_log緩沖區報告:
|
再運行以指令sp_cacheconfigpubs_log,"6M"后重啟,則改變為:
|
欲徹底刪除一個數據緩沖區,將其大小復位為0可使用sp_cacheconfigpubs_log,"0". 則將緩沖區狀態變為"pend/del",重啟SQLSERVER后此改變生效.在執行該操作前,緩沖區需保持激活狀態,捆綁至緩沖區的所有對象仍使用該緩沖區執行I/O操作.若有對象捆綁于待刪除的數據緩沖區,則SQLSERVER重啟后,緩沖區的捆綁被標記為無效,所有無效緩沖區捆綁的對象都使用默認數據緩沖區.當捆綁被標記為無效時,警告信息將被輸入錯誤日志.注意,默認數據緩沖區不能刪除。
(
