CREATE TRIGGER code running millions of times

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

CREATE TRIGGER code running millions of times

Author

Topic

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-07-07 : 16:43:29

I ran this query...

SELECT TOP 100
total_worker_time,
    total_worker_time/execution_count AS Avg_CPU_Time
        ,execution_count
        ,total_elapsed_time
        ,total_elapsed_time/execution_count as AVG_Run_Time
        ,(SELECT
              text FROM sys.dm_exec_sql_text(sql_handle)
         ) AS query_text 
FROM sys.dm_exec_query_stats 
--pick your criteria
ORDER BY total_worker_time DESC

To find the most CPU intensive queries in the database. Several of the results are the creation of triggers...


  CREATE TRIGGER tr_Students_Audit ON Students FOR INSERT, UPDATE, DELETE  AS    DECLARE @bit INT ,         @field INT ,         <snip>

And they've been run millions of times in about one week. I checked with our development team and nobody knows what is causing them to run. Has anyone come across this before and know what could be causing it?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-07 : 16:51:53

It's the trigger being executed, not created, millions of times.

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-07-07 : 16:52:45

That makes more sense :)

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-07 : 16:56:34

If it's being run that many times, then you've got that many DML operations on the Students table. If you don't need the trigger, then I'd suggest deleting it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-07-07 : 17:09:03

quote:
Originally posted by tkizer

If it's being run that many times, then you've got that many DML operations on the Students table. If you don't need the trigger, then I'd suggest deleting it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"need" is a difficult term to define. It is an audit trigger that tracks changes to the table and periodically we do query the audit table to see when and what changed. but i'm not sure it is worth the extra stress on the server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources