If you’ve ever created, or even looked at a Custom Condition, you may have noticed an option labeled, “Maximum Instance Count.” Since we’re in the SQL Server world, you possibly assumed (as I once did), that this is related to instances of the Database Engine, and you’d be incorrect in your assumption (as I was).
Maximum Instance Count
In SQL Sentry Custom Conditions, the Maximum Instance Count defines the number of results returned when the Any instance qualifier is used. You may choose to evaluate up to 100 items (data files, for example) in your results set. Speaking of the Any qualifier, that’s another option I’d like to discuss.
“Any” Qualifier
The “Any” Qualifier Option
You’ll notice in the SQL File Auto-growth Exceeds Free Space condition that it has a Maximum Instance Count of 100, Any before the “is less than comparison”, and a query that selects a DBFileName and a SpaceDebtMB value. All of these pieces work together.
Showing the highlighted Maximum Instance Count, Two-Column Select, and Any
If you hover over the value displayed in the custom condition, you will see a list of results:
Results Set of Keys and Values
In addition to the one result that you see, you now see two more. If I reduce the Maximum Instance Count to 2, then you only see two results, the default displayed one, and one additional:
Results Set is limited by the Maximum Instance Count
If you had used Value instead of Any, then only the first value selected would be used in the evaluation, regardless of the Maximum Instance Count:
Value returns just one result, the first result
You could have a custom condition that has more than one results set. The Maximum Instance Count could be set to 100, but the results set would only include the intersection of those results; so while it could be 100, it could also possibly be one, or none. You could have an Any on the left and right of the comparison to generate multiple results sets:
Comparing Any to Any
You might also have an Any on more than one step of the condition. For example, step one of the condition checks that Any x value is less than or equal to y, and step two checks that Any x value does not equal the Last Value for that specific x. I believe I have a couple of good examples of intersections in the next Custom Conditions Pack and will revisit that example when they’re published.
Two Columns in the Query
The Any option requires two columns in the selection to return a key/value pair in order to identify which value met the defined condition. This also ensures that conditions using queries on both sides of the condition, or comparing Last Value, will automatically match values with the same key. The first column is used to create the Key Value in the results set.
In the examples above, this allowed us to see tempdev, tempdev2, and templog in the results set to make the values identifiable. It actually allows you to see the server name too, when I don’t have it blacked out. 🙂
If the query is updated to select only one value:
Query with only one column in the Select
…then an error will occur:
Error message when there are not two columns
When you are planning for multiple results, you need to define a key to identify those individual results.
You’re not technically limited to one piece of information for the Key Value as you can concatenate multiple columns and text together. If you look closer at the query in this condition, you’ll see that the DBFileName does just that:
Custom Condition Query
Now that you know how Maximum Instance Count works, what conditions will you test?
Some key points to remember:
- Maximum Instance Count refers to the number of results (you may have up to 100)
- Maximum Instance Count works with the “Any” qualifier
- Using the “Any” qualifier requires two values in your query
