SQL Exec Stats
Copyright © 2006-2010 Elemental Inc.
All rights reserved.
Email: jchang@qdpma.com or jchang6@yahoo.com
License
The license to use this software is free. Elemental Inc. (www.QDPMA.com) assumes no liability for use.
About
The Distributed Management Views and functions (DMV and DMF) have greatly simplified the process of collecting performance data sufficient for basic assessment and SQL query and index tuning. Still, even the modest effort to collecting a sufficient set of information is not completely turn-key that people do this on a regular basis. Also, query tuning requires the additional effort to examine the execution plans.
Index tuning involves both indexes that should be added or expanded, and indexes that should be removed or consolidated. The DMVs provide some recommendations on missing indexes, but this is not always reliable, so a live expert is still essential. Identifying indexes not used is simple. The other part is identifying infrequently used indexes that could also be dropped or consolidated. This would require finding the execution plans that reference the infrequently used indexes to assess the implications or impact.
Much of this can be automated with the SQL Exec Stats so that regular performance data collection is nearly a zero effort routine, including correlation of the execution statistics in the DMV with information from the execution plans, and a cross reference of execution plans that reference each index. Finally, if performance expertise is not available onsite, the set of information is sufficiently compact to be sent by email or ftp for full assessment.
Requirements
- Either SQL Server 2005 any build, service pack 2 or later prefered (statement level query plan).
- or SQL Server 2008 any build, service pack 1 preferred (worker time for parallel execution plans).
- .NET Framework 3.0, (older version works with 2.0)
Supported versions are SQL Server 2005 (service pack 2 or later preferred), and SQL Server 2008 (service pack 1 or later preferred). SQL Server 2008 SP1 now correctly reports worker time for parallels execution plans. Previous 2005 and 2008 versions typically reported zero, negating the ability to weight by CPU correctly when parallel plans are present.
References in previous versions to SMO were removed.
The SMO dlls are 32-bit only.
It is highly desired to run ExecStats in a 64-bit environment
as a large virtual address space is desired in order to handle very large amount of plan contents.
A new data structure requires .NET Framework 3.0.
The previous requirements were .NET Framework 2.0.
A component from Gembox (www.gemboxsoftware.com)
is employed to generate Excel files.
Objectives: What does SQL Exec Stats do?
- Automate collection of key DMVs for query tuning.
- Collates query exec stats with execution plan details.
- Cross reference index usage by execution plan, what plans reference infrequently used indexes.
- Saves XML plan for top queries.
- Save information directly to Microsoft Excel 2003 file format and in binary.
DMV and DMFs include:
- dm_exec_query_stats
- dm_exec_query_plan
- or dm_exec_text_query_plan
- dm_exec_sql_text
- dm_db_index_usage_stats
- dm_io_virtual_file_stats
The collation involves parsing the XML plan so that plan information can be displayed along with execution statistics, and to build a cross reference between index usage statistics and the execution plans that reference each index.
The performance data is saved as a compressed binary file and as a Microsoft Excel 2003 file. The top XML plans can be saved in the Sqlplan XML format that can be opened from SSMS.
Comments
The DMV dm_exec_query_stats does not a replace for SQL Server Profiler and Trace. the Profiler/Trace can capture every statement or batch. The DMV will only maintain the overall summary. The DMV may miss SQL and RPC evicted from the procedure cache or will lose history for recompiles. Still, dm_exec_query_stats is easy to use, the data is already available on an active system, and is sufficiently useful in many situations.
Sure, SQL Server 2008 has the Management Data Warehouse along with several built-in reports. My tools does some similar functions, some different, but the main difference is portatibility of data for remote tuning.
Purpose: Why do we need or want this?
- Simplifies performance data collection (sufficiently complete)
- Encourages regular collection of performance data, i.e. keep a history of system health
- Automates several elements of performance analysis (reduces time and effort)
- Enable remote tuning assistance, key information is compact enough to be sent by email
The intent is that later versions of this utility will have the ability to compare execution and index usage statistics on a day-to-day basis. Another possibility is to have some of the capability of Database Tuning Assistant, with the ability to identify situations where rewriting SQL is the proper resolution, not just index or partition tuning.
Introduction
Everyone should know by now how really useful the DMVs dm_exec_query_stats, and dm_db_index_usage_stats and the associated DMFs for obtaining the SQL and XML plan: dm_exec_sql_text, dm_exec_query_plan, and dm_exec_text_query_plan. Of course it has been explained that dm_exec_query_stats is not a replacement for SQL Server Profiler and SQL Trace. The DMV is only reliable if execution plans are not frequently evicted from the procedure cache and if SQL is not frequently recompiled.
Still the DMV dm_exec_query_stats is popular because the results are available with a simple query. The complexity of setting up a trace, making sure it is not adversely impacting server performance, making sure space is available, and most of all, the effort of parsing the trace are all avoided.
So what do we do next? From the top query statistics we can start looking at the SQL and the execution plans. One minor annoyance is that if we use CROSS APPLY to dm_exec_query_plan, each XML plan has to be saved individually. This can be annoying if one has to remote into to a PC in the server room and this connection is slow.
There is much information in the XML plan that would be helpful if we could see it in a grid with the execution statistics. From the index usage statistics, we can eliminate unused indexes. There might also be indexes that are infrequently used. Sometimes it is obvious that certain indexes can be consolidated. Other times, it is necessary to examine the execution plan to determine if another index is sufficient. The XML plan analysis cross-references in which plans each index is used. All of this is tedious work which can be automated. So this is the purpose of the SQLExecStats tools.
Keep in mind the missing index information is not an optimized list. DTA would do a proper analysis, while the missing indexes generator may apply more indexes than necessary. It would be nice to have a magic index tuning wizard, but nothing today replaces a good expert. Also, an automated tuning tool cannot question the developer as to what was meant by a specific query.
Configuration:
The file SQLStatsExec.exe.config contains the configuration values. Disregard the entry for Target. Source is the preferred SQL Server Instance. Database is for the preferred database. Detailed table and index information is extracted for that database. Execution statistics are instance-wide.
Main Screen
Below is the main screen. Target SQL Servers and Databases need to be set. Default security is SSPI, so the client must have proper permissions. Uncheck the SSPI box to set SQL Server user and password if desired. After the Target SQL Server is set, the button to the right of Databases can bring up a list of databases.
There are options for collecting the full XML Execution Plans (Exec Plans), Statistics Header (Stat Hdr) which contains the date each data distribution statistics was generated. Histogram information is currently not enabled.
The execution statistics can be statement level, batch level aggregation. A query hash aggregation will be considered for later addition. Another possibility is simply generating the execution plans for all stored procedures.
The default mode collects table and index information on one database, using the database in the config file or manually entered in the Databases drop box to the right of the Server box.
New in Build 2010-02-0x
On startup, the Debug Messages group Textbox at bottom will now display the host operating system and
.NET Framework version, example
Microsoft Windows NT 5.1.2600 Service Pack 3, Win32NT, 5.1.2600.196608, Service Pack 3
2.0.50727.3603, 2, 0, 50727, Size of IntPtr is 4
The Size of IntPtr should indicate whether the current process is running as full 64-bit or 32-bit, even if the underlying OS is 64-bit.
Step 1
Click the button ... to the right of the Databases box or the Multi-DB button
Select the database(s) for which detailed table and index information should be collected.
Step 2
Click the “Connect Test” button at the lower left to start
data collection. The SQL Exec Stats utility will collect all information and
save the results in Excel and binary. Later versions may allow manual selection
of which components to execute.
General Information
General system information is displayed in the grid to right of the Connection tab. The information include xp_msver, sys.configurations, and dm_os_sys_info.
The tab “OS Perf Ctrs” collects the point in time OS Performance Counters. Hopefully I can add regular sampling to display key performance counters over a period.
Databases
The “Databases” tab is point in time file IO information from dm_io_virtual_file_stats. Later versions should show file IO over time.
File IO
The “File IO” tab is point in time file IO information from dm_io_virtual_file_stats. Later versions should show file IO over time.
The SQL for this is in the text file output. Example below:
--File IO Stats
SELECT DB_NAME(v.database_id) AS [Database], m.name AS [Filename]
, v.database_id AS dbid, v.file_id AS [file], m.type, m.data_space_id as dsid, m.state
, CONVERT(real, (m.size*8./1024.)) AS [Size]
, num_of_reads AS Reads, num_of_bytes_read AS ReadBytes, io_stall_read_ms AS RdIOStall
, num_of_writes AS Writes, num_of_bytes_written AS WrBytes, io_stall_write_ms AS WrIOStall
, ISNULL(p.io_pending_ms_ticks, 0) AS IOPen_ms, ISNULL(p.io_pending, 0) AS IOPending
, ISNULL(IOPenCnt,0) AS IOPenCnt
FROM sys.dm_io_virtual_file_stats(NULL,NULL) v
INNER JOIN sys.master_files m ON m.database_id = v.database_id AND m.file_id = v.file_id
LEFT JOIN (
SELECT io_handle, COUNT(*) AS IOPenCnt , SUM(io_pending_ms_ticks) AS io_pending_ms_ticks
, SUM(io_pending) AS io_pending
FROM sys.dm_io_pending_io_requests
GROUP BY io_handle
) p ON p.io_handle = v.file_handle
ORDER BY v.database_id, v.file_id
Table Space
The tab “Table Space” lists space usage by table for the selected database.
The query for table space is as follows:
--Table Space used:
DECLARE @dbid int SELECT @dbid = DB_ID()
SELECT @dbid AS dbid , [Schema], [Object], object_id, [Type]
, [Rows], Reserved, Data, [Index] = index2, Unused = Reserved - Used
, Clust, IX_Cnt, StatCnt, Partitions, Hypothetical, create_date
FROM(
SELECT p.object_id, o.schema_id, t.name AS [Schema], o.name AS [Object], o.Type
, Reserved = SUM(p.reserved), Used = SUM(p.used)
, Data = SUM( CASE WHEN (p .index_id < 2)
THEN (p.in_row_data + p.lob_used + p.row_overflow_used)
ELSE p.lob_used + p.row_overflow_used END
)
, index2 = SUM(CASE WHEN (p.index_id > 1) THEN (p.in_row_data) ELSE 0 END)
, Rows = SUM( CASE WHEN (p.index_id < 2) THEN p.row_count ELSE 0 END )
, Clust = SUM (CASE p.index_id WHEN 1 THEN 1 ELSE 0 END)
, IX_Cnt = SUM (CASE WHEN (p.index_id>1) THEN 1 ELSE 0 END)
, StatCnt = (SELECT COUNT(*) FROM sys.stats s WHERE s.object_id = p.object_id)
, Partitions = MAX(Partitions), Hypothetical = SUM(CASE i.is_hypothetical WHEN 1 THEN 1 ELSE 0 END)
, o.create_date
FROM (
SELECT object_id, index_id, COUNT(*) AS Partitions
, reserved = 8*SUM(reserved_page_count), used = 8*SUM(used_page_count)
, in_row_data = 8*SUM(in_row_data_page_count) , lob_used = 8*SUM(lob_used_page_count)
, row_overflow_used = 8*SUM(row_overflow_used_page_count)
, row_count = SUM(row_count)
FROM sys.dm_db_partition_stats GROUP BY object_id, index_id
) p
INNER JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
LEFT JOIN sys.objects o ON o.object_id = p.object_id
LEFT JOIN sys.schemas t ON t.schema_id = o.schema_id
WHERE o.type NOT IN ('S', 'IT', 'TF')
GROUP BY o.schema_id, p.object_id, o.name, o.type, t.name, o.create_date
) x
ORDER BY Data DESC
Index Space and Usage
The Index Usage view includes index space, and index usage system view.
The columns to the right indicated the number of execution plan references that include a scan, insert (or update) and delete, along with the specific plans that reference the operation.
The queries for index space and usage is as follows:
--Index Space SQL:
DECLARE @dbid int SELECT @dbid = DB_ID()
SELECT @dbid AS dbid, u.name AS [Schema], o.name AS [Object], ISNULL(i.name,'') AS [Index]
, i.object_id, i.index_id, o.type AS otype
, p.reserved, p.used, p.leaf, p.row_count
, i.type AS itype, data_space_id AS dsid, p.Partitions, is_unique, ignore_dup_key, is_primary_key
,is_unique_constraint, is_disabled, is_hypothetical, allow_row_locks, allow_page_locks
FROM sys.indexes i
INNER JOIN sys.objects o ON o.object_id = i.object_id
INNER JOIN sys.schemas u ON u.schema_id = o.schema_id
LEFT JOIN (
SELECT object_id, index_id, COUNT(*) AS Partitions
, reserved = 8*SUM(reserved_page_count) , used = 8*SUM(used_page_count)
, leaf = 8*SUM(in_row_data_page_count+lob_used_page_count+row_overflow_used_page_count)
, row_count = SUM(row_count)
FROM sys.dm_db_partition_stats
GROUP BY object_id, index_id
) p ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE o.type NOT IN ('S','IT','TF')
ORDER BY o.name , i.index_id
--Index Usage SQL:
DECLARE @dbid int SELECT @dbid = DB_ID()
SELECT s.database_id, s.object_id, s.index_id
, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
, s.last_user_seek, s.last_user_scan, s.last_user_lookup, s.last_user_update
, s.system_seeks ASs_seeks, s.system_scans AS s_scans
, s.system_lookups AS s_lookups, s.system_updates AS s_updates
, s.last_system_seek AS ls_seek, s.last_system_scan AS ls_scan
, s.last_system_lookup AS ls_lookup, s.last_system_update AS ls_update
FROM sys.dm_db_index_usage_stats s
Exec Stats
The first set columns are aggregations from dm_exec_query_stats. After Plan Generation (PGenN) are information extracted from the XML plan, including: compile cost, plan cost, estimated number of rows, the number of steps in the plan, number of parallel execution steps, the number of scans, inserts, updates and delete, and finally the compile parameters.
-- Exec Stats Stmt:
SELECT q.statement_start_offset, q.statement_end_offset
, q.plan_handle, q.plan_generation_num, q.creation_time, q.last_execution_time, q.execution_count
, q.total_worker_time, q.max_worker_time
, q.total_physical_reads , q.total_logical_writes , q.total_logical_reads
, q.total_clr_time, q.total_elapsed_time, q.max_elapsed_time
, q.query_hash, q.query_plan_hash
--, SUBSTRING(s.text, statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2) AS [Stmt], p.query_plan
FROM(
SELECT TOP 300 statement_start_offset, statement_end_offset, plan_generation_num, plan_handle
,creation_time, last_execution_time , execution_count, total_worker_time, max_worker_time
,total_physical_reads, total_logical_writes, total_logical_reads , total_clr_time, total_elapsed_time, max_elapsed_time
, query_hash, query_plan_hash
FROM sys.dm_exec_query_stats
WHERE statement_start_offset <> 86
ORDER BY total_worker_time DESC
) q
--CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS s
--CROSS APPLY sys.dm_exec_text_query_plan(plan_handle, statement_start_offset, statement_end_offset) AS p
Below is the output in batch mode.
-- Exec Stats Batch:
SELECT 0 AS statement_start_offset, -1 AS statement_end_offset
, q.plan_handle, q.plan_generation_num, q.creation_time, q.last_execution_time
, q.execution_count, q.total_worker_time, q.max_worker_time
, q.total_physical_reads, q.total_logical_writes, q.total_logical_reads
, q.total_clr_time, q.total_elapsed_time, q.max_elapsed_time, StmtCnt
-- , t.text, p.query_plan
FROM (
SELECT TOP 300 q.plan_handle
, MAX(q.plan_generation_num) AS plan_generation_num , MIN(q.creation_time) AS creation_time
, MAX(q.last_execution_time) AS last_execution_time
, AVG(q.execution_count) AS execution_count
, SUM(q.total_worker_time) AS total_worker_time, MAX(q.max_worker_time) AS max_worker_time
, SUM(q.total_physical_reads) AS total_physical_reads , SUM(q.total_logical_writes) AS total_logical_writes
, SUM(q.total_logical_reads) AS total_logical_reads
, SUM(q.total_clr_time) AS total_clr_time , SUM(q.total_elapsed_time) AS total_elapsed_time
, MAX(q.max_elapsed_time) AS max_elapsed_time
, COUNT(*) AS StmtCnt
FROM sys.dm_exec_query_stats q
WHERE statement_start_offset <> 86
GROUP BY q.plan_handle
ORDER BY SUM(q.total_worker_time) DESC ) q
-- CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
-- CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS p
Below is Plan Cache. Notice execution statistics is not available.
-- Plan Cache:
SELECT bucketid, refcounts, usecounts, size_in_bytes, objtype, plan_handle
FROM sys.dm_exec_cached_plans WHERE cacheobjtype = 'Compiled Plan'
Clicking on the SQL column in Exec Stats will shift to the tab page XML Plan. Also use the < and > buttons to navigate XML plans. Click the button “Save XML Plans” to save each execution plan as a separate sqlplan file.
The Show Plan tab is similar to the plan in the old SQL Server 2000 tabular format.
Data Distribution Statistics Header
Includes date that the column or index statistic was updated.
Run Time Information
A text file name in the format: Server_Instance_YYYYMMDD_hhmm.txt contains runtime information, including some of the queries sent.
Excel File
Much of the data collected is saved to an Excel file name format: Server_Instance_YYYYMMDD_hhmm.xls.
XML Plan files
XML plan files are not saved by default. Click the “Save XML Plans” button after running the data collection. Plans are saved in a directory starting with server and instance name followed by a date time code.
Click the Clear button to start a new sequence
Appendix
Index Usage columns
| No. | Column | Note |
| 0 | dbid | Database id |
| 1 | Schema | Schema |
| 2 | Object | Object name |
| 3 | Index | Index name |
| 4 | object_id | Object id |
| 5 | index_id | Index id |
| 6 | otype | Object type |
| 7 | Reserved | Reserved space in KB |
| 8 | Used | |
| 9 | Leaf | |
| 10 | row_count | |
| 11 | user_seeks | |
| 12 | user_scans | |
| 13 | user_lookups | |
| 14 | user_updates | |
| 15 | last_user_seek | |
| 16 | last_user_scan | |
| 17 | last_user_lookup | |
| 18 | last_user_update | |
| 19 | s_seeks | System seeks |
| 20 | s_scans | |
| 21 | s_lookups | |
| 22 | s_updates | |
| 23 | ls_seek | Last System seek |
| 24 | ls_scan | |
| 25 | ls_lookup | |
| 26 | ls_update | |
| 27 | itype | |
| 28 | dsid | |
| 29 | Partitions | |
| 30 | is_unique | |
| 31 | ignore_dup_key | |
| 32 | is_primary_key | |
| 33 | is_unique_constraint | |
| 34 | is_disabled | |
| 35 | is_hypothetical | |
| 36 | allow_row_locks | |
| 37 | allow_page_locks | |
| 38 | Seeks | The number of execution plans that reference this index in seek operations |
| 39 | SeekPlanRef | The execution plan(s) that reference this index in seek operations |
| 40 | Scans | |
| 41 | ScanPlanRef | |
| 42 | InsertUpds | The number of execution plans that reference this index in insert or update operations |
| 43 | InsUpdPlanRef | |
| 44 | Deletes | |
| 45 | DeletePlanRef | |
| 46 | IxCode | Cl-Clustered, N-nonclustered, PK-PrimaryKey, UC-Unique Contraint, U-Unique |
| 47 | IndexColumns | |
| 48 | ||
| 49 | ||
| 50 |
Exec Stats
The Exec Stats grid is populated by the query, with additional calculations.
The top row is the summary for all dm_exec_query_stats rows. The second row is the summary for only the top X rows shown below.
| No. | Column | Description |
| 0 | Seq | sequential number, may have later uses for cross-referencing ExecStats results for previous samples |
| 1 | SQL | SQL text |
| 2 | Cnt | Execution count |
| 3 | CPU-s | CPU or worker time in seconds. |
| 4 | Dur-s | Duration or elapsed time in seconds. |
| 5 | AvCPU-us | Average CPU in micro-seconds. |
| 6 | AvDur-us | Average Duration in micro-seconds. |
| 7 | MxCms | Max CPU in milli-seconds. |
| 8 | MxDms | Max Duration in milli-seconds. |
| 9 | PhyRds | Physical Reads |
| 10 | LogWr | Logical Writes |
| 11 | PGenN | Plan Generation Number |
| 12 | CompileTime | |
| 13 | CompileCPU | |
| 14 | EstRows | Estimate Rows |
| 15 | PlanCost | |
| 16 | NStmts | Number of statements. Always 1 for stmt mode, can be more for batch mode. |
| 17 | PlanSteps | Number of Plan Steps. Each RelOp node in the XML plan is a plan step |
| 18 | Parallel | Parallel Plan: In the summary rows (1 & 2),
this is the number of execution plans that have parallel operations. For the individual query plans (rows 3 and up) this is the number of plan steps that are parallel. |
| 19 | MissingIx | |
| 20 | Impact | |
| 21 | Warnings | |
| 22 | Scans | The number of plans that involve scan operations (summary) or the number of scan operations in the given query plan. (May only count scans of > 10MB?) |
| 23 | sCPU | Plan Estimate CPU cost of scan operations |
| 24 | sIO | Plan Estimate IO cost of scan operations, 1 = 1350 pages or 10.5MB |
| 25 | sRows | Estimate rows from scan operations. |
| 26 | RBOps | Summary: the number of plans that contain Rebind operations.
Query Plan: the number of plan steps that have rebind operations. |
| 27 | Rebinds | Total number of rows involved for all rebind ops in the plan. In SQL Server 2000, this was the number of executions? |
| 28 | Rewinds | |
| 29 | Predicates | Indicates that an index seek or table scan operation had a predicate (search condition) which was not involved in the index seek. |
| 30 | Inserts | Summary: the number of execution plans that have insert operations.
Query Plan: The number of insert operations in the query plan. |
| 31 | Updates | |
| 32 | Deletes | |
| 33 | IRows | Estimates number of rows for all insert operations |
| 34 | URows | Update rows |
| 35 | DRows | Delete rows |
| 36 | HashJ | Hash Joins |
| 37 | HashA | Hash Aggregates |
| 38 | Merge | Merge joins |
| 39 | MTM | Many-to-many merge joins |
| 40 | dbid | database id |
| 41 | Creation | Plan Creation date-time |
| 42 | LastExec | Last Execution date |
| 43 | OptLev | Optimization Level |
| 44 | EAR | SQL optimization Early Abort Reason |
| 45 | StmtType | Statement Type |
| 46 | Param | Parameters |
Show Plan
The intent of this screen is to somewhat replicate the SHOWPLAN_ALL output that was available even in SQL Server 2000. The SHOWPLAN_ALL columns are: StmtText, StmtId, NodeId, Parent, PhysicalOp, LogicalOp, Argument, DefinedValues, EstimateRows, EstimateIO, EstimateCPU, AvgRowSize, TotalSubstreeCost, OutputList, Warnings, Type, Parallel, EstimateExecutions
The top row is the summary for all dm_exec_query_stats rows. The second row is the summary for only the top X rows shown below.
| No. | Column | Description |
| 0 | Plan | Corresponds to the Seq column from the ExecStats page. |
| 1 | Stmt | StatementId |
| 2 | CpId | StatementCompId, not sure what this means |
| 3 | Line | There is one line for each RelOp in the XML plan |
| 4 | Database | For top level, Line 0, this is the StatementText field. For higher Line numbers, if a object is referenced, this is the Database |
| 5 | Schema | |
| 6 | Table | |
| 7 | Index | |
| 8 | Node | NodeId |
| 9 | Parent | |
| 10 | PhysicalOp | |
| 11 | Logical Op | |
| 12 | EstRows | |
| 13 | EstIO | |
| 14 | EstCPU | |
| 15 | AvgRowSz | |
| 16 | EstTotSubTr | |
| 17 | Operator | Operator cost. The Total Subtree cost is not always equal to the sum of the local operation CPU and IO plus the subtrees that feed into it. Certain operations like Key Lookup and Nested Loops inner source are executed multiple times. |
| 18 | Parallel | |
| 19 | Rebinds | |
| 20 | Rewinds | |
| 21 | TableCard | Table Cardinality is a SQL Server 2008 value |
| 22 | Alias | |
| 23 | TableRef | |
| 24 | SeekPred | |
| 25 | Predicate | |
| 26 | IndexSc | |
| 27 | Join1 | Should be join outer source |
| 28 | Join2 | should be join inner source |
| 29 | Join3 | additional join details |
| 30 | Warnings | |
| 31 | Misc | Table Cardinality is a SQL Server 2008 value |
| 32 | Output |
Distribution Stats
The Exec Stats grid is populated by the query, with additional calculations.
The top row is the summary for all dm_exec_query_stats rows. The second row is the summary for only the top X rows shown below.
| No. | Column | Description |
| 0 | dbid | |
| 1 | schema | |
| 2 | Object | |
| 3 | Stat | |
| 4 | object_id | |
| 5 | stats_id | |
| 6 | Columns | |
| 7 | oType | |
| 8 | cType | |
| 9 | auto_created | |
| 10 | user_created | |
| 11 | no_recompute | |
| 12 | Updated | |
| 13 | Rows | |
| 14 | Sampled | |
| 15 | Steps | |
| 16 | Density | |
| 17 | AvgKeyLen | |
| 18 | StringIndex | |
| 19 | Density1 | |
| 20 | Density2 |