This post is based on a question I received last week…

If we have a filtered index that was not a match for a query due to parameterization (or a variation of which the parameter is not known at compile time), we can see the unmatched filtered index name in the UnmatchedIndexes element of SHOWPLAN_XML output.

The question I received on this subject was, “what about for filtered statistics?”  Can those still be used for parameterized scenarios? The following is a quick test to see if the stats also don’t qualify for matching… I’ll start by showing the filtered statistics matching behavior using the following statistics object:

USE [Credit];
GO

CREATE STATISTICS fstat_charge_provider_no
ON [dbo].[charge] ([member_no], [category_no])
WHERE [provider_no] = 484;
GO

The following query will use the statistics, and I’ve included the trace flags to show the usage from a query optimizer perspective:

SELECT DISTINCT
        1.[member_no],
        1.[category_no],
        1.[provider_no]
FROM    [dbo].[charge] AS c
WHERE   1.[provider_no] = 484 AND
        1.[member_no] = 9527 AND
        1.[category_no] = 2
OPTION  (QUERYTRACEON 3604, QUERYTRACEON 9204);
GO

This returns the following message output based on an initial compilation:

Stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 2, ColumnName: category_no, EmptyTable: FALSE

Stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 3, ColumnName: provider_no, EmptyTable: FALSE

Stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 6, ColumnName: member_no, EmptyTable: FALSE

Filtered stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 7, ColumnName: member_no, 
Expr: ([provider_no]=(484)), EmptyTable: FALSE

Filtered stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 7, ColumnName: member_no, 
Expr: ([provider_no]=(484)), EmptyTable: FALSE

Filtered stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 7, ColumnName: member_no, 
Expr: ([provider_no]=(484)), EmptyTable: FALSE

Now let’s use a local variable instead for the provider_no predicate (used for the filtered statistics):

DECLARE @provider_no INT = 484;

SELECT DISTINCT
        1.[member_no],
        1.[category_no],
        1.[provider_no]
FROM    [dbo].[charge] AS c
WHERE   1.[provider_no] = @provider_no AND
        1.[member_no] = 9527 AND
        1.[category_no] = 2
OPTION  (QUERYTRACEON 3604, QUERYTRACEON 9204);
GO

This time we see the following trace flag 9204 output:

Stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 2, ColumnName: category_no, EmptyTable: FALSE

Stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 3, ColumnName: provider_no, EmptyTable: FALSE

Stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 6, ColumnName: member_no, EmptyTable: FALSE

Even though the provider_no value matched “484”, the filtered stats were not loaded as the value was not known at compile time. This is one specific scenario – so if you have a different one on this subject, please feel free to share on the comments of this post.

Thanks!

In this post I’ll just share the results of a simple set of tests regarding automatic-update sampling.  Nothing fancy – I just populated the category table from the Credit sample database with varying row counts and then took note of the associated duration and sampling percent.  The query I used to kick of auto-updates after the row inserts was the following:

    
SELECT  [category_no],
[category_desc],
[category_code]
FROM [dbo].[category]
WHERE [category_desc] = 'Meals'
OPTION (RECOMPILE);

I also show the StatMan MAXDOP just for kicks (once true sampling kicks in, it is set to MAXDOP “1”, so not terribly interesting). I executed a few test iterations beyond what I’m showing here, but I just include two test runs per scenario just for comparison:

A few comments:

• Notice the sample percent is identical between identical row/page tests (each test started with a restore to the same base state)
• Notice that as the data pages grow, the duration of the stats update doesn’t grow significantly, and then consider the sample percentage multiplied by the total data page value (for example – ~1757 data pages for 40M vs. ~2098 data pages for 80M)
• Duration is just based on my laptop and I was more interested in the relative duration across test runs

Now what happens if I rebuild the index with a low fill factor? (Just doing this on the 80M row version):

    

USE [Credit]
GO
ALTER INDEX [category_ident] ON [dbo].[category]
REBUILD PARTITION = ALL
WITH (FILLFACTOR = 10);
GO

I’ll do a non-updating update so that I can get the threshold for the column updates incremented and thus kick off an auto-update of statistics:

  
UPDATE TOP (16000500) [category]
SET [category_desc] = [category_desc];
GO

Here are the results (Test ID 3 in bold):

Same row count, significantly more pages, lower duration (interesting) and a reduced table sample percent – but if you do the math, more pages than the previous samplings. Lots of other fun things we could try, but that’s all for now…

I’ve been mulling over a potential Microsoft Connect item regarding selectivity guess warnings, but I’m uncertain if it would have enough demand to drive product team attention.  Selectivity guesses, which I talked a little about in the “Selectivity Guesses in absence of Statistics” post, are sometimes referred to as “magic numbers”, “heuristics” or just plain old QO guesses.

When a guess occurs, I think it would be interesting\helpful to know when it happens, especially considering the impact on query plan quality.  And while I’ve seen a high number of SQL Server instances over the last 16 years, I couldn’t tell you how many had bad plans due to selectivity guesses.  Assuming that there are supporting statistics – one would hope that selectivity guesses at the leaf-level of a plan should be infrequent.  And yet I feel it would be nice to confirm this through an exposed mechanism when it happens at the non-leaf levels.  For example – just knowing when the Query Optimizer says “ I don’t know what estimate to use for this Filter, so I’ll use a selectivity guess and put a warning in the plan.”

In theory, one could comb a plan’s XML and pull out the estimates vs. cardinality of the child operator (or if the leaf-level, the table cardinality).  Then those numbers could be evaluated for potential matches on known patterns.

And before we begin, I just want to confirm that this is not to be considered an actual real-world solution.  I’ll explain why later – but in the meantime just view these demo queries as a rough draft thrown together for use in exploring a proof-of-concept.

Let’s say you have a table that is missing statistics entirely – and auto-creation of statistics are disabled.  This is an upstream issue that we know has a fix – but in this scenario, it has not been addressed. Now let’s take a sprawling query example that hits a few different patterns that would generate known selectivity guesses in absence of statistics:

SELECT  [charge_no],
        [member_no],
        [provider_no],
        [category_no],
        SUM([charge_amt]) AS [total_charge_amt]
FROM    (SELECT [charge_no],
                [member_no],
                [provider_no],
                [category_no],
                [charge_dt],
                [charge_amt],
                [statement_no],
                [charge_code]
         FROM   dbo.[charge_guess]
         WHERE  [provider_no] = 500
         UNION
         SELECT [charge_no],
                [member_no],
                [provider_no],
                [category_no],
                [charge_dt],
                [charge_amt],
                [statement_no],
                [charge_code]
         FROM   dbo.[charge_guess]
         WHERE  [statement_no] BETWEEN 500 AND 1000
         UNION
         SELECT [charge_no],
                [member_no],
                [provider_no],
                [category_no],
                [charge_dt],
                [charge_amt],
                [statement_no],
                [charge_code]
         FROM   dbo.[charge_guess]
         WHERE  [charge_amt] < 100000
         UNION
         SELECT [charge_no],
                [member_no],
                [provider_no],
                [category_no],
                [charge_dt],
                [charge_amt],
                [statement_no],
                [charge_code]
         FROM   dbo.[charge_guess]
         WHERE  [category_no] LIKE 10) AS t
GROUP BY [charge_no],
        [member_no],
        [provider_no],
        [category_no]
HAVING  SUM([charge_amt]) > 3999.99;
GO

Now that this is in cache, let me extract the different operators and associated attributes into a temporary table:

WITH XMLNAMESPACES 
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')    
 SELECT s.[value]('@NodeId', 'int') AS [NodeId], 
		s.[value]('@PhysicalOp', 'nvarchar(128)') AS [PhysicalOp],
		s.[value]('@EstimateRows', 'real') AS [EstimateRows],
		s.[value]('@TableCardinality', 'real') AS [TableCardinality],
		c.[plan_handle], 
		q.[query_plan],
		t.text
 INTO #ProcessResults
 FROM sys.dm_exec_cached_plans AS 1 
 CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS [q] 
 CROSS APPLY query_plan.nodes('//RelOp') AS batch(s) 
 CROSS APPLY sys.[dm_exec_sql_text](c.plan_handle) AS [t] 
 WHERE t.text	LIKE '%provider_no%'
 OPTION(MAXDOP 1, RECOMPILE);
 GO

Next – let me look at a few common selectivity guess percentages:

-- Common guesses - not all inclusive!
SELECT  plan_handle,
		NodeId,
		PhysicalOp,
		EstimateRows AS EstimateRows,
		COALESCE(TableCardinality, 
		         LAG(EstimateRows, 1,0) OVER (ORDER BY NodeId DESC)) 
			AS Cardinality,
		CASE
			CAST(EstimateRows / COALESCE(TableCardinality, 
		         LAG(EstimateRows, 1,0) OVER (ORDER BY YEAR(NodeId)))
				 AS numeric (24,4))
			WHEN 0.0316 THEN 'Potential Selectivity Guess'
			WHEN 0.09 THEN 'Potential Selectivity Guess'
			WHEN 0.30 THEN 'Potential Selectivity Guess'
			WHEN 0.10 THEN 'Potential Selectivity Guess'
			ELSE '-' 
		END AS 'Potential Selectivity Guess?'
FROM [#ProcessResults] AS pr
ORDER BY NodeId DESC;

Looking at the results, I can see a few patterns for applicable operators that may be doing selectivity guesses:

So the demo query found where the guesses were originating at the leaf level for table scans due to four different expression patterns – and also caught a potential selectivity guess for the Filter operator further down towards the root of the plan.

This solution is imperfect though:

• I’m just pulling a few different known selectivity guess percentages.  I don’t know all the scenarios and I’m definitely missing some of them.  Not to mention the fact that some of the potential predicates would involve more sophisticated evaluation in order to detect potential selectivity guesses.
• Each Table Scan also had a “Columns With No Statistics” warning (at Node 20, 16, 13, and 10).  I would argue that this would be a good red flag in and of itself.

So why even raise this discussion in the first place?

• While the “Columns With No Statistics” warning surfaces for my table access, it didn’t surface for the Filter operator (Node 1).
• There are scenarios in the intermediate levels of a plan where I would love to know when the query optimizer has resorted to pure guesses.

Again, I’m not entirely sure a Connect item would be anything but a symbolic act – and perhaps justly so – but I’m curious to hear from those of you who have confirmed selectivity guess issues for their plans – and most particularly for scenarios where there aren’t other symptoms (for example, no associated warnings that might tip you off to an upstream issue).

My prior post demonstrated the influence of row and page counts on estimated CPU and I/O cost.  Now in this post I’m going to step through the influence of row and page counts on memory grants for a Sort operator.  I’ll be using the MemoryGrantInfo element from the query execution plan to measure the influence.  I wrote about this element in my “Memory Grant Execution Plan Statistics” blog post.

Starting State

I’ll be querying the member table which has 142 data pages and 10,000 rows.  The query itself will have an ORDER BY curr_balance.  The curr_balance column is currently not supported by an existing index and will require a Sort operator in the plan, which then means that a memory grant will be needed:

SELECT  [member].[member_no],
[member].[lastname],
[curr_balance]
FROM    [dbo].[member]
ORDER BY [curr_balance] ASC
OPTION  (RECOMPILE, MAXDOP 1);

Using an actual execution plan, I see the following MemoryGrantInfo data for the baseline query:

<MemoryGrantInfo
SerialRequiredMemory=”512″
SerialDesiredMemory=”1904″
RequiredMemory=”512″
DesiredMemory=”1904″
RequestedMemory=”1904″
GrantWaitTime=”0″
GrantedMemory=”1904″
MaxUsedMemory=”680″ />

I’m forcing a serial plan, so you’ll see that the SerialRequiredMemory matches the RequiredMemory attribute and the SerialDesiredMemory matches the DesiredMemory attribute (and others as well).  I used this hint for this blog post to maintain an apples-to-apples comparison (serial plan vs. serial plan).

The Sort operator has the following CPU and I/O costs (not including subtree costs):

• Estimated CPU Cost = 0.60342
• Estimated I/O Cost = 0.0112613

Inflating the page count while maintaining the row count

What happens if we inflate the page count while maintaining the row count?

-- Please don't use this in production - for demonstration purposes only!
UPDATE STATISTICS dbo.[member]
WITH ROWCOUNT = 10000, PAGECOUNT = 149000;

Re-executing the query, I see the following memory grant statistics:

<MemoryGrantInfo
SerialRequiredMemory=”512″
SerialDesiredMemory=”1904″
RequiredMemory=”512″
DesiredMemory=”1904″
RequestedMemory=”1904″
GrantWaitTime=”0″
GrantedMemory=”1904″
MaxUsedMemory=”680″ />

The memory grant statistics for the 149000 page plan are identical to the 142 page plan.

The Sort operator has the following CPU and I/O costs (excluding subtree costs):

• Estimated CPU Cost = 0.60342
• Estimated I/O Cost = 0.0112613

So we see identical CPU and I/O costs for the Sort operator even with inflated page counts – even though the leaf-level Clustered Index Scan itself has an inflated estimated I/O cost (110.373).

Inflating the row count while maintaining the page count

What happens if I inflate the row count while maintaining the page count (at the original 142 pages)?

  
UPDATE STATISTICS dbo.[member]
WITH ROWCOUNT = 100000000, PAGECOUNT = 142;

This time we see a difference:

<MemoryGrantInfo
SerialRequiredMemory=”512″
SerialDesiredMemory=”8564592″
RequiredMemory=”512″
DesiredMemory=”8564592″
RequestedMemory=”4579608″
GrantWaitTime=”0″
GrantedMemory=”4579608″
MaxUsedMemory=”680″ />

SerialRequiredMemory and RequiredMemory are the same as prior scenarios (512) – but we see a big spike in SerialDesiredMemory, DesiredMemory, RequestedMemory and GrantedMemory.

The Sort operator has the following CPU and I/O costs (excluding subtree costs):

• Estimated CPU Cost = 5003.06
• Estimated I/O Cost = 7875.01

So we see that the Sort operator CPU and I/O cost is sensitive to row counts, but not data page counts (and the leaf-level Clustered Index Scan shows a 0.107569 estimated I/O cost and a 110 estimated CPU cost).

Summary

So recapping what we see when manipulating row and page counts for a query that uses a Sort operator:

• The memory grant introduced by the Sort operator was sensitive to row counts, but not page counts.
• The Sort operator’s estimated CPU and I/O costs were both sensitive to row counts, but not page counts.