1

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:

MemoryConsuption

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

migrated from stackoverflow.com Jul 28 '14 at 11:31

This question came from our site for professional and enthusiast programmers.

  • What's the range of @b and @c? Is it the same all the time? Does it vary? If it varies, how much? – Mike Sherrill 'Cat Recall' Jul 28 '14 at 10:32
  • @MikeSherrill'CatRecall' @b AND @c are basically date ranges 1 - 30 days long but going usually maximum 2 months in the past. – Vojtěch Dohnal Jul 28 '14 at 11:37
  • @VDohnal - how much memory is in your server? If you have anything less than 64GB do your server a favor and add more memory. – Max Vernon Jul 28 '14 at 13:53
  • @MaxVernon It is now 12 GB, virtualized server. One thing is that I have difficulties to talk with the guys who are responsible for this server. On the other hand it is quite supprising fact for me and I would like to know why does it happen. – Vojtěch Dohnal Jul 28 '14 at 14:28
  • 4
    The table is in memory only because the data has been accessed, and no other data has taken it's place. SQL Server does not mysteriously "load" tables without necessity. If you are seeing poor performance, you need to communicate the issue to the people managing the server. Show them the results from the queries you've ran (from the answers to your previous question, etc). – Max Vernon Jul 28 '14 at 14:32
4

Honestly, you're basically saying "my application is using all of this data, how do I make it stop doing that?". Tell your end users or application to stop. Not going to go over well? Didn't think so.

There is an algorithm that is used to keep pages in cache, obviously these pages are used more often and aged out less. If you want to keep other pages in cache longer, use them more. If you want a table to be in cache, setup an agent job to run a select query against that tale every 2 minutes, that'll keep it with a high last used value and reference count.

If the problem is disk thrashing I would suggest asking for budget and installing more RAM. This is normal per how SQL Server works. You could also ask for faster disks if DAS/Local or have your storage team investigate to the slow nature of your disks if iSCSI/SAN/NAS. Either way the crux of your issue is either slow disk do to thrashing, slow disks in general, or not enough memory.

Lastly, I would check your plan cache to make sure it isn't bloated with a bunch of single use ad-hoc plans that aren't parameterized correctly. That could bring back a few hundred MB.

  • The problem is that I believe that my application does not use the data, but I may be wrong and then I shall give the credit to your answer. Could you please be more specific, how can I check for cached plans? msdn.microsoft.com/en-us/library/ms187404.aspx, D? – Vojtěch Dohnal Jul 28 '14 at 12:41
  • 2
    @VDohnal - Something touches the data pages or they wouldn't be in cache. This could be from a bad query plan (such as a CL Scan) or from a query that really does read all of the data. This really shouldn't be a problem unless your free page list is constantly extremely low (think 0-200ish). I'm not advocating throwing out your cache, I'm just wondering if you're having more single-use ad-hoc plans accessing the data than you thought. For example: select objtype, sum(size_in_bytes)/1024 AS Single_use_KB from sys.dm_exec_cached_plans where usecounts = 1 group by objtype – Sean Gallardy Jul 28 '14 at 13:33
  • This query returns plans for all databases on server, I think. It is 278 168 KB, which is quite okay I suppose... In fact the problematic database SupervisionP has excellent performance but sucks memory from the other DBs. It has lowered to 3 GB today but again after a hour it has risen to 5 GB. It is doing bulk inserts every hour, could it be a problem? – Vojtěch Dohnal Jul 28 '14 at 14:19
  • Who cares how much data it is using if you can't change the fact? If the application ad users need to use that data then there isn't anything you're going to be able to do about it. If it isn't causing performance issues, all I would say is to keep a close eye on it and make sure it doesn't become a performance issue. Using memory is how SQL Server works, there is nothing you can do (arguably) to stop that. If this is still causing you concern then add more memory to the server. – Sean Gallardy Jul 28 '14 at 15:22
  • 1
    If you can change the code then you'll need to go through and validate the execution plans, statistics, indexes, etc, to make sure you're at a place you want to be. In your example with the min and max, do you have an index on that column that can satisfy the query? Otherwise yes it'll need to scan all the data to figure it out. This may or may not change the number of data pages needed based on other queries or how other parts of the system works - but it's worth a try if you have the time to spare. – Sean Gallardy Jul 28 '14 at 17:06
3

Hopefully you now realise this is not a problem with SQL Server but a problem with the volume of data you have in your database and the small amount of memory on the server:

Recommendations:

  • 8GB is a really small amount of memory for even a Standard Edition server. I have 20GB in my laptop!? Consider upgrading to 64GB. If not, why not? It wouldn't be that expensive.
  • monitor Page Life Expectancy (PLE) Perfmon counter over an extended period of time, eg a few days, a week. Try and tie it with performance degradation you see. This will be the evidence you need to get more RAM

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

  • consider creating an archive table, a copy of dbo.PenData to hold the old historical data you don't really need. Create an overnight process that copies the data to the archive table and removes it from the main dbo.PenData table. Create a view over the top of the two tables ( main and archive ). Make a rule which says you only query the view out-of-hours. Queries running in the daytime only access the main table.
  • Optionally add a process which summarises / rolls / aggregates the data you need for your query. Your query logic seems to suggest you only need you only need 365 records.
  • Delete data you no longer need. Having most of your data in your archive table and only the records you need in the main table will transform the performance of queries that access the main table and reduce performance problems on the server

HTH

1

I agree with every fact that Sean mentioned. The simple answer is this is not an issue UNLESS you show me out of memory condition. This is how SQL Server behaves. Like I mentioned in thread you took reference SQL Server memory management is dynamic. If you see more pages for particular database then its being used frequently by queries or other wise it will be aged out. Database engine is not fool to favor memory to particular database.

You can do a test. This might put load on dataabse(so careful) but just to prove memory allocation is dynamic and is allocated as per needed. create a new table in Tarmac42 database start inserting records in it may be million at the same time run select * on some different table with large records you would see memory allocation increasing for database Tarmac42 and decreasing for SupervisorP database.

0

I suggest that use partitioning for your table in first. and then use page data compression for all of index on all partition except last partition. also recommendation you that set read only option of partitioned file group to true(except last file group). also you need to survey your queries in order to optimize them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.