I have ASP.NET web site that does it's own independent caching of data and data does not change for long periods of time, so it does not need to query SQL Server second time with same query. I need to improve performance of first time (virgin) queries that go to that SQL Server. Some queries process so much data that they may cause SQL Server to use
tempdb. I don't use temp table variables or temp tables, so SQL Server decides to use
tempdb by itself whenever it needs to.
My db size is 16Gb, I have 32Gb of physical RAM available on my server machine.
I understand that MS SQL Server caching strategy tries to keep data in RAM to speed-up performance of similar queries if they need same data to be loaded again. In addition to that it will try to use available RAM instead of tempdb to speed-up performance without causing disk access.
I suppose that when query that needs to store something in tempdb SQL Server comes and there is not enough RAM available, SQL Server has 2 choices:
1) to unload some cached data and use spared RAM instead of tempdb to avoid disk writes
2) keep cached data for future queries and start using tempdb, which causes writes to slow disk.
I don't know what choice SQL Server will make in this situation, but I would like it to make choice #1 because I care only about performance of first-time (virgin) queries, because I never send same query to SQL Server again (though I may send similar query).
What is SQL Server caching strategy for this scenario ?
How does it balance usage of RAM between avoiding tempdb for virgin queries and speed of second time queries ?
Is it possible to configure SQL Server in such a way that it will make choice #1 ? If yes then how ?
How else can I boost performance of all virgin SQL queries ?
Since I don't know about SQL Server caching strategy I want to place database on RAM Disk. This will make sure that any virgin query has high speed of loading of uncached data even if SQL Server always makes choice #1. The risk of it is that SQL Server may start using more tempdb with less available RAM (only 16Gb left after I use 16Gb for RAM Disk) if it keeps making choice #2, which will slow down those virgin queries that cause spills into
I am interested in solution for SQL 2008 R2, but I guess it's probably the same for SQL 2008, SQL 2005 and may be SQL 2000.
There are no other applications running on that box, it's dedicated to SQL Server. Website runs on separate box.
It's SQL Server 2008 R2 Standard Edition 64 bit on Windows Server 2008 R2 Enterprise 64 bit.
I run only read-only queries and database is set to be read-only.
Let's assume that there are already good indexes. This question is about SQL Server making choice #1 vs choice #2, how does it make it, if there is a way to control it and if RAM Disk helps it to make the right choice for virgin queries.