SQL Server Instant File Initialization (PQW)

Question

Post reply

SQL Server Instant File Initialization (PQW)

John.Sansom

SSCertifiable

Points: 7335
More actions
April 2, 2012 at 12:03 am

#266481

Comments posted to this topic are about the item SQL Server Instant File Initialization (PQW)
John Sansom (@sqlBrit) | www.johnsansom.com

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

great summary, John !

It's good you mention "DB restores" as well, as it is a "non trivial" usage area in many cases. :exclamationmark:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

John.Sansom SSCertifiable Points: 7335 More actions · Answer 2

Thanks Johan!

John Sansom (@sqlBrit) | www.johnsansom.com

Nitya Say Hey Kid Points: 699 More actions · Answer 3

Awesome post. Been using the feature ever since I learnt from Kim Tripp (> 4years)

A little bit of explanation around why log files are not zero initialized would have made it even more complete.

Thx

NCS

John.Sansom SSCertifiable Points: 7335 More actions · Answer 4

Thanks NCS.

That's a good suggestion.

John Sansom (@sqlBrit) | www.johnsansom.com

gmmastros SSC Enthusiast Points: 188 More actions · Answer 5

I wrote SQLCop a couple years ago. SQLCop can also detect whether Instant File Initialization is enabled.

Download SQLCop from http://sqlcop.lessthandot.com

Run SQLCop

Log in to any database on the server.

Expand "Configuration"

Click on "Instant File Initialization"

SQLCop will also show you a blog written by Brad M. McGehee that shows you how to enable Instant File Initialization.

George Mastros
Orbit Software, Inc.

M&M SSC-Insane Points: 21700 More actions · Answer 6

Good job John in explaining this concept. Looking forward for your future PQWs. 🙂

M&M

John.Sansom SSCertifiable Points: 7335 More actions · Answer 7

Thanks Mohammed! You can never have too many PQWs 🙂

John Sansom (@sqlBrit) | www.johnsansom.com

Edward Sun SSC Enthusiast Points: 150 More actions · Answer 8

Thanks much for this article. It implies that it's more important to pre-size the tlog files than the data files because the tlog files cannot take the advantage of Instant File Intialization.

heymiky SSCarpal Tunnel Points: 4875 More actions · Answer 9

Great artical John, i'm probably not alone in thinking why haven't I done that already 🙂 (implementing it in DEV as I type), looking forward to your next post.

Mike

John.Sansom SSCertifiable Points: 7335 More actions · Answer 10

Thanks Mike, glad you found it useful. Always test those changes in DEV first 😉

John Sansom (@sqlBrit) | www.johnsansom.com

heb1014 Hall of Fame Points: 3790 More actions · Answer 11

I just created a test database on my Dev machine and I don't see anything in the log about zeroing the data file or log file. What am I missing?

Thanks!

Nitya Say Hey Kid Points: 699 More actions · Answer 12

Try Creating a database with a large filesize. e.g. 8gb or more and see how long it takes with instant initialization and with out

M&M SSC-Insane Points: 21700 More actions · Answer 13

heb1014 (4/3/2012)
I just created a test database on my Dev machine and I don't see anything in the log about zeroing the data file or log file. What am I missing?
Thanks!

Did you enable those traces like how John had described.

M&M

heb1014 Hall of Fame Points: 3790 More actions · Answer 14

Thanks Mohammed! I must have read too fast. Both flags have to be enabled to see the zeroing in the log and data files. Then, the service account must be added to the volume maintenance policy (followed by a restart).

Works now!