Methods to find Tempdb Usage - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Methods to find Tempdb Usage

Author

Topic

ashwinreddy.c
Starting Member

13 Posts

Posted - 2006-04-27 : 00:05:07

Hi All

One of the best practices usually given for SQL Server is to have tempdb on it's own drive. I'd like to know about methods that will allow us to determine how heavily the tempdb databases are used.

Thanks in Advance

Regards
Ashwin Reddy
SQLServer DBA

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2006-04-27 : 10:24:37

I forget where I picked this up from, but this will give you a relative look at how much each file is being used. I would not advise running this every 15 seconds or anything, but I have not examined its impact on performance. It would certainly have an effect on tempdb usage, if you scheduled it, though ;-).


set nocount on

if exists (select * from tempdb..sysobjects where name like '#tempfilestats%')
drop table #tempfilestats
go
create table #tempfilestats
(dbid smallint,
 fileid smallint,
 timestamp int,
 numberreads bigint,
 numberwrites bigint,
 bytesread bigint,
 byteswritten  bigint,
 iostallms  bigint,
 groupid int)
 
declare filecurs cursor for select dbid, fileid, groupid from master..sysaltfiles

declare @dbid int
declare @fileid int
declare @groupid int

open filecurs 
fetch next from filecurs into @dbid, @fileid, @groupid
while @@fetch_status = 0
  begin
	insert into #tempfilestats
	select *, @groupid from ::fn_virtualfilestats(@dbid, @fileid)
	fetch next from filecurs into @dbid, @fileid, @groupid
  end

close filecurs
deallocate filecurs


select iostallms, (numberreads + numberwrites) as "I/O's", iostallms/(numberreads + numberwrites) as "Stall time per I/O", fileid, numberwrites*100.0/(numberreads + numberwrites) as "Write Percent", db_name(dbid)
from #tempfilestats
order by 2 desc

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-27 : 11:44:46

In the Performance Monitor, you can look at the database statistics for the tempdb database.

CODO ERGO SUM

loiter99
Starting Member

38 Posts

Posted - 2006-05-19 : 08:09:17

mcrowley,
Can you post the meanings of these columns? I don't know if what I am looking at is good or bad.

Thanks
J

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources