SQL Exec Stats, Copyright © 2006-2010 Elemental Inc. All rights reserved.

Read Me, Intro, Main, General, Databases, File IO, Tables, Indexes, Exec Stats, Stored Procs, Dist Stats, Appendix

Appendix

 

Databases Columns

Some of the columns in the Databases gridview are straight from the sys.databases DMV. Others are computed from multiple sources.

No.	Column	Note
0	Database	Database
1	dbid	Database id
2	FGs	Number of Filegroups
3	Files	Number of files
4	Size MB	Size in MB (data files only)
5	Reserved	Size in MB reserved
5	Data	Size in MB data
5	Index	Size in MB indexes
6	Unallocated	Size in MB
7	Log	Size in MB - log files only
8	Lused	Log MB used
9	Tables	Number of tables
10	Indexes	Number of Indexes (Nonclustered?)
11	XML Ix	Number of XML indexes
12	Stats	Number of statistics (not sure if IX+col or just col)
13	create_date	from sys.databases
14	compatibility_level	from sys.databases
15	user_access	from sys.databases
16	state	from sys.databases
17	is_in_standby	from sys.databases
18	recovery_model	from sys.databases
19	is_auto_create_stats_on	from sys.databases
20	is_auto_update_stats_on	from sys.databases
21	is_parameterization_forced	from sys.databases

 

File IO Columns

 

No.	Column	Note
0	Database	Database
1	Filename	File name
2	dbid	Database id
3	fileid	File id
4	type	type (0 data, 1 log)
5	dsid	data space id
6	state	state
7	Size MB	Size in MB
8	TotExtents	Total Extents (64K?)
9	UsedExtents	Used Extents
10	Reads	Reads (from dm_io_virtual_file_stats)
11	Read Bytes	Read Bytes
12	RdIOStall	Read IO Stall ms
13	Writes	Writes
14	Write Bytes	Write Bytes
15	WrIOStall	Write IO Stall ms
16	IOPen_ms	Sum IO Pending ms (from dm_io_pending_io_requests)
17	IOPending	Sum of IO Pending
18	IOPenCnt	IO Pending Count
19	AvgB/Read	Average bytes per wead
20	ms/Read	Average ms per read
21	AvgB/wr	Average bytes per write
22	ms/Wr	Average ms per write
23	PhyName	Physical file name

 

Table Space Columns

 

No.	Column	Note
0	dbid	Database id
1	Schema	Schema
2	Object	Object (table or view)
3	object_id	object_id
4	Type	Type
5	Rows	Rows
6	Reserved KB	Reserved KB
7	Data KB	Data KB
8	Index KB	Index KB
9	Unused	Unused
9	AvRsz	Average row size in bytes
10	Clust	Has Clustered index
11	IxCnt	Number of nonclustered indexes
12	UnqIx	Number of nonclustered unique indexes and constraints
13	XmlCt	Number of XML indexes
14	StCnt	Number of col statistics
15	Partitions	Number of tables and indexes that partitioned
16	Parts	Number of paritions
17	Compress	Number of compressed indexes (clustered and nonclustered)
18	rkey	Number of references by foreign keys
19	fkey	Number of foreign keys
20	def	Number of default constraints
21	trg	Number of triggers
22	create_date	create date
23	lob	lob pages (of KB?)
24	ovr	overflow pages
25	Filt	Number of filtered indexes
26	disabl	Number of disabled indexes
27	Hypothetical	Number of hypothetical indexes (DTA cleaned failed?)

 

Index Usage Columns

No.	Column	Note
0	dbid	Database id
1	Schema	Schema
2	Object	Object (table or indexed view)
3	Index	Index name
4	object_id	object_id
5	index_id	Index id
6	obj type	Object type
7	Reserved	Reserved space in KB
8	Used	Used KB
9	Leaf	Leaf KB
10	row_count	row count
11	Code	Cl clustered, N nonclustered, Pk Primary key, UC-Unique Contraint, U unique
12	Index Key Columns	Index key columns (column type in parenthesis)
13	user seeks	Number of users seeks (from dm_db_index_usage_stats)
14	user scans	Number of user scans
15	user lookups	Number of user lookups
16	user updates	Number of user updates
17	Plan Seeks	The number of execution plan references (tbd: change to count only once per plan or statement?) in seek operations
18	Plan Scans	The number of execution plan references in scan operations
19	Plan Lookup	The number of execution plan references in lookup operations
20	Plan Ins Upd	The number of execution plans that reference this index in insert or update operations
21	Plan Deletes	The number of execution plan references in delete operations
22	Seek Plan Ref	The execution plan(s) that reference the index in seek operations
23	Scan Plan Ref	The execution plan(s) that reference the index in scan operations
24	Lookup Plan Ref	The execution plan(s) that reference the index in lookup operations
25	Ins Upd Plan Ref	The execution plan(s) that reference the index in insert or update operations
26	Delete Plan Ref	The execution plan(s) that reference the index in delete operations
27	last user seek	last user seek
28	last user scan	last user scan
29	last user lookup	last user lookup
30	last user update	last user update
31	sys seeks	Number of system seeks
32	sys scans	Number of system scans
33	sys lookups	Number of system lookups
34	sys updates	Number of system updates
35	last sys seek	Last system seek
36	last sys scan	Last system scan
37	last sys lookup	Last system lookup
38	last sys update	Last system update
39	ix type	index type
40	dsid	data space id
41	Partitions	Number of partitions
42	is_unique	is unique
43	ignore_dup_key	ignore dup key
44	pri key	is primary key
45	is uniq con	is unique constraint
46	is disabled	is disabled
47	is hypo	is hypothetical
48	allow row locks	allow row locks
49	allow page locks	allow page locks
50	filtr	is filtered index
51	Compr	is compressed index
52	Key Cnt	Number of index key columns
53	Include Columns	Included columns
54	RowPagRatio	Index selectivity divided by pages
55	def	Filter definition

 

Exec Stats Columns

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	Prev	Previous rank, or the sequence # has sql handle at
2	Off	Sql handle offset
3	SQL	SQL text
4	Cnt	Execution count
5	CPU-s	CPU or worker time in seconds.
6	Dur-s	Duration or elapsed time in seconds.
7	AvCPU-us	Average CPU in micro-seconds.
8	AvDur-us	Average Duration in micro-seconds.
9	MxCms	Max CPU in milli-seconds.
10	MxDms	Max Duration in milli-seconds.
11	PhyRds	Physical Reads
12	LogRds	Logical Reads
13	LogWr	Logical Writes
14	GenN	Plan Generation Number
15	Plan Sz	Plan size
16	CompileTime	Compile time
17	CompileCPU	Compile CPU
18	EstRows	Estimate Rows
19	PlanCost	Plan cost
20	StCt	In batch (or proc) mode, the number of statements with this SQL handle In stored proc mode, the number of successful plans generated.
21	Stmts	Number of statements in the XML plan.
22	Steps	Number of Plan Steps. Each RelOp node in the XML plan is a plan step
23	sPrc	Number of stored procedure calls (from current stored procedure)
24	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.
25	MissingIx	Missing index
26	Impact	Missing index impact
27	Warnings	Warnings
28	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?)
29	sCPU	Plan Estimate CPU cost of scan operations
30	sIO	Plan Estimate IO cost of scan operations, 1 = 1350 pages or 10.5MB
31	sRows	Estimate rows from scan operations.
32	RBOps	Summary: the number of plans that contain Rebind operations. Query Plan: the number of plan steps that have rebind operations.
33	Rebinds	Total number of rows involved for all rebind ops in the plan. In SQL Server 2000, this was the number of executions?
34	Rewinds	Rewinds
35	Predicates	Indicates that an index seek or table scan operation had a predicate (search condition) which was not involved in the index seek.
36	Inserts	Summary: the number of execution plans that have insert operations. Query Plan: The number of insert operations in the query plan.
37	Updates	Update operations
38	Deletes	Delete operations
39	IRows	Estimates number of rows for all insert operations
40	URows	Update rows
41	DRows	Delete rows
42	HashJ	Hash Joins
43	HashA	Hash Aggregates
44	Merge	Merge joins
45	MTM	Many-to-many merge joins
46	Sort	Number of sort operations
47	dbid	database id
48	Creation	Plan Creation date-time
49	LastExec	Last Execution date
50	OptLev	Optimization Level
51	EAR	SQL optimization Early Abort Reason
52	StmtType	Statement Type
53	Param	from dm_exec: Parameters, in stored proc mode: list of sub procedures called

 

Show Plan Columns

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	Schema
6	Table	Table
7	Index	Index
8	Node	NodeId
9	Parent	Parent
10	PhysicalOp	Physical Op
11	Logical Op	Logical Op
12	EstRows	Estimate Rows
13	EstIO	Est IO
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 Columns

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