Avoid caching image datatype

[Marwin]

Hi all,

I have a database with 40GB of binary objects stored in image columns
in two tables.

Our database server is also used for another 15 databases.

SQL Server caches the image-column, causing the cache-hit-ratio to
dramatically decrease, since fetching a couple of binaries from the
image-column uses up the
memory, and throws other cached objects out of the cache.

Is there a way, and could someone tell me how, to avoid caching of
specific
columns/tables?

Regards
///Magnus

[Simon Hayes]

"Marwin" <al***@underlandet.com> wrote in message
news:f2**************************@posting.google.c om...

Hi all,

I have a database with 40GB of binary objects stored in image columns
in two tables.

Our database server is also used for another 15 databases.

SQL Server caches the image-column, causing the cache-hit-ratio to
dramatically decrease, since fetching a couple of binaries from the
image-column uses up the
memory, and throws other cached objects out of the cache.

Is there a way, and could someone tell me how, to avoid caching of
specific
columns/tables?

Regards
///Magnus

I don't believe there's any way to do this, and if MSSQL is caching the
image data rather than other data, then that suggests that the image data is
required more often anyway. By the way, when you say "cache hit ratio", do
you mean "buffer cache hit ratio"? The first relates to re-use of query
plans from the cache, the second is re-use of data pages, so that's the one
you want to watch. If the buffer cache hit ratio is low, the best option is
probably to add more memory to your server, assuming that you're seeing a
significant performance hit.

Simon

[Gang He [MSFT]]

There is no way to do that on specific tables. SQL Server internally
implemented a sophisticated page replacement algorithm that keeps the most
frequently used data page in memory. So the hot frequently accessed
objects/pages will not be kicked out of cache just because some apps read a
lot of image data.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
"Marwin" <al***@underlandet.com> wrote in message
news:f2**************************@posting.google.c om...

Hi all,

I have a database with 40GB of binary objects stored in image columns
in two tables.

Our database server is also used for another 15 databases.

SQL Server caches the image-column, causing the cache-hit-ratio to
dramatically decrease, since fetching a couple of binaries from the
image-column uses up the
memory, and throws other cached objects out of the cache.

Is there a way, and could someone tell me how, to avoid caching of
specific
columns/tables?

Regards
///Magnus