开发者

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

目录
  • 缓存池
    • 一、查看缓存使用情况
    • 二、清理缓存
    • 三、监控缓存命中率
  • 连接池
    • 一、查看当前活动的连接数
    • 二、查看当前连接的具体信息
    • 三、查看连接池信息
    • 四、查看每个连接的详细信息
    • 五、查看用户连接数
  • 彩蛋
    • 总结

      缓存池

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

      缓冲池 (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;

      截图如js下:

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

      二、清理缓存

      -- 清除缓冲池
      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');

      截图如下:

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

      连接池

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

      SELECT 
          DB_NAME(dbid) AS DatabaseName,
          COUNT(dbid) AS NumberOfConnections
      FROM sys.sysprocesses
      WHERE dbid > 0
      GROUP BY dbid;

      截图如下:

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

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

      SELECT 
          spid,
          ecid,
          status,
          loginame,
          hostname,
          db_name(dbid) AS DatabaseName,
          cmd,
          request_id
      FROM sys.sysprocesses;

      截图如下:

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

      三、查看连android接池信息

      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;

      截图如下:

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

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

      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;

      截图如下:

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

      五、查看用户连接数

      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.sessiphpon_id > 50
      GRandroidOUP BY login_name
      ORDER BY user_count DESC

      截图如下:

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

      彩蛋

      重启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;
      • 优化查询和索引:优化查询和索引,减少资源消耗
      • 定期维护和清理:定期维护数据库,清理不必要的数据和索引,释放资源

      总结

      以上为个人经验,希望能给大家一个参考,也希望大家多多支持编程客栈(www.devze.com)。

      0

      上一篇:

      下一篇:

      精彩评论

      暂无评论...
      验证码 换一张
      取 消

      最新数据库

      数据库排行榜