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.

tempdb-sgam1

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

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/

SQL Server – Script to Configure TempDB Files per Number of Logical Processors

http://www.sqlskills.com/blogs/paul/correctly-adding-data-files-tempdb/

http://adventuresinsql.com/2010/04/script-to-create-a-tempdb-file-per-processor/

Advertisements

About nguyennp

Làm Lập trình rồi quản trị hệ thống, tư vấn/triển khai hệ thống mạng, Tư vấn triển khai phần mềm ERP và Quản lý dự án ERP và phần mềm. Yêu thích học hỏi các kiến thức mới, đặc biệt là chuyên sâu trong lĩnh vực bảo mật, quản lý hệ thống và tối ưu, tìm cách sáng tạo, tăng hiệu suất làm việc của con người lẫn phần mềm.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: