IT俱乐部 MsSql Sql Server缓冲池、连接池等基本知识详解

Sql Server缓冲池、连接池等基本知识详解

发布: 2025年 2月 4日 183阅读

缓存池

缓存机制是指将经常访问的数据或查询结果保存在内存中，以提高查询性能和整体系统响应速度

缓冲池 (Buffer Pool)：

  • SQL Server 中最大的一块内存区域，用于存储从磁盘读取的页，以减少对磁盘的直接访问
  • 缓冲池中的页包括数据页、索引页、系统表页等

计划缓存 (Plan Cache)：

  • 将执行过的查询计划缓存在计划缓存中，以便重复使用，减少查询解析和优化的开销
  • 查询计划是查询优化器生成的执行查询的步骤

数据缓存 (Data Cache)：

  • 数据缓存是缓冲池的一部分，专门用于缓存数据页
  • 当查询访问表中的数据时，SQL Server 会首先检查数据缓存，如果数据已经在缓存中，则直接返回，否则从磁盘读取并缓存

一、查看缓存使用情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 查看缓冲池使用情况
DBCC DROPCLEANBUFFERS;  -- 清除缓冲池
 
SELECT
    COUNT(*) AS cached_pages_count,
    (COUNT(*) * 8.0) / 1024 AS cached_pages_in_MB
FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID('YourDatabaseName');
 
-- 查看计划缓存使用情况
SELECT
    cp.objtype AS [CacheType],
    OBJECT_NAME(st.objectid, st.dbid) AS [ObjectName],
    cp.usecounts AS [ExecutionCount],
    st.text AS [QueryText],
    cp.size_in_bytes / 1024 AS [SizeInKB]
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE cp.cacheobjtype = 'Compiled Plan'
ORDER BY cp.usecounts DESC;

截图如下：

二、清理缓存

1
2
3
4
5
6
7
8
9
-- 清除缓冲池
DBCC DROPCLEANBUFFERS;
 
-- 清除计划缓存
DBCC FREEPROCCACHE;
 
-- 清除数据缓存
CHECKPOINT;
DBCC DROPCLEANBUFFERS;

三、监控缓存命中率

1
2
3
4
5
6
7
-- 缓冲池命中率
SELECT
    object_name,
    counter_name,
    cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Buffer cache hit ratio', 'Buffer cache hit ratio base');

截图如下：

连接池

一、查看当前活动的连接数

1
2
3
4
5
6
SELECT
    DB_NAME(dbid) AS DatabaseName,
    COUNT(dbid) AS NumberOfConnections
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid;

截图如下：

二、查看当前连接的具体信息

1
2
3
4
5
6
7
8
9
10
SELECT
    spid,
    ecid,
    status,
    loginame,
    hostname,
    db_name(dbid) AS DatabaseName,
    cmd,
    request_id
FROM sys.sysprocesses;

截图如下：

三、查看连接池信息

1
2
3
4
5
6
7
8
SELECT
    pool_id,
    min_memory_percent,
    max_memory_percent,
    used_memory_kb,
    target_memory_kb,
    max_memory_kb
FROM sys.dm_resource_governor_resource_pools;

截图如下：

四、查看每个连接的详细信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
    session_id,
    login_time,
    host_name,
    program_name,
    client_interface_name,
    login_name,
    status,
    cpu_time,
    memory_usage,
    logical_reads,
    writes,
    reads
FROM sys.dm_exec_sessions;

截图如下：

五、查看用户连接数

1
2
3
4
5
6
7
8
9
10
SELECT login_name,
Count(0) user_count
FROM Sys.dm_exec_requests dr WITH(nolock)
RIGHT OUTER JOIN Sys.dm_exec_sessions ds WITH(nolock)
ON dr.session_id = ds.session_id
RIGHT OUTER JOIN Sys.dm_exec_connections dc WITH(nolock)
ON ds.session_id = dc.session_id
WHERE ds.session_id > 50
GROUP BY login_name
ORDER BY user_count DESC

截图如下：

彩蛋

重启Mysql不行，反而重启服务器才可以，考虑是否应用有死锁，导致应用在争夺资源

如果连接池信息满了，考虑如下方式重置资源池

1
2
3
4
5
ALTER RESOURCE POOL pool_name
WITH (
    MIN_MEMORY_PERCENT = 0,
    MAX_MEMORY_PERCENT = 100
);

重置资源配置调度：

1
ALTER RESOURCE GOVERNOR RECONFIGURE;

通过KILL的方式来清空连接：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DECLARE @session_id INT;
 
DECLARE session_cursor CURSOR FOR
SELECT session_id
FROM sys.dm_exec_sessions
WHERE session_id != @@SPID AND is_user_process = 1;
 
OPEN session_cursor;
 
FETCH NEXT FROM session_cursor INTO @session_id;
 
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC('KILL ' + @session_id);
    FETCH NEXT FROM session_cursor INTO @session_id;
END
 
CLOSE session_cursor;
DEALLOCATE session_cursor;

如果当前资源池的内存限制太低，可以增加这两个参数：

1
2
3
4
5
6
ALTER RESOURCE POOL pool_name
WITH (
    MIN_MEMORY_PERCENT = new_min_memory_percent,
    MAX_MEMORY_PERCENT = new_max_memory_percent
);
ALTER RESOURCE GOVERNOR RECONFIGURE;

还可分配更多的资源给高优先级的任务：（调整工作负载组的配置，以确保高优先级任务获得更多资源）

1
2
3
ALTER WORKLOAD GROUP group_name
USING pool_name;
ALTER RESOURCE GOVERNOR RECONFIGURE;

最终还需监控和优化

  • 监控资源使用情况：定期监控资源池的资源使用情况，确保配置合理
1
2
3
4
5
6
7
8
SELECT
    pool_id,
    min_memory_percent,
    max_memory_percent,
    used_memory_kb,
    target_memory_kb,
    max_memory_kb
FROM sys.dm_resource_governor_resource_pools;
  • 优化查询和索引：优化查询和索引，减少资源消耗
  • 定期维护和清理：定期维护数据库，清理不必要的数据和索引，释放资源

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持IT俱乐部。

本文收集自网络，不代表IT俱乐部立场，转载请注明出处。https://www.2it.club/database/mssql/14535.html
54赞
标签:
上一篇
下一篇

相关推荐

联系我们

联系我们

在线咨询: QQ交谈

邮箱: 1120393934@qq.com

工作时间：周一至周五，9:00-17:30，节假日休息

关注微信
微信扫一扫关注我们

微信扫一扫关注我们

返回顶部