April 14, 2016 at 1:34 am
Hi All.
Currently in one of our project we have a situation.
The server computer is Windows Server 2008 Standard SP2. Processor is Xeon (R) 2.13 Ghz. SQL server 2008 R2.
We need to implement database Trigger on a table which could have around 100 hits (for fetch and DML) per second. Trigger will work on Insert, Update and Delete. Wanted to know will the system performance go considerably due these triggers?
April 14, 2016 at 4:27 am
Potentially yes, it all depends on what your trigger is doing and what proportion of "hits" are selects compared to updates \ inserts \ deletes.
If you share your code it will help people take a best guess with some insight into what the trigger actually does.
MCITP SQL 2005, MCSA SQL 2012
April 14, 2016 at 10:10 am
shoab.shah (4/14/2016)
Hi All.Currently in one of our project we have a situation.
The server computer is Windows Server 2008 Standard SP2. Processor is Xeon (R) 2.13 Ghz. SQL server 2008 R2.
We need to implement database Trigger on a table which could have around 100 hits (for fetch and DML) per second. Trigger will work on Insert, Update and Delete. Wanted to know will the system performance go considerably due these triggers?
SELECTing data from the table will not be affected by the presence of a trigger.
INSERTing, UPDATEing, and DELETEing will. Triggers should always be coded to operate very fast. If you did that, then the overhead of 100 executions per second will probably be within bounds (unless your server is already at the threshold of being over capacity). However, when you code a slow trigger you can bring a server down by as little as a single execution per minute.
The only way to find out for sure is to set up a load test on your UAT server, which hopefully is as similar to your PROD server as possible.
April 14, 2016 at 10:29 am
Triggers should always be written for best efficiency first, even if it makes them harder to understand, especially so in your situation, with such heavy usage.
To keep each trigger as short as possible, use separate triggers for DELETE, INSERT and UPDATE.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply