Brad Chen's Developer Blog: 12月 2020

2020年12月21日星期一

Scale limits

Feature	Express with Advanced Services	Express
Maximum compute capacity used by a single instance - SQL Server Database Engine¹	Limited to lesser of 1 socket or 4 cores	Limited to lesser of 1 socket or 4 cores
Maximum compute capacity used by a single instance - Analysis Services or Reporting Services	Limited to lesser of 1 socket or 4 cores	Limited to lesser of 1 socket or 4 cores
Maximum memory for buffer pool per instance of SQL Server Database Engine	1410 MB	1410 MB
Maximum memory for Columnstore segment cache per instance of SQL Server Database Engine	352 MB	352 MB
Maximum memory-optimized data size per database in SQL Server Database Engine	352 MB	352 MB
Maximum memory utilized per instance of Analysis Services	N/A	N/A
Maximum memory utilized per instance of Reporting Services	4 GB	N/A
Maximum relational database size	10 GB	10 GB

database size 10GB (the limit is on the data file, it doesn't include log files)

Editions and supported features of SQL Server 2019 (15.x)

Microsoft® SQL Server® 2019 Express

SQL Server 下載| Microsoft

What is the TempDB database used for in SQL Server?

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.

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