Question :
I am trying to attach a trigger to the sys table sys.dm_db_missing_index_details
with the following query:
CREATE TRIGGER indexBackup
ON sys.dm_db_missing_index_details
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
EXEC PerformanceTest.[dbo].[usp_persist_missing_index_DMV_data]
END
GO
I am getting the following error:
The object
sys.dm_db_missing_index_details
does not exist or is invalid for this operation.
Is it allowed to attach a trigger to a sys view or am i missing something?
Answer :
No triggers on system DMVs, sorry.
Besides, you wouldn’t really want to do this: that DMV is updated every time a query is run with a missing index request. That could be hundreds or thousands of times per second – and performance matters there.
Instead, consider running an Agent job every 5-15 minutes to capture the data you’re looking for.