September 18, 2005 at 5:03 am
I have a situation where TEMPDB grows to over 16GB within 2 days of a SQL Server restart.
Consequently I am thinking of changing the default size of TEMPDB to something other than the 8MB default.
Obviously, if I set the default size to 16GB SQL Server is going to take an age to start up because the 16GB TEMPDB is going to have to be rebuilt.
Are there any other settings I have to worry about if I create a large TEMPDB such as "recovery interval"?
For those of you with large multi-GB TEMPDB's what strategies do you employ to cope with heavy TEMPDB usage?
What default size do you use? How do you cope with growth, % or MB?
If a file starts with 8MB and expands up to 16GB then surely this means that TEMPDB is fragmented across the disk?
September 19, 2005 at 2:45 am
Look this points.
1. Change Database Tempdb to recovery model Simple. (Default). The others recovery model is not necesary because you don´t recover the tempdb database never and the performance is better.
2. Avoid innecesary transactions. When finish a transaction the space in the file is available.
3. There are several operation that use tempdb (group by, sort,..) take with care.
4. Add another file in filegroup. See
http://support.microsoft.com/?id=328551
You can Try use a fast disk o raid system.
5. In a large enviroment can be necesary restart sqlserver to clean the cache.
6. You can monitor the grow in the database. Start with a razonable size, Can be 1 Gb and try with big files take into account the creation time. This creation time is waiting time for the user. The size depent on your sistem expecially the transactions, order joins, indexes, etc.
Luck
September 19, 2005 at 7:44 am
I have no real solutions but more thoughts.
Good link on the tempdb contention. I've had issues that I think have been helped somewhat with SP3 but still believe I should implement the multiple files on the tempDB to help more.
As for how should you grow the tempDB, % or size. How are you growing it now? I've had issues with growing files by % on large databases. In an environment with multiple DBs or files all growing by %, you wind up with fragmentation. But I had one problem caused by growing by % that shut down an eBusiness application. The DB was growing by % and the % got to a threshold were it could not grow the size requested within the timefraim required for a user transaction not to time out. So the application was basically dead. A transaciton would start, try to insert a record, DB would try to grow, transaction would time out, growth would roll back. Start it all over again with next transaction. The DB of interest was not TEMPDB though. I imagine the same thing could happen for tempdb also. It's a catch 22. You don't want tiny pieces all over the place, but you don't want to take the response hit of large increments. As for my growth problem, I got caught by the DB growing more than expected after the developers increased the user base of an application without telling me. I manually increased the DB to a size that I thouht was acceptable and set the growth to something that the application could handle in case the DB caught up to my defined size.
Do you have multiple tempdb files now? Maybe if you spread multiple over several disk, the time it took to create tempdb could be minimized although still not what you would optimally desire.
I don't know how you can tell TEMPDB to grow to a particular size AFTER SQL starts up. But that is what you really need. Start up at this small size, immediately grow to this large size - and don't remember this large size next time you start. If you put a very large increment in the autogrow field to accomplish this, you probalby run the risk of causing application problems. I thoght about a script to change file sizes that is run at startup. But, that would cause the DB to be recreated at that new size next startup.
September 19, 2005 at 3:13 pm
I had more or less decided to set the initial size to 4GB as creating such a file on the SAN is a surprisingly quick operation.
I was thinking of fixed growth of 500Mb.
I had ruled out % growth for the obvious reason that when you expand a 1GB file by 10% you increase by 100MB, however when you expand a 10GB file you are now creating an increment of 1GB. In other words the expansion gets slower and slower.
The Microsoft thread was interesting, particularly the bit about adding a TEMPDB ndf file for processors 2 ... n.
At my previous place we had been running Hotfix 818 with no problems however in my new role was are only on SP3a.
thanks for your help guys
September 19, 2005 at 4:27 pm
Out of my 22+ SQL Servers the smallest tempdb is 1024 Mb data/512 Mb. both data and log have 'autogrow' by Mb with limits. The growth factors are 512 Mb data/256 Mb log. The growth limits are 2048 Mb data/1024 Mb log. The largest is 5122 Mb data/1024 Mb log. The time difference in starting an instance with a small tempd and a large tempdb is seconds here. I even had to enlarge one for a conversion to 20Gb data/4096 Mb log. The startup time for the instance varied only by a few seconds. Also, I've never had to use multiple data files for tempdb. One server (a cluster) has 20+ databases occupying 300+ Gb of data space and the tempdb is only 4096 Mb data/2028 Mb log. Oh and most of our servers are using EMC SAN.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
September 23, 2005 at 1:55 pm
Thanks Rudy,
We are currently experiencing the old TempDB contention issue that was partially fixed by Hotfix 818. We are running back at SP3a on build 760.
I am going to recommend TEMPDB at 1GB per CPU with 512MB growth plus a 1GB log also with 512MB growth. Our live servers are also on a SAN but I am not allowed near them.
October 6, 2005 at 9:18 am
I see mention of setting the initial size of tempDB. How do you tell SQL Server to set tempdb at a particular size on startup? Is it by default created in the likeness of model and only if you specifically alter the database is the default overridden? Will that new size carry through reboots?
Thanks!
October 6, 2005 at 10:44 am
Whether you use EM or Transactl-SQL setting the size, growth factors and limits for growth works exactly just like any other user database on your server. There is a small difference on the Options tab in EM. Bwlow is a 'standard' script that we use at my site for master, msdb and tempdb:
---
--- alter_system_dbs.sql - 08-24-2004 Rudyx
---
--- to be executed on all new SQL Server Installations
---
use master
go
---
alter database master modify file
(name = master,
size = 25MB,
maxsize = 51MB,
filegrowth = 25MB)
go
checkpoint
go
alter database master modify file
(name = mastlog,
size = 25MB,
maxsize = 51MB,
filegrowth = 25MB)
go
checkpoint
go
---
alter database msdb modify file
(name = MSDBData,
size = 50MB,
maxsize = 101MB,
filegrowth = 25MB)
go
checkpoint
go
alter database msdb modify file
(name = MSDBLog,
size = 25MB,
maxsize = 51MB,
filegrowth = 25MB)
go
checkpoint
go
---
alter database tempdb modify file
(name = tempdev,
size = 1024MB,
maxsize = 2049MB,
filegrowth = 512MB)
go
checkpoint
go
alter database tempdb modify file
(name = templog,
size = 512MB,
maxsize = 1025MB,
filegrowth = 256MB)
go
checkpoint
go
---
--- end of alter_system_dbs.sql
---
This is our 'standard' starting point, and I stress the word 'starting'.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 6, 2005 at 11:32 am
Thanks! I was asking for info regarding how do you get the size to stick after a reboot. I found it here...
http://www.extremeexperts.com/SQL/Articles/AutoGrowth.aspx
The section "Tale of tempdb" give a perfect description. Basically, once you manually extend the database, it will stay at that size (it will be rebuilt at that size on reboot). Any autoextension is wiped out and its size is set back to the last size it was manually extended to. Good stuff. Not in BOL.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply