What is the TempDB database used for in SQL Server?
- User Objects
- User-defined tables and indexes
- System tables and indexes
- Global temporary tables and indexes
- Local temporary tables and indexes
- Table variables
- Tables returned in table-valued functions
- Internal Objects
- Work tables for CURSOR or SPOOL operations and temporary large object (LOB) storage.
- Work files for HASH JOIN or HASH aggregate operations.
- Intermediate sort results for operations such as creating or rebuilding indexes (if SORT_IN_TEMPDB is specified), or certain GROUP BY, ORDER BY, or UNION queries.
- Version Stores
- Row versions that are generated by data modification transactions in a database that uses snapshot or read committed using row versioning isolation levels. (Snapshot Isolation and Read-Committed Snapshot Isolation (If long running transaction exist cause tempdb growing up))
- Row versions that are generated by data modification transactions for features such as: Online Index Operations, Multiple Active Result Sets (MARS), and AFTER triggers.
tempdb檔案設定最佳化：
(1) 為tempdb交易紀錄檔設定足夠大的大小，以避免自動成長。
(2) 為每一個 CPU 建置一個 Tempdb 的資料檔案，或是以 CPU 個數的一半建置 (除以 2 的倍數)。
(3) 每個資料檔案大小必須相同。
(4) 每個資料檔案必須預留足夠的交易量空間。例如觀察平時 Tempdb 使用量約 3GB的話，建議設定總共 4GB 空間。(也就是所有tempdb資料檔加起來4GB，例如建立4個1GB資料檔)。
(5) 符合上述的配置條件，每個 CPU 可以獨立取用對應的 Tempdb 資料檔案，可以增進效能及併發性。
Optimizing tempdb Performance
Recommendations to reduce allocation contention in SQL Server tempdb database
Troubleshooting Insufficient Disk Space in tempdb
Capacity Planning for tempdb
沒有留言:
張貼留言