By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

Avoid caching image datatype

P: n/a
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
Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

"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
Jul 23 '05 #2

P: n/a
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

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.