Post reply

foreign key and sys.dm_db_index_usage_stats

  • June 29, 2011 at 8:49 am

    #242882

    I have a question about a foreign key and how it relates to index usage stats. We are trying to clean up some old tables and I noticed a table that is marked as old has its usage stats being updated. This table has a foreign key back to a table that is hit constantly. The old table has the foreign key defined as:

    ALTER TABLE [dbo].[TABLE_OLD] WITH CHECK ADD FOREIGN KEY([TocID])

    REFERENCES [dbo].[TOC] ([TocId])

    ON DELETE CASCADE

    If the TOC table is being pounded on constantly, would the delete cascade cause the usage stats in the DMV to be updated for TABLE_OLD? That is the only thing I can think of in this situation.

    The data in TABLE_OLD is definitely outdated and not used. To test the DMV update, I think I will remove the FK and see if they are updated.

    Thoughts? Ideas?

    Thanks....

  • October 10, 2012 at 9:11 am

    #1547717

    Whats the result after removing the FK?

    Also which column of sys.dm_db_index_usage_stats is effected for FK references?

    Thanks

  • October 10, 2012 at 9:17 am

    #1547721

    No idea. My post is almost a year and a half old, and references something from my last job. I left about 2 months after I posted this. I can't even go back and look at what happened.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply