So, I’ve been playing with generating counters within PAL to compare ratios of SQL Server Full Scans to Index Searches performance counters. This is an effort to get PAL v2.0 updated and more accurate than PAL v1.x. It works out quite nicely! I feel like an evil genius right now. Mhahahahahaha!!! :-)

By the way, PAL v1.x cannot do this. ;-) The counter “\SQLServer:Access Methods\Full Scans to Index Searches Ratio” is a fake counter that is PAL created during analysis.

Disclaimer: The purpose of this posting is to demonstrate PAL’s ability to generate and display counter data. Do not use this posting for SQL performance analysis. As always, be skeptical about blog postings.

Here is the analysis I have been playing with.

Full Scans to Index Searches Ratio

Description: This counter monitors the number of full scans on base tables or indexes. Values greater than 1 or 2 indicate that we are having table / Index page scans. If we see high CPU then we need to investigate this counter, otherwise if the full scans are on small tables we can ignore this counter. A few of the main causes of high Full Scans/sec are missing indexes, too many rows requested, queries with missing indexes, or too many rows requested will have a large number of logical reads and an increased CPU time.
This analysis throws a Warning alert if the ratio of Index Searches/sec to Full Scans/sec is less than 1000 to 1 and if there are more than 1000 Index Searches/sec.
Formula: (AvgSQLServerAccessMethodsIndexSearchessecAll / AvgSQLServerAccessMethods_FullScanssec) < 1000

clip_image001

clip_image002

clip_image003

Overall Counter Instance Statistics

Overall statistics of each of the counter instances. Min, Avg, and Max are the minimum, average, and Maximum values in the entire log. Hourly Trend is the calculated hourly slope of the entire log. 10%, 20%, and 30% of Outliers Removed is the average of the values after the percentage of outliers furthest away from the average have been removed. This is to help determine if a small percentage of the values are extreme which can skew the average.

Condition

\SQLServer:Access Methods\Full Scans/sec

Min

Avg

Max

Hourly Trend

Std Deviation

10% of Outliers Removed

20% of Outliers Removed

30% of Outliers Removed

OK

VSTP24/TP24PRD

0

7

377

-11

45

0

0

0

Condition

\SQLServer:Access Methods\Index Searches/sec

Min

Avg

Max

Hourly Trend

Std Deviation

10% of Outliers Removed

20% of Outliers Removed

30% of Outliers Removed

OK

VSTP24/TP24PRD

27

3,478

54,437

-6,486

8,188

1,076

411

325

Condition

\SQLServer:Access Methods\Full Scans to Index Searches Ratio

Min

Avg

Max

Hourly Trend

Std Deviation

10% of Outliers Removed

20% of Outliers Removed

30% of Outliers Removed

Ratio of Index searches/sec to Full scan/sec less than 1000 to 1.

VSTP24/TP24PRD

0

247

28,487

-155

2,265

0

0

0

Alerts

An alert is generated if any of the thresholds were broken during one of the time ranges analyzed. The background of each of the values represents the highest priority threshold that the value broke. See each of the counter's respective analysis section for more details about what the threshold means.

Time Range

           

12/18/2007 11:07:18 AM - 12/18/2007 11:08:49 AM

Condition

Counter

Min

Avg

Max

Hourly Trend

 

Ratio of Index searches/sec to Full scan/sec less than 1000 to 1.

\\VSTP24\MSSQL$TP24PRD:Access Methods\Full Scans to Index Searches Ratio

0

928

5,565

17,957

12/18/2007 11:21:12 AM - 12/18/2007 11:22:43 AM

Condition

Counter

Min

Avg

Max

Hourly Trend

 

Ratio of Index searches/sec to Full scan/sec less than 1000 to 1.

\\VSTP24\MSSQL$TP24PRD:Access Methods\Full Scans to Index Searches Ratio

0

589

3,532

1,767

12/18/2007 11:39:08 AM - 12/18/2007 11:40:39 AM

Condition

Counter

Min

Avg

Max

Hourly Trend

 

Ratio of Index searches/sec to Full scan/sec less than 1000 to 1.

\\VSTP24\MSSQL$TP24PRD:Access Methods\Full Scans to Index Searches Ratio

0

4,835

28,487

8,215

PAL v2.0 is still in Alpha right now, but give it a try and send me your feedback. http://pal.codeplex.com.

The thresholds used in PAL analysis is based on the SQL performance experience of David Pless. David and I are both in the Microsoft Premier Field Engineering (PFE) organization.