Here are three four options:
Option 1 - consider how long the jobs that populate the summary tables take to run. If that time is not prohibitively long, then consider running the jobs nightly regardless of whether there's been a change or not. That actual frequency of changes, especially in the biggest tables, would influence your decision here.
Option 2 - @SQLWorldWide suggested an alternative from a Pinal Dave article; using the sys.dm_db_index_usage_stats view to detect the last time the table was changed. (Clustered indexes have to change whenever any column changes; for tables without a clustered index, a "dummy" index (index_id = 0) will be shown, again showing the time when anything was inserted, updated, or deleted in the table). However, this has one flaw - these views are reset each time the server is restarted. So, if the server was restarted five minutes before your job ran, you might not update any of the summary tables.
(See "UPDATE" below, for an even better alternative):
Again, depending on how long it takes to refresh all the summary tables, and how often your server is restarted, there is an alternative:
SELECT MAX(last_user_update) as last_change
FROM (SELECT last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('<dbname>')
AND OBJECT_ID=OBJECT_ID('<tablename>')
UNION ALL
SELECT sqlserver_start_time FROM sys.dm_os_sys_info
) q
;
(Replace <dbname> and <tablename> with the appropriate values for your database and table, of course.)
This query will return the time of the most recent change to <tablename> of the last time your server was restarted, whichever came last.
Have your job store the time and date when it started running as its last step, and retrieve that last run time as its first step. If the last run time is less than last_changed, then update the summary table.
As noted, this will mean that all the summary tables will be updated after each restart; if that is a major issue, then this option wil lprobably not work for you.
Option 3 - This is your idea, tweaked slightly.
Instead of writing the table name down each time the trigger runs, I'd have UpdatedTables have two columns (more if needed, for instance for the name of the procedure that updates the summary table, or whatever you'd need to run that process when appropriate): tablename, and is_changed, a bit column set to 0 by default. Your trigger would update the bit column to one where the tablename matched and the bit column was zero; after the summary tables are updated, just reset the bit column to 0.
Note that you'd want to lock the table while updating the summary table - otherwise, a change might happen during that update, and no record would be made of it. That's a problem that would hold with your solution as originally written if you prefer that - and is the only thing that jumps out at me as a possible problem with it.
If locking the table isn't an option (and it probably isn't, if a large import/export could be going on when the job that checks for updates runs), you could reset is_changed before updating the summary table. However, in that case, you'd want to be sure to set is_changed back to one if the summary table update failed for some reason, so it would get run again next time.
With the tweaked solution, hopefully the trigger would run very quickly - and, you don't run the risk of have millions of records in UpdatedTables that all say 'Name_of_Updated_Table' - that does consume disk space, after all.
The last few paragraphs answer your first two questions. I would not turn the trigger off while a large job is updating a table (if you are using a trigger), unless the job manually updates your table noting it was changed when it finishes. If the summary table update job ran while your large import update was running, but after your first change had run the trigger, the summary job would clear the update flag for the table - and the rest of the import/update wouldn't get flagged as a change.
UPDATE:
Option 4 - Use a combination of options 2 and 3.
Credit to @SQLWorldwide again, as his comment made me think of this option.
For this option, UpdatedTables should be as described in option 3.
As in option two, record the time when you start the summary table update process each night after it's successfully completed. Record it in table LastSummaryRun, column RunDate. TRUNCATE the table and INSERT the value, so the table only has one row.
At the end of that process, clear the is_changed flags in UpdatedTables and run this:
UPDATE ut
SET is_changed = 1
FROM UpdatedTables ut
CROSS JOIN LastSummaryRun ls
WHERE EXISTS (SELECT 1
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('<dbname>')
AND OBJECT_ID=OBJECT_ID(ut.tablename)
AND last_user_update > ls.RunTime
)
;
(Again, replace <dbname> and <tablename> with the appropriate values for your database and table, of course.)
This will catch any changes that happened while your job was running, and will make sure we've primed all the values, in case there's a restart.
Execute this same statement in a job that runs every 5 minutes or so.
UpdatedTables should be correct unless:
- the server restarted, AND
- the only change that day happened in the five minutes before the restart.
If that seems like an acceptable risk, then this is probably the best of all the options. Note that the job can be run more often than every 5 minutes
