How to monitor the running status of mmsql

Microsoft SQL Server 2005 provides tools to monitor databases. One way is to manage the View dynamically. The server status information returned by the dynamic management View (DMV) and dynamic management function (DMF) can be used to monitor the running status of server instances, diagnose problems, and optimize performance.

Common server dynamic management objects include:

Dm_db _ *: database and database object

Dm_exec _ *: execute user code and the associated connection

Dm_ OS _ *: Memory, lock, and schedule

Dm_tran _ *: Transaction and isolation

Dm_io _ *: Network and disk input/output

This section describes common queries for these dynamic management views and function operations to monitor the running status of SQL Server.

Sample query
You can run the following query to obtain all DMV and DMF names:

SELECT * FROM sys. system_objects
WHERE name LIKE 'DM _ %'
Order by name

Monitor CPU bottlenecks
CPU bottlenecks are usually caused by the following reasons: the query plan is not optimal, improperly configured, poor design factors, or insufficient hardware resources. The following common query can help you determine the cause of the CPU bottleneck.

The following query gives you a better understanding of the batch processing or processes in the cache that occupy most of the CPU resources.

Select top 50
SUM (qs. total_worker_time) AS total_cpu_time,
SUM(qs.exe cution_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

 

The following query shows the total CPU usage (with SQL text) occupied by the cache plan ).

SELECT
Total_cpu_time,
Total_execution_count,
Number_of_statements,
S2.text
-- (Select substring (s2.text, statement_start_offset/2, (case when statement_end_offset =-1 THEN (LEN (CONVERT (NVARCHAR (MAX), s2.text) * 2) ELSE statement_end_offset END) -statement_start_offset)/2) AS query_text
FROM
(Select top 50
SUM (qs. total_worker_time) AS total_cpu_time,
SUM(qs.exe cution_count) AS total_execution_count,
COUNT (*) AS number_of_statements,
Qs. SQL _handle --,
-- MIN (statement_start_offset) AS statement_start_offset,
-- MAX (statement_end_offset) AS statement_end_offset
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

The following query shows the top 50 SQL statements with the highest average CPU usage.

Select top 50
Total_worker_time/execution_count AS [Avg CPU Time],
(Select substring (text, statement_start_offset/2, (case when statement_end_offset =-1 then LEN (CONVERT (nvarchar (max), text) * 2 ELSE statement_end_offset end-statement_start_offset) /2) FROM sys. dm_exec_ SQL _text (SQL _handle) AS query_text ,*
FROM sys. dm_exec_query_stats
Order by [Avg CPU Time] DESC

The following figure shows the DMV query used to find out excessive compilation/re-compilation.

Select * from sys. dm_exec_query_optimizer_info
Where
Counter = 'optimizations'
Or counter = 'elapsed time'

The following example shows the first 25 stored procedures that have been re-compiled. Plan_generation_num indicates the number of times the query has been re-compiled.

Select top 25
SQL _text.text,
SQL _handle,
Plan_generation_num,
Execution_count,
Dbid,
Objectid
From sys. dm_exec_query_stats
Cross apply sys. dm_exec_ SQL _text (SQL _handle) as SQL _text
Where plan_generation_num> 1
Order by plan_generation_num desc

A less efficient query plan may increase the CPU usage.

The following query shows which query consumes the most CPU usage.

The code is as follows:	Copy code
SELECT Highest_cpu_queries.plan_handle, Highest_cpu_queries.total_worker_time, Q. dbid, Q. objectid, Q. number, Q. encrypted, Q. [text] From (Select top 50 Qs. plan_handle, Qs. total_worker_time From Sys. dm_exec_query_stats qs Order by qs. total_worker_time desc) as highest_cpu_queries Cross apply sys. dm_exec_ SQL _text (plan_handle) as q Order by highest_cpu_queries.total_worker_time desc

The following query shows some operators that may occupy a large amount of CPU usage (such as '% Hash Match %' and '% Sort %') to identify suspicious objects.

The code is as follows:	Copy code
Select * From Sys. dm_exec_cached_plans Cross apply sys. dm_exec_query_plan (plan_handle) Where Cast (query_plan as nvarchar (max) like '% Sort %' Or cast (query_plan as nvarchar (max) like '% Hash Match %'

If you have detected a query plan with low efficiency and high CPU usage, run update statistics on the tables involved in the query to check whether the problem still exists. Then, collect relevant data and report the problem to the PerformancePoint planning support staff.

If your system has excessive compilation and re-compilation, it may cause CPU-related performance problems.

You can run the following DMV query to find out excessive compilation/recompilation.

The code is as follows:	Copy code
Select * from sys. dm_exec_query_optimizer_info Where Counter = 'optimizations' Or counter = 'elapsed time'

The following example shows the first 25 stored procedures that have been re-compiled. Plan_generation_num indicates the number of times the query has been re-compiled.

The code is as follows:	Copy code
Select top 25 SQL _text.text, SQL _handle, Plan_generation_num, Execution_count, Dbid, Objectid From sys. dm_exec_query_stats Cross apply sys. dm_exec_ SQL _text (SQL _handle) as SQL _text Where plan_generation_num> 1 Order by plan_generation_num desc

If you have detected excessive compilation or recompilation, collect as much data as possible and report it to the planning support staff.

Memory bottleneck
Before you start memory pressure detection and investigation, make sure that advanced options in SQL Server are enabled. Run the following query on the master database to enable this option.

The code is as follows:	Copy code
Sp_configure 'show advanced options' Go Sp_configure 'show advanced options', 1 Go Reconfigure Go

First, run the following query to check the memory-related configuration options.

The code is as follows:	Copy code
Sp_configure 'awe _ enabled' Go Sp_configure 'min server memory' Go Sp_configure 'Max server memory' Go Sp_configure 'min memory per query' Go Sp_configure 'query wait' Go

Run the following DMV query to view the CPU, scheduler memory, and buffer pool information.

The code is as follows:	Copy code
Select Cpu_count, Hyperthread_ratio, Scheduler_count, Physical_memory_in_bytes/1024/1024 as physical_memory_mb, Virtual_memory_in_bytes/1024/1024 as virtual_memory_mb, Bpool_committed * 8/1024 as bpool_committed_mb, Bpool_commit_target * 8/1024 as bpool_target_mb, Bpool_visible * 8/1024 as bpool_visible_mb From sys. dm_ OS _sys_info

I/O bottleneck
Check the latch wait statistics to determine the I/O bottleneck. Run the following DMV query to find the I/O Lock wait statistics.

The code is as follows:	Copy code
Select wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms, wait_time_ms/waiting_tasks_count From sys. dm_ OS _wait_stats Where wait_type like 'pageiolatch % 'and waiting_tasks_count> 0 Order by wait_type

If waiting_task_counts and wait_time_ms are significantly different from normal conditions, the I/O problem may exist. It is important to obtain the performance counters and the baseline of the primary DMV query output when SQL Server runs smoothly.

These wait_types can indicate whether your I/O subsystem encounters a bottleneck.

Use the following DMV query to find the currently suspended I/O requests. Perform this query regularly to check the running status of the I/O subsystem and isolate the physical disks involved in the I/O bottleneck.

The code is as follows:	Copy code
Select Database_id, File_id, Io_stall, Io_pending_ms_ticks, Scheduler_address From sys. dm_io_virtual_file_stats (NULL, NULL) t1, Sys. dm_io_pending_io_requests as t2 Where t1.file _ handle = t2.io _ handle

Normally, this query does not return any content. If this query returns some rows, further investigation is required.

You can also perform the following DMV query to find the I/O-related query.

The code is as follows:

Copy code

Select top 5 (total_logical_reads/execution_count) as avg_logical_reads, (Total_logical_writes/execution_count) as avg_logical_writes, (Total_physical_reads/execution_count) as avg_physical_reads, Execution_count, statement_start_offset, p. query_plan, q. text From sys. dm_exec_query_stats Cross apply sys. dm_exec_query_plan (plan_handle) p Cross apply sys. dm_exec_ SQL _text (plan_handle) as q Order by (total_logical_reads + total_logical_writes)/execution_count Desc

The following DMV query can be used to find which batches/requests generate the most I/O. The following DMV query can be used to find the first five requests that can generate up to I/O. Adjusting these queries improves system performance.

The code is as follows:	Copy code
Select top 5 (Total_logical_reads/execution_count) as avg_logical_reads, (Total_logical_writes/execution_count) as avg_logical_writes, (Total_physical_reads/execution_count) as avg_phys_reads, Execution_count, Statement_start_offset as stmt_start_offset, SQL _handle, Plan_handle From sys. dm_exec_query_stats Order by (total_logical_reads + total_logical_writes) Desc

Blocking
Run the following query to determine the blocked session.

The code is as follows:	Copy code
Select blocking_session_id, wait_duration_ms, session_id from Sys. dm_ OS _waiting_tasks Where blocking_session_id is not null

Use this call to find the SQL statement returned by blocking_session_id. For example, if blocking_session_id is 87, run this query to obtain the corresponding SQL statement.

The code is as follows:	Copy code
Dbcc INPUTBUFFER (87)

The following query shows the SQL wait analysis and the first 10 waiting resources.

The code is as follows:	Copy code
Select top 10 * From sys. dm_ OS _wait_stats -- Where wait_type not in ('clr _ SEMAPHORE ', 'lazywriter _ SLEEP', 'resource _ QUEUE ', 'sleep _ task', 'sleep _ systemtask', 'waitfor ') Order by wait_time_ms desc

To find out which spid is blocking another spid, you can create the following stored procedure in the database and then execute the stored procedure. This stored procedure reports this blocking. Type sp_who to find @ spid; @ spid is an optional parameter.

The code is as follows:

Copy code

Create proc dbo. sp_block (@ spid bigint = NULL) As Select T1.resource _ type, 'Database' = db_name (resource_database_id ), 'Blk object' = t1.resource _ associated_entity_id, T1.request _ mode, T1.request _ session_id, T2.blocking _ session_id From Sys. dm_tran_locks as t1, Sys. dm_ OS _waiting_tasks as t2 Where T1.lock _ owner_address = t2.resource _ address and T1.request _ session_id = isnull (@ spid, t1.request _ session_id)

The following is an example of using this stored procedure.

The code is as follows:	Copy code
Exec sp_block Exec sp_block @ spid = 7