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:

FROM
WHERE
GROUP BY
HAVING
SELECT

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 WordPress website for the company:

Craven4.IT GmbH

The company name has changed, not to CComPP, as expected, but to Craven4.IT and the paperwork is completed! The motto of our company is:

Think! Act! Harvest, Repeat.

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

Yours, Andrew

Durch die weitere Nutzung der Seite stimmst du der Verwendung von Cookies zu. Weitere Informationen

Die Cookie-Einstellungen auf dieser Website sind auf "Cookies zulassen" eingestellt, um das beste Surferlebnis zu ermöglichen. Wenn du diese Website ohne Änderung der Cookie-Einstellungen verwendest oder auf "Akzeptieren" klickst, erklärst du sich damit einverstanden.

Schließen