I have two separate servers, one application and one SQL server. There's a GIS (Geographical Information Systems application which you can zoom in and zoom out on a map and see the objects on it, similar to Google Maps) application running on the application server and SQL server 2005 Standard edition runs on the database server.
Database server specifications are.
64 bit Windows 2008 Server Operating System
4 x Intel Xeon E7530 CPUs
64 GB RAM
The size of the database is 3 GB, but some of the tables have 1 to 2.5 million rows.
My problem is when I make a zoom on the GIS Application, around 7 queries (with views) goes to the database server and the database server responds very slow. I tried to search on the internet about adding indexes to the views but unfortunately can't add index as there are image fields on the views.
SQL server's automatic caching not performing very well as the queries sent by the application is always changing (queries depend on the zoom level on the map, starting point of the zoom etc.)
Also I checked the server and saw that sqlservr.exe is only using 2.4 GB of ram.
My questions are:
1) Is it possible to increase the usage of RAM up to 50 GB etc?
2) Is it possible to add the entire database to RAM?
Each zoom takes around 9-16 seconds to complete which is unacceptable in my conditions.
First of all, I'd like to point out that scaling up the hardware is something i generally recommend when other options have been tried. You are correct, you cannot create clustered index on a view to materialize it. What you can do however, is looking at the select statement in your view, and create or alter indexes to support your view. That could solve your issues.
As for memory usage, only 2.4GB on a 64GB computer. That sounds pretty like either
* you may have installed 32-bit SQL Server on a 64-bit OS
* SQL Server does not need more memory
You can have a look at the Buffer Manager: Page Life Expectancy performance counter. If this still grows or stay unchanged when you run the 7 queries, it does not seem that these queries is causing memory pressure. Normally, I'ld say that this performance counter should stay over 900. While it grows though, even if it is below 900) you don't currently have memory pressure.
So, the short answers:
1) Yes, and SQL Server will normally do this without any help (64-bit to allocate so much memory)
2) No. You cannot enforce database to stay in RAM (without cache warming), but as long as SQL Server has enough memory that may eventually happen.