Calculating Sql Server logical reads using Perl


One important method for performance tuning is to decrease number of logical reads, by adding, updating, and/or refreshing indexes and statistics. To see if those changes make a difference for you, it is important to look at query statistics.

Here is a sample output after set statistics io on:
[sourcecode language=”text”]
Table ‘Table1’. Scan count 0, logical reads 1673, physical reads 4, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Table2’. Scan count 0, logical reads 1673, physical reads 10, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Table3’. Scan count 1, logical reads 11871, physical reads 1, read-ahead reads 11865, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘#TempTable_____________________________________________________________________________________________________________000000000022’. Scan count 2, logical reads 276, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Table2’. Scan count 156, logical reads 1723, physical reads 1, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
[/sourcecode]
I used to tally those numbers by hand. This is error prone, and can be time-consuming, especially if the output is big. So I cooked up a Perl script below for this:
[sourcecode language=”perl”]
#!/usr/bin/perl
use strict;

my $LogicalReads = 0;

Main: {
my $log = shift or die “***Err: $0 expects a file name.\n”;
open(LOG, “$log”) or die “***Err: couldn’t open $log.\n”;
while () {
findPattern($_);
}
close(LOG);
print “Total logical read is $LogicalReads\n”;
} # Main

#######################
sub findPattern {
my $line = shift or die “***Err: function findPattern() expects a string.\n”;
if ($line =~ /logical\sreads\s+(\d+)/ix) {
$LogicalReads = $LogicalReads + $1;
}
} # findPattern
[/sourcecode]

Thanks Linchi and Tom for ideas!

, ,

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.