我要评论缓存池
缓存机制是指将经常访问的数据或查询结果保存在内存中，以提高查询性能和整体系统响应速度
缓冲池 (buffer pool)：
- sql server 中最大的一块内存区域，用于存储从磁盘读取的页，以减少对磁盘的直接访问
- 缓冲池中的页包括数据页、索引页、系统表页等
计划缓存 (plan cache)：
- 将执行过的查询计划缓存在计划缓存中，以便重复使用，减少查询解析和优化的开销
- 查询计划是查询优化器生成的执行查询的步骤
数据缓存 (data cache)：
- 数据缓存是缓冲池的一部分，专门用于缓存数据页
- 当查询访问表中的数据时，sql server 会首先检查数据缓存，如果数据已经在缓存中，则直接返回，否则从磁盘读取并缓存
一、查看缓存使用情况
-- 查看缓冲池使用情况
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;截图如下：
二、清理缓存
-- 清除缓冲池 dbcc dropcleanbuffers; -- 清除计划缓存 dbcc freeproccache; -- 清除数据缓存 checkpoint; dbcc dropcleanbuffers;
三、监控缓存命中率
-- 缓冲池命中率
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');截图如下：
连接池
一、查看当前活动的连接数
select
db_name(dbid) as databasename,
count(dbid) as numberofconnections
from sys.sysprocesses
where dbid > 0
group by dbid;截图如下：
二、查看当前连接的具体信息
select
spid,
ecid,
status,
loginame,
hostname,
db_name(dbid) as databasename,
cmd,
request_id
from sys.sysprocesses;截图如下：
三、查看连接池信息
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;截图如下：
四、查看每个连接的详细信息
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;截图如下：
五、查看用户连接数
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不行，反而重启服务器才可以，考虑是否应用有死锁，导致应用在争夺资源
如果连接池信息满了，考虑如下方式重置资源池
alter resource pool pool_name
with (
min_memory_percent = 0,
max_memory_percent = 100
);重置资源配置调度：
alter resource governor reconfigure;
通过kill的方式来清空连接：
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;如果当前资源池的内存限制太低，可以增加这两个参数：
alter resource pool pool_name
with (
min_memory_percent = new_min_memory_percent,
max_memory_percent = new_max_memory_percent
);
alter resource governor reconfigure;还可分配更多的资源给高优先级的任务：（调整工作负载组的配置，以确保高优先级任务获得更多资源）
alter workload group group_name using pool_name; alter resource governor reconfigure;
最终还需监控和优化
- 监控资源使用情况：定期监控资源池的资源使用情况，确保配置合理
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;- 优化查询和索引：优化查询和索引，减少资源消耗
- 定期维护和清理：定期维护数据库，清理不必要的数据和索引，释放资源
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论