SQL and the like

My first useful powershell script – capturing performance counters

After playing around with powershell a bit, I managed to do something quite useful.  There are a multitude of ways to capture performance counters but I think this will have the advantage of being able to be fired by SqlAgent (or another timer process) every X many seconds.  All you now need to do is process the data within Excel (or power pivot as shown by David Castro  here ).  For every server in servers.txt it will collect the counters in counters.txt

$Servers = get-content c:\servers.txt
$CounterList = Get-Content c:\counters.txt


$sw = new-object system.IO.StreamWriter("c:\perf.res",1)
$Counters = $CounterList | Get-Counter -computer $Servers
foreach($counter in $counters){
 $counter.ToString()
 foreach($sampleset in $counter.CounterSamples){
       $sw.writeline($sampleset.Timestamp.ToString()+','+$sampleset.Path + ',' +$sampleset.CookedValue )  
 }
}

$sw.close()

Example Counters.Txt

\Memory\Available MBytes
\Paging File(_total)\% Usage
\PhysicalDisk(_total)\% Disk Time
\PhysicalDisk(_total)\Avg. Disk Bytes/Read
\PhysicalDisk(_total)\Avg. Disk Bytes/Write
\PhysicalDisk(_total)\Disk Reads/Sec
\PhysicalDisk(_total)\Disk Writes/Sec
\SqlServer:Buffer Manager\Buffer cache hit ratio
\SqlServer:Buffer Manager\Page life expectancy
\SqlServer:General Statistics\User Connections
\SqlServer:Memory Manager\Memory Grants Pending
\System\Processor Queue Length

Example Servers.Txt

Server1
Server2

Enjoy

The Observer Effect In Action

I’ve put my hand up to to a quick 10 - 15 minute slot at the London user group, so I was getting my scripts together to do a presentation based on my UDF Overhead blog entry.  Naturally this being my first time talking, I wanted to make sure that I was accurate in terms of the statement timings and how I was interpreting the results.  After running the examples a few times I started to notice a discrepancy that I was wasn't expecting.

Executing the UDF with ‘SET STATISTICS TIME ON’ was visibly slower that without. In fact in profiler the duration time with stats time on was 2056ms, without it duration time was 233ms. This was the only difference. Testing on 2008 showed the same effect but to a lesser degree.

Heres the script im testing with.

Drop Function GetSalesCommission
go
Create Function GetSalesCommission(@SalesAmount money)
returns money
as
begin 
    Declare @CommissionAmount money
    Select @CommissionAmount = (@SalesAmount/100.0) * 5
    return @CommissionAmount
end
go

SET STATISTICS TIME ON
select sum(dbo.GetSalesCommission(SubTotal))  as Commission
  from Sales.SalesOrderHeader
go
SET STATISTICS TIME OFF
select sum(dbo.GetSalesCommission(SubTotal))  as Commission
  from Sales.SalesOrderHeader
go

And a screen shot of the profiler output

With this in mind,I’m certainly going to re-asses a few performance evaluation practices.  It doesn't completely negate my previous post on UDF Overheads but a large portion of the timings would seem to be related to this.

And the winner is….

Me.  Ok, so there wasn't a massive field of runners and riders, in the second Phil Factor challenge.  But I'm still feeling pretty chuffed.