Re: tempdb size question

On Apr 3, 7:29 am, "Krisnamourt via SQLMonster.com" <u21487@uwe>
wrote:
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-...

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

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


.

Relevant Pages

  • Re: Temp tables vs Permanent table with deletes
    ... Table expressions are literally inline macros, the SQL is expanded into ... using a persistent table for my temp storage - something surprising to ... many programmers who are used to CPU bound tasks that _always_ run ... an application database rather than tempdb. ...
    (microsoft.public.sqlserver.programming)
  • Re: Temp tables vs Permanent table with deletes
    ... CTE the optimiser hasn't a real idea in most cases (given real world SQL) how many rows will be returned on the intermediary steps, so you get a general plan. ... The worst 'improvements' come from doing things like avoiding the table creation overhead by using a persistent table for my temp storage - something surprising to many programmers who are used to CPU bound tasks that _always_ run faster if I do less work here;) ... Using a permanet table to act as a temporary table just causes blocking, fragmentation and additional logging because people usually locate it in an application database rather than tempdb. ...
    (microsoft.public.sqlserver.programming)
  • Re: Temp tables vs Permanent table with deletes
    ... If you are interested rather than taking our word for it Google around for tempdb blocking and research the problem. ... Table expressions are literally inline macros, the SQL is expanded into the main query - there is no encapsulation benefits in using table expressions because of this expansion. ... The worst 'improvements' come from doing things like avoiding the table creation overhead by using a persistent table for my temp storage - something surprising to many programmers who are used to CPU bound tasks that _always_ run faster if I do less work here;) ... Using a permanet table to act as a temporary table just causes blocking, fragmentation and additional logging because people usually locate it in an application database rather than tempdb. ...
    (microsoft.public.sqlserver.programming)
  • Re: Temp tables vs Permanent table with deletes
    ... Table expressions are literally inline macros, the SQL is expanded into the main query - there is no encapsulation benefits in using table expressions because of this expansion. ... The worst 'improvements' come from doing things like avoiding the table creation overhead by using a persistent table for my temp storage - something surprising to many programmers who are used to CPU bound tasks that _always_ run faster if I do less work here;) ... Using a permanet table to act as a temporary table just causes blocking, fragmentation and additional logging because people usually locate it in an application database rather than tempdb. ... In my experience the best performance improvements come from teaching people to think in Sets; often people think the database isn't the right tool for the job because they don't understand Relational Theory so just poo poo the relational database. ...
    (microsoft.public.sqlserver.programming)
  • Re: Temp tables vs Permanent table with deletes
    ... CTE the optimiser hasn't a real idea in most cases (given real world SQL) ... a persistent table for my temp storage - something surprising to many ... programmers who are used to CPU bound tasks that _always_ run faster if I ... an application database rather than tempdb. ...
    (microsoft.public.sqlserver.programming)