SQL Server 2008 R2 SP1 and SQL Server 2012 RTM+ includes actual row count statistics in the sys.dm_exec_query_stats dynamic management view.\u00a0 This includes the total_rows, last_rows, min_rows and max_rows columns, which are intuitively named.<\/p>\n

One potential use of the row statistics data is to detect cardinality estimate issues where the actual count of rows is significantly different from the query plan\u2019s estimated rows.\u00a0 While I can see using this information as a valid approach, there are limits, and I\u2019ll walk through them in this post.<\/p>\n

To illustrate the use cases, I\u2019m going to use the Credit<\/a> database to execute three different queries.\u00a0 I\u2019ll show you the plan tree tab from SQL Sentry Plan Explorer<\/a> for each query.<\/p>\n

Query 1: No Cardinality Estimate Issue<\/strong><\/p>\n

\r\nSELECT  region.region_name,\r\n        member.lastname,\r\n        member.firstname,\r\n        member.member_no\r\nFROM    dbo.member\r\nINNER JOIN dbo.region\r\n        ON region.region_no = member.region_no\r\nWHERE   region.region_no = 9;\r\nGO\r\n<\/pre>\n
<\/pre>\n
The plan:<\/p>\n
<\/a><\/p>\n
For each operation in this query, the estimated rows match the actual rows.<\/p>\n
Query 2: Cardinality Estimate Issue, Leaf-Level + Final Operator<\/strong><\/p>\n
\r\nDECLARE @Column INT = 2,\r\n    @Value INT = 10;\r\n\r\nSELECT  [member].[member_no],\r\n        [member].[street],\r\n        [member].[city],\r\n        [charge].[charge_no],\r\n        [charge].[provider_no],\r\n        [charge].[category_no],\r\n        [charge].[charge_dt],\r\n        [charge].[charge_amt],\r\n        [charge].[charge_code]\r\nFROM    [dbo].[charge]\r\nINNER JOIN [dbo].[member]\r\n        ON [member].[member_no] = [charge].[member_no]\r\nWHERE   CHOOSE(@Column, [charge].[provider_no], [charge].[category_no]) = @Value;\r\nGO\r\n\r\n<\/pre>\n
The plan:<\/p>\n
<\/a><\/p>\n
Unlike the previous query, for this query we have leaf-level estimated vs. actual row count skews and skews that flow up to the final operation.<\/p>\n
Query 3: Cardinality Estimate Leaf-Level Skew and No Skew for Root Operator<\/strong><\/p>\n
\r\nSELECT TOP (1000)\r\n        [member].[member_no],\r\n        [member].[lastname],\r\n        [member].[firstname],\r\n        [region].[region_no],\r\n        [region].[region_name],\r\n        [provider].[provider_name],\r\n        [category].[category_desc],\r\n        [charge].[charge_no],\r\n        [charge].[provider_no],\r\n        [charge].[category_no],\r\n        [charge].[charge_dt],\r\n        [charge].[charge_amt],\r\n        [charge].[charge_code]\r\nFROM    [dbo].[provider]\r\nINNER JOIN [dbo].[charge]\r\n        ON [provider].[provider_no] = [charge].[provider_no]\r\nINNER JOIN [dbo].[member]\r\n        ON [member].[member_no] = [charge].[member_no]\r\nINNER JOIN [dbo].[region]\r\n        ON [region].[region_no] = [member].[region_no]\r\nINNER JOIN [dbo].[category]\r\n        ON [category].[category_no] = [charge].[category_no];\r\nGO\r\n\r\n<\/pre>\n
The plan:<\/p>\n
<\/a><\/p>\n
For this third query, we see leaf-level skews (and intermediate-level for the Hash Match), but then the root of the plan does NOT have a skew (estimated 1,000 vs. actual 1,000).<\/p>\n
Detecting Issues<\/strong><\/p>\n
So in my next query, I pull the estimated rows out from the query execution plan of the three queries and compare it to the last actual row count values from sys.dm_exec_query_stats (and I\u2019m keeping this example query as simple as possible):<\/p>\n
\r\nSELECT  t.text,\r\n\t\tp.[query_plan],\r\n\t\ts.[last_execution_time],\r\n\t\tp.[query_plan].value('(\/\/@EstimateRows)[1]', 'varchar(128)') AS [estimated_rows],\r\n\t\ts.[last_rows]\r\nFROM    sys.[dm_exec_query_stats] AS [s]\r\nCROSS APPLY sys.[dm_exec_sql_text](sql_handle) AS [t]\r\nCROSS APPLY sys.[dm_exec_query_plan](plan_handle) AS [p]\r\nWHERE   DATEDIFF(mi, s.[last_execution_time], GETDATE()) < 1\r\nGO\r\n<\/pre>\n
The results are as follows:<\/p>\n
<\/a><\/p>\n
So what do we see here?\u00a0 We show a final skew for just one query (Query #2).\u00a0 The other two queries show no final skew.\u00a0 For Query #1 \u2013 that is an accurate assessment, but for Query #3 the underlying skew is hidden since we\u2019re only looking at the final operator estimated rows vs. actual.\u00a0 So our actual row count stats are still useful \u2013 but for only one specific skew scenario.<\/p>\n
Why Does this Matter?<\/strong><\/p>\n
Even if the query optimizer estimates final row counts accurately, the leaf-level skews can drive performance issues.\u00a0 For example \u2013 for leaf-level or intermediate skews, you may be under-estimating memory grants sizes (risk of spills) or actually over-estimating memory grant sizes (potential concurrency issues for larger grant queries).\u00a0 Another example may be the seek vs scan decision, with unnecessary I\/O being driven by leaf-level skews.<\/p>\n
So to summarize, the additional sys.dm_exec_query_stats data is useful for situations where we can detect the skew at the root of the query plan, but it doesn\u2019t help you identify leaf-level and intermediate-level skews.<\/p>\n","protected":false},"excerpt":{"rendered":"
SQL Server 2008 R2 SP1 and SQL Server 2012 RTM+ includes actual row count statistics in the sys.dm_exec_query_stats dynamic management view.\u00a0 This includes the total_rows, last_rows, min_rows and max_rows columns, which are intuitively named. One potential use of the row statistics data is to detect cardinality estimate issues where the actual count of rows is […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[42,28],"tags":[],"class_list":["post-462","post","type-post","status-publish","format-standard","hentry","category-cardinality-estimation","category-performance"],"yoast_head":"\n