top of page

Data Collector vs Activity Monitor

The data collector is a core component of the data collection platform for SQL Server 2019 (15.x) and the tools that are provided by SQL Server. The data collector provides one central point for data collection across your database servers and applications. This collection point can obtain data from a variety of sources and is not limited to performance data, unlike SQL Trace.

The following illustration shows how the data collector fits in the overall strategy for data collection and data management in SQL Server 2019 (15.x).

Things you can do

SQL Server performance monitoring with Data Collector:

 

Activity Monitor displays information about SQL Server processes and how these processes affect the current instance of SQL Server.

Activity Monitor is a tabbed document window with the following expandable and collapsible panes: Overview, Processes, Resource Waits, Data File I/O, Recent Expensive Queries, and Active Expensive Queries. When any pane is expanded, Activity Monitor queries the instance for information. When a pane is collapsed, all querying activity stops for that pane. You can expand one or more panes at the same time to view different kinds of activity on the instance.

Reference:

https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/activity-monitor?view=sql-server-ver15

 

To view actual activity, you must have VIEW SERVER STATE permission. To view the Data File I/O section of Activity Monitor, you must have CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION permission in addition to VIEW SERVER STATE. To KILL a process, a user must be a member of the sysadmin or processadmin fixed server roles.

  • Step 1:Type CTRL+ALT+A to open Activity Monitor at any time.

  • Step 2: From the Tools menu, click Options.

  • Step 3: In the Options dialog box, expand Environment, and then select Startup.

  • Step 4: From the At startup drop-down list, select Open Object Explorer and Activity Monitor.

  • Step 5: Click OK.

  • Step 6: Right-click Overview, select Refresh Interval, and then select the interval in which Activity Monitor should obtain new instance information.

Reference:

 

Example:

Answer:

bottom of page