IT博客汇 | [原]SQLServer日常维护事项

dbcc memorystatus
/*
重点查看buffer counts部分
其中committed memory和target memory最重要。
Committed memory表示sql server已经得到的内存数量
target memory表示有效运行所需的内存数量。
当两个存在差别过大，说明可能存在内存压力
*/

3.1.2. SQLServer:Buffer Managerà Page Life Expectancy显示数据页在缓冲池中驻留的时间长度（单位是秒）。值越大，系统越健康。如果存在内存瓶颈，这个值会低于300s或者更少。

3.1.3. SQLServer:Buffer Managerà Buffer cache hit ratio显示数据库内存命中率，所请求的数据或者说页面在缓冲池被找到的次数，如果很低说明内存不足，此值一般大于98%

3.1.4. SQLServer:Buffer Managerà Stolen pages当这个指标与目标内存页面数比例较大时可能存在问题。

3.1.5. SQLServer:Buffer Managerà Memory Grants Pending显示等待内存授权的进程队列。这个指标值为0时理想状态。

3.1.6. SQLServer:Buffer Managerà Checkpoint pages/sec显示检查点操作每秒写入磁盘的脏页数目。如果这个值很高，说明缺少内存。

3.1.7. SQLServer:Buffer Managerà Lazy writes/sec显示每秒将脏页从内存写到磁盘的次数。这个值应该尽可能接近0，当大于20或者更多，确信缓冲池不够。

3.2. 磁盘

3.2.1. PhysicalDiskàAVG.Disk Aueue Length显示每一个磁盘的队列长度。不要选择_TOTAL对象，而是查看某驱动盘符的队列长度，如果参数值持续大于2，则可能影响性能。

3.2.2. PhysicalDiskAVG.Disk sec/Read显示每次读取的平均磁盘时间，理想情况下值小于10毫秒。

3.2.3. PhysicalDiskAVG.Disk sec/Write显示每次写入的平均磁盘时间，理想情况下值小于10毫秒。

3.2.4. SQL ServerAccess MethodsFullscans/sec显示每秒请求完全索引扫描或者全表扫描的数目，如果扫描频率每秒大于1，那么说明索引缺少或者索引比较差。

3.2.5. SQL ServerAccess MethodsPageSplits/sec显示每秒页面拆分的次数。可以通过适当的索引维护或者好的填充因子来避免发生。

3.3. CPU

3.3.1. SystemProcessor Queue Length显示系统队列长度，如果平均值大于3，那么说明cpu存在瓶颈

3.3.2. Processorà%Privilege Time显示操作系统内部操作所花费的时间。

3.3.3. SQL ServeràSQL StatisticsàSQL Compilations/sec显示查询计划的编译次数。

3.3.4. SQL ServeràSQL StatisticsàSQL Re-Compilations/sec显示查询计划的重编译次数。

3.3.5. 当SQL Compilations/sec和SQL Re-Compilations/sec次数过高的话，说明可能未使用绑定变量导致计划重新编译，或者说重新编译次数比编译次数过高的话，那么说明存在应用上的瓶颈。

4. 数据库维护

4.1. 主数据库（业务数据库）

4.1.1. 查看数据库的增长情况，每周增长量应该处平稳状态，如果出现异常增长，则需要注意查看原因。

4.1.2. 注意数据文件所在磁盘的空间，保证至少有一周的增长量空间。

4.1.3. 查看每个日志文件的增长情况，每周增长量应该处平稳状态，如果出现异常增长，则需要注意查看原因

4.1.4. 注意日志文件所在磁盘的空间，保证至少有一周的增长量空间。

4.1.5. 日志文件超大后，可考虑是否进行清理,如果清理使用备份日志然后收缩日志文件。

4.2. 镜像数据库

4.2.1. 除日志清除外步骤同主数据库

4.2.2. 镜像数据库日志文件超大后，可以在主数据库上进行日志清理操作。

4.3. 辅助数据库(Log Shipping)

4.3.1. 除日志清除外步骤同主数据库

4.3.2. 备用数据库日志文件超大后，可以在主数据库上进行日志清理操作。

4.4. 查看作业运行状况

可以通过以下脚本查看作业状况或者通过图形界面“作业活动监视器”查看

--0:Fail 1:Succ 3:Cancel 5:First run and msdb.dbo.syscategories.category_id not between 10 and 20
select
category = jc.name, category_id = jc.category_id, job_name = j.name,
job_enabled = j.enabled,
last_run_time = cast(js.last_run_date as varchar(10)) + '-' + cast(js.last_run_time as varchar(10)),
last_run_duration = js.last_run_duration,
last_run_status = js.last_run_outcome,
last_run_msg = js.last_outcome_message + cast(nullif(js.last_run_outcome,1) as varchar(2)),
job_created = j.date_created,
job_modified = j.date_modified
from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobservers js on j.job_id = js.job_id
inner join msdb.dbo.syscategories jc on j.category_id = jc.category_id
where j.enabled = 1 and js.last_run_outcome in (0,1,3,5)

4.5. 查看镜像状态

可以通过以下脚本查看镜像状态或者通过“镜像监视器”查看

select
dm.mirroring_partner_instance,
dm.mirroring_role,
dm.mirroring_state,
dm.mirroring_witness_state,
dm.mirroring_safety_level,
dm.mirroring_witness_name
from
sys.database_mirroring dm
join sys.databases d on (dm.database_id=d.database_id)
where (d.name=N'dbname') and mirroring_guid is not null

4.6. 查看Log Shipping状态

可以通过以下脚本在监视服务器上执行查看日志传送状态

use master
go
exec sp_help_log_shipping_monitor
go
exec sp_help_log_shipping_monitor_primary 'TestServer1','Northwind'
go
exec sp_help_log_shipping_monitor_secondary 'TestServer2','Northwind'
go