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 :
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
Last activity: Nov 10, 2021, 12:24 AM