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 :(

  • 1
    Hard to say without more precise detail. However there are some queries that can result in an DBMS using disk despite seemingly being capable of storing the whole DB in RAM. Queries using JOINs can result in large temporary tables that need disk space. Some text-based columns are often loaded to disk. And sometimes ORDER BY can result in disk space being used to store the results of a sort. – dave Jul 5 '15 at 7:27
  • Ok, I've updated question with sample query, it actually search in string stored in one of columns.... So I guess SSD Raid is only option (or big enough RAM disk) ? – mike86 Jul 5 '15 at 7:55
  • I suspect it's having to tablescan on your the like component of your query. – ServerMonkey Jul 8 '15 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.