How many temp database files ?

Đọc các bài viết về quản lý database tempdb thì nên tạo bao nhiêu data file trước giờ nhưng không chắc là việc thêm có hiệu quả hay không và lời khuyên thông thường là cứ mỗi logical CPUs thì cho 1 tempdb data file, trong khi Server SQL cho ERP đang là 2 x 8 cores mà lại có enable Hyperthread, vị chi là 32 logical CPUs chẳng lẽ lại add 32 data files cho tempdb. Thôi thì cứ add 8 files và theo dõi.

Mượn hình từ https://www.brentozar.com/blitz/tempdb-data-files/ chắc sẽ dễ hình dung.

Trong link bên dưới cũng chỉ cách để xem liệu có đúng là thiếu data files cho tempdb không.

Source : https://www.brentozar.com/blitz/tempdb-data-files/

https://www.brentozar.com/sql/tempdb-performance-and-configuration/

1 số SQL để add data files cho tempdb :

/* Re-sizing TempDB to 5 GB */

USE [master];
GO
alter database tempdb modify file (name=’tempdev’, size = 5GB);
GO

/* Adding three additional files */

USE [master];
GO
ALTER DATABASE [tempdb] ADD FILE (NAME = N’tempdev2′, FILENAME = N’T:\MSSQL\DATA\tempdev2.ndf’ , SIZE = 5GB , FILEGROWTH = 100MB);
ALTER DATABASE [tempdb] ADD FILE (NAME = N’tempdev3′, FILENAME = N’T:\MSSQL\DATA\tempdev3.ndf’ , SIZE = 5GB , FILEGROWTH = 100MB);
ALTER DATABASE [tempdb] ADD FILE (NAME = N’tempdev4′, FILENAME = N’T:\MSSQL\DATA\tempdev4.ndf’ , SIZE = 5GB , FILEGROWTH = 100MB);
GO

Find number of logical processors

SELECT cpu_count AS logicalCPUs FROM sys.dm_os_sys_info

Script to Check TempDB Speed

This query hits the dynamic management function (DMF) sys.dm_io_virtual_file_stats for all of the TempDB data files and lists out how fast they’re responding to write (and read) requests:

SELECT files.physical_name, files.name,
stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms,
stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_ms
FROM sys.dm_io_virtual_file_stats(2, NULL) as stats
INNER JOIN master.sys.master_files AS files
ON stats.database_id = files.database_id
AND stats.file_id = files.file_id
WHERE files.type_desc = ‘ROWS’

• Are writes being evenly distributed between data files?
• Are writes finishing in 20ms or less?

If the answer is no to either of those questions, we’ve got some performance tuning work to do.

More links & sources for above SQL commands :

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2009/01/11/tempdb-monitoring-and-troubleshooting-allocation-bottleneck/

https://sqlserverzest.com/2014/06/24/sql-server-script-to-configure-tempdb-files-per-number-of-logical-processors/

Correctly adding data files to tempdb

Script to Create A TempDB File Per Processor