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

一、前言

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

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

 

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

A, Find missing index warnings for cached plans in the current database

-- Note: This query could take some time on a busy instance

-- Helps you connect missing indexes to specific stored procedures or queries

-- This can help you decide whether to add them or not

SELECT TOP(25) OBJECT_NAME(objectid) AS [ObjectName],query_plan, 

cp.objtype, cp.usecounts

FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp

WHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE N'%MissingIndex%'

AND dbid = DB_ID()

ORDER BY cp.usecounts DESC OPTION (RECOMPILE);

B,Buffer usage by table and index

-- Breaks down buffers used by current database

-- by object (table, index) in the buffer cache

-- Tells you what tables and indexes are

-- using the most memory in the buffer cache

SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName],

p.index_id, COUNT(*)/128 AS [Buffer size(MB)], COUNT(*) AS [BufferCount],

p.data_compression_desc AS [CompressionType]

FROM sys.allocation_units AS a WITH (NOLOCK)

INNER JOIN sys.dm_os_buffer_descriptors AS b WITH (NOLOCK)

ON a.allocation_unit_id = b.allocation_unit_id

INNER JOIN sys.partitions AS p WITH (NOLOCK)

ON a.container_id = p.hobt_id

WHERE b.database_id = CONVERT(int,DB_ID())

AND p.[object_id] > 100

GROUP BY p.[object_id], p.index_id, p.data_compression_desc

ORDER BY [BufferCount] DESC OPTION (RECOMPILE);

C，获取表名字，记录行数，压缩状态

-- Get Table names, row counts, and compression status

-- for the clustered index or heap

-- Gives you an idea of table sizes, and possible data compression opportunities

SELECT OBJECT_NAME(object_id) AS [ObjectName],

SUM(Rows) AS [RowCount], data_compression_desc AS [CompressionType]

FROM sys.partitions WITH (NOLOCK)

WHERE index_id < 2 --ignore the partitions from the non-clustered index if any

AND OBJECT_NAME(object_id) NOT LIKE N'sys%'

AND OBJECT_NAME(object_id) NOT LIKE N'queue_%'

AND OBJECT_NAME(object_id) NOT LIKE N'filestream_tombstone%'

AND OBJECT_NAME(object_id) NOT LIKE N'fulltext%'

AND OBJECT_NAME(object_id) NOT LIKE N'ifts_comp_fragment%'

AND OBJECT_NAME(object_id) NOT LIKE N'filetable_updates%'

GROUP BY object_id, data_compression_desc

ORDER BY SUM(Rows) DESC OPTION (RECOMPILE);

D,获取最近一次更新统计信息的时间

-- When were Statistics last updated on all indexes?

SELECT o.name, i.name AS [Index Name],STATS_DATE(i.[object_id],

i.index_id) AS [Statistics Date], s.auto_created,

s.no_recompute, s.user_created, st.row_count

FROM sys.objects AS o WITH (NOLOCK)

INNER JOIN sys.indexes AS i WITH (NOLOCK)

ON o.[object_id] = i.[object_id]

INNER JOIN sys.stats AS s WITH (NOLOCK)

ON i.[object_id] = s.[object_id]

AND i.index_id = s.stats_id

INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)

ON o.[object_id] = st.[object_id]

AND i.[index_id] = st.[index_id]

WHERE o.[type] = 'U'

ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC OPTION (RECOMPILE);

-- Helps discover possible problems with out-of-date statistics

-- Also gives you an idea which indexes are most active

E，Fragmentation information for all indexes

-- Get fragmentation info for all indexes

-- above a certain size in the current database

-- Note: This could take some time on a very large database

-- Helps determine whether you have fragmentation in your relational indexes

-- and how effective your index maintenance strategy is

SELECT DB_NAME(database_id) AS [Database Name],

OBJECT_NAME(ps.OBJECT_ID) AS [Object Name],

i.name AS [Index Name], ps.index_id, index_type_desc,

avg_fragmentation_in_percent, fragment_count, page_count

FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,'LIMITED') AS ps

INNER JOIN sys.indexes AS i WITH (NOLOCK)

ON ps.[object_id] = i.[object_id]

AND ps.index_id = i.index_id

WHERE database_id = DB_ID()

AND page_count > 500

ORDER BY avg_fragmentation_in_percent DESC OPTION (RECOMPILE);

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

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