Relog utility script
I've been relogging (relog.exe) directories of perfmon counter files for years using a dusty old script when doing performance tuning assignments. Today I thought I'd pretty the script up for public consumption as my excuse for a reasonable blog entry.
When you undertake a SQL Server performance tuning engagement, you need to decide which & how much performance data you want to collect. If you haven't collected enough or the correct data, at some stage down the track you might have to repeat the exercise. Hence, I usually tend to suggest collecting a wider range of performance counters than you initially think you need, but this data can be difficult to manage if you don't have decent scripts handy to strip out the data you need later when performing your analysis.
Stripping .csv perfmon scripts is sometimes a tricky job because many text editors have limiting features & even tools like DTS can be finnicky at times.
I sometimes use the following script, which utilises Windows' RELOG.EXE to automate the process of seperating a specific perfmon counter from a whole directory of .csv perfmon log files. For example, I'll often configure a perfmon log to collect all Physical / Logical disk counters, then strip out the specific counters (eg Avg Read Queue Length or Disk % Idle Time) against specific disks or volumes using this script to zero in on specific problems (eg TempDB TLOG volume or Data volume i/o queuing).
With a little further scripting, you could autmoate things further by creating a scheduled SQL Agent task to poll a directory where you're logging perfmon counters and update a performance counter database. Even you're super keen, you could even wrap a Reporting Services graph on the data & publish it to your intranet. Good luck! (c:
Cheers,
Greg Linwood
/**********
* Relog Perfmon .csv files script
* Greg Linwood
* greg@SolidQualityLearning.com
* Jan 30th, 2006
*
* Strips (Relogs) a specific performance counter from a directory
* of perfmon .csv files & loads them to a permanent table..
**********/
set nocount on
/*
* SETUP
*/
--create a permanent table to store your counters
--create table tempdb..mycounters (countertime datetime, counterval decimal (15, 3))
--truncate table tempdb..mycounters
--user configurable variables
declare
@perfdir varchar(255) -- directory containing .csv perfmon files
, @archivedir varchar(255) -- archive directory for processed files
, @counter varchar(1000) -- counter name to be re-logged
-- alter this to point to your perfmon .csv file diretory
set @perfdir = 'C:\PerfmonLogFiles\'
-- alter this to point wherever you want processed files to be archived (you might need to add this dir)
set @archivedir = 'C:\PerfmonLogFiles\Archive\'
-- alter this to be the counter name you want re-logged
set @counter = '\\PRODSQL1\SQLServer:Buffer Manager\Page life expectancy'
/*
* RUN
*/
-- run-time variables (don't change)
declare
@filename varchar(200) -- filename variable for cursor
, @sql varchar (1000) -- dynamic sql variable
-- temp table to list .csv perfmon files to work through on
create table #files(filename varchar(200), d int, f int)
-- staging table within cursor to bulk load re-logged counter values
create table #counters (dt varchar(50), val varchar(50))
--populate #files via call to xp_dirtree
insert #files execute master.dbo.xp_dirtree @perfdir, 1, 1
delete from #files where f != 1
--for every file to be re-logged...
declare cr cursor for
select filename from #files order by filename
open cr
fetch next from cr into @filename
while @@fetch_status = 0
begin
--relog counters out to tempout.csv file
set @sql = 'exec master..xp_cmdshell ''relog '
set @sql = @sql + @perfdir + '' + @filename + ' -c "'
set @sql = @sql + @counter + '" -o ' + @perfdir + 'tempout.csv -f CSV'''
exec(@sql)
--bulk load counters from tempout.csv to #counters staging table
set @sql = 'bulk insert #counters from ''' + @perfdir + 'tempout.csv'' '
set @sql = @sql + 'with (FIELDTERMINATOR = '','', FIRSTROW = 2)'
exec(@sql)
--delete tempout.csv file
set @sql = 'exec master..xp_cmdshell ''del ' + @perfdir + 'tempout.csv'''
exec(@sql)
--move this perfmon .csv file to the archive dir
set @sql = 'exec master..xp_cmdshell ''move ' + @perfdir + ''
set @sql = @sql + @filename + ' ' + @archivedir + @filename + ''''
exec(@sql)
--strip any double quotes from counter datetimes / values
update #counters set dt = replace(dt, '"', ''), val = replace(val, '"', '')
--move re-logged counters to your permanent table
insert into tempdb..mycounters
select convert(datetime, dt) as dt
, convert(decimal(15, 3), val) as val
from #counters where isnumeric(val) = 1
--truncate #counters staging table for next cursor iteration
truncate table #counters
fetch next from cr into @filename
end
go
--cleanup cursor
close cr
deallocate cr
go
--cleanup temporary tables
if object_id('tempdb..#files') > 0 drop table #files;
if object_id('tempdb..#counters') > 0 drop table #counters;
go
--output some sample results
select top 20 * from tempdb..mycounters