主页 / dba / 问题 / 72256
Accepted
Vojtěch Dohnal
Asked: 2014-07-25 03:59:23 +0800 CST

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

  • 772

我们有 SQL Server 2008，上面运行着 3 个活动数据库。

  • DB1 - cca 400 MB 大小
  • DB2 - cca 8 GB 大小
  • DB3 - cca 42 GB 大小 - 但大部分记录根本没有使用

在 DB2 中我们有一张这张表

CREATE TABLE [dbo].[PenData](
    [IndicatorID] [smallint] NOT NULL,
    [Time] [datetime2](0) NOT NULL,
    [Value] [real] NULL,
    [ValueMax] [real] NULL,
    [ValueMin] [real] NULL,
 CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED 
(
    [IndicatorID] ASC,
    [Time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

该表本身占用大约 8 GB 并有 283 029 812 条记录。该表中的绝大多数记录都是历史记录，很少或从不访问。但是一小部分最近的记录被大量使用，并且每小时都有许多新记录被插入到这个表中。

问题是我们最近观察到 DB3 中的性能问题。尽管 DB2 和 PenData 的性能还可以。

我的问题是：

1.PenData 表的大小是否是服务器整体性能的重要因素？这么多未使用的表记录如何影响服务器分配的内存？

2. 如果我从非常大的表 PenData 中删除一半的记录，我能否在服务器（在 DB3 中）上获得显着的性能提升？

3.当我没有访问Activity monitor的权限时，有什么工具可以监控性能吗？

编辑

我非常害怕地看到（使用答案中提供的脚本）PenData 表占用了整个 SQL Server 内存的 60-70%（相对较低的 cca 6 GB）。我不确定为什么，因为这是我自己编写的应用程序，我看不出任何原因，为什么这个表中的这么多行仍缓存在内存中。在我试图查看有多少 PenData 仍缓存在内存中之前，运行 SELECT COUNT(*) FROM PenData 也是我的错误。

我在这张表中省略了一个外键，所以我在这里展示它：

ALTER TABLE [dbo].[PenData]  WITH NOCHECK ADD  CONSTRAINT [FK_Data_Indicator] FOREIGN    KEY([IndicatorID])
REFERENCES [dbo].[Indicator] ([IndicatorID])

我已经使用 PenData 的 SET RECORDCOUNT 100000 以 100 000 条记录为一组删除了数百万条记录。现在它有 211 120 425 条记录。我已经运行了 DBCC SHRINKDATABASE (PenData, 20) - 只有在这之后 PenData 的内存消耗才显着减少。

其他数据库的性能和内存消耗变得更好。

但是一天后 PenData 表再次占据了几乎所有的内存......

编辑

我在一个存储过程中更改了一个 SQL 命令，现在一切都很完美，数据库 SupervisionP 仅占用 184 MB 的缓存！在这里查看详细信息

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

谢谢你的帮助。

sql-server sql-server-2008

2 个回答

  1. 您可以使用以下查询来确定每个数据库使用了多少 RAM：

    USE master;

SELECT d.name, CAST(COUNT(1) AS BIGINT) * 8192 / 1048576 AS MBinMemory
FROM sys.dm_os_buffer_descriptors bd
    INNER JOIN sys.databases d ON bd.database_id = d.database_id
GROUP BY d.name
ORDER BY d.name;

    要查看特定数据库中对象使用的内存，请执行以下操作：

    USE <DB_NAME_IN_QUESTION>

SELECT 
    obj.name AS ObjectName
    , index_id
    , CAST(count(*) AS BIGINT) * 8192E0 / 1048576 AS CachedMB
FROM sys.dm_os_buffer_descriptors AS bd 
    INNER JOIN 
    (
        SELECT object_name(object_id) AS name 
            , index_id
            , allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_name(object_id) AS name   
            , index_id
            , allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.partition_id 
                    AND au.type = 2
    ) AS obj 
        ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id 
ORDER BY count(*) DESC;

    上述查询中的最后一列显示了给定数据库对象使用的 RAM 量。

    您可以通过查看以下内容来查看查询统计信息：（还有很多其他内容）

    SELECT qs.execution_count
    , qs.total_worker_time
    , qs.total_elapsed_time
    , qs.total_logical_reads
    , qs.total_physical_reads
    , qs.total_logical_writes
    , qt.text /* This is the actual query text */
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.execution_count DESC;
    • 4
  2. Best Answer

    我会明智地回答你的问题

    1.在我看来，未使用的大表极不可能导致针对不同数据库运行的查询出现问题。SQL Server 内存在本质上是动态的，如果假设大部分内存被 DB1 Lazy writer 的数据页占用，检查点页面将协同工作以老化最近未使用或已提交记录的页面。因此，如果 DB2 数据页需要内存，它们将被授予，我认为不会出现内存紧缩

    2.不，我不这么认为，就像我说的那样，对特定数据库（DB2）的查询不会影响其他数据库（DB1）中存在的记录，如果该查询中未使用 DB1 的数据库表。你能定义你的性能问题是查询缓慢，磁盘缓慢内存紧缩什么？请使用此链接分析运行缓慢的查询

    3.是的，市场上有很多监控工具，我的环境中有 Spotlight，您也可以使用 SCOM。

    您可能会参考此白皮书Troubleshooting performance problems in SQL Server

    • 3

相关问题