Based on this question and the answers given:

http://dba.stackexchange.com/questions/72256/sql-2008-server-performance-loss-possibly-connected-with-a-very-large-table

I have discovered memory allocation problem with SQL Server 2008.  

Basically we have 3 databases (EkDB cca 300MB, SupervisionP cca 8 GB and Tarmac42 cca 42 GB) on 1 SQL Server and one very large table sits in memory. The memory consumption is like this: 

![MemoryConsuption][1]

Almost 6 GB are allocated by only one table PenData with cca 211 millions of rows.

This table is defined as follows:

	CREATE TABLE [dbo].[PenData](
		[IDUkazatel] [smallint] NOT NULL,
		[Cas] [datetime2](0) NOT NULL,
		[Hodnota] [real] NULL,
		[HodnotaMax] [real] NULL,
		[HodnotaMin] [real] NULL,
	 CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED 
	(
		[IDUkazatel] ASC,
		[Cas] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[PenData]  WITH NOCHECK ADD  CONSTRAINT [FK_Data_Ukazatel] FOREIGN KEY([IDUkazatel])
	REFERENCES [dbo].[Ukazatel] ([IDUkazatel])

	ALTER TABLE [dbo].[PenData] CHECK CONSTRAINT [FK_Data_Ukazatel]


This table contains many historical records that are not used or used very seldom and the data are always accessed using condition like this 

     WHERE IDUkazatel=@a AND Cas BETWEEN @b AND @c

never without this condition. 
Data are being inserted every hour in series of bulk inserts, the database grows cca 10 MB per day.

I have tried deleting some records (83 milions) and then running DBCC SHRINKDATABASE - it seemed promising for the moment but the next day SupervisionP occupied againg 5-6 GB. 

I cannot find any reason why SQL Server allocates so much memory to this one table.
The problem is, that the other databases cached data get regularly unloaded and the queries on them are running slower before the data get loaded in memory again.

**EDIT**

I forgot to mention that it is Standard edition of SQL Server... So partitioning is not possible. I did consider it but I did not observe that it is for enterprise edition only.

**EDIT 2**

I have looked through all the stored procedures and I must admit that there are several SQL commands that may access more rows then I supposed, namely

	SELECT @minCas=MIN(cas) FROM PenData WHERE IDUkazatel=@IDUkazatel 

    SELECT @StazenoDoReal=MAX(cas) from PenData p INNER JOIN Ukazatel u ON u.IDUkazatel=p.IDUkazatel WHERE u.IDZapisovac=@IDZapisovac 

	SELECT  TOP 365   DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.PenData.Cas)) AS Den, MAX(dbo.PenData.Hodnota) AS MaxHodnota
	FROM         dbo.Zapisovac INNER JOIN
						  dbo.VyrobniLinka ON dbo.Zapisovac.IDVyrobniLinka = dbo.VyrobniLinka.IDVyrobniLinka INNER JOIN
						  dbo.Ukazatel ON dbo.Zapisovac.IDZapisovac = dbo.Ukazatel.IDZapisovac INNER JOIN
						  dbo.PenData ON dbo.Ukazatel.IDUkazatel = dbo.PenData.IDUkazatel
	WHERE     /*(dbo.PenData.Cas >= @Od) AND (dbo.PenData.Cas <= @Do) AND*/ (dbo.Zapisovac.IDVyrobniLinka = @IDVyrobniLinka AND dbo.Zapisovac.IDTypZapisovace!=4)
	GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.PenData.Cas)), ISNULL(dbo.Ukazatel.MinHodnotaProvoz, 10)
	HAVING MAX(dbo.PenData.Hodnota)>ISNULL(dbo.Ukazatel.MinHodnotaProvoz, 10) OR MAX(dbo.PenData.Hodnota) IS NULL
	ORDER BY Den DESC

Does SELECT MIN from all records need to read all the records all the time? I suppose yes, maybe silly question...

**EDIT 3**

So I did the following test. I have restored SupervisionP database on testing server. After restore it has occupied only 1 MB in cache. Then I run this simple query:

      SELECT MIN(cas) FROM PenData

The primary clustered key for PenData is a composite as shown above - containing Time + IndicatorID. So finding first or last record in a clustered index could be relatively easy task I suppose for SQL Server. But the memory usage grew very steeply to 1285 MB (the test server has 3,5 GB total usable memory).  The query is doing Clustered Index Scan.

When I do:

     SELECT MIN(cas)  FROM PenData WHERE IDUkazatel=10

then it is Clustered Index Seek and memory remains on 1 MB - it is very fast and OK.


  [1]: https://i.stack.imgur.com/AXa39.gif