While CPU numbers and batch requests/second are useful metrics, you do not necessarily need to see them updated in real-time. In the top section are a few charts that show us server utilization numbers. Let's take a look at a screenshot from my demo virtual machine: Beyond the performance implication, the frequent refreshing of the dashboard is distracting and unhelpful.īeyond Activity Monitor's performance impacts, it is frankly not a very effective tool for monitoring server performance. I've even seen scenarios where, on a busy server, TempDB performance can be negatively impacted by the frequent queries that populate temporary tables. In the case of Activity Monitor, it runs 13 queries every 10 seconds to constantly refresh its dashboard. While dashboards can be useful, they tend to cause a lot of observer overhead on the underlying database as they try to move toward real-time. In other cases, the data is simply point-in-time, where you execute some queries and review the data.Īctivity Monitor is a classic example of a real-time dashboard, like we see in some business intelligence systems. This data store can either be local (in the case of the Query Store, it's stored in the local database) or in another remote database (this is the more common pattern in third-party tools). The way most database performance tools work is by collecting a small amount of data at fixed intervals and moving that data into a data store. Many tools will even reduce data collection as load increases (or allow for some data to be lost). The basic idea of monitoring the performance of any type of computer system is to minimize resource consumption while doing the monitoring, which is called "observer overhead." Tools are designed with this in mind. However, Activity Monitor can consume a lot of server resources, particularly if it runs on SQL Server Management Studio on your database server. Activity Monitor is a tool inside Microsoft's SQL Server Management Studio that may seem innocuous. One thing I have noticed in a number of recent consultations is the use of Activity Monitor for troubleshooting performance issues on a server.
No one calls us when everything is running well. I get to work with a lot of customers, so I get to see a lot of SQL Servers - and a lot of SQL Server performance problems.
0 kommentar(er)
