Sample Header Ad - 728x90

Sql Server Disk I/O Troughput In Performance monitor

0 votes
2 answers
609 views
i use sql server 2019 and trace flag 1117(Grow All Files in a FileGroup Equally) is on in my DataBase. I need to consider the correct number of data files for primary file group according to my system resources. To do this, I need to check this operation with performance monitor software. But I do not know which counters I should use (for example writing on a disk per second)
First Test :
CREATE DATABASE TestIO
ON PRIMARY 
    ( NAME = N'PRIMARY1',            FILENAME = N'D:\DB\Temp\TestIO_PRIMARY1.mdf',FILEGROWTH=512GB,MAXSIZE=UNLIMITED,SIZE=2GB),
 FILEGROUP FG2 
    ( NAME = N'secondary',          FILENAME = N'D:\DB\Temp\TestIO_secondary.ndf',FILEGROWTH=512GB,MAXSIZE=UNLIMITED,SIZE=2GB)
 LOG ON 
    ( NAME = N'TestIO_log',		FILENAME = N'D:\DB\Temp\TEST_log.ldf' ,FILEGROWTH=2GB,MAXSIZE=2TB,SIZE=2GB)
GO
Seccond Test :
USE master
GO
DROP DATABASE IF EXISTS TestIO
CREATE DATABASE TestIO
ON PRIMARY 
    ( NAME = N'PRIMARY1',            FILENAME = N'D:\DB\Temp\TestIO_PRIMARY1.mdf',FILEGROWTH=512GB,MAXSIZE=UNLIMITED,SIZE=2GB),
	( NAME = N'PRIMARY2',            FILENAME = N'D:\DB\Temp\TestIO_PRIMARY2.mdf',FILEGROWTH=512GB,MAXSIZE=UNLIMITED,SIZE=2GB), 
	( NAME = N'PRIMARY3',            FILENAME = N'D:\DB\Temp\TestIO_PRIMARY3.mdf',FILEGROWTH=512GB,MAXSIZE=UNLIMITED,SIZE=2GB), 
	( NAME = N'PRIMARY4',            FILENAME = N'D:\DB\Temp\TestIO_PRIMARY4.mdf',FILEGROWTH=512GB,MAXSIZE=UNLIMITED,SIZE=2GB), 
 FILEGROUP FG2 
    ( NAME = N'secondary',          FILENAME = N'D:\DB\Temp\TestIO_secondary.ndf',FILEGROWTH=512GB,MAXSIZE=UNLIMITED,SIZE=2GB)
 LOG ON 
    ( NAME = N'TestIO_log',		FILENAME = N'D:\DB\Temp\TEST_log.ldf' ,FILEGROWTH=2GB,MAXSIZE=2TB,SIZE=2GB)
GO
Asked by AAA (21 rep)
Nov 8, 2021, 03:05 PM
Last activity: Nov 10, 2021, 12:24 AM