 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / March 2008SQL Server Database Creation
I was reading about the improvements in SQL 2005 database creation, specifically how this is now much quicker. A book I am reading sites and example of a 1 GB database being created in 1 second in SQL 2005 and ~38 seconds in SQL 2000. It states: "The reduction in creation time is attributed to a change in the way that the database files are initialized. The initialization of the file with binary zeroes is now deferred until the file is access via SQL queries. This results in much faster database creation and expansion. ... This new feature will make a lot of folks who create and support large databases very happy." So does this mean that there will be a slow down when a user first writes a T-SQL statement against that database, as it will be "initialized" at that time? Do the database files then expand at the OS level at that time? Thank-you, Rubens That feature is available in SQL 2005 Enterprise Edition (and Developer Edition). Query performance doesn't rely on this feature. Rather, you may be thinking of the effect of data cache. For a freshly-started SQL Server, not data are in cache. A query will then have to populate the cache by first reading from disk. The second invocation of the same query will get its data from cache and will be noticeably quicker.  SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau I was reading about the improvements in SQL 2005 database creation, specifically how this is now much quicker. A book I am reading sites and example of a 1 GB database being created in 1 second in SQL 2005 and ~38 seconds in SQL 2000. It states: "The reduction in creation time is attributed to a change in the way that the database files are initialized. The initialization of the file with binary zeroes is now deferred until the file is access via SQL queries. This results in much faster database creation and expansion. ... This new feature will make a lot of folks who create and support large databases very happy." So does this mean that there will be a slow down when a user first writes a T-SQL statement against that database, as it will be "initialized" at that time? Do the database files then expand at the OS level at that time? Thank-you, Rubens Ah ok, that makes sense. But even with the Enterprise or Developer Editions, the data has to expand on disk at some point, right? Wouldn't there be a slow-down then? If that's the case, when does that actually happen? Would you be able to e-mail me at rubensrose@hotmail.com? I have an interesting little story to tell you not related to this post. Thanks again, Rubens > That feature is available in SQL 2005 Enterprise Edition (and Developer > Edition). Query performance doesn't rely on this feature. Rather, you [quoted text clipped - 25 lines] > Thank-you, > Rubens The file gets created, but it doesn't zero out the pages at that time. If you check the file size, it is the size you asked for at creation. SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau Ah ok, that makes sense. But even with the Enterprise or Developer Editions, the data has to expand on disk at some point, right? Wouldn't there be a slow-down then? If that's the case, when does that actually happen? Would you be able to e-mail me at rubensrose@hotmail.com? I have an interesting little story to tell you not related to this post. Thanks again, Rubens > That feature is available in SQL 2005 Enterprise Edition (and Developer > Edition). Query performance doesn't rely on this feature. Rather, you [quoted text clipped - 25 lines] > Thank-you, > Rubens Ok great, thank-you Tom and Linchi. Tom, what I wanted to mention do you was I recognized your company name. My office is at Matheson and Explorer in case that sounds familiar! :-) Rubens > The file gets created, but it doesn't zero out the pages at that time. If > you check the file size, it is the size you asked for at creation. [quoted text clipped - 41 lines] >> Thank-you, >> Rubens That's not far from the airport. I've driven through there a few times when traffic was so bad, I had to get off of the 401. SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau Ok great, thank-you Tom and Linchi. Tom, what I wanted to mention do you was I recognized your company name. My office is at Matheson and Explorer in case that sounds familiar! :-) Rubens > The file gets created, but it doesn't zero out the pages at that time. If > you check the file size, it is the size you asked for at creation. [quoted text clipped - 21 lines] >> get >> its data from cache and will be noticeably quicker. Tom, > That feature is available in SQL 2005 Enterprise Edition (and Developer > Edition). Hmm, that surprises me. I did a few BOL searches but didn't find anything that say that IFI requires EE/Dev. You recall where you got this from? SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi > That feature is available in SQL 2005 Enterprise Edition (and Developer > Edition). Query performance doesn't rely on this feature. Rather, you may [quoted text clipped - 21 lines] > Thank-you, > Rubens Tibor, Check out: http://download.microsoft.com/download/A/C/D/ACD8E043-D69B-4F09-BC9E-4168B65AAA7 1/EnterpriseEditionFeatures.doc SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau Tom, > That feature is available in SQL 2005 Enterprise Edition (and Developer > Edition). Hmm, that surprises me. I did a few BOL searches but didn't find anything that say that IFI requires EE/Dev. You recall where you got this from? SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi > That feature is available in SQL 2005 Enterprise Edition (and Developer > Edition). Query performance doesn't rely on this feature. Rather, you may [quoted text clipped - 21 lines] > Thank-you, > Rubens Interesting... SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi > Tibor, > [quoted text clipped - 40 lines] >> Thank-you, >> Rubens In case anyone is listening: After some conversation in the MVP group, we found that Instant File Initialization *is* available in all editions (data files only). The WP that Tom referred to is incorrect and there is a more recent version of the white paper available: http://www.microsoft.com/sql/techinfo/whitepapers/empowerenterprise.mspx SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi > Interesting... > [quoted text clipped - 42 lines] >>> Thank-you, >>> Rubens Thank-you for the follow-up on this Tibor. Rubens > In case anyone is listening: > [quoted text clipped - 60 lines] >>>> Thank-you, >>>> Rubens > "The reduction in creation time is attributed to a change in the way that the > database files are initialized. Just want to note that this doesn't apply to all the database files. Rather, it applies to data files only. Linchi > I was reading about the improvements in SQL 2005 database creation, specifically how this is now much quicker. A book I am reading sites and example of a 1 GB database being created in 1 second in SQL 2005 and ~38 seconds in SQL 2000. It states: > [quoted text clipped - 4 lines] > Thank-you, > Rubens |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.