Opinion: Make sure monitoring isn't causing your SQL Server performance issue

There’s a well-known effect that’s often described in physics as the Observer effect. The argument is that whenever you measure things, you invariably alter them. The simple example given in Wikipedia is that it’s pretty hard to check the pressure of a tire (tyre) on a car, without letting at least some air out.

The same effect also happens in IT systems. A simple example is that to observe something, you might add logging or auditing, and the work to output those slows down the primary work that you are doing.

It’s really important to make sure that your monitoring isn’t having an adverse affect on your SQL Server workloads.

Example Issues

A few years back, I was at a client site. At the end of a room full of developers, they had a huge LCD screen that was showing how busy the system was. When I finished my tracing work, I could easily demonstrate that the heaviest workload that the system was executing, was the query that updated that LCD screen.

Yes, the system that was monitoring the workload was the largest source of the workload that it was monitoring.

At another site, there were hundreds of information kiosks. They were endlessly getting behind in communicating with the main system. Why? Because of endless monitoring queries being sent to them from the main system.

I now regularly come across really intrusive queries being performed by the very monitoring tools that customers purchased to help them avoid performance problems.

Query Quality

One aspect of this that isn’t often discussed is that in many cases, the queries that are part of the core system are well-written and tuned, yet the queries that are part of the monitoring are quite poorly written, and often haven’t been tuned at all. Many are added in quite an ad hoc way.

Systems Center Operations Manager (SCOM) monitoring queries are a common source of problems. I’ve lost count of how many times I’ve been confronted by nasty performance problems, caused entirely by queries that someone has added into SCOM, to keep an eye on the SQL Server system. Those types of queries don’t usually get the oversight that the application queries might.

Check the Effect

I often see people checking for performance issues in their systems, and the first thing they do is to filter out any monitoring-related queries. That might not be the best idea.

2019-08-27