Finding missing statistics - Revision history

Psap: /* Sample query for sysstatistics */

2009-12-29T11:39:45Z

‎Sample query for sysstatistics

Psap: /* Sample query for sysstatistics */

2009-12-29T11:39:25Z

‎Sample query for sysstatistics

Psap at 16:16, 19 July 2009

2009-07-19T16:16:14Z

Psap at 07:57, 17 July 2009

2009-07-17T07:57:36Z

Psap at 07:56, 17 July 2009

2009-07-17T07:56:13Z

Psap at 17:26, 16 July 2009

2009-07-16T17:26:33Z

Psap at 14:19, 18 March 2009

2009-03-18T14:19:40Z

New page

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.

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

[[Category:ASE]]

@@ Line 77: / Line 77: @@
                                   where   id      = @table_id
                                   and     colid   = @colnum
-−
                   set     @number_columns = @number_columns + 1
           end

@@ Line 40: / Line 40: @@
   declare @colid          varbinary(2),
           @colidarray     varbinary(100),
-          @colnum         tinyint,
+          @colnum         smallint,
           @colstring      varchar(1024),
           @table_id       int,
           @table_name     varchar(255),
@@ Line 53: / Line 54: @@
   while   @@sqlstatus = 0
   begin
-          select  @colstring      = ""
+          set     @colstring      = "",
           /* Decode the varbinary into usable column names */
@@ Line 75: / Line 77: @@
                                   where   id      = @table_id
                                   and     colid   = @colnum
           end
           set     @user_table_nm  = @user_name + "." + @table_name
-          print   "update statistics %1! (%2!)",
+          if      @number_columns > 32
-                 @user_table_nm,
+                 print "Cannot run update statistics on more than 32 columns"
-                 @colstring
+         else
           fetch c2 into @user_name, @table_name, @table_id, @colidarray

@@ Line 1: / Line 1: @@
-ASE 15 requires a lot more statistical information to make an optimal queryplan. In ASE 12 or 12.5 it was usually sufficient to run "update index statistics" on all tables but for ASE 15 additional statistics for other columns used in a query are needed.
+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 needed to calculate the best possible plan. This information can be captured.
+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. As with all other statistics, they should be updated regularly.
+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.
-==ASE 15.0.3 ESD 1 or later==
+==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.
@@ Line 13: / Line 13: @@
   go
-Now the missing statistics are stored into the sysstatistics system table, per database. The table is stored, the list of columns and also a count of how often this combination was detected.
+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.
@@ Line 94: / Line 95: @@
 * 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.
 * You can also take a look at the QPTune utility, located in $SYBASE/$SYBASE_ASE/qptune
-==ASE 15.0.2==
+==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.
@@ Line 125: / Line 127: @@
   sp_logintrigger "sp_dba_globallogin"
   go
-To disable the global login trigger run:
+When you want to disable the global login trigger:
   sp_logintrigger "drop"
   go

@@ Line 91: / Line 91: @@
   go
-Notes
+===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.

@@ Line 1: / Line 1: @@
-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.
+ASE 15 requires a lot more statistical information to make an optimal queryplan. In ASE 12 or 12.5 it was usually sufficient to run "update index statistics" on all tables but for ASE 15 additional statistics for other columns used in a query are needed.
 ==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.
+Now the missing statistics are stored into the sysstatistics system table, per database. The table is stored, the list of columns and also a count of how often this combination was detected.
-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.
+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.
 ===Sample query for sysstatistics===
@@ Line 87: / Line 93: @@
 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.
+* 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.
 * You can also take a look at the QPTune utility, located in $SYBASE/$SYBASE_ASE/qptune

@@ 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. 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.
 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
@@ Line 18: / Line 108: @@
   go
-==Setup the permissions==
+===Setup the permissions===
 Every login in the server should be able to execute the stored procedure.
   grant execute on sp_dba_globallogin to public
@@ Line 26: / Line 116: @@
   go
-==Configure the stored procedure as a global login trigger==
+===Configure the stored procedure as a global login trigger===
   sp_logintrigger "sp_dba_globallogin"
   go
@@ Line 33: / Line 123: @@
   go
-==Sample output in the errorlog==
+===Sample output in the errorlog===
   NO STATS on column #spdbusages.vdevno
   NO STATS on column #spdbusages.dbid
@@ Line 40: / Line 130: @@
   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
 * Multiple sessions that are tracing at the same time cause garbled messages in the errorlog
 [[Category:ASE]]