Wednesday, May 11, 2005

Fast file initialisation

Historically creating database files has always been a chore. Cast you mind back to SQL Server 6.5 and without vigilant monitoring (AKA a DBA doing their job correctly) you would get bitten on the backside when either the database or transaction log device filled up. There were some techniques for avoiding this but plenty of people didn't bother and just manually extended. Enter the realms of the modern day versions of SQL Server where this had been made a lot easier but the performance impact of a database expanding itself by 10% or X amount of Mb throughout a long hungry process became apparent so for critical systems you would end up doing similar things to what was done on 6.5. Now 2005.
Primary, secondary data and log files on SQL Server 2005 can be initialised almost instantly allowing for fast execution of tasks such as adding files to databases or increasing the size of an existing data file. Traditionally files were initialised to grab disk from previously deleted files. These files were initialised by first filling them with zeros, now this doesn’t happen and disk content is overwritten as new data is applied to the files.
There are a couple of caveats though. Firstly the Windows account the SQL Server service is running under must either be an administrator of the machine or have SE_MANAGE_VOLUME_NAME special privilege assigned to it. Secondly and perhaps more of an issue is that fast file initialization is only available on Windows XP or 2003 server systems. There are a lot of features in SQL Server 2005 that only become usable when on a XP or 2003 operating system but it’s likely the users who will adopt SQL Server 2005 are the ones with the latest server technologies so hopefully this shouldn’t prove so much of an issue.

No comments: