Trigger Resource Consumption
I made a trigger that activates every time an insert, update or delete has been made on a certain table. I have already implemented it into the tables in one of our servers.
I now need to know if that many triggers will have an impact in my server's memory Is it possible to capture how much memory is a trigger consuming?
Best Answer
A trigger is just a piece of T-SQL code, like a stored procedure, that just happens to be automatically fired in the defined scenario. So this means you can capture the execution plan which will give you information about the memory grants needed by each of the operators.
For queries "in flight" you can also use the DMV sys.dm_exec_query_memory_grants
