Hello,
i am seeing this Grant Perform volume maintenance tasks" privilege granted to the SQL Service check box, but dont know, can anyone explain it to me please? the purpose, benefits of it etc.? thanks in advance.
May 15, 2020 at 11:30 pm
The issue was that before, when you needed to create, or extend a data file in SQL Server, it would start a process called zeroing that will start writing zeroes to create or extend the datafile to a certain extend to allocate the space in the drive, i.e: if you have a datafile of size 1gb and you want to expand it to 10gb sql server will start zeroing that file to grab that space from disk to use later.
What was the issue? well when you extended or created large data files it would take quite a while and that could affect your performance, if you are in production and you need more space for a data file your transactions would have to wait for that process to end or it would affect the end user inside the SQL Server, it could become some sort of bottle neck until the process ends.
with performance volume maintenance tasks and using instant file initialization it will no longer start zeroing the data files hence you won't have to wait or your transactions won't have to wait until the operation was done.
it's basically a different approach to allocate the space from the disk for your data file, which will make your restore operations, extend and add data files operations faster.
Read the documentation i added earlier for more details.
May 18, 2020 at 5:56 pm
shoot really sorry, i hit report by accident and meant to hit Reply...
It's cool, here it is again in case you need it:
The issue was that before, when you needed to create, or extend a data file in SQL Server, it would start a process called zeroing that will start writing zeroes to create or extend the datafile to a certain extend to allocate the space in the drive, i.e: if you have a datafile of size 1gb and you want to expand it to 10gb sql server will start zeroing that file to grab that space from disk to use later.
What was the issue? well when you extended or created large data files it would take quite a while and that could affect your performance, if you are in production and you need more space for a data file your transactions would have to wait for that process to end or it would affect the end user inside the SQL Server, it could become some sort of bottle neck until the process ends.
with performance volume maintenance tasks and using instant file initialization it will no longer start zeroing the data files hence you won't have to wait or your transactions won't have to wait until the operation was done.
it's basically a different approach to allocate the space from the disk for your data file, which will make your restore operations, extend and add data files operations faster.
Database File Initialization
Read the documentation i added earlier for more details.
May 18, 2020 at 6:53 pm
big thank you again sir 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply