Click here to Skip to main content
15,877,966 members
Please Sign up or sign in to vote.
3.77/5 (3 votes)
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.
Posted
Comments
thatraja 4-Apr-14 4:55am    
You forgot to include some more details like 1) how do you import? 2) what's the content of query? 3) Are you using view or stored procedure? N) etc.,

So, update your question with these details
Muli G. 5-Jun-14 10:14am    
1. I think sql server doesn't release memory once it is allocated and most of the time it keeps the maximum amount of memory that was set to him, have you tried to close the connection in order to see if something that is session related like temporary tables are still available and not dropped?

2. I think the performance will be the same in case of index exists on this column because it will go the right side of the BTREE and get the valuse, however if you don't have an index it will need to scan all records in case of MAX and sort all the records in case of TOP 1, sorting is a very heavy operation so I suggest to go with the MAX.

Good luck :)
Muli G. 5-Jun-14 10:24am    
BTW there is something in the msdn forum about the memory of SQL server the confirms sql doesn't release memory unless it has to:
http://social.msdn.microsoft.com/forums/sqlserver/en-US/90d5422f-c924-4211-9323-7373e9d5ad1f/sql-server-not-releasing-memory-back
Amol_B 13-Jun-14 5:53am    
Thanks for the link.

1 solution

Thanks Muli G for sharing link.
MSDN link[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900