A Fine Slice Of SQL Server
Thomas Rushton has a warning for us:
Let’s test that script:
Create Table Trigger FiredCompletion time: 2021-02-27T14:17:09.4783931+00:00Great. Refresh the tables list in the object explorer to go poking around, and:
Hmm. We have a problem.
Click through to understand what happened.
Comments closedEd Pollack tries to improve our usage of triggers:
SQL Server triggers are a subject of both functionality and controversy on any database platform. They can provide immense utility at a low cost, but are often misused by developers, DBAs, and architects alike, resulting in performance bottlenecks or maintainability challenges.
This article briefly reviews DML (data manipulation language) triggers and delves into a discussion about how triggers can be efficiently and effectively used as well as when triggers can dig developers into holes that are hard to escape.
Read the whole thing. Triggers are rather useful tools when used correctly, but it’s easy to misuse them on accident.
Comments closedKenneth Fisher has a public service announcement:
While I was helping him with part of it I noticed that the trigger was only set up to handle single row updates. When I told him it needed to be able to handle multiple row updates he asked me in all seriousness, “Why? Since only one value can be changed at a time why should I worry about multiple rows being changed at once?”
Read on for Kenneth’s response, which approximates to Au contraire mon frère. Unhandled assumptions often become bugs.
Comments closedLouis Davidson walks us through a tricky problem:
Writing data integrity code in TRIGGER objects is generally is pretty simple. Typically you write a query to see “is there a row in inserted/deleted that is not set up as desired for the current transaction?” and no bad data is found, keep moving. And because MOST of the time, you would only use a TRIGGER object where transactions are quick coupled with the reality that a lot of the issues like I will describe would have to occur within milliseconds… concurrency issues seem to rarely occur. But…anything that isn’t impossible, is possible. Because of the rarity of the issue, if your code isn’t running a life of death system, when the anomalies happen, they are brushed off as “just something weird occurred”.
This blog will cover one such weird occurrence that can happen when your trigger validation code queries another table in the default isolation level. You expect that your data is protected until your transaction ends, but it may not be.
Do read the whole thing.
Comments closedJason Hall takes a look at how much tempdb space Accelerated Database Recovery might use:
You might have heard me talk about tempdb parasites in the past, or maybe you’ve read my blog post on the same topic, “Be Mindful of SQL Server Tempdb Use (aka Tempdb Parasites!).” I know that at least one person did, because they recently asked a great question.
In that blog post, I reviewed how triggers use the version store in tempdb for access to the “special” trigger tables we can use from within the trigger code to access the previous and new versions of data being modified. One person on Twitter was wondering if that is still the case for triggers on databases using Accelerated Database Recovery (ADR) in SQL Server 2019. I really wasn’t sure, so I decided to find out.
Click through for the answer.
Comments closedJared Poche writes up a fun scenario he discovered:
I’ve never been a fan of triggers. I don’t like the idea of them adding an additional tax on every operation. It’s easy to forget they are even there, consuming your cycles. I’ve even seen a few nasty death-by-a-thousand-cuts scenarios with them. But I found something out this week that makes me like them even less.
I was tuning a procedure that runs 284 million times a day.
Over a number of servers and databases, but yes, that number is correct. It takes 2.5ms to run on average, with 1.0ms of CPU time. I’ll spare you the math, but that means over 3 cores of SQL Server are doing nothing but running this procedure 24/7/365. Anything we can do to improve this will be significant, even if we just shave off half a millisecond.
The best stories start with “I was tuning a procedure [which] runs 284 million times a day.”
Comments closedHasan Savran shows how you can create a trigger in Cosmos DB:
You have options if you need to use any type of triggers in Cosmos DB. There are two types of triggers in Cosmos DB. First one which I will cover here is the regular triggers which can be executed before (Pre-Triggers) or after (Post-Triggers) an operation. This type of triggers is written in JavaScript and you need to register them to a collection just like stored procedures. Second type of triggers can be created by Azure Functions and you can find more information about them in my older posts.
Pre-Triggers and Post-Triggers do not take any input parameters. Since Cosmos DB needs to work more work to execute triggers, you will end up with higher Request Units for your queries. They might name triggers, but both do not get executed automatically with every operation. You need to call them programmatically if you want to run them. If trigger throws any error for any reason, transaction will roll back and data will not be saved to the database.
Naturally, triggers are going to have a performance impact on your system regardless of the choice of data platform.
Comments closedSometimes it’s best not to ask why. However, if for some reason you have a number of triggers on tables within a database that you would like to temporarily disable, read on.
I came across a situation recently while automating a process to refresh test environments where this exact scenario came up. As part of the process several scripts were run to obfuscate production data. While these ran all the UPDATE triggers were firing. Not only were the triggers adding a significant amount of time to the process, they were also updating dates and other values that we’d prefer kept their original values.
Jess has a tiny bit of Powershell code which does this work for you.
Comments closedJon Shaulis builds a logon trigger to notify when sysadmins log into his systems:
I was helping someone set up some monitoring in their database and they were asking about being notified when someone with administrative privileges logs into SQL Server. While many of you know that I try to use the right tool for the right job, some may cringe when I say triggers can help out in this scenario.
Now, there are other methods here but the goal was to be notified when someone logs into your SQL Server with administrative privileges. Triggers will consistently fire when this event occurs and allows you to perform an action based on this event.
Just make sure you get the trigger right and don’t block everybody from logging in. That’s an awkward situation.
Comments closedBrent Ozar points out a common problem with trigger design:
When you declare variables and set them using one row from the INSERTED or DELETED virtual table, you have no idea which row you’re going to get. Even worse, sometimes this trigger will update one row, and sometimes it won’t – because it might happen to grab a row with a reputation under 1,000!
It’s an easy mistake to make and one which can have a major impact.
Comments closed