Sql-server – Large table with historical data allocates too much of SQL Server 2008 Std. memory – performance loss for other databases

Based on this question and the answers given:

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:

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.

When I run:

 SELECT MIN(cas) from PenData p WHERE IDUkazatel IN (SELECT IDUkazatel FROM Ukazatel WHERE IDZapisovac=10)

or

 SELECT min(cas) from PenData p WHERE IDUkazatel=24 OR IDUkazatel=25 OR IDUkazatel=26 OR IDUkazatel=97 OR IDUkazatel=97 OR IDUkazatel=98

then it takes long time and memory allocation goes up steeply.
Nevertheless the following commands, that do the same thing, are executed in an instant and no memory allocation!!

SELECT min(cas) from PenData p WHERE IDUkazatel=24 
SELECT min(cas) from PenData p WHERE IDUkazatel=25
...

EDIT 5

I have opened a new question since it seems that this command is the reason of the problem

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

Index seek much slower with OR condition compared with separate SELECTs