November 22, 2007 at 11:40 am
I'm about to setup a migration to a new consolidated platform and would like to test the underlying disk subsytem.
I have a perfmon trace from a LIVE environment and have calculated the average read and write bytes per second for the data and log files. Is there an easy way to generate an equivalant SqlIO workload trace file to attempt to replicate this activity?
November 22, 2007 at 5:24 pm
Hi,
You can use the following to perform what you want.
This proc give you Input/output on Database files (from the starting of the server until now).
it's just a little bit of what you want, just customize the proc
Create proc getDataLogIO
As
Begin
Declare @loopCounter BigInt,@maxCounter BigInt,@dbId BigInt
Declare @fieldId BigInt,@dbName Sysname,@FileName Sysname,@strSQL Nvarchar(500)
Declare @databases Table (Id Int Identity,[dbId] Int,dbName Sysname)
Create Table #DBFiles (ID Int Identity,[dbId] Int,fileId Int,[filename] Sysname,databasename Sysname)
Insert Into @databases ([dbId],dbName)
Select
[dbId]
,[name]
From
master..sysdatabases
Order By
[dbId]
Set @loopCounter = 1
Select @maxCounter=MAX(ID) From @databases
While @loopCounter <= @maxCounter
Begin
Select
@dbId = [dbId],@dbName=dbName
From
Where
Id = @loopCounter
Set @strSQL = 'Insert Into #DBFiles(dbID,fileID,filename,databasename)
Select '+str(@dbId)+',fileId,name,'''+@dbName+''' AS databasename From ['+@dbName+'].dbo.sysfiles'
Exec sp_executesql @strSQL
Set @loopCounter = @loopCounter + 1
End
Create Table #FilesStat (ID Int Identity,dbID Int,fileID Int,databasename Sysname,filename Sysname,sampleTime datetime,numberReads BigInt,numberWrites BigInt,bytesRead BigInt,
bytesWritten BigInt,ioStAllMs BigInt)
Select
@maxCounter=max(Id)
From
#DBFiles
Set @loopCounter = 1
While @loopCounter <= @maxCounter
Begin
Select @dbId = dbId, @fieldId=fileID, @dbName=databasename, @FileName=filename
From #DBFiles
Where ID = @loopCounter
Insert Into #FilesStat(dbID,fileID,sampleTime,numberReads,numberWrites,bytesRead,bytesWritten,ioStAllMs,databasename,filename)
Select
dbID
,fileID
,getDate()
,numberReads
,numberWrites
,bytesRead
,bytesWritten
,ioStAllMs
,@dbName AS databasename
,@FileName AS filename
From
:: FN_VIRTUALFILESTATS(@dbId,@fieldId)
Set @loopCounter = @loopCounter + 1
End
Select
getDate()
,@dbName AS databasename
,@FileName AS filename
--,numberReads
--,numberWrites
,bytesRead
,bytesWritten
,ioStAllMs
from
#FilesStat
Drop Table #DBFiles
Drop Table #FilesStat
End
exec getDataLogIo
I recommand you the following links
http://technet.microsoft.com/en-us/library/ms187309.aspx
http://qa.sqlservercentral.com/articles/Administering/filestatsexample/862/
Regards,
Ahmed.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply