Do I need more free space on drive for tempdb?

Question

Post reply

Do I need more free space on drive for tempdb?

jordantx

Mr or Mrs. 500

Points: 588
More actions
November 14, 2010 at 12:04 pm

#231549

I'm working on a system that has only 6gb free on the C: drive where MSSQL is running from. The tempdb database is also on the C: drive and is set to autogrowth by 10%, unrestricted growth. The current size of tempdb is 7.4gb and it has 3.6gb free.
Our production databases are on another drive, which has a huge amount of free space. The main database itself has some very large tables, some of which are up to 2-3gb in size. The largest is 7gb, however we don't perform much processing against that table.
We are having some major performance issues, and as part of the diagnosis I checked the drive free space. 6gb seems like not very much space for any caching/tempdb activity that SQL needs to do.
Is there a way to find out whether we are running too close to the limit on drive space, during heavy processing/system usage? Does tempdb grow & then shrink again dynamically? Could it be churning a bit because of having only 6gb free?
Thanks for any thoughts on this. Not sure what other details to provide. I am thinking of moving tempdb to the larger drive, but unsure if it's necessary.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Perry Whittle SSC Guru Points: 233836 More actions · Answer 1

presumably your Windows page file occupies the C drive also?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

jordantx Mr or Mrs. 500 Points: 588 More actions · Answer 2

Additional note: I'm pinpointing the real reasons for our performance issues and tackling those already -- missing indexes, bad query plans, poorly written SQL, etc. I'd rather not get into all that in this thread, as I have a good handle so far on what is going on. If I run into areas of trouble I may ask specifically about those later. Thanks!

jordantx Mr or Mrs. 500 Points: 588 More actions · Answer 3

Perry Whittle (11/14/2010)
presumably your Windows page file occupies the C drive also?

Yes, the paging is on C: and is set to a custom size: 2046 to max 4092, rather than "System-managed size". We're on Windows Server 2003.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 4

I'd suggest checking the settings on the tempdb. It can be set to auto-shrink and then, yes, you're looking at serious issues, but in general, I'd definitely move tempdb to a different drive because you're going to see contention with the OS, which is not good.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 5

Once you get TempDB moved, do your self a couple of favors. First, set the initial size to 12GB (in your case based on the numbers you posted) The reason for this is, of course, your system will grow that large anyway so why not just give it what it needs up from? This will also mostly keep it from becoming fragmented as it grows.

Speaking of growing, you need to change the growth factor even though we just got done beating it up with preallocation. It takes 73 fragments to grow from 1MB to 1GB at a 10% growth factor. My recommendation (as well as setting the initial size to be quite large) is to set growth to be in MB rather than %. I typically use 100MB as a growth factor (rate) on most systems I setup even when I don't know how big it will eventually become.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 6

Jeff Moden (11/14/2010)
Once you get TempDB moved, do your self a couple of favors. First, set the initial size to 12GB (in your case based on the numbers you posted)

Obviously, this 12Gb is to be split over multiple files. Guestimate of 6 x 2Gb to ensure proportional fill.

Perry Whittle SSC Guru Points: 233836 More actions · Answer 7

MysteryJimbo (11/18/2010)
Guestimate of 6 x 2Gb to ensure proportional fill.

Could be overkill, how many logical processors do you have?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 8

Is there a way to find out whether we are running too close to the limit on drive space, during heavy processing/system usage?

Select disk space query results into a table and schedule it for every hour or two depends on business and see how was the growth.

Does tempdb grow & then shrink again dynamically?

Yes temp db grows and clears once the session is closed or restarted but not shrink unless auto shrink is enbled.

Could it be churning a bit because of having only 6gbfree?

Absolutely not but did you check whether its assigned value or used space?

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 9

And whatever you do, don't ever enable auto-shrink on any database... ever. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)