Update statistics script

To make sure the ASE optimizer generates efficient query plans, you need to run update statistics regularly. Most DBA's run it as a weekly job. Below is a sample of SQL code that does this job. It has the following features:

• Will process all databases except tempdb and user defined temporary databases.
• Processes system and user tables.
• Skips proxy tables.
• Uses "update index statistics".
• Will work on ASE 15, some very minor changes need to be made for older versions.
• Does an sp_recompile.
• Has a build in algorithm to calculate a sampling percentage.
• Does not use the datachange() function, since the value returned is not always reliable.

Before you run the SQL code, you need to install a loopback server into the server. Do it like this:

sp_addserver "loopback",null,@@servername

To start the update statistics task, just fire the following SQL code as a complete batch.

set nocount on
go

use tempdb
go

create proxy_table tempdb..pxy_sysobjects
       external table
       at "loopback.master.dbo.sysobjects"
go

create proc update_statistics @dbname varchar(30)
as

declare @user_name      varchar(30),
        @table_name     varchar(255),
        @rowcnt         bigint,
        @statement      varchar(1024),
        @min_sampling   tinyint,
        @work_sampling  int,
        @sampling       tinyint,
        @type           char(2)

declare c1 cursor for
        select  user_name(uid),name,type,row_count(db_id(@dbname),id)
                from    tempdb..pxy_sysobjects
                where   type    in ("S","U")            -- System, User tables
                and not (sysstat2 & 1024 = 1024 or      -- Remote
                        sysstat2 & 2048 = 2048)         -- Proxy
                and     id not in (8,14,32,34,38,39,42,43,46,47,50,53,57) -- Same fake system tables

-- Set the mininum amount of sampling
select  @min_sampling   = 2

open c1

fetch c1 into @user_name,@table_name,@type,@rowcnt

while @@sqlstatus = 0
begin
       select @work_sampling = round(100 - (sqrt(@rowcnt) / 10),0)
       if @work_sampling  < @min_sampling
          select @sampling = @min_sampling
       else
          select @sampling = @work_sampling

       print "%1!.%2!.%3! (%4! rows -> %5! %%)",
             @dbname,@user_name,@table_name,@rowcnt,@sampling

       select @statement = "update index statistics " + @dbname + "." + @user_name + "." + @table_name
                         + " with sampling = " + convert(varchar(3),@sampling) + " percent"

       exec (@statement)

       if @type = "U"
       begin
         select @statement = @dbname + "..sp_recompile " + "'" + @user_name + "." + @table_name + "'"

         exec (@statement)
       end

       fetch c1 into @user_name,@table_name,@type,@rowcnt
end

close  c1

deallocate cursor c1
go

drop table tempdb..pxy_sysobjects
go

declare c1 cursor for
        select name
               from    master..sysdatabases
               where   name    != "tempdb"
               and not status3 & 256 = 256
go

declare @dbname    varchar(255),
        @statement varchar(255)

open c1

fetch c1 into @dbname

while @@sqlstatus = 0
begin
       select @statement = "create proxy_table tempdb..pxy_sysobjects"
                         + " external table " + " at 'loopback." + @dbname + ".dbo.sysobjects'"

       exec (@statement)

       exec tempdb..update_statistics @dbname

       select @statement = "drop table tempdb..pxy_sysobjects"

       exec (@statement)

       fetch c1 into @dbname
end

close c1
go

deallocate cursor c1
go
drop proc update_statistics
go