Creating 20GB tempdb file

Looks like creating a huge tempdb file slows startup of SQL Server as it has
to recreate it every time.. Is it not advisable ?
What does "advisable" mean? It's all in the expectations of the
implementor, I suppose.
(a) do you really require that much room in tempdb? Are you using a large
amount of temp tables or forcing a lot of sorting through ad hoc queries?
Could you reduce this need at all? Is the performance hit of an autogrow in
tempdb noticeable?
(b) what kind of slowdown rate is affecting startup? Is this tolerable?
What kind of tradeoff are you willing to make between startup time and
autogrow mentioned in (a)?
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:esJvGmWRFHA.252@.TK2MSFTNGP12.phx.gbl...
> Looks like creating a huge tempdb file slows startup of SQL Server as it
> has
> to recreate it every time.. Is it not advisable ?
>
|||Hi
I have done tests that show that TempDB startup performance is no different
between 200Mb or 100Gb. It does not need to zero the pages like a normal
database after it has been created.
What timings are you showing?
Regards
Mike
"Aaron [SQL Server MVP]" wrote:

> What does "advisable" mean? It's all in the expectations of the
> implementor, I suppose.
> (a) do you really require that much room in tempdb? Are you using a large
> amount of temp tables or forcing a lot of sorting through ad hoc queries?
> Could you reduce this need at all? Is the performance hit of an autogrow in
> tempdb noticeable?
> (b) what kind of slowdown rate is affecting startup? Is this tolerable?
> What kind of tradeoff are you willing to make between startup time and
> autogrow mentioned in (a)?
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:esJvGmWRFHA.252@.TK2MSFTNGP12.phx.gbl...
>
>
|||> I have done tests that show that TempDB startup performance is no
> different
> between 200Mb or 100Gb. It does not need to zero the pages like a normal
> database after it has been created.
> What timings are you showing?
I didn't do any tests at all; in fact I don't have any systems where tempdb
is is in a non-default configuration...
I was assuming that the OP already established that the cause of his
slowdown was tempdb, which of course it might not be.
|||Interesting..
My tempdb is around 20GB and when i start the SQL service it takes 3-4 mins
to start and nothing pops up in the error log and hence made that conclusion
that it could be bcos of the tempdb creation everytime. I tried that on 2
servers and they both behave that way..
If not true, how can i troubleshoot this further of what the real reason may
be ?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uDTFtxZRFHA.2976@.TK2MSFTNGP10.phx.gbl...
> I didn't do any tests at all; in fact I don't have any systems where
tempdb
> is is in a non-default configuration...
> I was assuming that the OP already established that the cause of his
> slowdown was tempdb, which of course it might not be.
>
|||Profiler?
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eGJPlHeRFHA.508@.TK2MSFTNGP12.phx.gbl...
> Interesting..
> My tempdb is around 20GB and when i start the SQL service it takes 3-4
> mins
> to start and nothing pops up in the error log and hence made that
> conclusion
> that it could be bcos of the tempdb creation everytime. I tried that on 2
> servers and they both behave that way..
> If not true, how can i troubleshoot this further of what the real reason
> may
> be ?
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:uDTFtxZRFHA.2976@.TK2MSFTNGP10.phx.gbl...
> tempdb
>
|||In the SQL Server Event log, it indicates when the clearing of TempDB starts
and when it ends.
Have a look at that.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eGJPlHeRFHA.508@.TK2MSFTNGP12.phx.gbl...
> Interesting..
> My tempdb is around 20GB and when i start the SQL service it takes 3-4
> mins
> to start and nothing pops up in the error log and hence made that
> conclusion
> that it could be bcos of the tempdb creation everytime. I tried that on 2
> servers and they both behave that way..
> If not true, how can i troubleshoot this further of what the real reason
> may
> be ?
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:uDTFtxZRFHA.2976@.TK2MSFTNGP10.phx.gbl...
> tempdb
>
|||Cant really use the profiler as the SQL is starting
Anyways heres the log . As you can see theres quite a wait after the AWE is
enabled.
2005-04-21 10:47:50.10 server Copyright (C) 1988-2002 Microsoft
Corporation.
2005-04-21 10:47:50.10 server All rights reserved.
2005-04-21 10:47:50.10 server Server Process ID is 1884.
2005-04-21 10:47:50.10 server Logging SQL Server messages in file
'd:\MSSQL\log\ERRORLOG'.
2005-04-21 10:47:50.14 server SQL Server is starting at priority class
'normal'(4 CPUs detected).
2005-04-21 10:47:50.43 server Address Windowing Extensions enabled.
2005-04-21 10:54:27.59 server SQL Server configured for thread mode
processing.
2005-04-21 10:54:27.59 server Using dynamic lock allocation. [2500] Lock
Blocks, [5000] Lock Owner Blocks.
2005-04-21 10:54:27.69 server Attempting to initialize Distributed
Transaction Coordinator.
2005-04-21 10:54:29.91 spid3 Starting up database 'master'.
2005-04-21 10:54:30.15 server Using 'SSNETLIB.DLL' version '8.0.818'.
2005-04-21 10:54:30.15 spid5 Starting up database 'model'.
2005-04-21 10:54:30.15 spid3 Server name is 'ServerA'.
2005-04-21 10:54:30.16 spid9 Starting up database 'DB1'.
2005-04-21 10:54:30.16 spid8 Starting up database 'msdb'.
2005-04-21 10:54:30.23 server SQL server listening on 10.1.4.53: 1433.
2005-04-21 10:54:30.23 server SQL server listening on 127.0.0.1: 1433.
2005-04-21 10:54:30.27 spid5 Clearing tempdb database.
2005-04-21 10:54:30.26 spid9 Analysis of database 'DB1' (5) is 100%
complete (approximately 0 more seconds)
2005-04-21 10:54:30.29 spid8 Recovery is checkpointing database 'msdb'
(4)
2005-04-21 10:54:30.41 server SQL server listening on TCP, Shared Memory,
Named Pipes.
2005-04-21 10:54:30.41 server SQL Server is ready for client connections
2005-04-21 10:54:30.49 spid5 Starting up database 'tempdb'.
2005-04-21 10:54:30.51 spid5 Analysis of database 'tempdb' (2) is 100%
complete (approximately 0 more seconds)
2005-04-21 10:54:30.90 spid3 Recovery complete.
2005-04-21 10:54:30.90 spid3 SQL global counter collection task is
created.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:edFzeReRFHA.1236@.TK2MSFTNGP14.phx.gbl...
> In the SQL Server Event log, it indicates when the clearing of TempDB
starts[vbcol=seagreen]
> and when it ends.
> Have a look at that.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:eGJPlHeRFHA.508@.TK2MSFTNGP12.phx.gbl...
2
>
|||I'm pretty sure I've read somewhere that PAE can make startup time long. Probably has to do with the
fact that the memory is committed immediately, not only reserved and committed as needed. You might
want to search a bit on that (I search my old notes but didn't find anything).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:OSfj0F2RFHA.3704@.TK2MSFTNGP12.phx.gbl...
> Cant really use the profiler as the SQL is starting
> Anyways heres the log . As you can see theres quite a wait after the AWE is
> enabled.
> 2005-04-21 10:47:50.10 server Copyright (C) 1988-2002 Microsoft
> Corporation.
> 2005-04-21 10:47:50.10 server All rights reserved.
> 2005-04-21 10:47:50.10 server Server Process ID is 1884.
> 2005-04-21 10:47:50.10 server Logging SQL Server messages in file
> 'd:\MSSQL\log\ERRORLOG'.
> 2005-04-21 10:47:50.14 server SQL Server is starting at priority class
> 'normal'(4 CPUs detected).
> 2005-04-21 10:47:50.43 server Address Windowing Extensions enabled.
> 2005-04-21 10:54:27.59 server SQL Server configured for thread mode
> processing.
> 2005-04-21 10:54:27.59 server Using dynamic lock allocation. [2500] Lock
> Blocks, [5000] Lock Owner Blocks.
> 2005-04-21 10:54:27.69 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2005-04-21 10:54:29.91 spid3 Starting up database 'master'.
> 2005-04-21 10:54:30.15 server Using 'SSNETLIB.DLL' version '8.0.818'.
> 2005-04-21 10:54:30.15 spid5 Starting up database 'model'.
> 2005-04-21 10:54:30.15 spid3 Server name is 'ServerA'.
> 2005-04-21 10:54:30.16 spid9 Starting up database 'DB1'.
> 2005-04-21 10:54:30.16 spid8 Starting up database 'msdb'.
> 2005-04-21 10:54:30.23 server SQL server listening on 10.1.4.53: 1433.
> 2005-04-21 10:54:30.23 server SQL server listening on 127.0.0.1: 1433.
> 2005-04-21 10:54:30.27 spid5 Clearing tempdb database.
> 2005-04-21 10:54:30.26 spid9 Analysis of database 'DB1' (5) is 100%
> complete (approximately 0 more seconds)
> 2005-04-21 10:54:30.29 spid8 Recovery is checkpointing database 'msdb'
> (4)
> 2005-04-21 10:54:30.41 server SQL server listening on TCP, Shared Memory,
> Named Pipes.
> 2005-04-21 10:54:30.41 server SQL Server is ready for client connections
> 2005-04-21 10:54:30.49 spid5 Starting up database 'tempdb'.
> 2005-04-21 10:54:30.51 spid5 Analysis of database 'tempdb' (2) is 100%
> complete (approximately 0 more seconds)
> 2005-04-21 10:54:30.90 spid3 Recovery complete.
> 2005-04-21 10:54:30.90 spid3 SQL global counter collection task is
> created.
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:edFzeReRFHA.1236@.TK2MSFTNGP14.phx.gbl...
> starts
> 2
>