失眠网 > SQL Server活动监视器

SQL Server活动监视器

时间：2020-07-17 00:26:12

相关推荐

SQL Server活动监视器

什么是SQL Server活动监视器？ (What is SQL Server Activity Monitor?)

SQL Server Activity Monitor is a feature in SQL Server Management Studio that displays information about the SQL Server processes and their effect on SQL Server performance

SQL Server活动监视器是SQL Server Management Studio中的一项功能，可显示有关SQL Server进程及其对SQL Server性能的影响的信息

活动监视器窗格 (Activity Monitor panes)

Activity Monitor consists of several panes –Overview, Processes, Resource Waits, Data File I/O, andRecent Expensive Queries. Panes can be expanded and collapsed. The activities are queried only when the specific pane is expanded

活动监视器由几个窗格组成-概述，流程，资源等待，数据文件I / O和最近的昂贵查询。窗格可以展开和折叠。仅在展开特定窗格时才查询活动

“Activity Monitor runs queries on the monitored instance to obtain information for the Activity Monitor display panes. When the refresh interval is set to less than 10 seconds, the time that is used to run these queries can affect server performance”[1]

“活动监视器在受监视的实例上运行查询，以获取活动监视器显示窗格的信息。当刷新间隔设置为小于10秒时，用于运行这些查询的时间可能会影响服务器性能”[1]

概述窗格 (The Overview pane)

TheOverviewpane contains the graphs for the most important SQL Server instance information. The context menu contains theRefresh intervaloption that can be set to a predefined value from 1 second to 1 hour

概述窗格包含最重要SQL Server实例信息的图。上下文菜单包含“刷新间隔”选项，可以将其设置为从1秒到1小时的预定义值

% Processor Time– is the percentage of time the processors spend to execute threads that are not idle

％Processor Time–是处理器用于执行非空闲线程的时间百分比

Waiting Tasks– is the number of tasks that are waiting for processor, I/O, or memory to be released so the tasks can be processed

等待任务–是等待处理器，I / O或内存被释放以便可以处理任务的任务数

Database I/O– is the data transfer rate in MB/s from memory to disk, disk to memory, or disk to disk

数据库I / O–是从内存到磁盘，从磁盘到内存或从磁盘到磁盘的数据传输速率，以MB / s为单位

Batch Requests/sec– is the number of SQL Server batches received by the instance in a second

每秒批处理请求数–是实例在一秒钟内收到SQL Server批处理数

进程窗格 (The Processes pane)

TheProcessespane shows the information about the currently running processes on the SQL databases, who runs them, and from which application

“进程”窗格显示有关SQL数据库上当前正在运行的进程，谁运行它们以及从哪个应用程序运行的信息。

A tooltip for each table header shows a short description of the column data and system views used to obtain it. Each column can be filtered using the filters in the column header

每个表标题的工具提示显示了列数据的简短描述以及用于获取列数据的系统视图。可以使用列标题中的过滤器过滤每列

The context menu for the specific process provides options to see the last T-SQL command batch for the process, kill it, or trace it in SQL Server Profiler

特定进程的上下文菜单提供了一些选项，以查看该进程的最后一个T-SQL命令批处理，将其杀死或在SQL Server Profiler中对其进行跟踪

Session ID– is a unique value assigned by Database Engine to every user connection. This is thespidvalue returned by thesp_whoprocedure

会话ID–是数据库引擎分配给每个用户连接的唯一值。这是sp_who过程返回的spid值

User Process– 1 for user processes, 0 for system processes. The default filter is set to 1, so only user processes are shown

用户进程– 1用于用户进程，0用于系统进程。默认过滤器设置为1，因此仅显示用户进程

登录名–运行会话SQL Server登录名

Database– the database name on which the process is running

数据库–运行进程的数据库名称

Task State – the task state, blank for tasks in the runnable and sleeping state. The value can also be obtained using the sys.dm_os_tasks view, as the task_state column. The states returned can be:

“PENDING: Waiting for a worker thread.

RUNNABLE: Runnable, but waiting to receive a quantum.

RUNNING: Currently running on the scheduler.

SUSPENDED: Has a worker, but is waiting for an event.

DONE: Completed.

SPINLOOP: Stuck in a spinlock.”[2]

任务状态–任务状态，处于可运行和睡眠状态的任务为空白。也可以使用sys.dm_os_tasks视图作为task_state列获取该值。返回的状态可以是：

“正在等待：正在等待工作线程。

可运行：可运行，但正在等待接收量子。

运行：当前正在调度程序上运行。

暂停：有工人，但正在等待事件。

完成：完成。

SPINLOOP：卡在一个自旋锁中。[2]

Command– the current command type. The value can also be obtained using thesys.dm_exec_requestsview, as thecommandcolumn

Command–当前命令类型。也可以使用sys.dm_exec_requests视图作为命令列获取该值

Application– the name of the application that created the connection

应用程序–创建连接的应用程序的名称

Wait Time (ms)– how long in milliseconds the task is waiting for a resource. The value can also be obtained using thesys.dm_os_waiting_tasksview, as thewait_duration_mscolumn

等待时间（毫秒）–任务等待资源的时间（以毫秒为单位）。该值也可以使用sys.dm_os_waiting_tasks视图作为wait_duration_ms列获得

Wait Type – the last/current wait type. The value can also be obtained using thesys.dm_os_waiting_tasksview, as thewait_typecolumn. The waits can be resource, queue and external waits

等待类型–最后/当前的等待类型。也可以使用sys.dm_os_waiting_tasks视图作为wait_type列获取该值。等待可以是资源，队列和外部等待

Wait Resource– the resource the connection is waiting for. The value can also be obtained using thesys.dm_os_waiting_tasksview, as theresource_descriptioncolumn

等待资源–连接正在等待的资源。也可以使用sys.dm_os_waiting_tasks视图作为resource_description列获取该值

Blocked By– the ID of the session that is blocking the task. The value can also be obtained using thesys.dm_os_waiting_tasksview, as theblocking_session_idcolumn

阻止者–阻止任务的会话的ID。该值也可以使用sys.dm_os_waiting_tasks视图作为blocking_session_id列获得

Head Blocker– the session that causes the first blocking condition in a blocking chain

Head Blocker–导致阻塞链中第一个阻塞条件的会话

Memory Use (KB)– the memory used by the task. The value can also be obtained using thesys.dm_exec_sessionsview, as thememory_usagecolumn

内存使用（KB）–任务使用的内存。该值也可以使用sys.dm_exec_sessions视图作为memory_usage列获得

Host Name– the name of the computer where the current connection is made. The value can also be obtained using thesys.dm_exec_sessionsview, as thehost_namecolumn

主机名–建立当前连接的计算机的名称。也可以使用sys.dm_exec_sessions视图作为host_name列获取该值。

Workload Group– the name of the Resource Governor workload group[3]. The value can also be obtained using thesys.dm_resource_governor_workload_groupsview, as thenamecolumn

工作负载组–资源调控器工作负载组的名称[3]。也可以使用sys.dm_resource_governor_workload_groups视图作为名称列获取该值

资源等待窗格 (The Resource Waits pane)

Shows information about waits for resources

显示有关等待资源的信息

Wait Category– the categories are created combining closely related wait types. The wait types are shown in theWait Typecolumn of theProcessespane

等待类别–类别是结合紧密相关的等待类型而创建的。等待类型显示在“进程”窗格的“等待类型”列中

Wait Time (ms/sec)– the time all waiting tasks are waiting for one or more resources

等待时间（毫秒/秒）–所有等待任务等待一个或多个资源的时间

Recent Wait Time (ms/sec)– the average time all waiting tasks are waiting for one or more resources

最近等待时间（毫秒/秒）–所有等待任务等待一个或多个资源的平均时间

Average Waiter Count– is calculated for a typical point in time in the last sample interval and represents the number of tasks waiting for one or more resources

平均服务员计数–是针对最后一个采样间隔中的典型时间点计算的，表示等待一个或多个资源的任务数

Cumulative Wait Time (sec)– the total time waiting tasks have waited for one or more resources since the last SQL Server restart, or DBCC SQLPERF last execution

累积等待时间（秒）–自上一次SQL Server重新启动或DBCC SQLPERF上一次执行以来，等待任务等待一个或多个资源的总时间

数据文件I / O窗格 (The Data File I/O pane)

Shows information about the database files on the SQL Server instance. For each database, all database files are listed – MDF, LDF and NDF, their paths, and names

显示有关SQL Server实例上的数据库文件的信息。对于每个数据库，列出了所有数据库文件– MDF，LDF和NDF，它们的路径和名称

MB/sec Read– shows recent read activity for the database file

MB /秒读取–显示数据库文件的最近读取活动

MB/sec Written– shows recent write activity for the database file

已写入的MB /秒-显示数据库文件的最近写入活动

Response Time (ms)– average response time for recent read-and-write activity

响应时间（毫秒）–最近读写活动的平均响应时间

最近的昂贵查询窗格 (The Recent Expensive Queries pane)

Expensive queries are the queries that use much resources – memory, disk, network. The pane shows expensive queries executed in the last 30 seconds. The information is obtained from thesys.dm_exec_requestsandsys.dm_exec_query_statsviews. A double-click on the query opens the monitored statement

昂贵的查询是使用大量资源（内存，磁盘，网络）的查询。该窗格显示最近30秒执行的昂贵查询。该信息是从sys.dm_exec_requests和sys.dm_exec_query_stats视图获得的。双击查询将打开受监视的语句

The context menu for the specific query provides options to open the query in Query Editor, and show the execution plan

特定查询的上下文菜单提供了用于在“查询编辑器”中打开查询并显示执行计划的选项

Query– the SQL query statement monitored

查询–受监控SQL查询语句

Executions/min– the number of executions per minute, since the last recompilation. The value can also be obtained using thesys.dm_exec_query_statsview, as theexecution_countcolumn

处决/分钟-每分钟的执行次数，自上次重新编译。也可以使用sys.dm_exec_query_stats视图作为execution_count列来获取该值。

CPU (ms/sec)– the CPU rate used, since the last recompilation. The value can also be obtained using thesys.dm_exec_query_statsview, as thetotal_worker_timecolumn

CPU（ms / sec）–自上次重新编译以来使用的CPU速率。也可以使用sys.dm_exec_query_stats视图作为total_worker_time列获取该值

Physical Reads/sec, Logical Writes/sec,andLogical Reads/sec– the rate of physical reads/logical writes/logical reads per second. The value can also be obtained using thesys.dm_exec_query_statsview, as thetotal_physical_reads/ total_logical_writes/ total_logical_readscolumns

每秒物理读取数，每秒逻辑写入数和每秒逻辑读取数–每秒物理读取/逻辑写入/逻辑读取的速率。也可以使用sys.dm_exec_query_stats视图获取该值，如total_physical_reads / total_logical_writes / total_logical_reads列

Average Duration (ms)– average time that the query runs. Calculated based on thetotal_elapsed_timeandexecution_countcolumns in thesys.dm_exec_query_statsview

平均持续时间（ms）–查询运行的平均时间。根据sys.dm_exec_query_stats视图中的total_elapsed_time和execution_count列计算

Plan Count– the number of duplicate query plans. A large number requires investigation and potential explicit query parameterization

计划计数–重复查询计划的数量。大量需要调查和潜在的显式查询参数化

使用活动监视器的要求 (Requirements to use Activity Monitor)

The permission necessary to view Activity Monitor is VIEW SERVER STATE

查看活动监视器所需的权限是VIEW SERVER STATE

To view the Data File I/O pane, besides VIEW SERVER STATE, the login must be granted CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION permissions

要查看“数据文件I / O”窗格，除了“查看服务器状态”外，还必须授予登录名“创建数据库”，“更改任何数据库”或“查看任何定义”权限

To kill a process, it’s necessary to be a member of the sysadmin role

要终止进程，必须成为sysadmin角色的成员

如何启动活动监视器 (How to start Activity Monitor)

There are several ways to start Activity Monitor – in the SQL Server Management Studio toolbar click the activity Monitor icon, use the keyboard shortcut Ctrl+Alt+A, or inObject Explorerright-click the SQL Server instance and selectActivity Monitor

有几种启动活动监视器的方法–在SQL Server Management Studio工具栏中单击活动监视器图标，使用键盘快捷键Ctrl + Alt + A，或者在对象资源管理器中右键单击SQL Server实例，然后选择“活动监视器”。

One more option is to set Activity Monitor to be opened when SQL Server Management Studio is started

另一种选择是将活动监视器设置为在启动SQL Server Management Studio时打开

Options选项”。Open theEnvironment | Start Uptab 打开环境启动标签Open Object Explorer and Activity Monitoroption打开对象资源管理器和活动监视器”选项

On the next SQL Server Management Studio start up, Object Explorer will be shown on the left and Activity Monitor on the right

在下一次启动SQL Server Management Studio时，对象资源管理器将显示在左侧，活动监视器将显示在右侧。

Activity Monitor is a SQL Server Management Studio feature that tracks some of the most important metrics that affect performance. It shows the metrics in real time, without the out-of-the-box solution to save them for later analysis. Filtering by a specific database/parameter is easy, but excluding a specific value is not possible. Monitoring additional metrics is also not possible. Due to its limited set of features and monitored metrics, it’s not recommended for in-depth performance monitoring

活动监视器是SQL Server Management Studio的一项功能，可跟踪一些影响性能的最重要的指标。它实时显示指标，而无需开箱即用的解决方案来保存它们以供以后分析。通过特定的数据库/参数进行过滤很容易，但是不可能排除特定的值。也无法监视其他指标。由于功能和受监视的指标集有限，因此不建议对其进行深入的性能监视