Home Forums SQL Server 2005 SQL Server 2005 Performance Tuning Costly update trigger -70,000,000 logical reads for 30,000 rows updated! RE: Costly update trigger -70,000,000 logical reads for 30,000 rows updated!

  • February 2, 2009 at 10:48 am

    #936581

    GilaMonster (2/2/2009)

    Marios Philippopoulos (2/2/2009)

    Wow, would the trigger feed on itself that way? I hadn't thought of that...

    That's only possible if recursive triggers are enabled. It's a database-level option and it's disabled by default.

    I ran the following on the db in which the trigger resides and confirmed that recursive triggers is indeed turned off at the database level

    EXEC sp_dboption 'myDB', 'recursive triggers'

    However, the nested triggers setting is turned on at the server instance level. What is the difference between the 2 settings, apart from the scope in which they operate?

    EXEC sp_configure 'nested triggers'

    Returns:

    config_value: 1

    run_value: 1

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]