So I have allocated 40GB of RAM for SQL Server.
I can see that after I first run a query on database, that 40GB memory limit will be quickly filled during first run, however when I will do exactly same query there is no impact on performance, SQL still reads from hard drive instead of RAM.
Whole database is 42GB (55 million rows, 18 columns, 1 table, average result after query ~ 80 rows (using TOP and order by to select next 80 rows)) , maximum allocated size in RAM is 40GB.
SQL Server 2008 R2, Windows 2008 R2 DataCenter...
Example query: select * from DB..Table where Column A > 333 and Column B > 3333, and column C like '%test%' order by username
How force SQL Server to use data stored in RAM ???
Maybe it is better idea to create 42GB RAM disk and just move whole database in there ???
Currently I am using 15k 4x 450GB SAS in RAID 0 (not enough of performance in RAID10, plus I am doing backups every 6 hours) but seems that something is wrong with one of drives (~150MBps (normally 520MBps)) so currently I am waiting on delivery of 4x SSDs which I want to use in RAID 0 and hopefully achieve at least 1.2GBps (1x SSD achieves with this database ~ 570MBps).
How can I use that RAM as it seems that SQL Server is just wasting it without any benefits to the performance...
This database is not getting a lot of queries but every query is fairly big with a lot of 'where's.
CPU is 2x Xeon E5645 and isn't the bottleneck (almost always 10-15% usage during running queries)
This is result table calculated and created from two other tables (each over 1TB of data) and anticipated grow is ~ 20GB per day up to ~ 300 millions of rows, unfortunately I can't achieve comfortable performance :(