Sql Server 性能问题解决2则
一。负载均衡Web服务器所用Session数据库CPU占用高
-
应用背景。
Web系统在部署在云上，支持多租户，使用了多个Web服务器，支持负载均衡。
网站使用windows服务器，使用.net技术栈， ASP.net, 数据库使用SQLServer， 每个租户一个DB，为支持负载均衡，使用数据库存储Session。
所有web服务器共享一个Session数据库。 -
问题
用户报告系统出现问题，访问网页明显比以前慢，而且经常出现502错误【Web服务器没有及时响应】
这个问题已经不是第一次出现，以前也出现，一直没有找到原因，最后重启所有Web服务器的WWWW服务后问题消失。 -
诊断
根据用户反映症状，我们登录到云服务器，逐个排查
Web服务出现CPU， 内存， IO, 网络，都正常
用户数据库服务器， CPU, 内存， IO，正常
Session数据库CPU占用高。
--查询占用cpu高的前 50 个 SQL 语句 SELECT total_cpu_time,[total_physical_Reads], total_execution_count, number_of_statements, s2.text FROM (SELECT TOP 50 SUM(qs.total_worker_time) AS total_cpu_time, SUM(total_physical_reads) AS [total_physical_Reads], SUM(qs.execution_count) AS total_execution_count, COUNT(*) AS number_of_statements, qs.sql_handle FROM sys.dm_exec_query_stats AS qs GROUP BY qs.sql_handle ORDER BY SUM(qs.total_worker_time) DESC) AS stats CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2 --------------------------------------------------------------------
发现Sesssion数据库中，存储过程 TempGetStateItemExclusive3 出现每秒上千次的执行，导致SQL Serer服务器CPU资源被耗尽。
- 问题追溯
当Sql Server存储Session时，LockDate， Locked两个字段表示锁定时间及状态。在负载均衡模式下，有多个Web服务器使用同一个Session， 必须按顺序使用。
在一次请求中，首先锁定该SessionId， 等请求完成，再释放。
如果某个请求在锁定后，由于特殊原因，如服务器重启，或其他原因导致前一次请求的Session没有释放，则后续Session会一直轮询Session数据库，等待Session锁定状态为0.
且没有超时机制。
- 解决方案
针对这种情况， 跟清除过期Session存储过程【DeleteExpiresSessions】一样，新增一个存储过程【DeleteExpiresLockSessions】, 也是每分钟执行一次。
自动清理锁定时间在20分钟前，且Locked仍然为1的记录，将该记录删除。
二。SQL Server 无法抢到内存问题
-
问题
访问网站时，以前正常的页面，也报错sql执行超时。 -
问题排查
服务器环境：2核心8G内存
机器的CPU占用一直不高，最多也就在40-50%，
SQL Server进程的CPU 占用一般也就20%，IO 一直很高， 一般都在10MB /S 以上，但使用的内存一直上不去只有几十M。
使用资源监控器，检查SQL使用的CPU, 内存及 IO。
打开MSSM, 设置启用cpu时间和io统计
发现多次查询记录数仅仅为几千的小表，每次查询都需要进行物理读， 可见每次读取数据，都无法从内存中读取，需要从磁盘进行物理读。
-
原因
服务器安装程序较多，内存被其他进程占用，SQL Server进程并不能比其他进程优先，可使用更多内存， 甚至已经申请了内存， 过一段时间又被其他进程抢去。 -
问题解决
修正最小内存，从0改为 1024M。
观察任务管理器， SQL 占用内存慢慢上升， 系统也恢复正常。
