Usenet.com

www.Usenet.com

Group Index

Comp Thread Archive from Usenet.com

<-- __Chronological__ --> <-- __Thread__ -->

Sizing of mulitple tempdb's ? ? ?



We considering implementing multiple tempdb's to deal with a tempdb
system table contention problem.  I've read what I can from the
documentation
and white-papers (maybe I missed something), but there doesn't seem to be
any recommendation on how to size them.  BTW, we're currently running
12.5.0.3, but will be migrating to12.5.1 withing a few months.

Our current tempdb is 2.0 Gb.  It's that size because we were given some
extra disk space with a recent hardware upgrade.  We didn't do any careful
calculation  (is there one?) to determine the required tempdb size. We
figured if our old 1.0 Gb tempdb was big enough, increasing it to 2.0 Gb
couldn't hurt and will allow for future growth (without having to fight
operations
for more disk space <-- a sad story).

We have one particular application that is experiencing slowdowns because
of contention on syscolumns (among others).  There are about 35 users of
this application, but only about 20 of those 35 are doing the job function
that
experiences the contention.  The other 15 use a different function in the
application,
which doesn't seem to be affected.

We were thinking about creating three (no reason, it's just more) additional
tempdb's,
which we would bind that application to.  The space for the additional
tempdb's would
be taken from the existing 2.0 Gb tempdb, so it will be shrinking in size.

Here are the questions we have:

How do you determine the correct number of additional tempdb's for this
application?  Do you just add two, bind the application, and see if the
problem
goes away.  If not, add a third, and so on?

How do we know what size to make the additional tempdb's.  Since we're
only binding one application to them, we're thinking they should all be the
same
size.  The new tempdb's would have to be a minimum size of, the maximum
amount
of space any one user of the application would need. That would likely NOT
be
big enough for concurrent access.

When we bind the application to the new tempdb's, do we unbind from the
default
tempdb?  It seems like that should be the approach we take, but I don't
remember
reading that anywhere.

How do you determine the maximum size that our current tempdb is utilized?
I
recall a query based on OAM pages that showed usage at a point-in-time, but
that was long ago (4.9.2).  Maybe there's something better now.  Is it
possible
to determine the maximum amount of tempdb that any one user uses?  That
would be interesting to know for sizing the new tempdb's.

Thanks.

Richard





<-- __Chronological__ --> <-- __Thread__ -->


Usenet.com



Please check out one of the premium Usenet Newsgroup Service Providers below for access to Usenet.