提升 SQL SERVER 執行效率的小技巧：為 SQL 快取暖身

SQL Server 的效能調校是一門很大的學問，先不論 T-SQL 寫的好與壞如何衝擊效能，我們先假設 T-SQL 已經全部都調到最佳狀態（或是根本無法調整），在這種情境下如何再提升執行效能呢，答案還是非常多，例如：調整磁碟效能、加記憶體、….等，但我今天想特別強調的是快取 (Caching)。今天我來分享一個非常實用且簡單的伎倆，讓你的應用程式一瞬間即可加快查詢速度。

SQL Server 使用三種不同的檔案儲存你的資料：

MDF ( 主要資料檔 )：包括表格、索引、以及資料
NDF ( 次要資料檔 )：跟 MDF 一樣，只是放在不同的檔案群組而已
LDF ( 交易記錄檔 )：儲存所有資料檔的歷史變更紀錄

SQL Server 寫入資料時 (INSERT, UPDATE, DELETE)，至少會先確保資料有寫入交易記錄檔才會進行下一步動作，而寫入交易記錄檔這一段幾乎是無法最佳化的，考驗的就是你的磁碟 IO 的效能，由於交易記錄檔的寫入幾乎是「循序的」，所以磁碟 IO 的效能數據 (IOps) 要看的是 循序寫入(Sequential WRITE) 的速度，所以一般都建議使用獨立的硬碟來放置交易記錄檔，以確保資料寫入的效能。

SQL Server 讀取資料時 (SELECT)，就完全依賴 資料檔 (MDF, NDF) 的讀取速度，由於資料寫入資料檔時通常不是循序的，所以資料通常非常亂且透過內部索引指標指來指去的，所以 SQL Server 在讀取資料時幾乎都是「隨機存取」，所以磁碟 IO 的效能數據 (IOps) 要看的是 隨機讀取 (Random READ) 的速度，數據越高就代表讀取效能就越好。

由於 SQL Server 讀取資料檔的效能較差，所以會盡可能的使用記憶體快取所有從資料檔讀出來的資料，以加速資料查詢的速度，一般來說 SQL Server 伺服器的記憶體的確是多多益善 (但 SQL Express 有記憶體使用限制)，而且就單純的資料查詢來講，所有已經被載入到記憶體中的資料，SQL Server 都不會再去資料檔抓取資料，此時的 SQL Server 讀取速度最快，所以我們就打算用這個特性來優化資料庫的查詢效能。

所以當 SQL Server 主機重開機或是網站剛啟動且資料庫尚未被讀取任何資料時，此時的 SQL Server 資料庫的查詢效能最差，這個時候我們就可以透過 預儲程序(Stored Prodedure) 搭配 SELECT * 將所有你想要預先快取的資料表讀取並載入 SQL 記憶體快取 中。

當你的資料庫不太大且記憶體大到用不完的情況下，很適合執行以下 T-SQL 將特定資料庫中的所有資料表快取：

DECLARE @obj sysname , @sch sysname
DECLARE @owner varchar(100)
DECLARE @sql varchar(1000) 

DECLARE tblcur INSENSITIVE CURSOR FOR
    SELECT schema_name(uid) as schema_name, object_name(id) as obj_name 
    FROM sysobjects o WHERE OBJECTPROPERTY(o.id, N'IsUserTable') = 1 

OPEN tblcur 

WHILE 1 = 1
BEGIN
    FETCH tblcur INTO @sch, @obj
    IF @@fetch_status <0 BREAK 

    SET @sql = 'SELECT * FROM [' + @sch + '].[' + @obj + ']'
    EXEC(@sql)
END 

DEALLOCATE tblcur

如果你的 SQL Server 資料非常多，不建議一開始就將所有資料快取，因為如果記憶體不夠大，後來讀取的資料會把之前已經快取過的清除，如果快取到不常用的歷史資料表，反而踢掉常用的資料表時，這樣就沒有什麼效果了，所以建議的方式是將最常用的資料表或篩選特定條件下的資料快取起來就好。

如果你想查詢每一個資料表到底用了多少空間，可以利用 sp_spaceused 查詢各表格所使用的磁碟空間：

DECLARE @obj sysname , @sch sysname
DECLARE @owner varchar(100)
DECLARE @sql varchar(1000) 

DECLARE tblcur INSENSITIVE CURSOR FOR
    SELECT schema_name(uid) as schema_name, object_name(id) as obj_name 
    FROM sysobjects o WHERE OBJECTPROPERTY(o.id, N'IsUserTable') = 1 

OPEN tblcur 

WHILE 1 = 1
BEGIN
    FETCH tblcur INTO @sch, @obj
    IF @@fetch_status <0 BREAK 

    SET @sql = 'EXEC sp_spaceused N''[' + @sch + '].[' + @obj + ']'''
    EXEC(@sql)
END 

DEALLOCATE tblcur

利用 sp_spaceused 查詢各表格所使用的磁碟空間