Parent, SQL Exec Stats, Copyright 2006-2018 Elemental Inc. All rights reserved.
Read Me, Intro, Main, General, Databases, File IO, Tables, Indexes, ExecStats, StoredProcs, DistStats, IxOpStats, 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.ColumnNote
0DatabaseDatabase
1dbiddatabase id
2GroupsNumber of File Groups
3FilesNumber of data Files
4SizeSize in MB (data files only)
5ReservedSize in MB Reserved
6DataSize in MB in-row Data
7lobSize in MB lob - includes spatial? and columnstore
8ovrSize in MB overflow
9IndexSize in MB Index
10newIxSize in MB new Index (hekaton)
11UnallocatedSize in MB Unallocated
12LogSize in MB Log files
13LusedSize in MB Log used
14Tablesnumber of Tables
15Indexesnumber of nonclustered Indexes
16XmlIxNumber of Xml Indexes
17SpaIxNumber of Spatial Indexes
18cCsIxclustered Column store Index
19nCsIxnonclustered Column store Index
20StatsNumber of Statistics
21ftszfull-text index size MB
22prcNumber of stored procedures
23fnNumber of functions
24trgNumber of triggers
25create_datecreate_date - from sys.databases
26compatibility_levelcompatibility_level
27collation_namecollation_name
28user_accessuser_access
29statestate
30is_in_standbyis_in_standby
31recovery_modelrecovery_model
32is_auto_create_stats_onis_auto_create_stats_on
33is_auto_update_stats_onis_auto_update_stats_on
34ftefull-text enabled
35is_parameterization_forcedis_parameterization_forced
36is_date_corris_date correlation enabled
37is_encryptedis_encrypted
38ft_dsfull text data space
39DReadsData Reads
40DRd_MBData Read_MB
41DRdIOStData Read IO Stall ms
42DWritesData Writes
43DWr_MBDWr_MB
44DWrIOStDWrIOSt
45LWritesLog Writes
46LWr_MBLWr_MB
47LWrIOStLog Write IO Stall
48AvB/DRdAvg Bytes/Data Read
49ms/DRdAvg ms/Data Read
50AvB/LWrAvB/LWr
51ms/LWrms/LWr
52DbBUNumber of full Database Backups since SQL Server start?
53DBUFull Database BU MB
54CDBCompressed DB BU MB
55IBUDifferential Database BU MB
56LBULBU
57DBUsData BU-sec

 

 

File IO Columns

 

No.ColumnNote
0DatabaseDatabase
1FileFile
2dbiddatabase id
3fileidfile id
4typetype (0 data, 1 log)
5dsiddata space id
6statestate
7SizeSize in MB
8GrowGrow
9TotExtentsTotal Extents
10UsedExtentsUsed Extents
11ReadsReads (from dm_io_virtual_file_stats)
12ReadBytesRead Bytes
13RdIOStallRead IO Stall ms
14WritesWrites
15WrBytesWrite Bytes
16WrIOStallWrite IO Stall
17IOPen_mssum IO Pending_ms (from dm_io_pending_io_requests)
18IOPendingsum # IO Pending
19IOPenCntIO Pending Cnt
20AvgB/ReadAvg Bytes/Read
21ms/ReadAvg ms/Read
22AvgB/WrAvg Bytes/Wr
23ms/WrAvg ms/Wr
24PhyNamePhy Name
25is_percentis_percent
26ver_storversion_store
27user_resuser_res
28intern_resintern_res
29mixed_etcmixed_etc

 

 

Table Space Columns

#ColNote
0dbiddatabase id
1SchemaSchema
2ObjectObject (table or indexed view)
3object_idobject_id
4TypeType
5RowsRows
6ReservedReserved KB
7DataData KB
8loblob KB (includes spatial and columnstore)
9ovrover flow
10IndexIndex
11newIxmemory opt KB
12UnusedUnused
13AvRszAvg Row size in bytes
14Clusthas Clustered index
15IxCnt# of nonclustered indexes
16UnqIx# of unique indexes and constraints
17XmlIxXml Indexes
18SpaIxSpatial Indexes
19cCsIxclustered Columnstore Index
20nCsIxnonclustered Columnstore Index
21MoIxMemory optmized Index
22Stats# of column Stats
23Partitioned# of Partitioned indexes
24Parts# of Partitions
25Compress# of Compress indexes
26dsiddataspace id (data)
27ldslob/image dataspace id
28ftctfulltext indexes
29ftszfulltext size
30cols# of columns
31mcumcu
32rkey# of reference key
33fkey# of foreign key
34def# of defaults
35trg# of triggers
36CIxSkClustered Ix Seeks
37NIxSkNIxSk
38LkUpsLkUps
39ScansScans
40UpdsUpds
41ZrIxZrIx
42MsIxMsIx
43create_datecreate_date
44FiltFilt
45disabldisabl
46HypotheticalHypothetical

 

No.ColumnNote
16UnqIxNumber of nonclustered unique indexes and constraints
24CompressNumber of compressed indexes (clustered and nonclustered)
27ftctfulltext indexes
28ftszfulltext index size KB (need to check this)
29colnumber of columns
30rkeyNumber of references by foreign keys
31fkeyNumber of foreign keys
32defNumber of default constraints
33trgNumber of triggers
34CIxSkclustered index seeks
35NIxSknonclustered index seeks
36LkUpsKey Lookups
37ScansScans
38UpdsUpdates (clust index)
39ZrIxindexes with zero seeks?
40MsIxMissing Indexes
41create_datecreate date
42FiltNumber of filtered indexes
43disablNumber of disabled indexes
44HypotheticalNumber of hypothetical indexes (DTA cleaned failed?)

 

Index Usage Columns

No.ColumnNote
0dbidDatabase id
1SchemaSchema
2ObjectObject (table or indexed view)
3IndexIndex name
4object_idobject_id
5index_idIndex id
6obj typeObject type
7dsdataspace
8ReservedReserved space in KB
9UsedUsed KB
10LeafLeaf KB
11row_countrow count
12CodeCl clustered, N nonclustered, Pk Primary key, UC-Unique Contraint, U unique
13Index Key ColumnsIndex key columns (column type in parenthesis)
14Include ColumnsIncluded columns
15user seeksNumber of users seeks (from dm_db_index_usage_stats)
16user scansNumber of user scans
17user lookupsNumber of user lookups
18user updatesNumber of user updates
19Plan SeeksThe number of execution plan references (tbd: change to count only once per plan or statement?) in seek operations
20Plan ScansThe number of execution plan references in scan operations
21Plan LookupThe number of execution plan references in lookup operations
22Plan Ins UpdThe number of execution plans that reference this index in insert or update operations
23Plan DeletesThe number of execution plan references in delete operations
24Seek Plan RefThe execution plan(s) that reference the index in seek operations
25Scan Plan RefThe execution plan(s) that reference the index in scan operations
26Lookup Plan RefThe execution plan(s) that reference the index in lookup operations
27Ins Upd Plan RefThe execution plan(s) that reference the index in insert or update operations
28Delete Plan RefThe execution plan(s) that reference the index in delete operations
29last user seeklast user seek
30last user scanlast user scan
31last user lookuplast user lookup
32last user updatelast user update
33sys seeksNumber of system seeks
34sys scansNumber of system scans
35sys lookupsNumber of system lookups
36sys updatesNumber of system updates
37last sys seekLast system seek
38last sys scanLast system scan
39last sys lookupLast system lookup
40last sys updateLast system update
41ix typeindex type
42PartitionsNumber of partitions
43is_uniqueis unique
44ignore_dup_keyignore dup key
45pri keyis primary key
46is uniq conis unique constraint
47is disabledis disabled
48is hypois hypothetical
49allow row locksallow row locks
50allow page locksallow page locks
51Compris compressed index
52filtris filtered index
53filt deffilter definition
54Key CntNumber of index key columns
55UpdatedStatistics updated
56SampledRows Sampled
57AvKyLAvg Key Length
58RowPagRatioIndex selectivity divided by pages
xtbdimplement dm_db_stats_properties
xtbdlast_updated, rows, rows_Sampled, steps, unfiltered rows, modification_counter

 

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.ColumnDescription
0Seqsequential number, may have later uses for cross-referencing ExecStats results for previous samples
1Hndsequence # has sql handle at
2OffSql handle offset
3SQLSQL text
4CntExecution count
5CPU-sCPU or worker time in seconds.
6Dur-sDuration or elapsed time in seconds.
xClr-sClr time in seconds.
7AvCPU-usAverage CPU in micro-seconds.
8AvDur-usAverage Duration in micro-seconds.
9MxCmsMax CPU in milli-seconds.
10MxDmsMax Duration in milli-seconds.
11PhyRdsPhysical Reads
12LogRdsLogical Reads
13LogWrLogical Writes
xRowsRows
14GenNPlan Generation Number
15Plan SzPlan size
16CompileTimeCompile time
17CmpCPUCompile CPU
16CmpMCompile memory
18EstRowsEstimate Rows
19PlanCostPlan cost
20StCtIn batch (or proc) mode, the number of statements with this SQL handle
In stored proc mode, the number of successful plans generated.
21StmtsNumber of statements in the XML plan.
22StepsNumber of Plan Steps. Each RelOp node in the XML plan is a plan step
23sPrcNumber of stored procedure calls (from current stored procedure)
23TVFNumber of Table Value Functions
23UDFNumber of User Defined Functions
24ParallelParallel 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.
25MissingIxMissing index
26ImpactMissing index impact
27WarningsWarnings
28ScansThe 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?)
29sCPUPlan Estimate CPU cost of scan operations
30sIOPlan Estimate IO cost of scan operations, 1 = 1350 pages or 10.5MB
31sRowsEstimate rows from scan operations.
32RBOpsSummary: the number of plans that contain Rebind operations.
Query Plan: the number of plan steps that have rebind operations.
33RebindsTotal number of rows involved for all rebind ops in the plan. In SQL Server 2000, this was the number of executions?
34RewindsRewinds
xMx RowsRewinds
xtsBrRewinds
xSDMRewinds
xEPCRewinds
xEDPRewinds
35PredicatesIndicates that an index seek or table scan operation had a predicate (search condition) which was not involved in the index seek.
36InsertsSummary: the number of execution plans that have insert operations.
Query Plan: The number of insert operations in the query plan.
37UpdatesUpdate operations
38DeletesDelete operations
39IRowsEstimates number of rows for all insert operations
40URowsUpdate rows
41DRowsDelete rows
42HashJHash Joins
43HashAHash Aggregates
44MergeMerge joins
45MTMMany-to-many merge joins
46SortNumber of sort operations
xSplLNumber of sort operations
xSplENumber of sort operations
47dbiddatabase id
xoiddatabase id
48CreationPlan Creation date-time
49LastExecLast Execution date
50OptLevOptimization Level
51EARSQL optimization Early Abort Reason
52StmtTypeStatement Type
53Paramfrom dm_exec: Parameters, in stored proc mode: list of sub procedures called
xMisc1Statement Type
xMisc2Statement Type
xPrevStatement Type
xPlanHndStatement Type

 

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.ColumnDescription
0PlanCorresponds to the Seq column from the ExecStats page.
1StmtStatementId
2CpIdStatementCompId, not sure what this means
3LineThere is one line for each RelOp in the XML plan
4DatabaseFor top level, Line 0, this is the StatementText field. For higher Line numbers, if a object is referenced, this is the Database
5SchemaSchema
6TableTable
7IndexIndex
8NodeNodeId
9ParentParent
10PhysicalOpPhysical Op
11Logical OpLogical Op
12EstRowsEstimate Rows
13EstIOEst IO
14EstCPU
15AvgRowSz
16EstTotSubTr
17OperatorOperator 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.
18Parallel
19Rebinds
20Rewinds
21TableCardTable Cardinality is a SQL Server 2008 value
22Alias
23TableRef
24SeekPred
25Predicate
26IndexSc
27Join1Should be join outer source
28Join2should be join inner source
29Join3additional join details
30Warnings
31MiscTable Cardinality is a SQL Server 2008 value
32Output

 

Distribution Stats Columns

No.ColumnDescription
0dbid
1schema
2Object
3Stat
4object_id
5stats_id
6Columns
7oType
8cType
9auto_created
10user_created
11no_recompute
12Updated
13Rows
14Sampled
15Steps
16Density
17AvgKeyLen
18StringIndex
19Density1
20Density2

 

Prev