ch15,Delivering a SQL Server Health Check--PART 2

一、前言

      做为DBA，在接手一台全新的或是不熟悉的SQL Server DB服务器时，首先需要做的，就是尽可能详尽的收集这台服务器的相关信息，这包括硬件信息和存储子系统以及OS相关信息，SQL server instance本身的信息等。做为数据库专业人士，尤其是对于一个专业的DBA，这是必须知道的基本信息。

     对于可以接直接登录到DB server OS的DBA，则可以通过登录到OS直接收集相关信或使用DMV来收集，而对于不能或是不被允许直接登录到OS的DBA，则只有通过使用DMV/DMF了。

 

 

二，使用DMV/DMF收集DB SERVER相关信息

A,获得SQL Server memory dump信息，返回非空结果集，

表示需要进一步调查SQL server Errorlog

This query, which was also added in SQL Server 2008 R2 SP1, tells you if and when your SQL

Server instance has generated any memory dumps. Hopefully, you will not see any results for this

query. If you do, start looking in the SQL Server Error Log(s) that correspond to the times for the

SQL Server memory dumps to see if you can find any relevant information about what happened to

generate the memory dump. You should also look at the Windows Event logs, and maybe even get

ready to open a support case with Microsoft.

-- Get information on location, time and size of any memory dumps from SQL Server

SELECT [filename], creation_time, size_in_bytes

FROM sys.dm_server_memory_dumps WITH (NOLOCK) OPTION (RECOMPILE);

-- This will not return any rows if you have

-- not had any memory dumps (which is a good thing)

B，获得当前instance上数据库的文件以及路径

--Database filenames and paths

-- File Names and Paths for Tempdb and all user databases in instance

--type_desc= ROWS,数据文件；type_desc =LOG，日志文件

-- Things to look at:

-- Are data files and log files on different drives?

-- Is everything on the C: drive?

-- Is TempDB on dedicated drives?

-- Are there multiple tempdb data files?

-- Are there multiple data files?

SELECT DB_NAME([database_id])AS [Database Name],

[file_id], name, physical_name, type_desc, state_desc,

CONVERT( bigint, size/128.0) AS [Total Size in MB]

FROM sys.master_files WITH (NOLOCK)

WHERE [database_id] > 4

AND [database_id] <> 32767

OR [database_id] = 2

ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);

C，获得当前instance上数据库的属性

--get database property information on the current sql server instance

-- Things to look at:

-- How many databases are on the instance?

-- What recovery models are they using?

-- What is the log reuse wait description?

-- How full are the transaction logs ?

-- What compatibility level are they on?

-- Recovery model, log reuse wait description, log file size, log usage size

-- and compatibility level for all databases on instance

SELECT db.[name] AS [Database Name], db.recovery_model_desc AS [Recovery Model],

db.log_reuse_wait_desc AS [Log Reuse Wait Description],

ls.cntr_value AS [Log Size (KB)], lu.cntr_value AS [Log Used (KB)],

CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) *

100 AS

[Log Used %], db.[compatibility_level] AS [DB Compatibility Level],

db.page_verify_option_desc AS [Page Verify Option], db.is_auto_create_stats_on,

db.is_auto_update_stats_on, db.is_auto_update_stats_async_on,

db.is_parameterization_forced,

db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,

is_auto_shrink_on, is_auto_close_on

FROM sys.databases AS db WITH (NOLOCK)

INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK)

ON db.name = lu.instance_name

INNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK)

ON db.name = ls.instance_name

WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%'

AND ls.counter_name LIKE N'Log File(s) Size (KB)%'

AND ls.cntr_value > 0 OPTION (RECOMPILE);

D,get I/O stall information by database file

可以用来识别在当前实例,哪些数据库文件占有最多IO资源,

最忙

-- Helps determine which database files on

-- the entire instance have the most I/O bottlenecks

-- Calculates average stalls per read, per write,

-- and per total input/output for each database file.

SELECT DB_NAME(fs.database_id) AS [Database Name], mf.physical_name,

io_stall_read_ms, num_of_reads,

CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS

[avg_read_stall_ms],io_stall_write_ms,

num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS

[avg_write_stall_ms],

io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes

AS [total_io],

CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS

NUMERIC(10,1))

AS [avg_io_stall_ms]

FROM sys.dm_io_virtual_file_stats(null,null) AS fs

INNER JOIN sys.master_files AS mf WITH (NOLOCK)

ON fs.database_id = mf.database_id

AND fs.[file_id] = mf.[file_id]

ORDER BY avg_io_stall_ms DESC OPTION (RECOMPILE);

E,获得当前实例各数据库占用的buffer pool size大小

此查询会返回在当前实例上的各个DB所占用的总的buffer pool大小，

在调查内部内存压力时，可以知道哪些数据库占用了最多的buffer pool，

一种思路是，确定是否有某些数据库的某些大表缺失了索引，导致表扫描，

另外就是看看是不是可以通过启用数据压缩，以减少对buffer pool的占用，

当然，这需要权衡，因为启用数据压缩会加大CPU的负载：

SELECT DB_NAME(database_id) AS [Database Name],

COUNT(*) * 8/1024.0 AS [Cached Size (MB)]

FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)

WHERE database_id > 4 -- system databases

AND database_id <> 32767 -- ResourceDB

GROUP BY DB_NAME(database_id)

ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

F，各数据库使用的CPU百分比，可以帮助识别哪些数据库占用了最多的

CPU资源

-- Get CPU utilization by database

WITH DB_CPU_Stats

AS

(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], 

 SUM(total_worker_time) AS [CPU_Time_Ms]

 FROM sys.dm_exec_query_stats AS qs

 CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] 

              FROM sys.dm_exec_plan_attributes(qs.plan_handle)

              WHERE attribute = N'dbid') AS F_DB

 GROUP BY DatabaseID)

SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],

       DatabaseName, [CPU_Time_Ms], 

       CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) 

  OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]

FROM DB_CPU_Stats

WHERE DatabaseID > 4 -- system databases

AND DatabaseID <> 32767 -- ResourceDB

ORDER BY row_num OPTION (RECOMPILE);

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/101629/viewspace-761532/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/101629/viewspace-761532/