Psap at 15:10, 24 August 2007

2007-08-24T15:10:37Z

New page

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

[[Category:ASE]]

Update statistics script - Revision history

Psap at 15:10, 24 August 2007