(Apologies to Star Trek).<\/p>\n
I received a question about trigger performance, especially the do’s and dont’s about how they are used.<\/p>\n
Let’s first start by saying that triggers can be very useful tools to facilitate server-side programming in OLTP systems. They are powerful ways to do things like audit changes to a table, validate business logic, or extend the features that the database server provides to you. So, in the proper hands, these can really provide a lot of business value.<\/p>\n
The problem with this area is that there is a great temptation to think about databases procedurally, as you would with a programming language like C++ or C#. You can write code that looks like a procedural function call and have it get called for each insert into table! Before you know it, non-database programmers are checking in code to your production sysem. Suddenly, your application grinds to a halt because a trigger plan has no index or is poorly designed. <\/p>\n
Databases are great tools for working on SETS of rows. You can define queries that can query the whole data efficiently. If you do it properly, you can solve a lot of problems very easily. So, for any given query, the startup time for a query is relatively high, but the per-row cost is optimized by lots of smart people. I can assure you that they count instructions, worry about CPU architectures, memory access trends, cache hierarchies, and every other trick in the book to make the per-row cost of a query as low as possible. So, it may take a few million instructions to start a query vs. a few thousand or tens of thousands to process a row in a query.<\/p>\n
When you add a trigger, you should think that you are adding another query whenever you run the first one. Furthermore, if you have written your application to call the database more often than necessary (say, once per row instead of batching things up as sets), then you are now adding the cost of that second query to the PER-ROW cost of the query. That is pretty close to the definition of a “bad thing” in the dictionary…<\/p>\n
In addition to the number of times that a trigger is called, the trigger plan itself may not be set up to run efficiently. If the trigger enforces business logic that touches a lot of tables or rows, you need to consider what indexes to add. I usually create an equivalent SELECT statment and run it to look at the query plan (I’d fake data and a table for the inserted table, for example). This will help me find slow queries before the trigger is deployed – you probably want to look closely at every table scan to see if it is really necessary).<\/p>\n
I’ve put together some examples that you can try to see how the system behaves under different scenarios with triggers. I would publish some numbers to give you relative ideas about how fast things are, but I think that the current CTP of SQL Server that I am running has a few issues with memory on my machine that cause it to page wildly occassionally, so I will let you go run these on your own. The basic idea is that things will get slower as you do more and more “bad practices” with triggers.<\/p>\n
create<\/span> table<\/span> table1 (col1 int<\/span> primary<\/span> key<\/span> identity<\/span>, col2 int<\/span>, col3 binary<\/span>(2000), col4 binary<\/span>(4000))\r\ndeclare<\/span> @a int<\/span>\r\nset<\/span> @a=0\r\nwhile<\/span> @a < 20000\r\nbegin<\/span>\r\ninsert<\/span> into<\/span> table1 (col2) values<\/span> (@a\/100);\r\nset<\/span> @a+=1\r\nend<\/span>\r\ngo\r\n-- target table on which we are creating triggers<\/span>\r\ncreate<\/span> table<\/span> table2 (col1 int<\/span> primary<\/span> key<\/span> identity<\/span>, col2 int<\/span>, col3 binary<\/span>(2000), col4 binary<\/span>(4000))\r\n\r\n-- audit table<\/span>\r\ncreate<\/span> table<\/span> table3(col1 int<\/span>, col2 binary<\/span>(100))\r\n\r\n-- try to insert 20,000 rows with no triggers defined - should be pretty fast.<\/span>\r\nbegin<\/span> transaction<\/span>\r\ninsert<\/span> into<\/span> table2 select<\/span> col2, col3, col4 from<\/span> table1\r\nrollback<\/span>\r\ngo\r\n\r\n-- now create a simple trigger that reads all the inserted rows and copies them to table3 (an audit table)<\/span>\r\ncreate<\/span> trigger<\/span> trig1 ON<\/span> table2 FOR<\/span> INSERT<\/span> AS<\/span> INSERT<\/span> INTO<\/span> table3(col1, col2) select<\/span> col1, null<\/span> from<\/span> inserted\r\nbegin<\/span> transaction<\/span>\r\ninsert<\/span> into<\/span> table2 select<\/span> col2, col3, col4 from<\/span> table1\r\nrollback<\/span>\r\n-- this will run the trigger query once with all 20,000 rows. <\/span>\r\ngo\r\n\r\n-- here is where triggers starts to hurt. Let's write the same query except that <\/span>\r\n-- we read each row separately in its own query. (This will be even worse if you remove the begin transaction<\/span>\r\n-- since each row will need to commit the log to disk).<\/span>\r\nbegin<\/span> transaction<\/span>\r\ndeclare<\/span> @i int<\/span>\r\nset<\/span> @i=0\r\nwhile<\/span> @i < 20000\r\nbegin<\/span>\r\ninsert<\/span> into<\/span> table2 select<\/span> col2, col3, col4 from<\/span> table1 where<\/span> col1 = @i\r\nset<\/span> @i+=1\r\nend<\/span>\r\nrollback<\/span>\r\n-- This is a very poor use of the database - In almost every case, using a set-based approach will <\/span>\r\n-- yield better performance. You avoid multiple transactions. You avoid running through a long codepath<\/span>\r\n-- to start each query. You give the optimizer to consider more advanced plans.<\/span>\r\ngo\r\n\r\n-- let's remove trig1 for our next example<\/span>\r\ndrop<\/span> trigger<\/span> trig1 \r\ngo\r\n\r\n-- now let's create a trigger that copies ~100 rows every time it is called. Furthermore, let's <\/span>\r\n-- restrict on a non-indexed column in the source table. This will lead to a table scan for each call.<\/span>\r\n-- This should simulate your average \"bad\" trigger formulation.<\/span>\r\ncreate<\/span> trigger<\/span> trig2 ON<\/span> table2 FOR<\/span> INSERT<\/span> AS<\/span> INSERT<\/span> INTO<\/span> table3(col1, col2) select<\/span> col1, null<\/span> from<\/span> table1 where<\/span> col2=5;\r\ngo\r\n\r\n-- and, for fun, we'll run the one row at a time example again.<\/span>\r\n-- (go get some coffee, or whatever else you do to amuse yourself - this will take awhile.)<\/span>\r\nbegin<\/span> transaction<\/span>\r\ndeclare<\/span> @i int<\/span>\r\nset<\/span> @i=0\r\nwhile<\/span> @i < 20000\r\nbegin<\/span>\r\ninsert<\/span> into<\/span> table2 select<\/span> col2, col3, col4 from<\/span> table1 where<\/span> col1 = @i\r\nset<\/span> @i+=1\r\nend<\/span>\r\nrollback<\/span><\/span><\/pre>\nSo what is a responsible DBA or database developer to do??? \ud83d\ude42<\/p>\n
Well, there is hope. A few things to consider:<\/p>\n
1. Look at that trigger – do you need it? really? Perhaps not. Consider each one and make sure that you really, really need it.<\/p>\n
2. Make sure that you call any trigger the minimum number of times. SET STATISTICS PROFILE ON can help you see how many calls are happening for any given query. <\/p>\n
3. You can often pull the logic for a trigger up into the stored procedure that is calling your query. This works if you can control all of the codepaths into the system for changes to the system – this is often the case in OLTP applications.<\/p>\n
4. SQL Server 2005 added some neat features that help with benchmarks. However, they are also quite useful for you if you have the time to consume them. Look up INSERT… with OUTPUTs. This lets you insert rows into one table and then perform an operation with the rows after the insertion (return them as a select query, insert them into another table, etc). This is a very cool feature. It takes quite a bit of knowledge to wrap your head around what the database system is doing to make this all work (too much blogging for this post, to be sure), but I can assure you that it is pretty fast :).<\/p>\n
Bottom line – I recommend that you have one person at your company who is responsible to understand the schema, including things like triggers. This means that someone can think through the tradeoffs and make reasonable decisions to avoid the most common pitfalls.<\/p>\n
Happy Querying, folks!<\/p>\n
Conor Cunningham<\/p>\n","protected":false},"excerpt":{"rendered":"
(Apologies to Star Trek). I received a question about trigger performance, especially the do’s and dont’s about how they are used. Let’s first start by saying that triggers can be very useful tools to facilitate server-side programming in OLTP systems. They are powerful ways to do things like audit changes to a table, validate business […]<\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[],"class_list":["post-390","post","type-post","status-publish","format-standard","hentry","category-design"],"yoast_head":"\n
The Trouble with Triggers - Conor Cunningham<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/conor\/the-trouble-with-triggers\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Trouble with Triggers - Conor Cunningham\" \/>\n<meta property=\"og:description\" content=\"(Apologies to Star Trek). I received a question about trigger performance, especially the do’s and dont’s about how they are used. Let’s first start by saying that triggers can be very useful tools to facilitate server-side programming in OLTP systems. They are powerful ways to do things like audit changes to a table, validate business […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/conor\/the-trouble-with-triggers\/\" \/>\n<meta property=\"og:site_name\" content=\"Conor Cunningham\" \/>\n<meta property=\"article:published_time\" content=\"2008-06-17T19:47:55+00:00\" \/>\n<meta name=\"author\" content=\"Conor Cunningham\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Conor Cunningham\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/the-trouble-with-triggers\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/the-trouble-with-triggers\/\",\"name\":\"The Trouble with Triggers - Conor Cunningham\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#website\"},\"datePublished\":\"2008-06-17T19:47:55+00:00\",\"dateModified\":\"2008-06-17T19:47:55+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/the-trouble-with-triggers\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/conor\/the-trouble-with-triggers\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/the-trouble-with-triggers\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Trouble with Triggers\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/\",\"name\":\"Conor Cunningham\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3\",\"name\":\"Conor Cunningham\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g\",\"caption\":\"Conor Cunningham\"},\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/author\/conor\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"The Trouble with Triggers - Conor Cunningham","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/conor\/the-trouble-with-triggers\/","og_locale":"en_US","og_type":"article","og_title":"The Trouble with Triggers - Conor Cunningham","og_description":"(Apologies to Star Trek). I received a question about trigger performance, especially the do’s and dont’s about how they are used. Let’s first start by saying that triggers can be very useful tools to facilitate server-side programming in OLTP systems. They are powerful ways to do things like audit changes to a table, validate business […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/conor\/the-trouble-with-triggers\/","og_site_name":"Conor Cunningham","article_published_time":"2008-06-17T19:47:55+00:00","author":"Conor Cunningham","twitter_misc":{"Written by":"Conor Cunningham","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/the-trouble-with-triggers\/","url":"https:\/\/www.sqlskills.com\/blogs\/conor\/the-trouble-with-triggers\/","name":"The Trouble with Triggers - Conor Cunningham","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#website"},"datePublished":"2008-06-17T19:47:55+00:00","dateModified":"2008-06-17T19:47:55+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/the-trouble-with-triggers\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/conor\/the-trouble-with-triggers\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/the-trouble-with-triggers\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/conor\/"},{"@type":"ListItem","position":2,"name":"The Trouble with Triggers"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/conor\/","name":"Conor Cunningham","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/conor\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3","name":"Conor Cunningham","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g","caption":"Conor Cunningham"},"url":"https:\/\/www.sqlskills.com\/blogs\/conor\/author\/conor\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts\/390","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/comments?post=390"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts\/390\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/media?parent=390"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/categories?post=390"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/tags?post=390"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}