This is a relatively
difficult question that can be used to assess the skills
of a senior administrator on a performance and tuning-related
topic. Target interviewee for this question is a person
who will be tuning a production server. A beginner to intermediate
candidate will understand the question, but might not get
the point. A candidate with at least two years of production
DBA experience should be able to answer the question correctly.
You
have determined that you need to accommodate up to 2 gigabytes
of tempdb in a high-performance environment. Should you:
a)
Create it small, and allow the autogrow to build it as necessary;
b) Create it small, and allow autogrow and autoshrink to
make it bigger or smaller as needed; or
c) Create it at 2 gig, and turn off autogrow and autoshrink
And,
what are autogrow, autoshrink, and tempdb anyway?
Autogrow is
a database property that instructs the server to increase
the size of a database either by a certain percentage or
a certain size if/when it needs more space.
Autoshrink allows
the server to recover unused space on the disk.
These two properties
let the systems administrator manage space on the disk,
and the sizes of the database, based upon current need.
The tempdb database
is used by the server for intermediary result sets from
a variety of operations, and well as being used by programmers
for the same.
Turning on autoshrink
and possibly autogrowth is a very bad idea for tempdb. Because
of the volatility of the database (tempdb is almost always
a hot spot in a high-volume database), the autogrow and
autoshrink could be working very hard allocating and deallocating
space at a time when the system is already busy (as evidenced
by the fact that more tempdb space is needed.
The best performance
answer for tempdb would be answer c, which is to make it
as big as it could possible be expected to be. If a candidate
suggests that an autogrow on top of that be allowed, that's
a reasonable precaution.
About
the author
Jeff Garbus's background includes a B.S. Degree from Rensselaer
Polytechnic Institute, and work experience from PC's to
Mainframes and back again. Jeff has many years of client
/ server, Sybase, and Microsoft SQL Server experience, with
a special emphasis on assisting clients in migrating from
existing systems to pilot and large-scale projects. He is
very well known in the industry, having spoken at user conferences
and user groups for many years, written articles and columns
for many magazines nationally and internationally, as well
as having written over a dozen books. Recently his focus
has been on high-volume database-based web applications.
Jeff has been in consulting for seventeen years, training
for ten, and in the software business for twenty. He has
a demonstrated talent for staying at the leading edge of
technology, as well as transferring his knowledge to others.
Jeff@soaringeagle.biz
