Sybase SQL – Truncation error occurred. Command has been aborted and find CACHE QUALITY

sybasesybase-ase

Need help with below code, as it fails with truncation error

Truncation error occurred.
Command has been aborted.

create table monCacheQuality (
        ServerName sysname
        ,CollectionDateTime smalldatetime not null
        ,PhysicalWrites decimal(15, 0) not null
        ,PhysicalReads decimal(15, 0) not null
        ,LogicalReads decimal(15, 0) not null
        ,CacheQuality decimal(15, 0) not null
        ,CacheHitPct decimal(15,4) not null
        )

-- Main code starts here 
declare @physical_read1 decimal(15, 0)
    ,@logical_read1 decimal(15, 0)
    ,@physical_write1 decimal(15, 0)
    ,@cache_search1 decimal (15,4)

declare @physical_read2 decimal(15, 0)
    ,@logical_read2 decimal(15, 0)
    ,@physical_write2 decimal(15, 0)
    ,@cache_search2 decimal (15,4)

while (1=1)
begin
    select @physical_write1 = PhysicalWrites
        ,@physical_read1 = PhysicalReads
        ,@logical_read1 = LogicalReads
        ,@cache_search1 = CacheSearches
    from master..monDataCache

    waitfor delay '00:00:20' -- Log every 20 sec

    select @physical_write2 = PhysicalWrites
        ,@physical_read2 = PhysicalReads
        ,@logical_read2 = LogicalReads
        ,@cache_search2 = CacheSearches
    from master..monDataCache

    insert monCacheQuality
    select @@servername as ServerName
        ,getUTCdate()
        ,@physical_write2 - @physical_write1
        ,@physical_read2 - @physical_read1
        ,@logical_read2 - @logical_read1
        ,case 
            when @physical_read2 - @physical_read1 = 0
                then - 1
            else (@logical_read2 - @logical_read1) / (@physical_read2 - @physical_read1)
            end as CacheQuality
        ,100-(((@physical_read2-@physical_read1)/(@cache_search2-@cache_search1))*100) as CacheHitPct
end

Best Answer

Just in case if any one wants to set up for CACHE QUALITY for Sybase ASE 15 and up, below script will help:

Changes for the script to work fast without overhead of querying MDA tables and the truncation issue is resolved by switching to int datatype.:

  • The where clause now makes the script work when Sybase ASE is under heavy loads. where CacheID = 0

  • The last change is just a logic change for when physical reads = 0, this way we save the logical reads. then @logical_read2 - @logical_read1

        -- CREATE A TABLE FOR DURATION SIMULATION
    if not exists(select 1 from sysobjects where name = 'TimeControl')
    begin
      create table TimeControl (counter int)
    end
    go

    -- CREATE TABLE FOR SQL PIPE RESULTS
    if not exists(select 1 from sysobjects where name = 'monCacheQuality')
    begin
      create table monCacheQuality (
      ServerName sysname
      ,CollectionDateTime smalldatetime not null
      ,PhysicalWrites decimal(15, 0) not null
      ,PhysicalReads decimal(15, 0) not null
      ,LogicalReads decimal(15, 0) not null
      ,CacheQuality decimal(15, 0) not null
      )
    end
    go
    create table cache_Begin (
  CacheID int not null,
  InstanceID tinyint not null,
  RelaxedReplacement int not null,
  BufferPools int not null,
  CacheSearches int not null,
  PhysicalReads int not null,
  LogicalReads int not null,
  PhysicalWrites int not null,
  Stalls int not null,
  CachePartitions smallint not null,
  CacheName varchar(30) null
)
go
create table cache_End (
  CacheID int not null,
  InstanceID tinyint not null,
  RelaxedReplacement int not null,
  BufferPools int not null,
  CacheSearches int not null,
  PhysicalReads int not null,
  LogicalReads int not null,
  PhysicalWrites int not null,
  Stalls int not null,
  CachePartitions smallint not null,
  CacheName varchar(30) null
)
go

use tempdb --- change this to be other than the db being monitored !!
go

declare @physical_read1 int
      ,@logical_read1 int
      ,@physical_write1 int
declare @physical_read2 int
      ,@logical_read2 int
      ,@physical_write2 int

while (select counter from TimeControl) =1 
begin
      select @physical_write1 = PhysicalWrites
            ,@physical_read1 = PhysicalReads
            ,@logical_read1 = LogicalReads
      from master..monDataCache
      where CacheID = 0

      waitfor delay '00:01:00'

      select @physical_write2 = PhysicalWrites
            ,@physical_read2 = PhysicalReads
            ,@logical_read2 = LogicalReads
      from master..monDataCache
      where CacheID = 0

      insert monCacheQuality
      select @@servername as ServerName
            ,getUTCdate()
            ,@physical_write2 - @physical_write1
            ,@physical_read2 - @physical_read1
            ,@logical_read2 - @logical_read1
            ,case 
                  when @physical_read2 - @physical_read1 = 0
                        then @logical_read2 - @logical_read1
                  else (@logical_read2 - @logical_read1) / (@physical_read2 - @physical_read1)
                  end as CacheQuality
end


-- Report the data that is collected.........
select *
from monCacheQuality








---- reset it back ... (in another Query window)
update TimeControl 
set counter = 0 
go




--- cleanup ... start and end cache table for next reuse 
drop table cache_Begin
go




drop table cache_End
go