cancel
Showing results for 
Search instead for 
Did you mean: 

Why do CacheHits and CacheRead return zero for a 17.0.4.2100 database?

Breck_Carter
Participant
1,715

Update 3: This behavior was caused by the new dbsrv17 -k option which disables all sorts of performance-related properties like PROPERTY ( 'ActiveReq' ).


Update 2: FWIW the behavior of CacheHits and CacheRead was first noticed on August 1 so it dates from before build 2100.


Update 1: This may be the tip of the iceberg... there may be other properties returning zero, such as the database-level ActiveReq and ConnCount properties. The phrase "may be" is carefully chosen because I haven't created a reproducible like the one shown below, I have only noticed that the Foxhound 4 Database Monitor display for a fresh 17.0.4.2100 database ... is ... starkly ... useless.


When a database file created with 17.0.0.1359 is run on an 17.0.4.2100 engine, the database-level and connection-level CacheHits and CacheRead properties all work OK; see Test 1 below.

However, when the database file is created with 17.0.4.2100, all those properties return zero; see Test 2 below.

Background: This startling symptom turned up in Foxhound 4 regression testing. Test 1 used the original demo17 database as delivered with SQL Anywhere 17 GA, and Test 2 uses a fresh 17.0.4.2100 database filled from demo17 via dbunload and dbisql reload.

-- Test 1: 17.0.0.1359 database file on 17.0.4.2100 engine

SELECT COUNT(*) FROM GROUPO.SalesOrderItems;

SELECT version AS database_file_version FROM SYSHISTORY WHERE operation = 'INIT';

SELECT @@VERSION AS engine_version, Number, PropName, Value 
  FROM sa_conn_properties() 
 WHERE PropName IN ( 'CacheHits', 'CacheRead' )
 ORDER BY Number, PropName;

SELECT @@VERSION AS engine_version, PropName, Value 
  FROM sa_db_properties() 
 WHERE PropName IN ( 'CacheHits', 'CacheRead' )
 ORDER BY PropName;

COUNT()
1097

database_file_version
'17.0.0.1359'

engine_version,Number,PropName,Value
'17.0.4.2100',1,'CacheHits',65257
'17.0.4.2100',1,'CacheRead',65257
'17.0.4.2100',24,'CacheHits',60139
'17.0.4.2100',24,'CacheRead',60139

engine_version,PropName,Value
'17.0.4.2100','CacheHits',255825204
'17.0.4.2100','CacheRead',255825205

-- Test 2: 17.0.4.2100 database file on 17.0.4.2100 engine

SELECT COUNT(*) FROM GROUPO.SalesOrderItems;

SELECT version AS database_file_version FROM SYSHISTORY WHERE operation = 'INIT';

SELECT @@VERSION AS engine_version, Number, PropName, Value 
  FROM sa_conn_properties() 
 WHERE PropName IN ( 'CacheHits', 'CacheRead' )
 ORDER BY Number, PropName;

SELECT @@VERSION AS engine_version, PropName, Value 
  FROM sa_db_properties() 
 WHERE PropName IN ( 'CacheHits', 'CacheRead' )
 ORDER BY PropName;

COUNT()
1097

database_file_version
'17.0.4.2100'

engine_version,Number,PropName,Value
'17.0.4.2100',1,'CacheHits',0
'17.0.4.2100',1,'CacheRead',0
'17.0.4.2100',3,'CacheHits',0
'17.0.4.2100',3,'CacheRead',0

engine_version,PropName,Value
'17.0.4.2100','CacheHits',0
'17.0.4.2100','CacheRead',0
Former Member
0 Kudos

That is odd ... I don't see this behaviour with 2088 ... will check into this more ...

MarkCulp
Participant
0 Kudos

I tried it with 2109 and also don't see the issue.

Breck_Carter
Participant

Apparently the new dbsrv17 -k option does more than just "Controls the collection of Windows Performance Monitor statistics and statement performance summary statistics."

Former Member
0 Kudos

Not even when initialized with build 2100 either. (neither 32bit nor 64bit)

Cannot suggest what has gone wrong. Is it possible you have mismatched binaries?

Breck_Carter
Participant
0 Kudos

Let's just say I am on a journey of discovery 🙂

Accepted Solutions (1)

Accepted Solutions (1)

Breck_Carter
Participant

Don't use dbsrv17 -k unless you want to fly blind, performance-wise.

MarkCulp
Participant

Yes, that would do it. I looked at the code and -k will affect the accumulation of cache hits and cache reads (and maybe some others?) on platforms with an affinity scheduler (i.e. Windows). This is done for performance reasons.

FWIW: The 17 documentation[1] states: "This option should only be used in situations where the database server is running on a multi-processor computer where it can be shown by testing to improve performance"

[1] http://dcx.sap.com/index.html#sqla170/en/html/3bc9a65f6c5f10148ce4e54a4344dfc5.html

Answers (0)