監控TempDB01

在TempDB主要儲存的對象有三：

1.      User Obejcts

2.      Internal Objects

3.      Version store Objects

當有空間容量異常時，要如何知道是哪一部分造成的是非常重要的，檢查這三種儲存對象儲存容量，可以讓我們更精確地知道TempDB容量變化。

檢查程式碼如下：

--下列出三種儲存對象儲存容量

SELECT

SUM (user_object_reserved_page_count)*(8.0/1024.0) as UserObjects_MB,

SUM (internal_object_reserved_page_count)*(8.0/1024.0)  as InternalObjects_MB,

SUM (version_store_reserved_page_count)*(8.0/1024.0)   as VersionStore_MB,

SUM (unallocated_extent_page_count)*(8.0/1024.0)  as FreeSpace_MB,

SUM (mixed_extent_page_count)*(8.0/1024.0)  as MixedExtent_MB

FROM sys.dm_db_file_space_usage

執行結果：

透過上述的程式碼可以幫助我們判斷TempDB造成容量異常的部份，接著就是要判斷容量異常的原因。

TempDB最佳化03

為了減少TempDB的IO，容量設定是一個關鍵因素，設定重點為：

1.      檔案初始大小：如果TempDB檔案一開始設定2MB，如果同時間許多使用者存取資料庫時會產生10MB暫存的資料，此時檔案因為太小就會自動成長導致產生IO降低效能。

2.      檔案成長量：如果TempDB檔案一開始設定2MB，假設檔案會增加到10MB，此時如果成長量設定一次成長1MB，與設定一次增加2MB比起來IO多了許多。

為了掌握正確的設定適當的初始大小與成長量資訊，必須透過監控TempDB的空間容量變化。至於如何監控TempDB檔案請參考【Day2_監控TempDB的空間容量】。

最後TempDB有二個(含)資料檔案以上時，每個檔案的大小與成長量都要設定一致，原因是SQL SERVER會依資料檔案大小按比例分散IO。

以下是實作設定檔案容量與成長量：

--查詢TempDB檔案的大小

SELECT    

name AS FileName, size*1.0/128 AS FileSizeinMB,

CASE max_size  WHEN 0 THEN 'Autogrowth is off.'

         WHEN -1 THEN 'Autogrowth is on.'

               ELSE 'Log file will grow to a maximum size of 2 TB.'        END Autogrowth ,

growth AS GrowthValue,

CASE WHEN growth = 0 THEN 'Size is fixed and will not grow.'

     WHEN growth > 0 AND is_percent_growth = 0  THEN 'Growth value is in 8-KB pages.'

        ELSE 'Growth value is a percentage.' END GrowthIncrement

FROM tempdb.sys.database_files;

查詢結果：

--修改檔案容量與成長量

USE [master]

GO

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 102400KB, FILEGROWTH = 10%)

GO

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev2', SIZE = 102400KB, FILEGROWTH = 10%)

GO

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', FILEGROWTH = 10%)

GO

查詢結果：

參考網址

TempDB最佳化02

為了避免TempDB與其他DB有IO的競爭，可以把它移到獨立的硬碟。

此外將TempDB放置在RAID 0磁碟陣列上可以大幅增加效能，為什麼TempDB檔案放在RAID 0磁碟陣列呢？

1.      RAID 0效能最好：可處理較多的IO。

2.      不必考慮TempDB資料的遺失：啟動SQL SERVER就會自動建立TempDB。

實作移動TempDB：

USE tempdb

GO

SP_HELPFILE;

--2.執行下列語法

USE MASTER

GO

ALTER DATABASE tempdb MODIFY FILE (name = tempdev,FILENAME = 'C:\TempDB\tempdb.mdf')

GO

ALTER DATABASE tempdb MODIFY FILE (name = templog,FILENAME = 'C:\TempDB\templog.ldf')

GO

ALTER DATABASE tempdb MODIFY FILE (name = tempdev2,FILENAME = 'C:\TempDB\tempdb2.ndf')

GO

執行結果

--3.重新啟動SQL SERVER

--4.查看結果

USE tempdb

GO

SP_HELPFILE;

執行結果

TempDB最佳化01

TempDB的檔案對於資料庫的查詢效能有很大的影響，適當增加檔案可以分散IO，例如當TempDB有兩個檔案，IO產生時會分散至這兩個檔案，效能好過於一個檔案單獨承擔IO。

TempDB的檔案的新增需視CPU數目而定，一顆CPU可以建立一個檔案，二顆CPU可以建立二個檔案以此類推。若CPU是雙核心可被視為兩個CPU。

在SQL SERVER 2005以上的版本可以使用sys.dm_os_sys_info確認CPU的個數。

--判定cpu個數

select     cpu_count as 'cpu個數'

from     master.sys.dm_os_sys_info

GO

執行結果：

從執行結果CPU的個數是2，所以需要增加一個檔案。

--增加一個檔案

USE [master]

GO

ALTER DATABASE [tempdb]

ADD FILE ( NAME = N'tempdev2', FILENAME = N'D:\TempDB\tempdb2.ndf' , SIZE = 20480KB , FILEGROWTH = 1024KB )

GO

查看檔案

--查看檔案

use tempdb

GO

sp_helpfile

監控TempDB的空間容量

(1)   為什麼要監控TempDB的空間容量:

之前的文章TempDB特性與效能簡介文章架構有提到設定適當的TempDB檔案成長量可以增加資料庫的效能。

原因是當TempDB空間不夠時， TempDB的空間容量會增加，空間容量增加會有產生IO而造成低落的效能。

當TempDB檔案成長量設定太小會造成檔案在變大的頻率上升造成產生IO使資料庫效能下降，而設定太大會浪費空間，所以設定TempDB適當的檔案成長量可以使TempDB的空間容量增大的頻率變小。

要精確地設定TempDB空間容量成長量，就要取得空間容量成長空間大小與頻率，而監控TempDB的空間容量可以達成這個目的。

(2)   如何監控TempDB的空間容量

使用sys.dm_db_file_space_usage這個dynamic management view(簡稱dmv)可以取得TempDB的檔案空間資訊，包含TempDB總空間容量、使用與未使用的空間容量。

(3)   實作監控TempDB空間容量

SELECT

SUM(    unallocated_extent_page_count

        + user_object_reserved_page_count

        + internal_object_reserved_page_count

        + mixed_extent_page_count

        + version_store_reserved_page_count) * (8.0/1024.0) AS [TempDB空間容量MB]

,SUM(   user_object_reserved_page_count

        + internal_object_reserved_page_count

        + mixed_extent_page_count

        + version_store_reserved_page_count) * (8.0/1024.0) AS [已使用TempDB空間容量MB]        

, SUM(unallocated_extent_page_count * (8.0/1024.0))       AS [未使用TempDB空間容量MB]

FROM sys.dm_db_file_space_usage

執行結果：

將上述的程式碼使用sql job每天晚上紀錄一次就可以知道TempDB總空間容量、使用與未使用的空間容量的變化並進一步TempDB空間容量成長量。


參考網址:Total, free, and used space in tempdb in SQL Server

Day01TempDB特性與效能簡介

(1)          TempDB的簡介：是SQL SERVER的系統資料庫之一，用來儲存暫存的物件或資料。

(2)          一些TempDB重要的特性：

1.    重新啟動SQL SERVER時，系統會重新建立TempDB，所以TempDB不需要備份和還原。

2.    TempDB不能增加檔案群組。

3.    所有連線到SQL SERVER的使用者都可以使用TEMPDB。

4.    TempDB的復原模式為簡單模式。

(3)          TempDB的儲存對象

物件	物件內容	備註
User objects	Local temporary tables and indexes Global temporary tables and indexes Table variables User-defined tables and indexes	可以透過 sys.dm_db_session_space_usage查看Table  variables儲存所在，驗證Table  variables確實是存在於TempDB中的。
Internal objects	Sort results Hash joins XML variables Work tables for cursors Temporary large object (LOB) storage	可以透過 sys.dm_db_session_space_usage查看Internal objects在TempDB中已配置和取消配置的頁數。
Version store objects	Snapshot isolation Triggers Multiple active result sets (MARS) Online index build	可以透過sys.dm_tran_version_store查看Version store objects的內容

(4)          TempDB對資料庫效能的關聯：因為許多暫存物件(如Local temporary tables and indexes)都會使用到TempDB，所以TempDB設計的好壞會大大影響資料庫系統的效能，而要最佳化TempDB必須要朝下列幾點著手。

TempDB效能最佳化要點	說明
TempDB復原模式	確認tempdb復原模式設定為簡單模式(SIMPLE)
TempDB 檔案成長模式	設定為自動成長
TempDB檔案成長量	TempDB檔案成長增量設成合理的大小
TempDB檔案大小初始大小	如果TempDB檔案初始太小設大一點，減少檔案增大時所費的IO
TempDB檔案個數	視機器上的CPU數而定
TempDB每一個資料檔大小相同	可等比例寫入TempDB的檔案
TempDB放在快速I/O的儲存體上	建議放在RAID 0的儲存體上
與使用者資料庫分開	避免TempDB影響到使用者資料庫

(5)          TempDB的監控：監控TempDB的成長量與IO，可以有效的預防因TempDB引起的效能低落的問題。