A Million Little Things: Last Value

Richard Douglas

Published On: July 20, 2018

Categories: Alerting, SQL Sentry, Advisory Conditions 0

DBAs in the know use SentryOne. The reason is simple -- we have the best alerting engine in the business. Today, I want to introduce you to a comparative operator that I love to use. I don't see it used in the field very often and that's a shame because it's just awesome. That operator is "Last Value".

When creating Advisory Conditions (alerts) in SentryOne, you can add in several values to compare against. Can you guess what "Last Value" does?

I'm assuming you said "Yes!" at this point, although probably silently or that would be really weird for your coworkers.

That's right, "Last Value" allows you to compare the current value for whatever you are checking against the value last returned in your condition. This means you can do all kinds of cool stuff to make sure you know when a change has occurred in your environment.

Obligatory Example

Let's pretend that we want to create a condition to let us know if somebody is changing system settings in SQL Server, specifically the Max Degree of Parallelism. After all, setting that to 1 is the best way to solve CXPACKET waits, right? I'm joking, of course, but somebody in your organization may believe that. So let's look at this condition, which is already available in the SentryOne Advisory Conditions pack.

In this case, we would want to write a SQL Server query to pull the current value.

SELECT value_in_use
FROM sys.configurations
WHERE name = N'max degree of parallelism';

We can then compare the current value to the last value collected.

Note that in SentryOne, we added in a prerequisite "Value is greater than" step in case this is a VM with only one core. No point checking for parallelism changes if there is only one core!

To find out more about how you can create Advisory Conditions in SentryOne to solve your business problems, check out Lori Edwards's post Creating Advisory Conditions in SentryOne.

Richard (@SQLRich) is a Principal Solutions Engineer at SentryOne, specializing in our SQL Server portfolio offering in EMEA. He has worked with SQL Server since version 7.0 in various developer and DBA roles and holds a number of Microsoft certifications. Richard is a keen member of the SQL Server community; previously he ran a PASS Chapter in the UK and served on the organizing committee for SQLRelay.