question

Teykyon avatar image
Teykyon asked

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?

sql-servertriggermemorytriggerssql memory consumption
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered

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

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.