sql caching disadvantage?

Question

Hi. I have a web server with a lot of web sites with many database operations, and i am tryng sql caching as a way to improve the performance of my server.

In general, is there any disadvantage about sql caching in a common environment?

Thanks

Answer 1

Well, caching consumes RAM memory, so you'll need plenty of that.

I'm not sure about what caching mechanism SQL server employs, but it might be possible that your queries return stale data for some time.

Your best options of performance improvement is to load as much data into RAM as possible instead of caching.

Answer 2

The main problem with caching in a normal environment is cache expiration and stale data.

If you invalidate your cache every time data changes, you could end up rarely or never hitting the cache.

If you try to invalidate just the part of the cache that is changed, you have extra processing time to determine what to invalidate.

If you do not invalidate the cache or have cache timers, you may end up with stale data.

Depending on your environment and your requirements, you need to pick which solution best meets your needs. Sometimes it is ok to have some stale data, and in other applications it is not.