Dear All,
Greetings.
Could you please let us know your thoughts on below problem:
We are using SQL 2012 Enterprise Edition in our ERP application.
We have around 40 GB of database in the same which is kind of Datawarehouse.
There is import process which runs daily on the server and imports data from multiple systems to destination database.
There are different SQL based calculations,updation of tables involved which aggregate functions ,etc.
We have tables containing records more than 5 million and 25 million.
The windows machine has server 2008 R2 as OS and 20 GB RAM with 64 BIT.
We have allocated 12 GB to SQL Server.
We have our web application on the same server.
Problem Statements :
1) When the importing runs, SQL Server uses all the available memory and so it increases physical memory of the server.
As the physical memory gets increased, it affects the performance of the web application as well.
SQL server does not releases the physical memory once the query is executed. It only releases once the instance is restarted.
(E.g. If we fetch records from 5 million records table then it takes the memory but do not releases once data gets fetched)
We have applied indexes to the table.
2) We have used MAX function at some places to fetch MAX (Date) from table which contains records varies from 5 million to 25 million.
Please guide us which approach should we follow.
I) To use MAX.
II) To use TOP 1 ... ORDER BY DESC.
Please guide us further on above two problems.
Thanks in Advance.