Re: Sizing of mulitple tempdb's ? ? ?

"How'd they do that?" <[EMAIL PROTECTED]> wrote
in message news:[EMAIL PROTECTED]
> 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 would add on thresholds at, say, 1800MB, 1600MB, 1300MB, and 1100MB
(assuming a 2 GB tempdb).  You could exepect a number of messages in the
error log and would sequentially eliminate the highest-valued thresholds
until you felt comfortable that you wouldn't trip the remaining high-value
threshold.  A threshold is listed below for printing to your error log.
Then you can basically divide 2GB by the needed tempdb size to determine how
many tempdbs you could have.

The difficulty is that you can't directly identify the application that's
hogging the tempdb.  (I have a more complex version for that - but you can't
disaggregate the tempdb information, so it is limited.)

> 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
>
>

create procedure sp_thaprint (@DBNAME       varchar (30),

                              @SEGMENTNAME  varchar (30),

                              @FREE_SPACE   int,

                              @STATUS       int)  as



   -- IF THIS STORED PROCEDURE IS USER EXECUTED THE PRINT MESSAGES ARE SENT
TO

   -- THE CLIENT.  IF SYSTEM INVOKED THE PRINT MESSAGES GO TO THE ERROR LOG.



   set ansinull                      on

   set flushmessage                  on

   set string_rtruncation            on



   declare @fsprint  varchar (11)



   print '-----------------------------------------------'



   print 'Sp_thaprint invoked.'



   print '   Database  = %1!,', @DBNAME



   print '   Segment   = %1!,', @SEGMENTNAME



   select @fsprint = ltrim (substring (convert (char (14),

                                                convert (money,

                                                         @FREE_SPACE),

                                                1),

                                       1,

                                       11))



   print '   Threshold = %1! free pages,', @fsprint



   print '   Status    = %1!.', @STATUS



   print '-----------------------------------------------'

go