sp_showoptstats

Description

Similar in function to the optdiag standalone utility in an XML document but in a system procedure format, sp_showoptstats extracts and displays statistics and histograms for various data objects from system tables such as systabstats and sysstatistics.

Syntax

sp_showoptstats  [[database_name.[owner].]table_name], [column_name], [h]

Parameters

database_name: is the name of the database for which sp_showoptstats displays statistics and histograms. dbname has these restrictions:

Cross-database execution is not supported

You must currently be in the specified database to execute sp_showoptstats.

If you do not specify a database, sp_showoptstats displays statistics and histograms about the current database

owner: is the name of the table owner. If owner name is not specified, the current user or dbo is used.

table_name: is the name of the table for which sp_showoptstats displays statistics and histograms. table_name has these restrictions:

If you do not specify a table, sp_showoptstats displays statistics and histograms about all tables in the current database. However, to reduce the length of output, sp_showoptstats does not display column statistics and histograms are at database level.

table_name must exist in the current database.

column_name: is the name of the column for which Adaptive Server displays statistics and histograms. If you do not specify a column, Adaptive Server displays the statistics for all columns and all indexes on the table you specify. If you specify a column_name, sp_showoptstats displays statistics and histograms for only this column

h: displays help information about the procedure.

Examples

Example 1

Displays statistics for all user tables in the pubs2 database:

1> use pubs2
2> go
1> sp_showoptstats 'pubs2..publishers'
2> go

Example 2

Displays statistics and histograms for the publishers table in the pubs2 database, in XML format:

1> sp_showoptstats publishers

 <?xml version="1.0" encoding="UTF-8"?>
<optStats>
  <procVersion>sp_showoptstats/1.1/AnyPlatform/AnyOS/Tues April 3 14:21:21 2012</procVersion>
  <serverVersion>Adaptive Server Enterprise/15.7.0/EBF 20161 SMP ESD#02 Prelim#2/P/x86_64/Enterprise Linux/ase157x/3087/64-bit/FBO/Tue May 15 05:35:01 2012</serverVersion>
    <serverName></serverName>
    <specifiedDatabase>pubs2</specifiedDatabase>
    <specifiedTableOwner></specifiedTableOwner>
    <specifiedTable>publishers</specifiedTable>
    <specifiedCol></specifiedCol>
    <tables>
        <tableOwner>dbo</tableOwner>
        <tableName>publishers</tableName>
        <clusteredIndStats>
            <indName>pubind</indName>
            <colList>"pub_id"</colList>
            <stats>
                <pgCnt>1</pgCnt>
                <emptyPgCnt>0</emptyPgCnt>
                <rowCnt>3.0000000000000000</rowCnt>
                <fwdRowCnt>0.0000000000000000</fwdRowCnt>
                <delRowCnt>0.0000000000000000</delRowCnt>
                <CRCnt>1.0000000000000000</CRCnt>
                <oamAllocPgCnt>2</oamAllocPgCnt>
                <firstExtLeafPgs>0</firstExtLeafPgs>
                <dataRowSz>39.3333333333333357</dataRowSz>
                <indHeight>1</indHeight>
                <joinDegree>0.0000000000000000</joinDegree>
                <unusedPgCnt>14</unusedPgCnt>
                <oamPgCnt>1</oamPgCnt>
                <derivedStats>
                    <clusterRatio>0.0000000000000000</clusterRatio>
                    <spaceUtil>0.0072162426614481</spaceUtil>
                    <IOEfficiency>0.5000000000000000</IOEfficiency>
                </derivedStats>
            </stats>
        </clusteredIndStats>
        <colStats>
            <colName>pub_id</colName>
            <lastUpdate>May 15 2012  4:44:40:136PM</lastUpdate>
            <cellDensity>0.3333333333333333</cellDensity>
            <totalDensity>0.3333333333333333</totalDensity>
            <select>default used (0.33)</select>
            <inBetSel>default used (0.25)</inBetSel>
            <rangeVal>0.3333333333333333</rangeVal>
            <totalVal>0.3333333333333333</totalVal>
            <avgColWidth>default used (4.00)</avgColWidth>
            <statsVer>4</statsVer>
            <histogram>
                <colName>pub_id</colName>
                <dataType>char(4)</dataType>
                <requestedStepCnt>20</requestedStepCnt>
                <actualStepCnt>6</actualStepCnt>
                <samplingPct>0</samplingPct>
                <TuningFact>20</TuningFact>
                <steps>
                    <step>1</step>
                    <weight>0.00000000</weight>
                    <equation>&lt;</equation>
                    <value>"0736"</value>
                </steps>
                <steps>
                    <step>2</step>
                    <weight>0.33333334</weight>
                    <equation>=</equation>
                    <value>"0736"</value>
                </steps>
                <steps>
                    <step>3</step>
                    <weight>0.00000000</weight>
                    <equation>&lt;</equation>
                    <value>"0877"</value>
                </steps>
                <steps>
                    <step>4</step>
                    <weight>0.33333334</weight>
                    <equation>=</equation>
                    <value>"0877"</value>
                </steps>
                <steps>
                    <step>5</step>
                    <weight>0.00000000</weight>
                    <equation>&lt;</equation>
                    <value>"1389"</value>
                </steps>
                <steps>
                    <step>6</step>
                    <weight>0.33333334</weight>
                    <equation>=</equation>
                    <value>"1389"</value>
                </steps>
            </histogram>
        </colStats>
        <noStatsCol>city,pub_name,state
        </noStatsCol>
    </tables>
 </optStats>

Example 3

Shows the syntax of the procedure:

1> sp_showoptstats a,b,h
2> go

Usage: sp_showoptstats  [[database.[owner].]table], [column], [option]
(return status = 0)

Usage

You cannot execute sp_showoptstats across databases.
sp_showoptstats does not include the system tables unless you explicitly specify them.
Nonprintable and univarchar characters appear in hexidecimal format.
sp_showoptstats displays both global and partition-level statistics.
When the output is larger than the value you set for @@textsize, Adaptive Server returns a message to increase the @@textsize setting so that it can display the large output.
Parameter values that include a period (.) require double quotation marks.
You can issue sp_showoptstats against system tables.
sp_showoptstats does not return statistical information if you specify only the database and owner.

DTD XML output

The DTD file for the XML output of sp_showoptstats is:

<?xml version="1.0" encoding="UTF-8"?>
<!ELEMENT optStats (procVersion, serverVersion, serverName?, specifiedDatabase?, specifiedTableOwner?, specifiedTable?, specifiedCol?, tables*)>
<!ELEMENT procVersion (#PCDATA)>
<!ELEMENT serverVersion (#PCDATA)>
<!ELEMENT serverName (#PCDATA)>
<!ELEMENT specifiedDatabase (#PCDATA)>
<!ELEMENT specifiedTableOwner (#PCDATA)>
<!ELEMENT specifiedTable (#PCDATA)>
<!ELEMENT specifiedCol (#PCDATA)>

<!ELEMENT tables (tableOwner, tableName, partitionCnt?, (tableStats|clusteredIndStats|indStats|partitionStats|partitionClusteredIndStats|partitionIndStats)*,  (colStats|colPartitionStats)*, noStatsCol?)>

<!ELEMENT tableOwner (#PCDATA) >
<!ELEMENT tableName (#PCDATA) >  

<!ELEMENT tableStats (tableName, stats)>

<!ELEMENT clusteredIndStats (indName, colList, stats)>

<!ELEMENT indName (#PCDATA) >  
<!ELEMENT colList (#PCDATA) >  

<!ELEMENT partitionStats (partition*, stats*)>

<!ELEMENT partition (#PCDATA) >  

<!ELEMENT partitionIndStats (indName, partition, colList, stats)>

<!ELEMENT partitionClusteredIndStats (indName, partition, colList, stats)>

<!ELEMENT stats (pgCnt?, leafCnt?, (emptyPgCnt|emptyLeafCnt)?, CRCnt?, indCRCnt?, indPgCRCnt?, (dataRowCRCnt|leafRowCRCnt)?, rowCnt?, fwdRowCnt?, delRowCnt?, indPgCRCnt?, CRCnt?, oamAllocPgCnt?, (firstExtDataPgs|firstExtLeafPgs)?, (dataRowSz|leafRowSz)?, indHeight?, dataPages?, joinDegree?, unusedPgCnt?, oamPgCnt?, derivedStats?) > 

<!ELEMENT pgCnt (#PCDATA) >  
<!ELEMENT leafCnt (#PCDATA) >
<!ELEMENT CRCnt (#PCDATA) >  
<!ELEMENT indCRCnt (#PCDATA) >
<!ELEMENT dataRowCRCnt (#PCDATA) >  
<!ELEMENT leafRowCRCnt (#PCDATA) >  
<!ELEMENT emptyPgCnt (#PCDATA) >
<!ELEMENT emptyLeafCnt (#PCDATA) >
<!ELEMENT rowCnt (#PCDATA) >  
<!ELEMENT fwdRowCnt (#PCDATA) >
<!ELEMENT delRowCnt (#PCDATA) >  
<!ELEMENT oamAllocPgCnt (#PCDATA) >  
<!ELEMENT firstExtDataPgs (#PCDATA) >
<!ELEMENT firstExtLeafPgs (#PCDATA) >
<!ELEMENT dataRowSz (#PCDATA) >  
<!ELEMENT leafRowSz (#PCDATA) >  
<!ELEMENT indHeight (#PCDATA) >  
<!ELEMENT dataPages (#PCDATA) >  
<!ELEMENT joinDegree (#PCDATA) >
<!ELEMENT unusedPgCnt (#PCDATA) >  
<!ELEMENT oamPgCnt (#PCDATA) >
<!ELEMENT rowClusterRatio (#PCDATA) >

<!ELEMENT derivedStats (clusterRatio, indClusterRatio?, (dataClusterRatio|rowClusterRatio)?, spaceUtil?, IOEfficiency?) >  

<!ELEMENT clusterRatio (#PCDATA) >
<!ELEMENT indClusterRatio (#PCDATA) >
<!ELEMENT dataClusterRatio (#PCDATA) >
<!ELEMENT spaceUtil (#PCDATA) >  
<!ELEMENT IOEfficiency (#PCDATA) >

<!ELEMENT indStats (indName, colList, stats?) > 
	
<!ELEMENT colStats ((colName|colGroup)?, lastUpdate?, cellDensity?, totalDensity?, select?, inBetSel?, rangeVal?, totalVal?, avgColWidth?, 
statsVer? statsSamDen?, statsSamU?, histogram?) > 
	
<!ELEMENT colGroup (#PCDATA) >
<!ELEMENT lastUpdate (#PCDATA) >  
<!ELEMENT cellDensity (#PCDATA) >
<!ELEMENT totalDensity (#PCDATA) >  
<!ELEMENT selectivity (#PCDATA) >
<!ELEMENT inBetweenSelectivity (#PCDATA) >  
<!ELEMENT rangeVal (#PCDATA) >
<!ELEMENT totalVal (#PCDATA) >  
<!ELEMENT avgColWidth (#PCDATA) >
<!ELEMENT statsVer (#PCDATA) >
<!ELEMENT statsSamDen (#PCDATA) >
<!ELEMENT statsSamU (#PCDATA) >

<!ELEMENT colPartitionStats (ptnName,(colName|colGroup)?, lastUpdate?, cellDensity?, totalDensity?, select?, inBetSel?, rangeVal?, totalVal?, avgColWidth?, statsVer? statsSamDen?, statsSamU?, histogram?) > 

<!ELEMENT ptnName (#PCDATA) >  

<!ELEMENT histogram (colName, dataType, requestedStepCnt, actualStepCnt, samplingPct?, TuningFact?, statsOutRan?, statsHashLow?, statsHashHigh?, statsSamSt?, statsStepSt?, statsHtSt?, statsPHashSt?, statsHashSt?, statsNoHashSt?, steps*) >  

<!ELEMENT colName (#PCDATA) >
<!ELEMENT dataType (#PCDATA) >
<!ELEMENT requestedStepCnt (#PCDATA) >
<!ELEMENT actualStepCnt (#PCDATA) >  
<!ELEMENT samplingPct (#PCDATA) >  
<!ELEMENT TuningFact (#PCDATA) > 
<!ELEMENT statsOutRan (#PCDATA) >
<!ELEMENT statsHashLow (#PCDATA) >
<!ELEMENT statsHashHigh (#PCDATA) >
<!ELEMENT statsSamSt (#PCDATA) >
<!ELEMENT statsStepSt (#PCDATA) >
<!ELEMENT statsHtSt (#PCDATA) >
<!ELEMENT statsPHashSt (#PCDATA) >
<!ELEMENT statsHashSt (#PCDATA) >
<!ELEMENT statsNoHashSt (#PCDATA) >

<!ELEMENT steps (step, weight, equation, value) >

<!ELEMENT step (#PCDATA) >
<!ELEMENT weight (#PCDATA) >
<!ELEMENT equation (#PCDATA) >
<!ELEMENT value (#PCDATA) >  

<!ELEMENT noStatsCol (#PCDATA) >

Permissions

Any user can execute sp_showoptstats. Permission checks do not differ based on the granular permissions settings.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect