{"action":"created","issue":{"url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/334","repository_url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit","labels_url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/334/labels{/name}","comments_url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/334/comments","events_url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/334/events","html_url":"https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/334","id":162945622,"number":334,"title":"BlitzCache - Unparameterized Query warning doesn't seem to trigger","user":{"login":"BlitzErik","id":11590415,"avatar_url":"https://avatars.githubusercontent.com/u/11590415?v=3","gravatar_id":"","url":"https://api.github.com/users/BlitzErik","html_url":"https://github.com/BlitzErik","followers_url":"https://api.github.com/users/BlitzErik/followers","following_url":"https://api.github.com/users/BlitzErik/following{/other_user}","gists_url":"https://api.github.com/users/BlitzErik/gists{/gist_id}","starred_url":"https://api.github.com/users/BlitzErik/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/BlitzErik/subscriptions","organizations_url":"https://api.github.com/users/BlitzErik/orgs","repos_url":"https://api.github.com/users/BlitzErik/repos","events_url":"https://api.github.com/users/BlitzErik/events{/privacy}","received_events_url":"https://api.github.com/users/BlitzErik/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit/labels/bug","name":"bug","color":"fc2929"},{"url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit/labels/help%20wanted","name":"help wanted","color":"159818"},{"url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit/labels/sp_BlitzCache","name":"sp_BlitzCache","color":"009800"}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2016-06-29T14:50:36Z","updated_at":"2016-06-29T19:30:41Z","closed_at":null,"body":"Problem: Unparameterized Query warning is not populating in BlitzCache, but the [separate query we have to detect them does](https://www.brentozar.com/blitzcache/unparameterized-queries/).\r\n\r\nRepro script:\r\n\r\nOur unparameterized query\r\n```\r\nDBCC FREEPROCCACHE\r\n\r\nDECLARE @i INT = 0\r\nDECLARE @s VARCHAR(50) = ''\r\nSELECT @i = (SELECT MAX(Id) FROM [dbo].[VoteTypes] AS [vt])\r\nSELECT @s = (SELECT SUBSTRING('ApproveEditSuggestion', 1, 21))\r\n\r\nSELECT [Id]\r\n      ,[Name]\r\n  FROM [StackOverflow].[dbo].[VoteTypes]\r\n  WHERE [Id] = @i\r\n  AND [Name] = @s\r\nOPTION(OPTIMIZE FOR UNKNOWN)\r\n```\r\n\r\nDetection:\r\n\r\n```\r\nEXEC [master].[dbo].[sp_BlitzCache] @DatabaseName = 'StackOverflow', @ExpertMode = 2, @HideSummary = 1\r\n\r\n;WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)\r\nSELECT  st.text,\r\n        qp.query_plan,\r\n        qs.*\r\nFROM    (\r\n    SELECT  TOP 50 *\r\n    FROM    sys.dm_exec_query_stats\r\n    ORDER BY total_worker_time DESC\r\n) AS qs\r\nCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st\r\nCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp\r\nWHERE (\r\n  query_plan.exist(\r\n  '//p:StmtSimple[@StatementOptmLevel[.=\"FULL\"]]/p:QueryPlan/p:ParameterList') = 1\r\n  AND  query_plan.exist(\r\n  '//p:StmtSimple[@StatementOptmLevel[.=\"FULL\"]]/p:QueryPlan/p:ParameterList/p:ColumnReference') = 0\r\n)\r\nOR\r\n(\r\n  query_plan.exist(\r\n  '//p:StmtSimple[@StatementOptmLevel[.=\"FULL\"]]/p:QueryPlan/p:ParameterList') = 0\r\n  AND query_plan.exist(\r\n  '//p:StmtSimple[@StatementOptmLevel[.=\"FULL\"]]/*/p:RelOp/descendant::p:ScalarOperator/p:Identifier/p:ColumnReference[contains(@Column, \"@\")]')= 1\r\n)\r\nOPTION(RECOMPILE)\r\n\r\n```\r\n\r\nResults:\r\nNo warning in BlitzCache, but the lone query successfully finds them in cache.\r\n![image](https://cloud.githubusercontent.com/assets/11590415/16456815/3df30d3c-3de7-11e6-8497-e4d4697baba8.png)\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/comments/229462954","html_url":"https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/334#issuecomment-229462954","issue_url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/334","id":229462954,"user":{"login":"BlitzErik","id":11590415,"avatar_url":"https://avatars.githubusercontent.com/u/11590415?v=3","gravatar_id":"","url":"https://api.github.com/users/BlitzErik","html_url":"https://github.com/BlitzErik","followers_url":"https://api.github.com/users/BlitzErik/followers","following_url":"https://api.github.com/users/BlitzErik/following{/other_user}","gists_url":"https://api.github.com/users/BlitzErik/gists{/gist_id}","starred_url":"https://api.github.com/users/BlitzErik/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/BlitzErik/subscriptions","organizations_url":"https://api.github.com/users/BlitzErik/orgs","repos_url":"https://api.github.com/users/BlitzErik/repos","events_url":"https://api.github.com/users/BlitzErik/events{/privacy}","received_events_url":"https://api.github.com/users/BlitzErik/received_events","type":"User","site_admin":false},"created_at":"2016-06-29T19:30:41Z","updated_at":"2016-06-29T19:30:41Z","body":"I think I have this fixed, but the query is ugly. XML is the antichrist. It was a problem with (I think) aggregation. Yeehaw.\r\n\r\n```\r\n;WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)\r\n, c1 AS (\r\nSELECT \r\nQueryPlanCost_check = CASE WHEN QueryType LIKE '%Stored Procedure%' THEN\r\n                        statement.value('sum(/p:StmtSimple/@StatementSubTreeCost)', 'float')\r\n                     ELSE\r\n                        statement.value('sum(/p:StmtSimple[xs:hexBinary(substring(@QueryPlanHash, 3)) = xs:hexBinary(sql:column(\"QueryPlanHash\"))]/@StatementSubTreeCost)', 'float')\r\n\t\t\t\t\t  END ,\r\ncompile_timeout_check = CASE WHEN statement.exist('/p:StmtSimple/@StatementOptmEarlyAbortReason[.=\"TimeOut\"]') = 1 THEN 1 END ,\r\ncompile_memory_limit_exceeded_check = CASE WHEN statement.exist('/p:StmtSimple/@StatementOptmEarlyAbortReason[.=\"MemoryLimitExceeded\"]') = 1 THEN 1 END ,\r\nunmatched_index_count_check = statement.value('count(//p:UnmatchedIndexes/Parameterization/Object)', 'int') ,\r\nis_trivial_check = CASE WHEN statement.exist('/p:StmtSimple[@StatementOptmLevel[.=\"TRIVIAL\"]]/p:QueryPlan/p:ParameterList') = 1 THEN 1 END ,\r\nunparameterized_query_check = CASE WHEN statement.exist('//p:StmtSimple[@StatementOptmLevel[.=\"FULL\"]]/p:QueryPlan/p:ParameterList') = 1 AND\r\n                                  statement.exist('//p:StmtSimple[@StatementOptmLevel[.=\"FULL\"]]/p:QueryPlan/p:ParameterList/p:ColumnReference') = 0 THEN 1\r\n                             WHEN statement.exist('//p:StmtSimple[@StatementOptmLevel[.=\"FULL\"]]/p:QueryPlan/p:ParameterList') = 0 AND\r\n                                  statement.exist('//p:StmtSimple[@StatementOptmLevel[.=\"FULL\"]]/*/p:RelOp/descendant::p:ScalarOperator/p:Identifier/p:ColumnReference[contains(@Column, \"@\")]') = 1 THEN 1\r\n                        END,\r\ns.QueryHash\r\nFROM  #statements s\r\nJOIN ##bou_BlitzCacheProcs b\r\nON b.QueryHash = s.QueryHash\r\n), c2 AS (\r\nSELECT\r\n\t   QueryHash,\r\n\t   QueryPlanCost = MAX([c1].[QueryPlanCost_check]) ,\r\n       compile_timeout = MAX([c1].[compile_timeout_check]) ,\r\n       compile_memory_limit_exceeded = MAX([c1].[compile_memory_limit_exceeded_check]) ,\r\n       unmatched_index_count = MAX([c1].[unmatched_index_count_check]) ,\r\n       is_trivial = MAX([c1].[is_trivial_check]) ,\r\n       unparameterized_query = MAX([c1].[unparameterized_query_check])\r\nFROM c1\r\nGROUP BY QueryHash\r\n)\r\nUPDATE b\r\nSET\t\r\nb.QueryPlanCost = c2.QueryPlanCost,\r\nb.compile_timeout = c2.compile_timeout,\r\nb.compile_memory_limit_exceeded = c2.compile_memory_limit_exceeded,\r\nb.unmatched_index_count = c2.unmatched_index_count,\r\nb.is_trivial = c2.is_trivial,\r\nb.unparameterized_query = c2.unparameterized_query\r\nFROM [c2] AS c2\r\nJOIN ##bou_BlitzCacheProcs AS b\r\nON c2.QueryHash = b.QueryHash\r\nOPTION (RECOMPILE);\r\n```\r\n\r\nThis didn't appear to be updating some items correctly. This may be why stored procedures never had costs, too, I think, but I'd have to test. Anyway, by using the query above, we get the correct warning. This also requires a fix to [this issue](https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/331) to remove the duplicate check (was ending up with '23, 23' in the opserver warnings line.\r\n\r\nAnyway, new results look correct:\r\n![image](https://cloud.githubusercontent.com/assets/11590415/16465778/6700ac3a-3e0e-11e6-89a2-421fe256be88.png)\r\n\r\nGoing to fork dev.\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n"}}
{"actor":{"display_login":"BlitzErik"}}