May 30, 2008 at 6:29 am
All,
I wanted to know the TempDB size while a long running procedure is going on in the server.
Say for example, Procedue P1 is running on the server, i want to know how much size (TempDB) the procedure P1 occupied ? How to know it ?
Inputs are welcome !
karthik
May 30, 2008 at 6:43 am
Also , i have some questions on TempDB.
1) what is the best size of TempDB ? The answer may be 'it depends',But if anybody added some valid points then it would be appreciated.
2) How to determine the TempDB size ? is there any formula available to determine the size?
My PROD DB size is :
PRODDB Size TEMPDB Size
2GB ???
10GB ???
15GB ???
25GB ???
50GB ???
100GB ???
over 100GB ???
How to determine it ?
3) How to know whether TempDB is going to full ? i.e say for example, if my TempDB size is 3 GB, assume it reached 2.8 GB , how to know the size alomost reached the maximum size ? or we can find it after the tempdb full ?
4) what is the default TempDB size ? I think it is 2 MB. Am i correct ?
5) Assume,The size of my tempdb database is steadily increasing. How can I control its growth?
6) What is the best way to optimize tempdb performance ?
I know we should avoid distinct,# tables, Union ,order by and Group by. But these are all the basic things. Ok. Aslo we should avoid Cursor. Am i correct ?
I want to know some more ways.
7) Is there any chance to decrease size of tempdb without restarting the server ?
karthik
May 30, 2008 at 9:03 am
This is really a lot of questions for one post. I'd split them up.
I'm not sure about knowing which objects from which databases are in tempdb, but the size you need is the size required for operations. There is no relation to database size and tempdb size. Just as there's no relation with log size.
It depends on how much activity goes on and how much needs space in tempdb. Lots of order bys will require more tempdb space for sorting. Also memory impacts tempdb as when worktables and table vars can't be kept in memory, they get materialized in tempdb
You can use System Monitor or a custom job to monitor tempdb size. If it's getting full, be sure you increase the size.
Doesn't make sense to decrease the size of tempdb. It's the size you need to handle the peak loads on the server. If you run low on space, buy more space.
May 30, 2008 at 7:45 pm
karthikeyan (5/30/2008)
1) what is the best size of TempDB ? The answer may be 'it depends',But if anybody added some valid points then it would be appreciated.
I've found a good starting point to be 10 to 20% more than what it grows to after 1 month of usage.
2) How to determine the TempDB size ? is there any formula available to determine the size?
No... no formula... as Steve said, it depends on a lot of things. Just on gut feel, I'll start a new TempDB at something less than 5 gig and let 'er grow for a month.
3) How to know whether TempDB is going to full ? i.e say for example, if my TempDB size is 3 GB, assume it reached 2.8 GB , how to know the size alomost reached the maximum size ? or we can find it after the tempdb full ?
Profiler is one way... but you should never NOT let TempDB grow because if it "gets full" and cannot grow... BOOM! Whole server is dead.
4) what is the default TempDB size ? I think it is 2 MB. Am i correct ?
In SQL Server 2000, it's 1 MB with a 10% growth factor... and you must ALWAYS change it to something else because it will create 73 disk fragments to grow from 1MB to 1 Gig at a 10% rate. It's not likely that I'd ever set a TempDB to be smaller than 1 Gig unless I knew for sure that not much was going to be happening on a particular server... even then...
5) Assume,The size of my tempdb database is steadily increasing. How can I control its growth?
By setting it's intial size correctly and by setting the growth rate to some value of MB. You should never stop TempDB from growing (well, until it reaches something like 90% of disk capacity just to control a runaway query).
6) What is the best way to optimize tempdb performance ?
I know we should avoid distinct,# tables, Union ,order by and Group by. But these are all the basic things. Ok. Aslo we should avoid Cursor. Am i correct ?
It should be on a separate disk "spindle" is one of the best ways. Another way is to split TempDB into 1 file per CPU and each file would be best on a separate "spindle". Of course, you've already stated the best way to optimize TempDB... write good code.
I want to know some more ways.
I realize you trust a lot of good folks on this forum, but you really need to do some of this research on your own. Many people have written articles on the subject... you need to read some of them... starting with BOL and the Microsoft web sites.
7) Is there any chance to decrease size of tempdb without restarting the server ?
Yes... just like any other database... just remember that gowing or shrinking a database file may prevent it's use. Certainly, it will slow things down just like any growth or shrinking.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply