Início / dba / Perguntas / 39297
Accepted
Kin Shah
Asked: 2013-04-05 14:13:43 +0800 CST

Sybase SQL - Ocorreu um erro de truncamento. O comando foi abortado e encontra CACHE QUALITY

  • 772

Precisa de ajuda com o código abaixo, pois falha com erro de truncamento

Ocorreu um erro de truncamento. O comando foi abortado.

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
sybase sybase-ase

1 respostas

  1. Best Answer

    Caso alguém queira configurar a CACHE QUALITY para Sybase ASE 15 e superior, o script abaixo ajudará:

    Mudanças para que o script funcione rápido sem sobrecarga de consultar tabelas MDA e o problema de truncamento é resolvido ao mudar para tipo de intdados.:

    • A cláusula where agora faz o script funcionar quando o Sybase ASE está sob cargas pesadas. OndeCacheID = 0

    • A última alteração é apenas uma alteração lógica para quando leituras físicas = 0, assim salvamos as leituras lógicas.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
    • 0

relate perguntas