Tempdb size?

  • Thread starter brother_of_karamazov
  • Start date

brother_of_karamazov

Legendary Poster
You SQL Server folks mind running the statement below and reporting the results?

--BEGIN
Select

cast(alt.filename as char(25)) as 'Filename',
cast (alt.name as char(10)) as 'Database Name',
alt.size * 8.0 / 1024.0 AS [Originalsize (MB)],
files.size * 8.0 / 1024.0 AS [Currentsize (MB)]

FROM master.dbo.sysaltfiles alt

INNER JOIN tempdb.dbo.sysfiles files

ON alt.fileid = files.fileid

WHERE alt.name in ('tempdev','templog')
--END



This will give you the original size of tempdb and its transaction log and also what size it has grown to.

The results (if you hit CTRL-T in query analyzer to get results in text) will look something like this:

Filename Database Name Originalsize (MB) Currentsize (MB)
------------------------- ------------- ---------------------- ----------------------
H:\tempdb.mdf tempdev 8192.0000000 21248.3125000
H:\templog.ldf templog 1024.0000000 1024.0000000

(2 row(s) affected)



Thanks,
Jeff
 
Hi Jeff
C:\Program Files\Microsof tempdev 8.0000000 5508.0000000
C:\Program Files\Microsof templog .5000000 300.5000000

Regards
 
This is form my PD DB

e:\MSSQL7\DATA\TEMPDB.MDF tempdev 1686.3750000 2716.0625000
e:\MSSQL7\DATA\TEMPLOG.LD templog 10.1796875 10.1796875

This is from my DEV DB

E:\TemE:\Tempdb\temdb.mdf tempdev 8.0000000 5333.1250000
F:\TransactionLogs\templo templog .5000000 46.5000000
 
Jeff,

This statement may produce misleading data, if the number of files has changed...
 
For our Dev database.
E:\mssql\tempdb\tempdb.md tempdev 1500.0000000 1500.0000000
E:\mssql\tempdb\tempdb.ld templog 100.0000000 100.0000000

For our Prod database:
M:\mssql\tempdb\tempdb.md tempdev 7940.0625000 7940.0625000
M:\mssql\tempdb\tempdb.ld templog .5000000 146.1875000

Tom
 
I suppose it could if someone has added files to tempdb. Here's a better statement:

--BEGIN
Select

cast(alt.filename as char(25)) as 'Filename',
cast (alt.name as char(10)) as 'Database Name',
alt.size * 8.0 / 1024.0 AS [Originalsize (MB)],
files.size * 8.0 / 1024.0 AS [Currentsize (MB)]

FROM master.dbo.sysaltfiles alt

INNER JOIN tempdb.dbo.sysfiles files

ON alt.fileid = files.fileid

WHERE alt.dbid = 2
--END





[ QUOTE ]
Jeff,

This statement may produce misleading data, if the number of files has changed...

[/ QUOTE ]
 
Here are my results. I have a file for each processor - a recomendation from MS - Http://support.microsoft.com/kb/328551

All set to autogrow and no limits - however I have never had them need to grow since I implemented this quite some time ago.

I probably have them larger then they'll ever need to be - but I have another SQL server that has processes that run on it that do fill up (4) 4 gig files and then some - so I just wanted to give me enough to avoid growth - because having a file grow in the middle of a process simply slows that process down.

I believe that this has increased my performance in some areas - I discoverd this in trying to improve perfomance when the users hit the Cardex file - in watching tempdb closely I see it getting hit heavily by simply hitting the find button or going to the end of a grid when that will return a lot of records. This seems to spread that process out against these mutliple files.


Filename Database Name Originalsize (MB) Currentsize (MB)
F:\TempDB\tempdb.mdf tempdev 4048 4048
E:\TempDB\templog.ldf templog 100 100
F:\TempDB\tempdev_1.ndf tempdev_1 4048 4048
F:\TempDB\tempdev_2.ndf tempdev_2 4048 4048
F:\TempDB\tempdev_3.ndf tempdev_3 4048 4048
F:\TempDB\tempdev_4.ndf tempdev_4 4048 4048
F:\TempDB\tempdev_5.ndf tempdev_5 4048 4048
F:\TempDB\tempdev_6.ndf tempdev_6 4048 4048
F:\TempDB\tempdev_7.ndf tempdev_7 4048 4048
 
Thanks for the reply.

Are you aware that to take advantage of the concurrency fix you need to add the -T1118 flag and make all tempdb files the same size and turn off autogrowth?


Proportional fill cannot be implemented unless your hotfix level is 8.00.0765 or above. You can check this by running (in SQL2000)

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')


If the result is greater than 8.00.0765 (it is likely 8.00.0818), you can put the -T1118 flag in the startup parameters, create a bunch of tempdb files of equal size with filegrowth = 0, and experience much better performance as it relates to tempdb.

I would go as far as saying that tempdb management is in my top five SQL tuning tricks for EnterpriseOne.



[ QUOTE ]
Here are my results. I have a file for each processor - a recomendation from MS - Http://support.microsoft.com/kb/328551

All set to autogrow and no limits - however I have never had them need to grow since I implemented this quite some time ago.

I probably have them larger then they'll ever need to be - but I have another SQL server that has processes that run on it that do fill up (4) 4 gig files and then some - so I just wanted to give me enough to avoid growth - because having a file grow in the middle of a process simply slows that process down.

I believe that this has increased my performance in some areas - I discoverd this in trying to improve perfomance when the users hit the Cardex file - in watching tempdb closely I see it getting hit heavily by simply hitting the find button or going to the end of a grid when that will return a lot of records. This seems to spread that process out against these mutliple files.


Filename Database Name Originalsize (MB) Currentsize (MB)
F:\TempDB\tempdb.mdf tempdev 4048 4048
E:\TempDB\templog.ldf templog 100 100
F:\TempDB\tempdev_1.ndf tempdev_1 4048 4048
F:\TempDB\tempdev_2.ndf tempdev_2 4048 4048
F:\TempDB\tempdev_3.ndf tempdev_3 4048 4048
F:\TempDB\tempdev_4.ndf tempdev_4 4048 4048
F:\TempDB\tempdev_5.ndf tempdev_5 4048 4048
F:\TempDB\tempdev_6.ndf tempdev_6 4048 4048
F:\TempDB\tempdev_7.ndf tempdev_7 4048 4048

[/ QUOTE ]
 
Yeah, I have the -T1118 flag in - If I read the article correctly, autogrowth doesn't have to be turned off - it's recommended - I believe by reading the MS article it would only be an issue if the files do grow and/or they are no longer of equals size. Which hasn't happened to me since I implemented this.

At the time I implemented this I wasn't sure if any JDE or non JDE (index maintenance, etc.) process would need all of the tempdb space and didn't want something crashing because there wasn't enough space and it couldn't grow.

Guess I could go back and turn it off - or if they ever do need to grow, stop SQL server, delete the physical files and restart SQL to get them back to the original size.

Curious - you started this thread - I assumed you were trying to figure out something about your own tempdb and how to improve performance - but it appears you already knew about this fix. So what are you trying to find out?

Have a good weekend,

Dave Schlieder
 
Dave,

I am really surprised that the one tiny tempdb file you have has not grown given that you have proportional fill turned on. That does not make any sense to me at all.

I really just wanted to know how other folks were dealing with tempdb.


Thanks for taking the time,
Jeff



[ QUOTE ]
Yeah, I have the -T1118 flag in - If I read the article correctly, autogrowth doesn't have to be turned off - it's recommended - I believe by reading the MS article it would only be an issue if the files do grow and/or they are no longer of equals size. Which hasn't happened to me since I implemented this.

At the time I implemented this I wasn't sure if any JDE or non JDE (index maintenance, etc.) process would need all of the tempdb space and didn't want something crashing because there wasn't enough space and it couldn't grow.

Guess I could go back and turn it off - or if they ever do need to grow, stop SQL server, delete the physical files and restart SQL to get them back to the original size.

Curious - you started this thread - I assumed you were trying to figure out something about your own tempdb and how to improve performance - but it appears you already knew about this fix. So what are you trying to find out?

Have a good weekend,

Dave Schlieder

[/ QUOTE ]
 
Ignore my last post about the one smaller tempdb file. If I had looked closer I would have realized that the tiny file was the transaction log file.
 
Dave,
We see performance issues on an ongoing basis and it generally occurs when there is a lot of activity in the cardex/F4111 (ship confirm, OT receipts). I was wondering how you tracked the problem to the tempdb and if you measured the performance increase you received after making this change. Any help would be appreciated.

Tom Nollan
 
Tom,

>>I was wondering how you tracked the problem to the tempdb<<

Well, I did this a quite a long time ago so my memory on how I got there isn't that great...

The users were complaining about the delay when they query the cardex - sometimes it returned records quickly, other times it didn't - this lead to some research - it boiled down to a cache issue - when the data is in the cache - quick results - but when someone does something to cause that data to be flushed from the cache or it isn't in the cache, it takes longer to get the results.

I saw that there was a lot of activity in tempdb when they hit the cardex by watching the processes with sp_who2 and sp_lock.

So somewhere along the line, probably through one of the SQL forums, I stumbled across the KB article and implemented it.

>>f you measured the performance increase you received after making this change<<

No I didn't - I think it has helped - haven't heard as many complaints - but I think the next thing to do would be to increase the resources, i.e. memory on the SQL box. I haven't been able to do so as the powers that be won't pop for the Enterprise edition of SQL server - I'm stuck with the standard edition so stuck with the memory I have.

I see you are on SP3a - I wouldn't advise SP4 - keep in mind this is my opinion - I put it in and had to take it out which was a real PITA for 3 servers.

I have some linked servers and the queries across the linked servers took way to long to process on SP4 - 3 or 4 times longer - or flat timed out - when I put SP4 in. Reverted back to SP3a and all was okay. MS hasn't really acknowledged any direct issue along these lines but I couldn't keep production going with it in.

It was either take it out or send everyone home<g>.

Other things I've done to keep SQL running as best as I can - defrag indexes weekly - there are a number of scripts on www.sql-server-performance.com that address ways to do that. Most need mods to work with tables owned by other than dbo but realativly simple mods - mine logs when it starts and ends the defrag on each index onto a table so I can analyze the results, etc.

None of my data files are > 4gig - can't rememeber where I can up with that number - but I beleive it has to do with the way SQL server maps out alllocate pages, etc. and when the file is > 4 gig, it has to read 4 gig into the file to get the next set of pointers - or something along those lines - so I set everthing up that way - no autogrowth except maybe on one file for emergency purposes - like when I go on vaction. So I just make sure I have plenty of space all the time.

Hope this helps.

Dave Schlieder
 
Back
Top