Multiple TempDB Files (Data & Log)

Question

Post reply

Multiple TempDB Files (Data & Log)

Viewing 6 posts - 16 through 20 (of 20 total)

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

uncle_boris SSCommitted Points: 1588 More actions · Answer 1

I have run the code below on the temp db.

SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'tempdb'),null);

The output shows it has had activity on both tempdb datafiles (ID 1 & 3)

Do you know if these counters reset on a SQL Service restart or not.

I am trying to see if I can find out at what point the extra tempdb data file was actually accessed.

matt.bowler SSCrazy Points: 2677 More actions · Answer 2

This is a really good article on working with and speccing tempdb. It is for 2005 but is still relevant for 2008. It also has some good diagnostic scripts.

matt.bowler SSCrazy Points: 2677 More actions · Answer 3

The round robin definately does not apply to log files - they are used serially.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 4

uncle_boris (9/20/2011)
I have run the code below on the temp db.
SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'tempdb'),null);
The output shows it has had activity on both tempdb datafiles (ID 1 & 3)
Do you know if these counters reset on a SQL Service restart or not.
I am trying to see if I can find out at what point the extra tempdb data file was actually accessed.

The counters are reset with instance restart.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

uncle_boris (9/20/2011)
and this round robin pattern is for both TempDB data files and also the log files (if their were multiple log files of course).

No, it's for the data files. SQL uses logs sequentially. First one, then the second, etc. There is no good reason for multiple log files for a DB.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

iainthepitman SSCommitted Points: 1666 More actions · Answer 6

Marios Philippopoulos (9/20/2011)
uncle_boris (9/20/2011)
I have run the code below on the temp db.
SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'tempdb'),null);
The output shows it has had activity on both tempdb datafiles (ID 1 & 3)
Do you know if these counters reset on a SQL Service restart or not.
I am trying to see if I can find out at what point the extra tempdb data file was actually accessed.
The counters are reset with instance restart.

except the sample_ms which strangely, is reset when the computer restarts.