3

I'm currently doing some work on monitoring SQL Server performance. I've found the following script[1] which calculates 'Page Lookups Percentage' - the suggestion being that a "good" value is something less than 100.

SELECT 1.0*cntr_value /
(SELECT 1.0*cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Batch Requests/sec') 
AS [PageLookupPct]
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page lookups/sec'

Now I've run this script on about a dozen different servers - mostly virtual but a couple physical, mostly 2008R2 Enterprise but a couple of 2005, some live and some development, and even a physical 2008R2 Express edition that has only ever been a witness server and is currently dormant (no mirrors set up with active failover). On only one of these servers has the value been below 100. In most cases it has been in the 1000's or at least in the mid 100's. On the dormant witness server the value was a ridiculous 20,000 !

Does this indicate that we have widespread problems in this area, or is there some innocent explanation for why this figure should be so high on so many different servers? We are experiencing slow performance on some of these servers, so it would be useful for me to get a better understanding of what this figure is telling me.

[1] - http://thomaslarock.com/2012/05/are-you-using-the-right-sql-server-performance-metrics/

Improve this question
3
  • 3
    Do you have actual performance problems and can correlate other statistics with this, or are you just concerned by the ratio? Page lookups/sec just means SQL Server is spending a lot of time reading. I don't buy the advice that this always means inefficient use of the plan cache (sometimes you just need to read a lot of data) or the recommendation that this ratio should always be under 100 (sometimes you just don't have high batch requests/sec). I think this is one of those nonsense guidelines that made sense once but don't anymore, like everyone's page life expectancy should always be > 300.
    – Aaron Bertrand
    Jul 10, 2013 at 12:34
  • 1
    Definitely agree with both Aaron and Remus. This is where baselining comes into play. You need to compare apples with apples, and not just a generic number sometimes. To reiterate what Aaron asked, are you actually having perf issues? If you look for problems, you will find them 11 times out of 10.
    – Thomas Stringer
    Jul 10, 2013 at 12:48
  • Most of the servers I'm looking at are hosting databases used by third-party applications (each vendor app has its own server), and some of those applications are, according to reports, running slow. To investigate whether the SQL servers are a problem I'm gathering performance data. We don't have baselines (we will do soon, but that won't help me now!). Most other metrics look fine other than disk I/O on the virtual servers is slow. I was considering that the Page Lookups counter may indicate a problem, but when I found it consistently high across most servers I started doubting its worth.
    – paulH
    Jul 10, 2013 at 13:29

1 Answer 1

Reset to default
7

The metric is trying to measure the number of logical IO operations performed by each request, in average. If your server is, lets say, serving a busy web site, then is probably a good metric (perhaps 100 is even too high for such!).

The witness will only have internal activity. In other words it will touch data files internally, due to various background processes, despite never seeing any actual batch. As such all internal logical IO will be reported as caused by the few batches this server has seen.

I would say that the metric Thomas is recommending has some value but only in a a workload similar to what Thomas had in mind. A server that has only background activity, like your witness, is not going to report anything meaningful with this ratio. A server that runs intensive DW reports may see only 1 batch request per hour and run millions of page lookups for that one request. An absolute number like 'must be below 100' is only valuable accompanied by smallprint disclaimers like must be bellow 100 if your workload has frequent enough batch request to establish significant statistical trends and each batch requests is expected to perform under 100 page lookups in average. You get the idea...

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.