Difference between revisions of "Finding missing statistics"

Revision as of 18:26, 16 July 2009

ASE 15 requires a lot more statistical information to make an optimal queryplan. In ASE 12 it was sufficient to run "update index statistics" on all tables but now also other columns in a query need up to date statistics.

There are two known methods available.

ASE 15.0.3 ESD 1 or later

Configure the server to capture missing statistics

sp_configure "capture missing statistics",1
go

This configuration option will store all missing statistical info into the sysstatistics system table, per database. On a regular basis you can query this table and generate statistics. Missing statistics can be found through formatid, it will have a value of 110. Each time a missing statistic has been detected it is recorded in the sysstatistics table and the column c0 contains a running total for each miss. Column c0 records this as a hexadecimal value, wrapping at 0xffff to x00000.

Sample query for sysstatistics

set     flushmessage on
set     nocount on
go

declare c2 cursor for
        select  distinct user_name(so.uid),so.name,so.id,st.colidarray
                from    sysobjects so, sysstatistics st
                where   so.type                 = "U"           -- User tables
                and     not (so.sysstat2 & 1024 = 1024 or       -- Remote
                        so.sysstat2 & 2048      = 2048)         -- Proxy
                and     st.id                   = so.id
                and     st.formatid             = 110           -- Missing
                order   by 1,2
go

exec    sp_flushstats
go

declare @colid          varbinary(2),
        @colidarray     varbinary(100),
        @colnum         tinyint,
        @colstring      varchar(1024),
        @table_id       int,
        @table_name     varchar(255),
        @user_name      varchar(30),
        @user_table_nm  varchar(300)

open    c2

fetch   c2 into @user_name, @table_name, @table_id, @colidarray

while   @@sqlstatus = 0
begin
        select  @colstring      = ""

        /* Decode the varbinary into usable column names */
        while   @colidarray     != null
        begin
                select  @colid          = left(@colidarray,2),
                        @colidarray     = substring(@colidarray,3,255)

                select  @colnum = convert(smallint,@colid)

                if      @colstring      != ""
                        select  @colstring      = @colstring
                                                + ","
                                                + name
                                from    syscolumns
                                where   id      = @table_id
                                and     colid   = @colnum
                else
                        select  @colstring      = name
                                from    syscolumns
                                where   id      = @table_id
                                and     colid   = @colnum
        end

        set     @user_table_nm  = @user_name + "." + @table_name

        print   "update statistics %1! (%2!)",
                @user_table_nm,
                @colstring

        fetch c2 into @user_name, @table_name, @table_id, @colidarray
end

close   c2
go

deallocate cursor c2
go

Notes

• After the creation of statistics with "update statistics", the row in the sysstatistics table with formatid = 110, is automatically removed.
• Since "update statistics" records statistical info based on data in a table, it cannot create that for tables with no rows. This means that the entry in sysstatistics with formatid = 110 will not disappear.
• You can also take a look at the QPTune utility, located in $SYBASE/$SYBASE_ASE/qptune

ASE 15.0.2

Below is a method described to capture information from the optimizer once it finds that some statistical information is missing. It is based on the creation of a global login trigger where a trace option is enabled. The trace output from the optimizer is written into the errorlog where it can be captured by the DBA.

Create a stored procedure

use sybsystemprocs
go

create	procedure sp_dba_globallogin
as

dbcc traceon(3605)

set option show_missing_stats on

return	0
go

Setup the permissions

Every login in the server should be able to execute the stored procedure.

grant execute on sp_dba_globallogin to public
go

Allow all users to start optimizer tracing

grant set tracing to public
go

Configure the stored procedure as a global login trigger

sp_logintrigger "sp_dba_globallogin"
go

To disable the global login trigger run:

sp_logintrigger "drop"
go

Sample output in the errorlog

NO STATS on column #spdbusages.vdevno
NO STATS on column #spdbusages.dbid
NO STATS on column #spdbusages.lstart
NO STATS on density set for #spdbusages={vdevno, dbid}
NO STATS on density set for master.dbo.spt_values={msgnum, type}

Notes

• Statistics for temporary tables are also traced and written into the errorlog
• Multiple sessions that are tracing at the same time cause garbled messages in the errorlog