Recently my customer's sybase production db has performance problem for some queries, but not in their testing db. I noticed that the I/O messages from the showplan of the same query on the db servers are different.
from production db server, it is 16kbytes, while on test db server it is 32kbytes, but i found no 32kbytes I/O size from sybases website....
from sybase website:
================Quote from Sybase Site================
The I/O messages are:
Using I/O size N Kbtyes for data pages.
Using I/O size N Kbtyes for index leaf pages.
These messages report the I/O sizes used in the query. The possible sizes are 2K, 4K, 8K, and 16K.
I am confused here. Could anyone enlighten me? also want to ask whether the different I/O size could affect the performance of the query greatly.
You are confusing 2 things, one thing is pagesize of the server 2k thats the size of the blocks where the data is written on disk.
Another thing is cache size, thats memory.
Check sp_cacheconfig command on sybase.
Normally for cache you have the global size for example i. your case 2k server and with a cache of 20 mb.
2k cache - used for some ops. disk init, some dbcc
16k cache- ( max size 4 times server pgsize) used for other comands like dbcc checkdb
Regarding the performance if its data cache like this ones, i dont think they have impact on performance since they are used for data, indexes, etc.
Procedure caches however hace impact, they store statistics, parallel query plans.