i have written a program that each 10 seconds hit a query in sql server 2012 db and than refresh a graphic page; this page is function of a single record from 4 different table in the database.
The db has set his maximum size as 1024 MB, while his minimum size is 0.
The db save a row each minutes, than in just 1 Month there are over 50.000 record each table, so the growing memory consumption (observed by Windows perfomance meter) raise the maximum size.
When the maximum has achieved, a later query (just a record) selected from these 4 table, takes about 15 seconds and the UI looks like freezed.
I have observed that instead a single record asked, SQL returns the entire table; i think cause it is cached...
Any suggestion to improve the speed?
What I have tried:
DBCC... buffer clean... eccetera