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

一、前言

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

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

 

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

   use [your databasename]

   go

 

 A,获取特定数据库的Database file sizes and space available

-- Individual File Sizes and space available for current database

-- Look at how large and how full the files are and where they are located

-- Make sure the transaction log is not full!!

SELECT name AS [File Name], physical_name AS [Physical Name], size/128.0 AS [Total

Size in MB],

size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space

In MB], [file_id]

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

B,事物日志的大小以及可用空间(sql server 2012 and above可用)

-- Get transaction log size and space information for the current database

-- Another way to look at transaction log file size and space

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

CAST((total_log_size_in_bytes/1048576.0) AS DECIMAL(10,1))

AS [Total_log_size(MB)],

CAST((used_log_space_in_bytes/1048576.0) AS DECIMAL(10,1))

AS [Used_log_space(MB)],

CAST(used_log_space_in_percent AS DECIMAL(10,1)) AS [Used_log_space(%)]

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

C，获取当前DB各文件的I/O statistics

 -- I/O Statistics by file for the current database

-- This helps you characterize your workload better from an I/O perspective

SELECT 

         DB_NAME(DB_ID()) AS [Database Name],

         [file_id], num_of_reads, 

         num_of_writes,

         io_stall_read_ms, 

         io_stall_write_ms,

         CAST(100. * io_stall_read_ms/(io_stall_read_ms + io_stall_write_ms) AS DECIMAL(10,1)) AS [IO Stall Reads Pct],

         CAST(100. * io_stall_write_ms/(io_stall_write_ms + io_stall_read_ms) AS DECIMAL(10,1)) AS [IO Stall Writes Pct],

        (num_of_reads + num_of_writes) AS [Writes + Reads], 

        num_of_bytes_read,

        num_of_bytes_written,

        CAST(100. * num_of_reads/(num_of_reads + num_of_writes) AS DECIMAL(10,1)) AS [# Reads Pct],

        CAST(100. * num_of_writes/(num_of_reads + num_of_writes) AS DECIMAL(10,1)) AS [# Write Pct],

        CAST(100. * num_of_bytes_read/(num_of_bytes_read + num_of_bytes_written) AS DECIMAL(10,1)) AS [Read Bytes Pct],

        CAST(100. * num_of_bytes_written/(num_of_bytes_read + num_of_bytes_written) AS DECIMAL(10,1)) AS [Written Bytes Pct]

FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) OPTION (RECOMPILE);

D，获取当前数据库的Virtual Log File count

-- Get VLF count for transaction log for the current database,

-- number of rows equals the VLF count. Lower is better!

This query simply tells you how many VLFs you have in your transaction log fi le. Having a large

number of VLFs in your transaction log can affect write performance to your transaction log.

More important, it can have a huge effect on how long it takes to restore a database, and how long

it takes a database to become available in a clustering failover. It can also affect how long it takes to

recover a database when your instance of SQL Server is started or restarted.

-- High VLF counts can affect write performance

-- and they can make database restore and recovery take much longer

DBCC LOGINFO;

 

E,查看特定数据库上的查询活动

--looking at the query activity on this particular database

-- Top cached queries by Execution Count (SQL Server 2012)
SELECT qs.execution_count, qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows,
qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time,
SUBSTRING(qt.TEXT,qs.statement_start_offset/2 +1,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
AS query_text
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.execution_count DESC OPTION (RECOMPILE);
-- Uses several new rows returned columns
-- to help troubleshoot performance problems

  

F，Top cached stored procedures by execution count

-- Top Cached SPs By Execution Count (SQL Server 2012)
-- Tells you which cached stored procedures are called the most often
-- This helps you characterize and baseline your workload

SELECT TOP(250) p.name AS [SP Name], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0)
AS [Calls/Second],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],

qs.total_worker_time AS [TotalWorkerTime],qs.total_elapsed_time,
qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.execution_count DESC OPTION (RECOMPILE);

 

 

G，Top cached stored procedures by average elapsed time

-- Top Cached SPs By Avg Elapsed Time (SQL Server 2012)

-- This helps you find long-running cached stored procedures that
-- may be easy to optimize with standard query tuning techniques

SELECT TOP(25) p.name AS [SP Name], qs.total_elapsed_time/qs.execution_count
AS [avg_elapsed_time], qs.total_elapsed_time, qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time,
GETDATE()), 0) AS [Calls/Second],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
qs.total_worker_time AS [TotalWorkerTime], qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY avg_elapsed_time DESC OPTION (RECOMPILE);

H，Top cached stored procedures by total worker time

Worker time relates to the CPU cost of a query or stored procedure

-- Top Cached SPs By Total Worker time (SQL Server 2012).
-- Worker time relates to CPU cost

-- This helps you find the most expensive cached
-- stored procedures from a CPU perspective
-- You should look at this if you see signs of CPU pressure

SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0)
AS [Calls/Second],qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

 

 

I，Top cached stored procedures by total logical reads

Logical reads equate to memory pressure, and indirectly to I/O pressure

 

-- Top Cached SPs By Total Logical Reads (SQL Server 2012).
-- Logical reads relate to memory pressure

-- This helps you find the most expensive cached
-- stored procedures from a memory perspective
-- You should look at this if you see signs of memory pressure

SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads
AS [TotalLogicalReads], qs.total_logical_reads/qs.execution_count
AS [AvgLogicalReads],qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0)
AS [Calls/Second], qs.total_elapsed_time,qs.total_elapsed_time/qs.execution_count
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);

 

J，Top cached stored procedures by total physical reads

 

Physical reads equate to disk I/O cost

-- Top Cached SPs By Total Physical Reads (SQL Server 2012).
-- Physical reads relate to disk I/O pressure

-- This helps you find the most expensive cached
-- stored procedures from a read I/O perspective
-- You should look at this if you see signs of I/O pressure or of memory pressure

SELECT TOP(25) p.name AS [SP Name],qs.total_physical_reads
AS [TotalPhysicalReads],qs.total_physical_reads/qs.execution_count
AS [AvgPhysicalReads], qs.execution_count, qs.total_logical_reads,
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
AND qs.total_physical_reads > 0
ORDER BY qs.total_physical_reads DESC,
qs.total_logical_reads DESC OPTION (RECOMPILE);

 

K,Top cached stored procedures by total logical writes

-- Top Cached SPs By Total Logical Writes (SQL Server 2012).
-- Logical writes relate to both memory and disk I/O pressure

-- This helps you find the most expensive cached
-- stored procedures from a write I/O perspective
-- You should look at this if you see signs of I/O pressure or of memory pressure

SELECT TOP(25) p.name AS [SP Name], qs.total_logical_writes
AS [TotalLogicalWrites], qs.total_logical_writes/qs.execution_count
AS [AvgLogicalWrites], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0)
AS [Calls/Second],qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_logical_writes DESC OPTION (RECOMPILE);

L,Top statements by average I/O

-- Lists the top statements by average input/output
-- usage for the current database

-- Helps you find the most expensive statements for I/O by SP

SELECT TOP(50) OBJECT_NAME(qt.objectid) AS [SP Name],
(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count
AS [Avg IO],SUBSTRING(qt.[text],qs.statement_start_offset/2,
(CASE
WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) AS [Query Text]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.[dbid] = DB_ID()
ORDER BY [Avg IO] DESC OPTION (RECOMPILE);

M，Possible bad nonclustered indexes

-- Possible Bad NC Indexes (writes > reads)

-- Look for indexes with high numbers of writes

-- and zero or very low numbers of reads

-- Consider your complete workload

-- Investigate further before dropping an index!

SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name],

i.index_id,user_updates AS [Total Writes],

user_seeks + user_scans + user_lookups AS [Total Reads],

user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]

FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)

INNER JOIN sys.indexes AS i WITH (NOLOCK)

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

AND i.index_id = s.index_id

WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1

AND s.database_id = DB_ID()

AND user_updates > (user_seeks + user_scans + user_lookups)

AND i.index_id > 1

ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION

(RECOMPILE);

N，Missing indexes by index advantage

-- Missing Indexes current database by Index Advantage

-- Look at last user seek time, number of user seeks

-- to help determine source and importance

-- SQL Server is overly eager to add included columns, so beware

-- Do not just blindly add indexes that show up from this query!!!

SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)

AS [index_advantage],

migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],

mid.equality_columns, mid.inequality_columns, mid.included_columns,

migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost,

migs.avg_user_impact

FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)

INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)

ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)

ON mig.index_handle = mid.index_handle

WHERE mid.database_id = DB_ID() -- Remove this to see for entire instance

ORDER BY index_advantage DESC OPTION (RECOMPILE);

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

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