Sybase SQL - 發生截斷錯誤。命令已中止並找到記憶體質量

需要以下程式碼的幫助，因為它因截斷錯誤而失敗

發生截斷錯誤。命令已中止。

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 ASE 15 及更高版本設置記憶體質量，以下腳本將有所幫助：

腳本的更改可以快速執行而無需查詢 MDA 表的成本，並且通過切換到int數據類型來解決截斷問題。：

• 現在，where 子句使腳本在 Sybase ASE 負載過重時工作。在哪裡CacheID = 0
• 最後一個更改只是物理讀取 = 0 時的邏輯更改，這樣我們就保存了邏輯讀取。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

引用自：https://dba.stackexchange.com/questions/39297