Caching vs More RAM | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Caching vs More RAM

I’m building a large-scale social networking application. Some of the application data is cached in the application layer however any changes made to these data needs to be UP-TO-DATE in the cache. I can’t use an SqlCacheDependency because these cached data varies per-user or access role and I don’t won’t a users data in the cache purged because some other user updated their data. I’m thinking of dumping the application cache layer. The database is expected to have 10 million rows in each table (blogs, blogposts, users, forums, posts, groups, albums, comments etc). If I don’t use caching, If the database server has 8-16gigs of ram, 2x quad-core processors (8 cpus), 2 74gig 15K RPM SCSCI raid 0, will performance be ok? I’m not worried about the application server at all, it’s the DB server I’m most worried about
SQL 2005 does provide such features with caching as all caches share single common caching frame work that is enabled automatically. This is possible in multiple different ways, say if your database is SQL Server 2005 then the cache can use .NET event notifications for synchronization which is quite efficient. But, if your database is SQL Server 2000, Oracle, or any other OLEDB compliant database, then the only way to synchronize is by polling the database looking for any updates. This method is of course not as efficient because the cache ends up making a lot of unnecessary trips to the database.
Fyi about taking advantage of caching
http://www.sql-server-performance.com/rd_data_cache.asp
http://www.c-sharpcorner.com/Upload…rticleID=3caa7d32-dce0-44dc-8769-77f8448e76bc Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
What if I don’t want to use the caching techniques mentioned? ALL the cache techniques mentioned in those articles are VERY inefficient for ANY table that are frequently updated (which is in my case). This is why I’m deciding whether to remove the caching completely (because my application requires the LATEST up to date data). I’m worried however about removing the cache….how efficient is MS SQL 2005 in handling complex joins and queries IF IT has FAST raid scsi disks and LOTS of RAM?
if you use stored procedures
and design you tables correctly, so the sp do not do stupid things fast scsi/ now sas disks, but 2 disks is not fast,
60 disks is fast
IMHO Caching you cannot avoid even though if you use SPs as it is accessed by SQL by default.
As explaining having proper SPs with optimized queries will get you more performance. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
No No No. Caching as in APPLICATION CACHING (NOTHING to do with SQL database ENGINE caching). For an example: My application has this method: public BookCollection GetBooks()
{
BookCollection books = Cache.Get("Books");
if( books == null ) //ok books is not in the application cache, lets hit the database
{
string sql = "SELECT * FROM books";
BookCollection books = LoadBooks(DataCommand.ExecuteReader());
Cache.Add("books", books, CacheFrequency.5Minutes);
}
return books;
}
The problem with this is that books need to RETURN THE MOST LATEST DATA. Sometimes, the application cache gets STALE and old. I can’t use SQLCacheDependancy because that flushes out EVERYTHING in the cache if ANY update occurs to the books table. So I’m thinking of removing ALL APPLICATION CACHING and just adding more RAM to SQL server so THE DATABASE ENGINE CAN PERFORM DATABASE CACHING (so it doesn’t have to hit the disk).
But I have never done this before, do you think I would loose performance if I took out the application cache and instead add more memory and tell sql server to use it to cache it inside the database engine?
You are clear now where you want to remove the caching, the below are useful to remove but it has downtrend to remove SQL caching from SPs too: DBCC FREEPROCCACHE – Remove all elements from the procedure cache.
DBCC FREESESSIONCACHE – Flush the distributed query connection cache.
DBCC FREESYSTEMCACHE – Release all unused cache entries from all caches. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
lol no no no, that’s not what I meant. I meant that I want to remove MY CUSTOM CACHING in the APPLICATION CODE. It caches "recordsets" returned BY SQL SERVER 2000 so next time someone requests that "recordsets", it comes from MY APPLICATIONS cache INSTEAD of the database. However, I decided to cancel this application cache and just hit the database everytime. Do you think it’s a bad idea?
It is not bad idea but if you know that you are getting the same result set all the time then why bother network trip to get the result set even it is cached on the db server? If your app. server has memory then better to cache for small result set like CAR MAKES and Years etc..and you can configure it to expire every 30/60 minutes or so… MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Hit database everytime, you have to consider the configuration of the server to see whether it can cope up (I presume as per the configuration above it can) with the frequent trips to cater the application needs. Unless you test it I wouldn’t suggest to take away or cancle the application cache. For information sake refer to Tom’s articlehttp://www.sql-server-performance.com/tp_analyzing_sql_server_2000_data_caching.asp here andhttp://sqlserver-qa.net/blogs/perft…-optimizer-reuses-for-better-performance.aspx too. On the other hand you might have a discussion with Webmaster of this site about how it is handled, I believe it is controlled via Snitz as it uses. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>