Now, here's a cool new SQL 2005 Profiler event I used for the first time in anger yesterday.
What I love about this event is that it actually spits out significant & useful data from both the blocked process & blocking process - locked resource, input buffer, time, spid etc into a single (xml) report.
Very handy for digging into query timeout / blocking issues indeed.
I'm up-grading a script I posted a few weeks back which measures i/o at database file level. Included in the new script are:
(a) Physical O/S filenames
(b) Physical O/S file volumes
(c) A secondary, summary report of i/o, aggregated to volume level.
Cheers,
Greg
/**********
* fn_virtualfilestats() script
* Greg Linwood
* greg@SolidQualityLearning.com
* Mar 15th, 2006
*
* Collects fn_virtualfilestats() for all databases
* on either SQL 2000 or SQL 2005 instances.
**********/
set nocount on
go
if object_id('tempdb..#filestats') > 0
drop table #filestats
go
declare @ver int
set @ver = cast(serverproperty('Productversion') as char(1))
create table #filestats (
DbId int not null,
DBName varchar (255) not null,
fileid int not null,
FileName varchar (255) not null,
SampleTime datetime not null,
TS bigint null,
NumberReads bigint null,
BytesRead bigint null,
IoStallReadMS bigint null,
NumberWrites bigint null,
BytesWritten bigint null,
IOStallWriteMS bigint null,
IOStallMS bigint null,
BytesOnDisk bigint null,
FilePhysicalName varchar (1000) null,
FileVolume varchar (20) null
constraint pk primary key (DbId, fileid, SampleTime))
declare @database_name varchar(255)
declare @database_id int
declare @file_id int
declare @file_name varchar (1000)
declare @sql nvarchar(4000)
declare cr cursor for
select sd.name, sd.dbid, saf.fileid, saf.name
from master..sysaltfiles saf
join master..sysdatabases sd on saf.dbid = sd.dbid
order by sd.name, saf.fileid
open cr
fetch next from cr
into @database_name, @database_id, @file_id, @file_name
while @@fetch_status = 0
begin
if @ver = 8
begin
select @sql = N'
insert into #filestats (DBName, FileName, DbId, FileId, SampleTime,
TS, NumberReads, BytesRead, NumberWrites, BytesWritten, IOStallMS)
select @dn, @fn, DbId, FileId, getdate(), [Timestamp], NumberReads,
BytesRead, NumberWrites, BytesWritten, IOStallMS
from :: fn_virtualfilestats(@database_id, @file_id) '
execute sp_executesql @sql
, N'@dn varchar (255), @fn varchar (255), @database_id int, @file_id int'
, @dn = @database_name
, @fn = @file_name
, @database_id = @database_id
, @file_id = @file_id
end
if @ver = 9
begin
select @sql = N'
insert into #filestats (DBName, FileName, DbId, FileId, SampleTime,
TS, NumberReads, BytesRead, IoStallReadMS, NumberWrites, BytesWritten,
IOStallWriteMS, IOStallMS, BytesOnDisk)
select @dn, @fn, DbId, FileId, getdate(), [Timestamp], NumberReads,
BytesRead, IoStallReadMS, NumberWrites, BytesWritten,
IOStallWriteMS, IOStallMS, BytesOnDisk
from :: fn_virtualfilestats(@database_id, @file_id) '
execute sp_executesql @sql
, N'@dn varchar (255), @fn varchar (255), @database_id int, @file_id int'
, @dn = @database_name
, @fn = @file_name
, @database_id = @database_id
, @file_id = @file_id
end
set @sql = 'update #filestats set FilePhysicalName = convert(varchar(1000), '
set @sql = @sql + 'rtrim(sf.filename)), FileVolume = left(convert(varchar(1000), '
set @sql = @sql + 'rtrim(sf.filename)), 3) from #filestats fs join ' + db_name(@database_id)
set @sql = @sql + '..sysfiles sf on fs.fileid = sf.fileid where fs.DbId = '
set @sql = @sql + convert(varchar, @database_id) + ' and fs.fileid = '
set @sql = @sql + convert(varchar, @file_id)
exec (@sql)
fetch next from cr
into @database_name, @database_id, @file_id, @file_name
end
go
close cr
deallocate cr
go
select * from #filestats
go
select FileVolume
, sum(NumberReads) as NumberReads
, sum(BytesRead) as BytesRead
, sum(NumberWrites) as NumberWrites
, sum(BytesWritten) as BytesWritten
from #filestats
group by FileVolume
order by FileVolume
go
if object_id('tempdb..#filestats') > 0
drop table #filestats
go