Based on this question and the answers given:
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.
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.
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...
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)
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 ...
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