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

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> use pubs2
2> go
1> sp_showoptstats publishers
2> go

The latest output is:
-----------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
  <optStats>
    <procVersion>sp_showoptstats/1.0/AnyPlatform/AnyOS/Fri Feb 18 18:18:18 
2011</procVersion>
    <serverVersion>Adaptive Server Enterprise/15.7/EBF XXXXX SMP Drop#2/B/NT 
(IX86)/Windows 2008 R2/aseasap/2617/32-bit/DEBUG/Wed Nov 17 02:14:21 
2010</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.3333333333333360</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>Dec 10 2010  3:58:14:266PM</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>
            <histogram>
                <colName>pub_id</colName>
                <dataType>char(4)</dataType>
                <requestedStepCnt>20</requestedStepCnt>
                <actualStepCnt>6</actualStepCnt>
                <samplingPct>0</samplingPct>
                <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

• 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.

See also