| Author |
Topic |
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
 Posted - 2010-11-23 : 01:39:59
|
| Dear All,How to monitor the temp DB size.One of my production database is using lots of temp table and if the temp DB is limited in size that could be the bottle neck.How do i check the best temp db size for production server and any related tempdb recommendations for optimal performance will be helpfulThanks,Gangadhar |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2010-11-23 : 21:04:14
|
| Be sure to split TempDB into multiple (Equally sized) Datafiles as well.-Chad |
 |
|
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
Posted - 2010-11-23 : 21:54:02
|
| Hi,I have added total 3 files to tempdb totally 4 data files altogether with initial size of 100 MB and autogrowth is disabled.But when i saw one of my scheduled job failed indicating following error.Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. [SQLSTATE 42000] (Error 1101).Now i have the temp DB to autogrow for all the datafiles is this is good practice just want to confirm as this is the production database.Tara-Could you please guide me how to test in test environment to simulate production load.Thanks in advance.Thanks,Gangadhar |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2010-11-23 : 21:59:23
|
| So you didn't allocate enough space for TempDB. How many procs (Cores) do you have? Autogrow is fine to have on, but TempDB should be sized such that it doesn't autogrow (Except in rare occasions when something goes awry). If your workload changes and creates the need for TempDB to be larger, you should do that during maintenence window, and not via autogrow.-Chad |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
Posted - 2010-11-23 : 23:43:56
|
| Thank you very much Tara,you are the Goddess for SQL server.Thank you very much for your valuable comments here.Also i want to confrim how many CPU are there in my server.When i check with properties of my server it says Dual -core AMD opetron(tm) but when i see Device manager i could see 8 Dual-Core AMD Opeteron(tm) processores.Could you please confirm this.Thanks,Gangadhar |
|
|
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
Posted - 2010-11-24 : 00:16:15
|
| Hi,I have confirmed from Windows team that 4 CPU processors are exist int he server and i have added 2GB of Datafile of 4 and auto growth is disabled.Thank you very much. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-24 : 04:56:20
|
"auto growth is disabled."Why?Your processes will fail if they use up the space in TEMPDB. When things like that happen here something else breaks because of it, and we then have lots of tidy-up to do. Let it just use the disk space. If the disk fills ... well then you have a real problem  When SQL restarts it will reset TEMPDB to the "start up size".Monitor the size, if TEMPDB gets bigger than the size you are comfortable with then investigate. If it was a one-off then do nothing. If TEMPDB clearly needs the extra space then increase the START UP SIZE so that on each restart of SQL it resets TEMPDB to the size that you have decided is sensible |
|
|
|
|
Temp DB size