Difference between revisions of "Finding missing statistics"

Latest revision as of 12:39, 29 December 2009

ASE 15 requires a lot more statistical information to make an optimal queryplan when compared to ASE 12 or 12.5. Before ASE 15 it was usually sufficient to run "update index statistics" on all tables but now additional statistics for other columns that are used in a query are needed too.

This page describes some methods to find the so called missing statistics. When the optimizer calculates a queryplan it can indicate which statistics are missing but needed to calculate the best possible plan. This information can be captured.

Once missing statistics have been identified you need to create them in the database with the "update statistics" command so that they are no longer missing. As with all other statistics, they should be updated regularly.

Store missing statistics in the sysstatistics system table

This method stores the missing statistics in the sysstatistics system table. It can only be used on ASE 15.0.3 ESD 1 or later.

Configure the server to capture missing statistics

First you need to configure the server to capture the missing statistics as indicated by the optimizer.

sp_configure "capture missing statistics",1
go

Now the missing statistics are stored into the sysstatistics system table, per database. The internal id of the table is stored, the list of columns and also a count of how often this combination was detected as being missing.

In the sysstatistics table the formatid will have a value of 110 for missing statistics. The count is stored in column c0, it contains a running total for each miss. It is stored as a hexadecimal value, wrapping at 0xffff back to x00000.

On a regular basis you can query the sysstatistics table and start generating "update statistics" statements.

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         smallint,
        @colstring      varchar(1024),
        @number_columns tinyint,
        @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
        set     @colstring      = "",
                @number_columns = 0

        /* 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

                set     @number_columns = @number_columns + 1
        end

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

        if      @number_columns > 32
                print "Cannot run update statistics on more than 32 columns"
        else
        begin
                -- Insert your update statistics statement here.
                print   "update statistics %1! (%2!)",
                        @user_table_nm, @colstring
        end

        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 when the table is empty.
• Use the "sampling" option when you run "update statistics" on large tables
• You can also take a look at the QPTune utility, located in $SYBASE/$SYBASE_ASE/qptune

Print missing statistics into the errorlog

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

When you want to disable the global login trigger:

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