I have application that makes different queries with different results so the caching in my case is harmful.
This means there is no common data that sql may benifit from it to fetch the result from memory directly instead of making hard disk access.
+1
A:
Your question is wrong: caching means data is in memory, and avoids disk access.
In any event, SQL Server caching is not harmful. For the pedants, there are always exceptions but you'd have to have seriously screwed up code and configuration before it was.
gbn
2009-05-24 08:14:13
The sql takes alot of memory over the time and I do not get benifit from this huge memory consumption, the logic of the application is to get random data from the database so I do not want this caching at all
Ahmed Said
2009-05-24 08:33:30
You can restrict memory usage in sql using management studio though i am still not sure why you would - how much random data is there ? You can do this on server properties options in management studio; reduce the maximum memory down to the minimum and restart sql server. The less memory you give it the more it will thrash your disks.
u07ch
2009-05-24 08:49:24
@ahmed: SQL Server uses memory for a reason. It won't normally use memory unless it needs it.
gbn
2009-05-24 09:08:13
A:
Turning off (or attempting to turn off) SQL Server caching is thinking about the problem completely the wrong way. If the data is cached in your data layer tier, you should refresh it there. SQL Server will never serve up stale data.
Mitch Wheat
2009-05-24 08:20:56
+1
A:
From your comments it sounds like you don't want SQL to use at much memory as you feel that it's pointless as any queries you run are effectively random.
Firstly, this memory is probably being used to store indexes and query plans, rather than the actual data you want returned, therefore you will probably find this cache is a lot more useful than you think.
Secondly, it is highly unlikely that sql using this amount of memory will cause any reduced performance on a truly random unseen query. If SQL Server needs to go to disk to get any information then the amount of memory its using is barely relevant.
However, it is possible to reduce the amount of memory that SQL Server uses. (I don't know where the option is in SQL 2008, but I assume its fairly similar) In Management Studio, right click on the Server and do Properties. Under there you will have a page for Memory. In here you can select the Minimum and Maximum values that SQL will use. This will effectively limit any caching sql does based upon your values, rather than the physical servers limitations.
I really don't think you will see any performance gains though. In my experience SQL is always best left doing it's own thing.
Robin Day
2009-05-24 08:55:34
Okay, if I limit the maximum memory for sql server what will happen if it needs more than the specified maximum? it will crash?
Ahmed Said
2009-05-24 10:00:50