Blog
Finding SQL Server Problems
By Don Kiely | November 21, 2012
The Activity Monitor in SQL Server 2012 gives you a view into the current activity in the SQL Server instance. This can be of enormous help in finding problems before they get out of hand or finding a problem that causes slow server performance. In fact, any time a SQL Server instance is behaving oddly, you can often get right to the root of the problem, or at least get a good idea of what is going on, by viewing Activity Monitor.
You can view the Activity Monitor by right-clicking the name of the server instance in Object Explorer and selecting Activity Monitor from the pop-up menu.
The figure below shows the Activity Monitor on a lightly used server after running a few simple queries. The Overview section provides a graphical view into current server activity, with graphs for the percent of processor time being used, the number of waiting tasks, database I/O in megabytes per second, and batch requests in number per second. Click on each of the section headers to expand the section and view the data.
The other sections display tabular data about current activity:
- Processes displays information about database connections and the types of tasks each performs. This data is listed by server process ID, or SPID. You can right-click on any of the SPIDs to launch SQL Server Profiler to trace the activity on that process.
- Resource Waits indicates the amount of time that various threads are waiting for resources, such as memory and processor time.
- Data File I/O shows database and log file input/output information, including statistics on the amount of data read from and written to disk. This information can help find databases that are being especially hard hit with activity, causing disk activity to be a bottleneck to performance.
- Recent Expensive Queries displays information about recent queries that made the greatest demands on various server resources. This can help identify problem queries that are making unusually high demands on the system.
All of the tabular views let you search and filter the various columns. For example, to filter the list, click the down arrow to the right of the column name as shown in the Figure below and select the item to filter on. You can sort by clicking on the appropriate column header, repeating to reverse the sort order.
TIP: Activity Monitor makes heavy use of Tooltips. Hover the mouse over the dark green portion of graphs or column headers in tabular data to get more information about that section of the view.
