Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

dbf2mysql parameter, WWWXXXAPC, wwwxxxAPC, How to unsubscrube from dategen spam, docmd.close 2585, WWWXXXDOCO, nu vot, dhcpd lease file "binding state", WWWXXXDOCO, how to setup procmail to process html2text

Links

XODOX
Impressum

#1: tempdb size question

Posted on 2008-04-02 18:21:31 by AJ

SQL Server 2005 SP2

I know that, for OLTP, the rule of thumb for LOGS is around 25% of
database size.

What is the rule of thumb for TEMP size? I know that its a good idea
(for TEMP) to have .25 to 1 data files per CPU, but how big should
TEMP be in total? Whats the rule of thumb there? I would assume its
some multiple of the total size of all databases in the instance?

thanks

aj

Report this message

#2: Re: tempdb size question

Posted on 2008-04-03 04:30:53 by Dan Guzman

Tempdb sizing depends on a number of factors. Besides explict tempdb usage
(temp tables/variables), other considerations include work tables needed for
large/complex queries and the version store for snapshot isolation levels.

I usually allocate about 20% of the largest table size for tempdb with
autogrow as a safety net and monitor actual usage.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"aj" <ronald@mcdonalds.com> wrote in message
news:fsWdnbO6r9sML27anZ2dnUVZ_tGonZ2d@supernews.com...
> SQL Server 2005 SP2
>
> I know that, for OLTP, the rule of thumb for LOGS is around 25% of
> database size.
>
> What is the rule of thumb for TEMP size? I know that its a good idea
> (for TEMP) to have .25 to 1 data files per CPU, but how big should
> TEMP be in total? Whats the rule of thumb there? I would assume its
> some multiple of the total size of all databases in the instance?
>
> thanks
>
> aj

Report this message

#3: Re: tempdb size question

Posted on 2008-04-03 16:29:14 by u21487

Friend,

TempDB is the soul of SQL and everything you can do to gain performance, do
it.

See this article from Kimberly and try to make this configuration. You'll see
the diference!

http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid= 8be9d388-b354-429f-8c98-e45989a4bebe


For example, SQL OLTP with 4 processors, I create five files, 4 with the same
size without autogrow and the last one with the same size, but with autogrow
true, because we need to garantee space for any necessary task, but often we
don't need and we manager well this.

http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid= 8be9d388-b354-429f-8c98-e45989a4bebe



aj wrote:
>SQL Server 2005 SP2
>
>I know that, for OLTP, the rule of thumb for LOGS is around 25% of
>database size.
>
>What is the rule of thumb for TEMP size? I know that its a good idea
>(for TEMP) to have .25 to 1 data files per CPU, but how big should
>TEMP be in total? Whats the rule of thumb there? I would assume its
>some multiple of the total size of all databases in the instance?
>
>thanks
>
>aj

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-general /200804/1

Report this message

#4: Re: tempdb size question

Posted on 2008-04-17 23:44:57 by vips

On Apr 3, 7:29=A0am, "Krisnamourt via SQLMonster.com" <u21487@uwe>
wrote:
> Friend,
>
> TempDB is the soul of SQL and everything you can do to gain performance, d=
o
> it.
>
> See this article from Kimberly and try to make this configuration. You'll =
see
> the diference!
>
> http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid= 3D8be9d388-...=

>
> For example, SQL OLTP with 4 processors, I create five files, 4 with the s=
ame
> size without autogrow and the last one with the same size, but with autogr=
ow
> true, because we need to garantee space for any necessary task, but often =
we
> don't need and we manager well this.
>
> http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid= 3D8be9d388-...=

>
> aj wrote:
> >SQL Server 2005 SP2
>
> >I know that, for OLTP, the rule of thumb for LOGS is around 25% of
> >database size.
>
> >What is the rule of thumb for TEMP size? =A0I know that its a good idea
> >(for TEMP) to have .25 to 1 data files per CPU, but how big should
> >TEMP be in total? =A0Whats the rule of thumb there? =A0I would assume its=

> >some multiple of the total size of all databases in the instance?
>
> >thanks
>
> >aj
>
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx=
/sql-server-general/200804/1

REGARDING TEMP DB SIZE:

-For a small db server that does about 10-20 GB of logging per day, I
would recommend having a size that does not go through AUTO_GROW. i.e
ensure that the size of tempdb is big enough that it will hold the
whole days work.

- for larger dbs, you need to come up with a cycle time that is
sufficient for your applications. i.e a 30 minute to 3 hour growth
between tempdb's log file ie zero'ed.

Report this message