What is an example to understand that having multiple data files helps with latch pages in parallel?
1
vote
1
answer
118
views
> You will always benefit from multiple data files, because then SQL
> Server is able to latch multiple system pages in parallel in the
> Buffer Pool.
Link: https://www.sqlpassion.at/archive/2016/08/29/files-and-file-groups-in-sql-server/
I currently have one data file. I'm exploring the use case of adding a new data file, or increasing the size of an existing data file.
Given that my IO subsystem (Data drive) can withstand high IOPS, I understand that there is no performance improvement whether I have one or more data files. And because there is no limit to how many threads can use the data file for read/write in parallel, SQL server must allow parallel latch of pages.
So then, why does the author say that - having multiple data files gives the Server ability latch multiple system pages in parallel in the Buffer Pool.
Why would this not be possible with single data file?
Asked by variable
(3590 rep)
Jan 17, 2024, 07:07 PM
Last activity: Feb 1, 2024, 10:32 AM
Last activity: Feb 1, 2024, 10:32 AM