Extended Events for Anything But Query Tuning: Unique Constraint Violations

Most of the time when I talk about or demo Extended Events, I spend more time talking about query tuning (I have a problem). However, there are tons of things that you can do with Extended Events. Here’s a little one that came up, auditing unique constraint violations.

Unique Constraint Violations

Whether we’re talking a primary key or just a constraint, the error you get is number 2627 when you attempt to add a non-unique value. So, the code for a simple way to track this in Extended Events would look like this:

CREATE EVENT SESSION [UniqueConstraintViolation]
ON SERVER
    ADD EVENT sqlserver.error_reported
    (WHERE ([error_number] = (2627)));

That’s it. That’s all you need. Probably, it’d be a good idea to output this to a file (that’s usually what I do). However, here’s your info returned:

Done!

However, which database is that? What was the actual query run? We can add those to the event:

CREATE EVENT SESSION [UniqueConstraintViolation]
ON SERVER
    ADD EVENT sqlserver.error_reported
    (ACTION
     (
         sqlserver.database_name,
         sqlserver.sql_text
     )
     WHERE ([error_number] = (2627))
    );

And now the unique constraint violation details look like this:

Now we know which query on which database. Nice.

What if we wanted it even simpler. I just want to know how often this occurred. Let’s add a different target; the histogram:

CREATE EVENT SESSION [UniqueConstraintViolation]
ON SERVER
    ADD EVENT sqlserver.error_reported
    (ACTION
     (
         sqlserver.database_name,
         sqlserver.sql_text
     )
     WHERE ([error_number] = (2627))
    )
    ADD TARGET package0.histogram
    (SET source = N'sqlserver.database_name');

Now I’ll simply see a count of every instance of this error, grouped by database:

Conclusion

Obviously not earth shattering stuff, but knowing the level of flexibility that you have with what you can easily capture within Extended Events, you begin to see the power of it. I especially love the ability to add that histogram target. It’s so useful.

However, there you go, monitoring the unique constraint violations with varying levels of detail.