June 30, 2009 at 12:58 pm
Just wondered if any have opted to use this.
From the article:
"We recommend using instant file initialization for performance reasons. In-house testing for creating and growing files shows a significant improvement in performance when instant file initialization is used."
"By default, when a file is created or grows larger, the pages in that file are initialized by writing zeros before the file gets used. This overwrites any existing data that remains on the disk. Instant initialization is only used for data files (not log files) and is enabled when the account running SQL Server has the Windows SE MANAGE VOLUME NAME privilege, which is available only on Microsoft Windows XP, Windows Server 2003 or later versions. This occurs in five scenarios:
*
During file creation
o
CREATE DATABASE, including tempdb creation at server startup.
o
RESTORE DATABASE
*
During file modification
o
ALTER DATABASE...MODIFY FILE.
o
Modifications that result in autogrow activity.
The zeroing process can affect performance, especially during modifications that trigger an autogrow. SQL Server 2005 offers the instant file initialization feature. This skips zeroing out of data pages. Instant file initialization reduces time during the creation of very large databases and tempdb.
The previous data on newly allocated space is never zeroed. The previous data gets overwritten with new data when the data page is actually allocated. Avoiding zeroing out applies to any application that is running with the Windows SE_MANAGE_VOLUME_NAME privilege, and is making the call to the SetFileValidData Windows API.
Note: By default, if SQL Server is running under the Network Service account, this permission is default OFF.
For more information, see “Database File Initialization” in SQL Server Books Online. "
June 30, 2009 at 1:26 pm
Yes. Used correctly (which is pretty straightforward), it works quite well.
Did you have a specific question about it? Or just wanted to know if it's in use?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 30, 2009 at 1:35 pm
Generally wanted to know how many had turned it on. But we have some major database/file changes coming up ( new file groups / volumes etc ) which will move a 400GB database around quite a bit, so it might be useful when that is rolled out to the production system ( sql cluster with Netapp storage ).
June 30, 2009 at 1:50 pm
In my opinion there's only one case where you may not want this turned on.
If the same drive that's used for the SQL data files also contains (or did contain) highly confidential data that the SQL admins are not permitted to see, then the use of this option may be considered a security threat. The reason is that without initialising the files (writing 0's across the entire file), the previous data stored at that location on the disk may be visible using DBCC Page.
This is not a common situation, but if you do have such concerns then instant initialisation should perhaps not be used.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2009 at 1:59 pm
Indianrock (6/30/2009)
( sql cluster with Netapp storage ).
Have you done IO stress tests on that?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2009 at 2:11 pm
Netapp. I've heard the term at work and sat in on a seminar from one of their system engineers. I believe Benchmark Factory is going to be used to do load testing, etc.
I saw articles on this instant file initialization and called a meeting so our decision-makers etc can consider it.
June 30, 2009 at 2:21 pm
http://chucksblog.typepad.com/chucks_blog/2006/12/benchmarketing_.html
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2009 at 2:50 pm
Thanks for the link Gail, I passed it on to our Network guys and Senior DBA. I had to note that the author was a marketing officer at EMC.
June 30, 2009 at 2:55 pm
Yes he is and he doesn't claim that his tests are independent. There's a doc with the reproducible tests if you wish to check for yourself.
http://chucksblog.typepad.com/ntap/NetAppProveItKit1006.doc
Other thing to note is that the post is 2 years old and may no longer be valid, but it's worth investigating at least?
Note that I've used neither NetApp nor EMC sans before. By coincidence, there's a discussion on NetApp sans on one of the MS private newsgroups at the moment.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2009 at 6:43 pm
only time i have this problem is when restoring a database through netbackup. a lot of times the job will fail while the files are initializing. i just restart it and it works. no big deal and probably won't bother turning this on
June 30, 2009 at 7:48 pm
SQL Noob (6/30/2009)
only time i have this problem is when restoring a database through netbackup. a lot of times the job will fail while the files are initializing. i just restart it and it works. no big deal and probably won't bother turning this on
Life is way too short to sit around waiting for data files to be initialized with zeroes up front. Pages are zeroed out when actually written to the file anyway. It's so simple to turn on, why would you tolerate the failed jobs and backup delays unnecessarily? Seems quite odd to me.
As Gail mentioned, the only scenario where I would not turn IFI on immediately is one where there is a need for super-extreme security.
On a separate note, on the various EMC SANs I have used (mostly Clariion and DSM-IV) instant file initialization was even more important, since SANs are great for a lot of things, but their lack of raw performance can be a shock for some.
Paul
June 30, 2009 at 10:23 pm
I agree with the others re IFI - if ultra-high security isn't a requirement I advise my clients to enable it.
I have had some clients on NetApp SAN's for SQL Server usage and they were disappointed. This was a few years ago, but back then a NetApp SAN seemed to be just a NAS with a few tweaks and a shiny lable. I don't know if they have upped their configuration and capabilities, but I hope they have if you require significant performance and reliability. I will note that I have also had more than a few clients on EMC, DELL, EqualLogic, etc SAN's that have had performance problems as well but I know that those issues were because they (the clients) didn't know jack didly about sizing, configuration, etc. That wasn't the case with the NetApps.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 1, 2009 at 1:00 am
SQL Noob (6/30/2009)
only time i have this problem is when restoring a database through netbackup. a lot of times the job will fail while the files are initializing. i just restart it and it works. no big deal and probably won't bother turning this on
What's the biggest DB that you work with?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 1, 2009 at 5:55 am
Our largest database is currently about 400GB but is expected to become a few terabytes.
July 1, 2009 at 7:20 am
Honestly, if you're storing ultra-secure data in the database or other files, IFI is the least of your worries. You need to have something set up to re-initialize hard drive space the moment it's not being used.
It's WAY too easy to read data off of previously used hard drive space. Lots of tools for that kind of thing.
If you're waiting for the space to be re-used before you re-initialize it, and it matters, then you're waiting too long.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply