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

asked 20 Aug '16, 20:46

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 22 Aug '16, 15:40

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

(22 Aug '16, 11:56) Nick Elson S...
Replies hidden

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

(22 Aug '16, 12:05) Mark Culp
1

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

(22 Aug '16, 14:42) Breck Carter

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?

(22 Aug '16, 16:09) Nick Elson S...
Replies hidden

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

(23 Aug '16, 11:35) Breck Carter

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

permanent link

answered 22 Aug '16, 14:45

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

1

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

(22 Aug '16, 15:15) Mark Culp
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×11

question asked: 20 Aug '16, 20:46

question was seen: 1,439 times

last updated: 23 Aug '16, 11:35