Execution plan for a trigger?

Hello
is there a way to view the execution plan for a trigger without
executing the trigger? I have tried to do SET SHOWPLAN_ALL ON and run
an INSERT statement that fires the trigger, but i am not getting back
the execution plan for the trigger... any idea, anyone? (and again: i
want to do this without executing the trigger)
thanks
Yoni Sion
EvoYoni Sion wrote:
> Hello
> is there a way to view the execution plan for a trigger without
> executing the trigger? I have tried to do SET SHOWPLAN_ALL ON and run
> an INSERT statement that fires the trigger, but i am not getting back
> the execution plan for the trigger... any idea, anyone? (and again: i
> want to do this without executing the trigger)
> thanks
> Yoni Sion
> Evo
You could try wrapping the call in a transaction and rolling it back at
the end. THis would require executing the SQL which should be tested in
a dev environment first to make sure it works.
You can probably do some basic tuning if you pull out the SQL from the
trigger and execute using the estimated plan option from QA. Have you
tried using the Estimated Plan option from Query Analyzer?
David G.|||Yoni,
From the Books Online on displaying the execution plan, it says: "DML
statements can have up to two children. The first child is the execution
plan for the DML statement. The second child represents a trigger, if used
in or by the statement."
That does not sound like an execution plan on the trigger, but merely
acknowledgment of the trigger. (FWIW, User defined functions do not show up
usefully in the execution plan either.)
You could make an equivalent query (not including inserted and deleted
tables, alas) and wrap it in a BEGIN TRAN and a ROLLBACK to get some sense
of what is up. What that will not reveal is how expensive the inserted and
deleted accesses are. If you do more that a query or two with the inserted
and deleted virtual tables, your performance will be better by first
selecting them into temp tables and then working with those.
Russell Fields
"Yoni Sion" <yonision@.hotmail.com> wrote in message
news:5334705d.0409021030.7de36ff1@.posting.google.com...
> Hello
> is there a way to view the execution plan for a trigger without
> executing the trigger? I have tried to do SET SHOWPLAN_ALL ON and run
> an INSERT statement that fires the trigger, but i am not getting back
> the execution plan for the trigger... any idea, anyone? (and again: i
> want to do this without executing the trigger)
> thanks
> Yoni Sion
> Evo|||Thanks for your quick response,
Yes I am aware of these options. The thing is, i just want something
ilke SET SHOWPLAN_ALL to show me a trigger's execution plan without
executing it. The reason for this is: i am writing a little utility
that shows me which stored procedures currently reside on the DB which
do not compile. I iterate all the stored procedures and execute them
with SET SHOWPLAN_ALL ON: this has no impact on the DB and is very
quick (unlike the transaction rollback option... which would be my
last resort). so its all working nice and good, but i want to include
triggers in that utility... any idea?
thanks
Yoni Sion|||Yoni,
Not that I know how to do. Except, of course, to create stored procedures
that imitate the triggers and get a showplan on them. As noted before,
though, inserted and deleted would have to be imitated. (Same problem with
UDFs.)
Russell Fields
"Yoni Sion" <yonision@.hotmail.com> wrote in message
news:5334705d.0409030539.75bbc705@.posting.google.com...
> Thanks for your quick response,
> Yes I am aware of these options. The thing is, i just want something
> ilke SET SHOWPLAN_ALL to show me a trigger's execution plan without
> executing it. The reason for this is: i am writing a little utility
> that shows me which stored procedures currently reside on the DB which
> do not compile. I iterate all the stored procedures and execute them
> with SET SHOWPLAN_ALL ON: this has no impact on the DB and is very
> quick (unlike the transaction rollback option... which would be my
> last resort). so its all working nice and good, but i want to include
> triggers in that utility... any idea?
> thanks
> Yoni Sion