Finding missing statistics

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