setting MAX Size To Tempdb - NO AUTO GROWTH

Question

setting MAX Size To Tempdb - NO AUTO GROWTH

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
August 4, 2010 at 10:10 am

#225119

We have dedicated LUN for tempdb. We never had any space issues,however we are facing some peformance issues. I wanted to specify max size of tempdb and disable autogrowth, so that sql doesn't need to calculate the growth everytime it needs. I am hoping this would improve the performance, does it make sense? Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I'm not sure I understand what you are saying. Calculating required space is a very fast operation, what takes time is growing the file. So in that sense, yes you can improve perf. by avoiding autogrow.

For that you need to set the size of tempdb and then set the maxsize to that size as well and you should be done.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

Is this the correct sql? I am trying to initialize filesize of mdf with 100 mb and ldf with l0 mb and max size for mdf and ldf would be 100 and 10 mb respectively.

alter database tempdb modify file (name='tempdev',filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdb.mdf',size=100)

alter database tempdb modify file (name='templog',filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\templog.ldf',size=10)

Please let me know if i need to add anything else here.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Here's what I get when I script my modifications from the UI in SSMS

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 2097152KB , FILEGROWTH = 0)

BTW 100MB for tempdb is very small. Are you so short on HD space that you need to enforce this? If so that will be your biggest problem with perf.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 4

this is strange, when i use my query i get a message to restart sql server and when i use yours it just does it dynamically without starting the sql server. Could it be because i am specifying the actual location of mdf and ldf? And yeah the script which i sent is just from a test server, not the actual one. I didn't see max size parameter is your script, do we need to mention one?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Filegrowth = 0 means no auto grow.

Yes if you specify the location that assumes you want to move the files so you need to restart.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 6

thanks..i am assuming when u specify size it does for initialization and also to set max size right?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

From the user interface you cannot set a max size, the only option is to disable auto grow which achieves the same thing.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

iqtedar (8/4/2010)
We have dedicated LUN for tempdb. We never had any space issues,however we are facing some peformance issues. I wanted to specify max size of tempdb and disable autogrowth, so that sql doesn't need to calculate the growth everytime it needs. I am hoping this would improve the performance, does it make sense? Thanks

Performance issues CAN come from file growths since SQL Server will zero out every bit of the added file fragment before releasing it for use (unless you have enabled instant file initialization). Thus you can benefit from simply sizing tempdb to be it's max size and leaving it.

Having said that, my guess is that you have a poorly configured IO subsystem (as virtually every client I have ever encountered has had) and likely some missing indexes leading to hashing as well. Oh, and maybe lots of GROUP BY and ORDER BY queries too? 🙂 Do a file IO stall analysis and see how your IO system is performing. Or maybe have a performance tuning professional come in for a day or two to give your system a review.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 9

thanks SQL Guru, i did some analysis on IO stalls but the result is very weird. There are equal number of reads and writes done on tempdb on a RAID 10 LUN but the wait type for read is just 497 and for write 20192? Does this make sense?

nkanne724 Valued Member Points: 51 More actions · Answer 10

Hello Ninja,

I want to set the AutoGrowth to off.

How do we calculate the size that need to be allocated to the LDF files?

Is it a recommended practise to set fixed size for MDF files too?

If so, Are there any recommendations on how to set the max size for mdf file?

Thanks

Nate

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 11

This is a 4 year old thread. Please start a new one with your specific questions. Thanks!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service