Difference between revisions of "Finding missing statistics"

From SybaseWiki
Jump to: navigation, search
 
(Sample query for sysstatistics)
 
(5 intermediate revisions by the same user not shown)
Line 1: Line 1:
ASE 15 requires a lot more statistical information to make an optimal queryplan. Previously is was sufficient to run "update index statistics" on all tables but now also other columns in a query are taken into account.
+
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.
 
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==
+
===Create a stored procedure===
  
 
  use sybsystemprocs
 
  use sybsystemprocs
Line 18: Line 125:
 
  go
 
  go
  
==Setup the permissions==
+
===Setup the permissions===
 
Every login in the server should be able to execute the stored procedure.
 
Every login in the server should be able to execute the stored procedure.
 
  grant execute on sp_dba_globallogin to public
 
  grant execute on sp_dba_globallogin to public
Line 26: Line 133:
 
  go
 
  go
  
==Configure the stored procedure as a global login trigger==
+
===Configure the stored procedure as a global login trigger===
 
  sp_logintrigger "sp_dba_globallogin"
 
  sp_logintrigger "sp_dba_globallogin"
 
  go
 
  go
To disable the global login trigger run:
+
When you want to disable the global login trigger:
 
  sp_logintrigger "drop"
 
  sp_logintrigger "drop"
 
  go
 
  go
  
==Sample output in the errorlog==
+
===Sample output in the errorlog===
 
  NO STATS on column #spdbusages.vdevno
 
  NO STATS on column #spdbusages.vdevno
 
  NO STATS on column #spdbusages.dbid
 
  NO STATS on column #spdbusages.dbid
Line 40: Line 147:
 
  NO STATS on density set for master.dbo.spt_values={msgnum, type}
 
  NO STATS on density set for master.dbo.spt_values={msgnum, type}
  
==Notes==
+
===Notes===
 
* Statistics for temporary tables are also traced and written into the errorlog
 
* 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
 
* Multiple sessions that are tracing at the same time cause garbled messages in the errorlog
  
 
[[Category:ASE]]
 
[[Category:ASE]]

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