One Common SQL Programming Mistake (Red-Gate)

Predicate evaluation order

If you are familiar with the logical query processing order, then you may expect that a query is executed in the following order:


The sequence above outlines the logical order for executing query. Logically the FROM clause is processed first defining the source data set, next the WHERE predicates are applied, followed by GROUP BY, and so on.

However, physically, the query is processed differently and the query optimizer is free to move expressions in the query plan in order to produce the most cost efficient plan for retrieving the data. This leads to a common misunderstanding that a filter in the WHERE clause is applied before the next phases are processed. In fact, a predicate can be applied much later in the physical execution plan. Also, there is no left to right order for execution of predicates. For example, if you have a WHERE clause containing „WHERE x=1 AND y=2“, there is no guarantee that „x=1“ will be evaluated first. They can be executed in any order.

via Ten Common SQL Programming Mistakes.

SCOM of the (Microsoft) World

I was doing some Performance Analysis today (again) and on one Server I found that the CPU load regularly went to 100% and even, at times, stayed there for a while.

I happened to have a Microsoft Premier Field Engineer looking over my shoulder at the time and he said something to the effect „oh, that’s probably the Operations Manager Monitoring“… normally one would assume that monitoring is a quiet little task sitting in the background doing its work unnoticed until there is something to report. However, in this case it soon turned out to be that the „oh“ was justified, the „System Center Operation Manager“ was often top of the CPU% list and often over 50%.

We could start to talk about the various options to restrict SCOM monitoring activity but instead i decided to use a procedure which had cured a problem on another node of the same cluster.

  • Stop the „System Center Management“ service
  • Delete all files in „C:\Program Files\System Center Operations Manager\Agent\Health Service State“
  • Restart the „System Center Management“ service

And it did bring some relief. The CPU Load was very rarely at 100% and the monitoring service was never above 50%. At least not in the few hours I continued to look into it.

Ultimately the SCOM stuff will have to be configured so as not to take so many resources on the monitored machines but for now we have one more bottleneck widened a little.

We can, again, consider whether the SCOM Team/Guy received a warning message that the SCOM Agent had been stopped, as I would expect, but that is a question for another day.

Think! Act! Harvest, and repeat.

Welcome to our new WordPress website for the company:

Hector Entwicklungs GmbH

The company name will be changing, probably to CComPP, but until the paperwork ist completed we are using the name to lay claim to it. The new motto of our company is:

Think! Act! Harvest, Repeat.

Although we may replace excamation marks with commas and commas with exclamaton marks as we feel fitting the occasion or posting.

Yours, Andrew