sql server tempdb更改資料庫檔案位置,改善資料庫的效能

sql server的tempdb設計如果設計不好會影響到資料庫的運行效能，預設的起始大小是8MB如果資料庫的訪問量小，不會有什麼影響，如果大量的的訪問做join時，就會大大的影響SELECT的效能，把TEMPDB一開始啟動的大小設大一點，可以減小TEMPDB的擴充次數。大小容量可以參考平時的容量來設定。
另一個是設定檔案位置到少使用到的磁碟機，這樣也可以增加效能，如果有大一點的記憶體空間可以做成RAMDISK，把TEMPDB設到這裡來是最好的做法。

TEMPDB檔案數增加的數目，最大化磁碟頻寬，並減少配置結構中的爭用的 tempdb 中的資料檔案。一般而言，如果的邏輯處理器數目小於 8 或等於 8，請使用相同數目的資料檔案做為邏輯處理器。如果邏輯處理器數目大於 8，使用 8 為資料檔案，然後如果爭用持續，請增加資料檔案數目 (最多的邏輯處理器數目) 的 4 的倍數直到爭用降低至可接受的層級，或變更的工作負載/程式碼。

以下是更改TEMPDB的檔案路徑到F:\(RAMDISK的磁碟機代號)，執行完後下次重新啟動就會在新的磁碟機建立TEMPDB.MDF了。


USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'f:\tempdb.mdf');

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'f:\templog.ldf');

GO

以下SQL為查詢TEMPDB的語句，可以監控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

--查詢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;

--檢視tempdb的資料庫檔案

USE tempdb
GO
SELECT name N'邏輯名稱' , size/128.0 N'使用的磁碟空間(MB)' ,
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 N'資料實際上使用的空間(MB)',
case is_percent_growth
 when 0 then concat(growth * 8, 'KB')
 else concat(cast(growth as decimal(5, 2)), '%')
end N'檔案成長方式',
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 N'剩餘的可用空間(MB)', physical_name N'完整檔案路徑與名稱'
FROM sys.database_files;

GO