This is required so the database engine can maintain parity bits in each 512 byte disk sector. The transaction log always requires binary zeroes to be written when space is allocated to the log file. When instant file initialization is turned on, the data files are not initialized with zeros. Instant file initialization has been around since SQL Server 2005. By writing binary zeroes across the disk space, the SQL Server engine makes sure that data previously stored in the unused disk space is completely overwritten before the disk space is allocated to a database. This initialization process writes binary zeroes (“0”) across all the bits and bytes of space being allocated. By avoiding the writing of binary zeroes, there is a lower performance impact when disk space is allocated for several database operations.īy default, when SQL Server creates a database, increases the size of a database, or restores a database, it needs to initialize the disk space prior to allocating the space. Instant file initialization is a SQL Server setup option that keeps SQL Server from writing binary zeroes to data files when a database is first created, expanded, or restored. This article discusses how instant file initialization can be configured to improve the performance of allocating disk space to a database when it is created, expanded, or restored. There are lots of different aspects of securing and tuning performance for your SQL Server environment. As a database administrator, one of your jobs is to make sure each SQL Server instance you manage is set up in such a way as to meet security requirements and is configured to optimize performance.
0 Comments
Leave a Reply. |