Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Probably the cacheQuality or CacheHitPct is being truncated. I suspect CacheHitPct, but cant get it to work. –  Kin Apr 4 '13 at 22:29
    
What's the table definition for monCacheQuality? –  Simon Righarts Apr 4 '13 at 22:34
    
Updated my SQL with create table. –  Kin Apr 4 '13 at 22:41
    
And idea or help to the right resource is highly appreciated –  Kin Apr 8 '13 at 19:22
1  
quick question: col CollectionDateTime is smalldatetime, while function getUTCdate() returns datetime (at least in MS SQL). Do you have an implicit convert there (not familiar with Sybase)? PS: can you add also the schema for master..monDataCache? –  Marian May 10 '13 at 9:02

1 Answer 1

up vote 0 down vote accepted

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
    
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.