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

"How'd they do that?" <rmcgorman_usenet@n__o__s__p__a__m__spamex.com> wrote
in message newsaKxb.516658$9l5.256570@pd7tw2no...
> 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

Carl Kayser wrote:
>
> 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.)
>

Have not yet implemented multiple tempdb, but ...

Why did Sybase choose to implement multiple tempdb this way anyway ?
I would have preferred that Sybase let me, the SA, tell the database
which tempdb a user database or a login will use.

>
> Have not yet implemented multiple tempdb, but ...
>
> Why did Sybase choose to implement multiple tempdb this way anyway ?
> I would have preferred that Sybase let me, the SA, tell the database
> which tempdb a user database or a login will use.
>

If memory serves, you can bind a logins or applications to one or more
of the new tempdb's.

I'm not sure as to what you mean by "which tempdb a user database ...will
use". You may want to look at
http://www.sybase.com/detail/1,6904,1023731,00.html.

However, I believe there are some options in 12.5.1 that are available in
12.5.0.3. Unfortunately the documentation is unclear on the differences.

"noone" <noone@noone.org> wrote in message
news:mNuyb.34067$aT.20881@news-server.bigpond.net.au...
> Carl Kayser wrote:
> >
> > 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.)
> >
>
> Have not yet implemented multiple tempdb, but ...
>
> Why did Sybase choose to implement multiple tempdb this way anyway ?
> I would have preferred that Sybase let me, the SA, tell the database
> which tempdb a user database or a login will use.
>

Thanks for the post Carl. :-)

I'll certainly implement the thresholds to help us determine how "used" our
current tempdb is. That'll help with the sizing of new tempdb's.

Hopefully, someone will respond to my other questions.

Richard

Carl Kayser wrote:

> I'm not sure as to what you mean by "which tempdb a user database ...will
> use".

Meaning, I don't what ASE to round-robin among several tempdbs.
I was thinking of "assigning / attaching" a tempdb to a user database.
I could then have each user database "assigned" to their own tempdb.

OK, I see your point. I guess that you would assign a large tempdb to a DSS
database and a smaller one to an OLTP database. I presume that the current
ASE technique would be to allocate DSS applications (e.g., InfoMaker) to the
large tempdb and everything else default to a smaller tempdb. (A devious
user could "use DSS database" and select stuff from the OLTP database in
order to get more tempdb space.)

To clarify (I hope) on the White Paper previously sent:

In 12.5.0.3 applications can be bound to a tempdb. Also the "sa" login can
be bound to a tempdb. (I find this bothersome since it is generally
recommended that the "sa" account be locked and individual accounts with
sa_role be used instead.)

In 12.5.1 any login can be bound to a tempdb as well.

"noone" <noone@noone.org> wrote in message
news:O%Vyb.35987$aT.25443@news-server.bigpond.net.au...
> Carl Kayser wrote:
>
> > I'm not sure as to what you mean by "which tempdb a user database
....will
> > use".
>
> Meaning, I don't what ASE to round-robin among several tempdbs.
> I was thinking of "assigning / attaching" a tempdb to a user database.
> I could then have each user database "assigned" to their own tempdb.
>
>
>