{"id":850,"date":"2013-01-10T15:16:09","date_gmt":"2013-01-10T23:16:09","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/joe\/?p=850"},"modified":"2013-12-29T19:18:14","modified_gmt":"2013-12-30T03:18:14","slug":"detecting-selectivity-guesses","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/detecting-selectivity-guesses\/","title":{"rendered":"Detecting Selectivity Guesses"},"content":{"rendered":"

I\u2019ve been mulling over a potential Microsoft Connect item regarding selectivity guess warnings, but I\u2019m uncertain if it would have enough demand to drive product team attention.\u00a0 Selectivity guesses, which I talked a little about in the \u201cSelectivity Guesses in absence of Statistics<\/a>\u201d post, are sometimes referred to as \u201cmagic numbers\u201d, \u201cheuristics\u201d or just plain old QO guesses.<\/p>\n

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

In theory, one could comb a plan\u2019s XML and pull out the estimates vs. cardinality of the child operator (or if the leaf-level, the table cardinality).\u00a0 Then those numbers could be evaluated for potential matches on known patterns.<\/p>\n

And before we begin, I just want to confirm that this is not to be considered an actual real-world solution<\/em>.\u00a0 I\u2019ll explain why later \u2013 but in the meantime just view these demo queries as a rough draft thrown together for use in exploring a proof-of-concept.<\/p>\n

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

\r\nSELECT  [charge_no],\r\n        [member_no],\r\n        [provider_no],\r\n        [category_no],\r\n        SUM([charge_amt]) AS [total_charge_amt]\r\nFROM    (SELECT [charge_no],\r\n                [member_no],\r\n                [provider_no],\r\n                [category_no],\r\n                [charge_dt],\r\n                [charge_amt],\r\n                [statement_no],\r\n                [charge_code]\r\n         FROM   dbo.[charge_guess]\r\n         WHERE  [provider_no] = 500\r\n         UNION\r\n         SELECT [charge_no],\r\n                [member_no],\r\n                [provider_no],\r\n                [category_no],\r\n                [charge_dt],\r\n                [charge_amt],\r\n                [statement_no],\r\n                [charge_code]\r\n         FROM   dbo.[charge_guess]\r\n         WHERE  [statement_no] BETWEEN 500 AND 1000\r\n         UNION\r\n         SELECT [charge_no],\r\n                [member_no],\r\n                [provider_no],\r\n                [category_no],\r\n                [charge_dt],\r\n                [charge_amt],\r\n                [statement_no],\r\n                [charge_code]\r\n         FROM   dbo.[charge_guess]\r\n         WHERE  [charge_amt] < 100000\r\n         UNION\r\n         SELECT [charge_no],\r\n                [member_no],\r\n                [provider_no],\r\n                [category_no],\r\n                [charge_dt],\r\n                [charge_amt],\r\n                [statement_no],\r\n                [charge_code]\r\n         FROM   dbo.[charge_guess]\r\n         WHERE  [category_no] LIKE 10) AS t\r\nGROUP BY [charge_no],\r\n        [member_no],\r\n        [provider_no],\r\n        [category_no]\r\nHAVING  SUM([charge_amt]) > 3999.99;\r\nGO\r\n\r\n<\/pre>\n
Now that this is in cache, let me extract the different operators and associated attributes into a temporary table:<\/p>\n
\r\nWITH XMLNAMESPACES\r\n    (DEFAULT 'http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan')\r\n SELECT s.[value]('@NodeId', 'int') AS [NodeId],\r\n\t\ts.[value]('@PhysicalOp', 'nvarchar(128)') AS [PhysicalOp],\r\n\t\ts.[value]('@EstimateRows', 'real') AS [EstimateRows],\r\n\t\ts.[value]('@TableCardinality', 'real') AS [TableCardinality],\r\n\t\tc.[plan_handle],\r\n\t\tq.[query_plan],\r\n\t\tt.text\r\n INTO #ProcessResults\r\n FROM sys.dm_exec_cached_plans AS [c]\r\n CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS [q]\r\n CROSS APPLY query_plan.nodes('\/\/RelOp') AS batch(s)\r\n CROSS APPLY sys.[dm_exec_sql_text](c.plan_handle) AS [t]\r\n WHERE t.text\tLIKE '%provider_no%'\r\n OPTION(MAXDOP 1, RECOMPILE);\r\n GO\r\n<\/pre>\n
Next \u2013 let me look at a few common selectivity guess percentages:<\/p>\n
\r\n-- Common guesses - not all inclusive!\r\nSELECT  plan_handle,\r\n\t\tNodeId,\r\n\t\tPhysicalOp,\r\n\t\tEstimateRows AS EstimateRows,\r\n\t\tCOALESCE(TableCardinality,\r\n\t\t         LAG(EstimateRows, 1,0) OVER (ORDER BY NodeId DESC))\r\n\t\t\tAS Cardinality,\r\n\t\tCASE\r\n\t\t\tCAST(EstimateRows \/ COALESCE(TableCardinality,\r\n\t\t         LAG(EstimateRows, 1,0) OVER (ORDER BY YEAR(NodeId)))\r\n\t\t\t\t AS numeric (24,4))\r\n\t\t\tWHEN 0.0316 THEN 'Potential Selectivity Guess'\r\n\t\t\tWHEN 0.09 THEN 'Potential Selectivity Guess'\r\n\t\t\tWHEN 0.30 THEN 'Potential Selectivity Guess'\r\n\t\t\tWHEN 0.10 THEN 'Potential Selectivity Guess'\r\n\t\t\tELSE '-'\r\n\t\tEND AS 'Potential Selectivity Guess?'\r\nFROM [#ProcessResults] AS pr\r\nORDER BY NodeId DESC;\r\n<\/pre>\n
Looking at the results, I can see a few patterns for applicable operators that may be doing selectivity guesses:<\/p>\n
\"image\"<\/a><\/p>\n
So the demo query found where the guesses were originating at the leaf level for table scans due to four different expression patterns \u2013 and also caught a potential selectivity guess for the Filter operator further down towards the root of the plan.<\/p>\n
This solution is imperfect though:<\/p>\n