October 2, 2009 at 7:33 pm
We are considering a dedicated for the TempDB, with no other files in the drive.
Will it be a good idea to remove/disable both autogrowth and also autoshrink and keep the TempDB size right away at the max of the drive capacity? (the drive is 35GB, so should I set the TempDB size right away at 34.99GB, or should I leave room for any other factors )
Thx
Dan.
October 2, 2009 at 8:48 pm
Refer to this white paper: http://technet.microsoft.com/en-us/library/cc966545.aspx
Essentially, you never want to enable auto shrink but use auto-grow as an emergency just in case. You should try to configure tempdb up front with large enough data files to minimize or eliminate auto growth. Typically, if you run the system for awhile, you'll see how large it grows and can use that as your bench mark. Create additional data files based on the number of CPU's in the system. For the 8-way I have, I've created 8 files that are 4096 MB to start and they don't ever auto-grow. Only create one log file as it's written to sequentially so you never need more than one.
HTH...
October 2, 2009 at 10:10 pm
repent_kog_is_near (10/2/2009)
We are considering a dedicated for the TempDB, with no other files in the drive.Will it be a good idea to remove/disable both autogrowth and also autoshrink and keep the TempDB size right away at the max of the drive capacity? (the drive is 35GB, so should I set the TempDB size right away at 34.99GB, or should I leave room for any other factors )
Thx
Dan.
I would. Autoshrink is ALWAYS a bad idea, and since you're at max size, autogrow doesn't much make sense either.
Ultimately - your one decision might be whether you do one file or several (which can in some cases end up improving performance.)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 3, 2009 at 9:54 am
George,
Thanks for the article. It has lots of good queries to monitor on a peridic basis.
so you have set your TempDB to 32MB (4*8). If the TempDB should grow more than 32MB, will be grow only in the first one, or in all 8 proportionately? So, have you put a small autogrowth factor in all the 8?
also, could you kindly share the t-SQL code you used to create the additional data files. Have you named it TempDB1,TempDB2 etc
thx
Dan
October 3, 2009 at 9:56 am
Matt
Thanks for the tip. I like making additional data files out of TempDB; it can only help with I/O.
In my case, do you think I can set the TemPDB Size to 34.99 right away? (What I am thinking is - should I I need to keep it at 33 or 34GB, and is there any other process that will need some space?)
Dan
October 3, 2009 at 11:22 am
repent_kog_is_near (10/3/2009)
MattThanks for the tip. I like making additional data files out of TempDB; it can only help with I/O.
In my case, do you think I can set the TemPDB Size to 34.99 right away? (What I am thinking is - should I I need to keep it at 33 or 34GB, and is there any other process that will need some space?)
Dan
That's a question only you can can answer. If I plan on dedicating a drive to this - then I would probably steer clear of putting any other process on this drive, but that's really a choice that needs to be answered in your own specific context.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 3, 2009 at 3:29 pm
Matt
I do not need to plan any other process on the dedicated drive for TempDB. What I am asking is if SQL or Windows Server will need any extra space for/from the TempDB drive, for any system process, that I need to understand or account for; or can I allocate all of it (34.99GB) to the file(which is what I prefer, provided Windows does not need any KB/MB for itself)?
Dan
October 3, 2009 at 4:16 pm
repent_kog_is_near (10/3/2009)
MattI do not need to plan any other process on the dedicated drive for TempDB. What I am asking is if SQL or Windows Server will need any extra space for/from the TempDB drive, for any system process, that I need to understand or account for; or can I allocate all of it (34.99GB) to the file(which is what I prefer, provided Windows does not need any KB/MB for itself)?
Dan
You should be able to fill it up entirely.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 3, 2009 at 7:06 pm
Thanks Matt.
I'll wait for George is he is able to share the code to get the TempDB into 8 junior TempDBs!!
October 4, 2009 at 5:33 am
repent_kog_is_near (10/3/2009)
I'll wait for George is he is able to share the code to get the TempDB into 8 junior TempDBs!!
No need to wait for George. Use Books Online and loom up ALTER DATABASE, specifically the section on adding more files to a database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 4, 2009 at 6:16 am
after Kimberly Trip:
1) SQL 2000 - #tempdb files = #logical cpus
2) SQL2005+ - #tempdb files = 1/4 ~ 1/2 #logical cpus (typically not more then 8)
October 4, 2009 at 6:20 am
thanks.
October 4, 2009 at 6:14 pm
Marcin
Is the 2k vs 2005 recommendation for the # of TempDB files from your experience or is it a MS reco?
Thx
Dan
October 4, 2009 at 6:15 pm
If you had already 16 TempDB files in SQL Server 2005, could you reduce it to 8, using Alter Database commands?
Also, when you restart the server, do the TempDB files Stay or is it reset to one file?
October 4, 2009 at 6:16 pm
Marcin
I should have asked if it was from Kim's experience or is it a MS reco? If you have the link for that, kindly share.
Dan
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply