主页 / dba / 问题 / 72554
Accepted
Vojtěch Dohnal
Asked: 2014-07-29 01:56:23 +0800 CST

具有历史数据的大型表分配了过多的 SQL Server 2008 Std。内存 - 其他数据库的性能损失

  • 772

基于这个问题和给出的答案：

SQL 2008 Server - 性能损失可能与非常大的表有关

我发现了 SQL Server 2008 的内存分配问题。

基本上，我们在 1 个 SQL Server 上有 3 个数据库（EkDB cca 300MB、SupervisionP cca 8 GB 和 Tarmac42 cca 42 GB），并且内存中有一个非常大的表。内存消耗是这样的：

内存消耗

几乎 6 GB 仅由一个表 PenData 分配，大约有 2.11 亿行。

该表定义如下：

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]

该表包含许多未使用或很少使用的历史记录，并且始终使用这样的条件访问数据

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

从来没有这个条件。数据以每小时一次的批量插入方式插入，数据库每天增长约 10 MB。

我曾尝试删除一些记录（8300 万条），然后运行 ​​DBCC SHRINKDATABASE - 目前看起来很有希望，但第二天 SupervisionP 再次占用了 5-6 GB。

我找不到 SQL Server 为这张表分配这么多内存的任何原因。问题是，其他数据库缓存的数据会定期卸载，并且在数据再次加载到内存之前，对它们的查询运行速度较慢。

编辑

我忘了提到它是 SQL Server 的标准版......所以分区是不可能的。我确实考虑过它，但我没有发现它仅适用于企业版。

编辑 2

我浏览了所有的存储过程，我必须承认有几个 SQL 命令可以访问比我想象的更多的行，即

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

SELECT MIN from all records 是否需要一直读取所有记录？我想是的，也许是愚蠢的问题......

编辑 3

所以我做了以下测试。我已经在测试服务器上恢复了 SupervisionP 数据库。恢复后它只占用了 1 MB 的缓存。然后我运行这个简单的查询：

  SELECT MIN(cas) FROM PenData

PenData 的主聚集键是如上所示的复合键 - 包含 Time + IndicatorID。因此，我认为对于 SQL Server 而言，在聚集索引中查找第一条或最后一条记录可能是相对容易的任务。但是内存使用量急剧增长到 1285 MB（测试服务器的总可用内存为 3.5 GB）。查询正在执行聚集索引扫描。

当我做：

 SELECT MIN(cas)  FROM PenData WHERE IDUkazatel=10

然后它是聚集索引搜索，内存保持在 1 MB - 它非常快而且还可以。

当我运行时：

 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

那么它需要很长时间并且内存分配急剧上升。然而，执行相同操作的以下命令会立即执行，并且不会分配内存！

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

编辑 5

我已经提出了一个新问题，因为这个命令似乎是问题的原因

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

与单独的 SELECT 相比，使用 OR 条件的索引查找要慢得多

sql-server sql-server-2008

4 个回答

  1. Best Answer

    老实说，您基本上是在说“我的应用程序正在使用所有这些数据，我该如何让它停止这样做？”。告诉您的最终用户或应用程序停止。不会顺利过关？没想到。

    有一种算法用于将页面保存在缓存中，显然这些页面使用得更频繁，老化更少。如果您想让其他页面在缓存中保留更长时间，请更多地使用它们。如果您希望表处于缓存中，请设置一个代理作业以每 2 分钟对该故事运行一次选择查询，这将使其具有较高的上次使用值和引用计数。

    如果问题是磁盘抖动，我建议询问预算并安装更多 RAM。根据 SQL Server 的工作方式，这是正常的。如果是 DAS/Local，您还可以要求更快的磁盘，或者如果是 iSCSI/SAN/NAS，请让您的存储团队调查磁盘的慢速特性。无论哪种方式，您的问题的症结在于慢速磁盘对抖动，一般慢速磁盘或没有足够的内存。

    最后，我会检查您的计划缓存，以确保它不会因一堆未正确参数化的一次性临时计划而臃肿。这可以带回几百 MB。

    • 4

  2. 希望您现在意识到这不是 SQL Server 的问题，而是您的数据库中的数据量和服务器上的少量内存的问题：

    建议：

    • 即使是标准版服务器，8GB 的​​内存量也非常小。我的笔记本电脑有20GB！？考虑升级到 64GB。如果不是，为什么不呢？它不会那么贵。
    • 监视页面预期寿命 (PLE) Perfmon 计数器在较长的时间段内，例如几天、一周。尝试将其与您看到的性能下降联系起来。这将是您获得更多 RAM 所需的证据

    该表包含许多未使用或很少使用的历史记录，并且始终使用这样的条件访问数据

    • 考虑创建一个存档表，一个 dbo.PenData 的副本来保存您并不真正需要的旧历史数据。创建一个通宵进程，将数据复制到存档表并将其从主 dbo.PenData 表中删除。在两个表（ main 和 archive ）的顶部创建一个视图。制定一条规则，规定您只在非工作时间查询视图。白天运行的查询仅访问主表。
    • （可选）添加一个流程，用于汇总/滚动/聚合查询所需的数据。您的查询逻辑似乎表明您只需要您只需要 365 条记录。
    • 删除不再需要的数据。将大部分数据保存在归档表中，并且只将您需要的记录保存在主表中，这将改变访问主表的查询的性能并减少服务器上的性能问题

    高温高压

    • 3

  3. 我同意肖恩提到的每一个事实。简单的答案是这不是问题，除非您向我展示内存不足的情况。这就是 SQL Server 的行为方式。就像我在线程中提到的那样，您参考了 SQL Server 内存管理是动态的。如果您看到特定数据库的更多页面，那么它会被查询频繁使用，否则它将被老化。数据库引擎不会傻到偏向特定数据库的内存。

    你可以做一个测试。这可能会给数据库增加负担（非常小心），但只是为了证明内存分配是动态的并且是根据需要分配的。在 Tarmac42 数据库中创建一个新表开始在其中插入记录可能是百万同时在一些具有大记录的不同表上运行 select * 您会看到数据库 Tarmac42 的内存分配增加，而 SupervisorP 数据库的内存分配减少。

    • 1

  4. 我建议首先对您的表使用分区。然后对除最后一个分区之外的所有分区上的所有索引使用页面数据压缩。还建议您将分区文件组的只读选项设置为 true（最后一个文件组除外）。您还需要调查您的查询以优化它们。

    • 0

相关问题