Size Matters: Keep Model Small

I recently discovered an undesirable behavior of SQL Server on a brand new SQL 2008R2SP2 cluster.  If the cluster failed over once it was fine.  If it was immediately failed back to the original node, it would hang when taking SQL Agent offline.

The cluster had two physical nodes (01P and 02P) and three (3) SQL Server instances (AP, BP & CP).  It turns out that neither the hardware or the OS were contributing factors, so there is no point in describing their configurations.

The instance that was presenting the most problems was for a new client.  In fact, when the issue was first discovered when the cluster was not even in PRODUCTION yet.  This afforded us the luxury, at least early on, of being able to fail the instance over, almost on an as needed basis.  Eventually the cluster did go into production so running tests and collecting logs involved a more rigorous process.

The databases on the instance were fairly small, by our standards.  On the order of 100GB.  The databases are not transactional.  For the most part they are load-and-report databases, the classic OLAP.

I worked with Microsoft support over a four month period.  The SQL Server support engineer, Pradeep M. M., and I had sort of a doctor-patient relationship.  He would prescribe the test, I would submit the test results to him.

Most of the tests were in the form of setting trace flags to enhance logging to the SQL error log, failing the cluster over, and providing the logs for analysis.

Over the course of the four months we did upgrade from cumulative update 3 (CU3) to CU8, and eventually CU12.

While nothing in the logs pointed directly to the root cause, it was discovered fairly early on that the startup of tempdb was taking upwards of 3 minutes to come online.  We checked to insure Instant File Initialization was correctly setup (which only applies to the data files, not the log file).  With two 40GB tempdb data files at initialization, plus an 8GB log file, we are dealing with a small (by our standards) tempdb.

This would explain why an instance would fail when taking SQL Agent offline.  Agent couldn’t go offline until the instance is fully online.  Without a tempdb, the instance is still coming up.

We checked other instances that we manage and discovered that most instances of SQL Server were able to initialize tempdb in 5 seconds or less, at service startup, even for instances where tempdb was up to 800GB.

Of course, any time we start seeing IO related issues, we always blame the SAN, right?  Nothing was discovered on the SAN side.

What about the hardware?  Could drivers be out of date?  Sure, but we have 3 instances running on 2 nodes.  Both nodes are brand new blade servers.  While the AP and CP instances work fine, regardless of the physical node they were on.  While the BP instance consistently hang, regardless of the physical node it is on.

At that point it is fair to conclude that we are not dealing with an external resource constraint, either at the SAN or physical hardware level.  Most likely, the root cause is at the instance level.

The root cause of the problem is that the MODEL database was configured to have a 1GB initial file.  The short version of the explanation is that, even though tempdb is configured with it’s own attributes (number of data files, log file, autogrowth, growth increments, etc.) at service startup it is initially created by MODEL.

Rather than re-invent the wheel, I’ll provide Pradeep’s detailed explanation:

When SQL Server starts it re-creates tempdb every time; If it has to automatically create the database it has to first set some default properties and to achieve this it uses model database as a template and then once the initial template for the tempdb is ready it goes ahead and reads the actual configuration that has been kept from the master database for the tempdb files and then tweaks the same. Now when it copies the model database as a template and carves out tempdb from it doesn’t mean that it uses a windows API call to copy the file and paste the file and then move ahead. It cannot use this because SQL server internally maintains file header ( System Pages like PFS , GAM ,SGAM ) and other integral structures and it has to modify the file header and change other internal pages to remove model reference and add tempdb references to it.  Due to this it actually reads all the allocate and un-allocated extents from model in 64kb chunks and then copies them to the tempdb database. When compared against normal file copy operation this operation will be pretty slow. But in the current product this is how it has been designed. The bigger the model database the number of extents available will increase and it will take more time to copy the extents to the new database in 64kb chunks.

The above was the design which exists till SQL Server 2008 R2; In SQL Server 2012 some significant design changes have been made which avoids the problem completely and fastens the data copy of model database extents to tempdb as well as in SQL Server agent there is some code changes as well which avoids the waiting situation. At this point of time the same architecture changes cannot be ported back to SQL Server 2008 R2 as the product has already reached Extend Support Phase where in only security fixes will be made and any other new issues/design change requests will be denied.

Be mindful that this is only relevant through versions SQL Server 2008R2.  As Predeep has indicated above, it is not an issue in SQL Server 2012.

If find it curious that this issue has not been raised in the 6 years that this product has been in the field.  At this point in time, there may not ever be a KB article on this, since the product is past the maintenance support end date (July 8, 2014).

After resizing model to the default 2mb data and 1mb log, the delays in instance failovers disappeared.  That 80GB tempdb database came on line in less than 2 seconds.

How do you know if your server has the issue?

  1. Are you running SQL Server 2008R2 or earlier? And…
  2. Has your model database been changed to have a data file larger than 2MB? And…
  3. Run:
    1. Capture the @startTime = current_timestamp
    2. Create DATABASE myTest (ie., a plain copy of the model database)
    3. Capture the @endTime = current_timestamp
  4. The delta between @startTime and @endTime is more than a second, such as 3 minutes, (ie., the time to create that database) you probably have an issue with model.

Lesson Learned: Contrary to popular beliefs, for SQL Server 2008R2 (and earlier), leave the model data file size at the default 2MB.  Increasing the size of model will create delays in the creation of tempdb at service startup.

Stay curious, keep learning…


Leave a Reply

You must be logged in to post a comment.

Using Gravatars in the comments - get your own and be recognized!

XHTML: These are some of the tags you can use: <a href=""> <b> <blockquote> <code> <em> <i> <strike> <strong>

top personal essay ghostwriting services online custom definition essay editing for hire for phd best admission essay on hacking top research proposal editing service gb esl masters essay proofreading sites for mba best school essay editing website usa cheap dissertation introduction ghostwriters site uk popular university thesis example professional dissertation abstract editor website for school popular school paper example top bibliography ghostwriters services ca best masters essay proofreading services for university best letter writer websites for university esl school personal statement topic top reflective essay ghostwriting website usa top article writing sites usa richard rodriguez aria analysis birthday essay write me anthropology case study best bibliography editing websites online cheap university essay editor services au definition essay ghostwriters services us professional descriptive essay proofreading for hire for school esl case study writing websites for phd popular dissertation chapter writer website for school professional homework editor site top research proposal proofreading websites for mba best definition essay editor service for phd tom and daisy esl critical essay writers for hire uk best college assignment examples phd ghostwriters service gb top critical analysis essay writing service online best creative writing editor website au esl dissertation hypothesis ghostwriters for hire online popular mba course work topics march 1917 russian revolution esl cover letter proofreading websites for college pay to get government application letter pay to do culture report cheap literature review ghostwriters for hire ca professional descriptive essay writing websites online custom home work editor websites for university popular mba essay ghostwriters website gb cheap papers writing website phd essay writers services uk buy esl essay online esl persuasive essay editor services online type my cheap biography write cheap best essay on brexit cheap admission paper writing site for school esl phd essay writing service usa cheap masters phd essay assistance professional masters movie review assistance professional critical essay writers websites uk top dissertation abstract ghostwriter site for school home work editing service us cheap creative writing editing service online type my custom creative essay on hacking pay to write management movie review esl homework editor for hire ca popular admission paper writing services us popular custom essay writer website us letter writer for hire online professional dissertation abstract ghostwriters for hire au analysis ghostwriting site us pay to get top school essay on brexit judge danforth best phd critical essay assistance physical activity essay esl phd essay proofreading service uk best mba persuasive essay topics popular literature review editor websites ca essay ghostwriter for hire online pay for my management cv esl problem solving ghostwriter service for mba buy art & architecture case study popular blog editor sites for phd dissertation hypothesis editor websites uk top dissertation introduction ghostwriters service stranger in the village summary popular cv proofreading websites online type my health content custom personal statement proofreading service gb pay for my cheap college essay on hillary custom mba essay proofreading for hire us custom dissertation methodology editing services au dissertation results editing websites online best assignment proofreading website for phd sample obituary for father top thesis writers services for university do my leadership letter cheap university essay writing service uk cheap university college essay help geology essay writers website esl resume editor site gb esl research proposal writing websites popular assignment editor website for masters best research paper writing website usa best cover letter proofreading service ca school uniforms are a bad idea professional scholarship essay proofreading site for masters custom bibliography writers site for phd custom custom essay editor services au best term paper editing website for phd top biography writer services for school professional writers service for mba popular critical thinking editing for hire ca best dissertation conclusion writer site gb top dissertation abstract proofreading for hire for university analysis essay on anthropology cheap thesis statement ghostwriter website for mba best expository essay writing sites ca cheap argumentative essay ghostwriter site for college esl phd essay editing sites online top expository essay proofreading sites gb learning to read and write by frederick douglass essay custom reflective essay editor websites esl application letter ghostwriting site for college help me write custom best essay descriptive writing site ca popular admission paper writing services ca esl blog post proofreading site for masters custom cv proofreading site for university political culture can be defined as the write earth science essays pay for professional personal essay on lincoln cheap cheap essay ghostwriters website online help writing management biography professional critical essay editor sites us esl school paper assistance movie review writer website usa top book review editor websites uk esl expository essay ghostwriter for hire for university esl essays editor websites for masters top paper ghostwriters for hire for college pay to get critical essay on hillary esl critical analysis essay ghostwriters service online write me economics homework business ghostwriting site do my algebra personal statement type my esl college essay on shakespeare best content writer site for mba top book review editor for hire usa esl movie review proofreading service for phd esl college essay ghostwriter websites for masters analysis of a movie type my art & architecture dissertation methodology popular analysis essay ghostwriting service for masters mercutio personality traits popular school speech sample order poetry resume top definition essay editing website for school esl phd dissertation proposal example cheap rhetorical analysis essay editor websites for school popular custom essay editing site gb popular cover letter ghostwriter sites for college cheap persuasive essay writing website usa popular case study writing websites for phd top dissertation ghostwriting site for mba best business plan editing website usa best book review proofreading sites for school type my music dissertation chapter cheap curriculum vitae ghostwriters service for mba pay for my professional analysis essay transcendentalist essay popular university essay writer for hire for college popular business plan writer site ca popular cover letter ghostwriters sites top analysis essay ghostwriters sites for college professional term paper ghostwriting for hire for school definition essay on happiness professional descriptive essay writer websites uk cheap problem solving ghostwriter sites usa best letter ghostwriter websites ca buy physics bibliography professional dissertation proposal proofreading services au top phd analysis essay samples professional cover letter ghostwriting services for masters professional thesis statement editing service gb write my mathematics blog letter proofreading service uk write best expository essay on pokemon go professional critical essay writers service ca professional reflective essay editor sites online type my physics home work professional research proposal ghostwriting services usa school ghostwriter services online write sociology movie review esl persuasive essay proofreading website for school best report writer website us top home work ghostwriters sites for college top biography writing site uk popular creative writing writers service for mba thank god for the atom bomb top homework editor websites ca top personal statement proofreading sites gb pay for my sociology creative writing top blog post editing service au top persuasive essay editing for hire au cheap blog writing service au custom article writer sites for mba top problem solving editing service for phd popular annotated bibliography writing websites usa esl assignment ghostwriter services for school pay to get music curriculum vitae scholarship essay writer sites au popular creative essay proofreading website uk esl movie review proofreading for hire au article ghostwriting service gb custom presentation editing websites for school help me write anthropology business plan custom college resume examples earth science essay ghostwriters for hire write shakespeare studies home work top presentation writers websites us best speech writers service for phd custom course work editing website popular persuasive essay editing service au cheap critical thinking ghostwriters website for university custom letter ghostwriters website for school cheap content writing sites for mba cover letter ghostwriters sites uk custom definition essay ghostwriters services uk cheap article proofreading services ca popular thesis proposal ghostwriting for hire for school help me write best academic essay on hillary clinton professional report proofreading sites for college popular blog writer websites usa cheap creative writing writer site uk best cheap essay ghostwriters website usa esl dissertation results ghostwriting for hire uk popular speech writing site for phd themes for the necklace popular personal statement editor website us best homework writers sites us custom university essay writing websites for phd professional research proposal writing websites for school article ghostwriter sites uk homework editing website us criminal law ghostwriters for hire analysis writing for hire type my management article review popular expository essay ghostwriters website usa cheap resume editing site usa top cheap essay ghostwriter website for phd write me best article review cheap thesis statement editing for hire usa cheap dissertation proposal ghostwriting services for mba best curriculum vitae ghostwriters site professional university personal essay samples popular course work writers websites for masters best dissertation hypothesis writers service uk custom thesis statement writers services gb music essay editing services best dissertation results writer site usa custom ghostwriting sites for college do my culture cover letter custom homework writing services gb write me top expository essay on founding fathers cheap cv proofreading service gb top research paper ghostwriters services usa help with statistics article review pay for custom university essay on shakespeare government writing service best case study ghostwriters services ca help me write government dissertation write my esl scholarship essay on hillary clinton professional letter ghostwriter for hire uk earth science ghostwriters service top bibliography proofreading sites for university professional custom essay ghostwriting website cheap dissertation proposal writing service ca critical essay ghostwriters services usa professional thesis writer services gb themes in robin hood cheap home work proofreading site write custom college essay on hacking cheap critical thinking writing websites au popular admission essay editor for hire for university professional analysis essay editing websites online professional university essay editor services gb custom movie review writer for hire online mark twain and realism popular argumentative essay editor websites au top dissertation results writing website for university custom papers ghostwriting sites for masters help me write esl descriptive essay on hillary esl definition essay writing websites gb esl critical essay writers websites popular thesis proposal ghostwriters site online do my math speech cheap masters essay editor websites for masters the necklace conflict esl letter ghostwriter websites esl persuasive essay writers for hire usa cheap blog post ghostwriting services usa order education thesis professional admission paper editing site online essays ghostwriter services ca professional blog editing service for masters custom best essay ghostwriter sites for phd thesis statement writers service online custom papers writer websites us problem solving proofreading for hire us top cover letter writer service for mba best dissertation methodology ghostwriters website au pay to get science dissertation methodology cheap cover letter editing website for masters top reflective essay proofreading websites for phd cheap essays editor service for mba esl school scholarship essay assistance top dissertation results ghostwriter service for phd type my critical analysis essay on presidential elections cheap cv proofreading sites uk cheap application letter editor site au pay to do political science application letter thesis ghostwriters site usa ideas for cv cheap resume ghostwriters for hire ca best dissertation writers service for school cheap content proofreading service for phd esl article review writing for hire us essay on sportsmanship custom mba essay editor service online popular case study proofreading for hire for college professional course work ghostwriters for hire us cheap content writing sites online write popular movie review custom cheap essay editing site for university help writing cheap rhetorical analysis essay on donald trump custom letter ghostwriter for hire for mba cheap school phd essay samples types of deterrence help writing popular blog post online best cover letter editing services for college esl personal essay writing for hire for university popular college scholarship essay topic custom literature review proofreading site for college best essays ghostwriters websites usa top proofreading site usa cheap course work proofreading websites online esl essays editing site ca professional thesis statement ghostwriter websites popular critical thinking ghostwriter service for masters buy professional university essay on lincoln pay for political science bibliography best college admission paper pay to do cheap rhetorical analysis essay on presidential elections esl article review editor service ca dissertation introduction ghostwriting websites au write my mathematics dissertation abstract top admission paper writers site usa nursing ghostwriter website top writing for hire for university decomposition of copper carbonate help me write top admission essay on hillary type my geology dissertation chapter best course work writer service for college personal essay ghostwriters for hire online professional college essay writers service for mba film bodies gender genre and excess best definition essay writing website for mba popular blog post editor websites gb cheap scholarship essay writer sites for masters how boats work popular college movie review ideas write me esl argumentative essay on usa cheap scholarship essay editor website for phd esl personal statement proofreading for hire for university popular essay ghostwriter website gb popular curriculum vitae writers service for mba popular dissertation methodology writer site for mba help with best dissertation abstract best resume writer service gb esl homework editing website au cheap definition essay writer service for phd esl home work ghostwriter site for university help with my top school essay on civil war popular paper ghostwriting service ca custom dissertation results writers sites gb critical ghostwriter services usa custom dissertation results writer services usa my life philosophy pay for custom expository essay on usa cheap dissertation chapter ghostwriter services for college professional dissertation results ghostwriting site for mba pay to do poetry homework top critical thinking ghostwriting services gb professional curriculum vitae editing websites ca creative writing service usa professional scholarship essay ghostwriter websites usa esl dissertation conclusion proofreading site us essays on war professional homework proofreading service online best cover letter writers sites for college custom cheap essay proofreading site for masters cheap bibliography editing sites usa popular best essay writer site us best blog ghostwriter website ca dissertation proposal ghostwriter services us professional cv ghostwriting websites uk esl personal essay ghostwriters service au cheap curriculum vitae proofreading website for college professional dissertation editing sites gb professional dissertation introduction ghostwriters service for phd best best essay editing service for college freedom in america essay custom dissertation conclusion writing website for mba cheap blog editing site online custom writers website us best problem solving writing website uk custom academic essay writer websites ca cheap application letter writers sites for mba professional phd essay editor website help with sociology dissertation chapter top admission paper proofreading websites uk popular personal essay ghostwriting for hire au best phd dissertation topic pay to write investments literature review dare program essays professional biography ghostwriters website for university professional assignment editor sites for masters cheap paper editing websites for university top reflective essay writer service uk best critical analysis essay editing services for university best research proposal ghostwriting site for school essay writing site uk custom scholarship essay ghostwriter for hire for university ed masry law firm custom thesis statement ghostwriters site us custom admission essay writers services gb esl cheap essay editor service esl blog proofreading for hire gb custom bibliography writer service usa dissertation conclusion editing sites online custom letter writing sites for mba best argumentative essay ghostwriting service popular masters essay ghostwriting services analysis ghostwriter sites usa top book review ghostwriters websites for college esl argumentative essay writing for hire usa best cheap essay ghostwriter sites custom masters dissertation topics pay to do best cheap essay on pokemon go top movie review ghostwriting site uk best descriptive essay writing sites for phd custom resume writing sites for university esl rhetorical analysis essay ghostwriting sites for mba thesis proposal editing site online custom dissertation chapter ghostwriting site gb popular phd essay proofreading website usa cheap literature review editor site online cheap university essay editing for hire au persuasive essay proofreading website ca buy government cv james cone books esl application letter editing sites for phd custom case study ghostwriter website online custom school essay ghostwriter site au curriculum vitae ghostwriter site us custom persuasive essay editing website for university samples for masters essay the 60s miniseries custom paper ghostwriters websites gb best dissertation hypothesis ghostwriters websites us professional dissertation conclusion writers website esl home work writers for hire for mba cheap article review writing websites uk popular academic essay ghostwriting for hire ca top essay writing services for college college ghostwriter for hire uk dissertation introduction writer site usa popular best essay editor service write me best dissertation conclusion custom report writers services usa buy professional critical analysis essay on civil war top admission paper editor site uk custom phd essay writer services for phd esl best essay writing site for school custom university essay proofreading services usa write shakespeare studies cover letter professional thesis ghostwriter for hire ca pay for my education speech top thesis statement ghostwriter services for university pay for custom college essay on hillary esl cover letter proofreading websites for university best phd dissertation introduction examples popular essays writers websites for masters best expository essay editing services us course work editing sites top definition essay editor sites cheap blog post editor sites uk scholarship essay on civil war top term paper ghostwriting services for masters help writing english home work custom mba letter help cheap research proposal writers sites for school cheap definition essay editing websites ca best term paper writers site for college