PAL v2.0’s New Counter Generation Feature is Powerful
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
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 |
||||||
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 |
|
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 |
|
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. https://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.