locked
Remove foreign keys for audit columns? RRS feed

  • Question

  • We have a database with hundreds of tables, each with "CreatedByLoginId" and "ModifiedByLoginId" FK columns back to the Login table.  This is all fine and well, but 500+ tables all link back to Login table every time a record is inserted or updated.

    For strictly performance reasons, what do you think of us REMOVING the FK constraints on all of our tables?  While this does mean that a GUID that is not a valid LoginId could potentially be put in a table, I'm not too worried about it because users don't have direct access to the database.

    Thoughts?  Think we'd get a big performance gain?  We're pretty sluggish currently.

    Thanks.

    Tuesday, August 25, 2015 7:41 PM

Answers

  • We have a database with hundreds of tables, each with "CreatedByLoginId" and "ModifiedByLoginId" FK columns back to the Login table.  This is all fine and well, but 500+ tables all link back to Login table every time a record is inserted or updated.

    For strictly performance reasons, what do you think of us REMOVING the FK constraints on all of our tables?  While this does mean that a GUID that is not a valid LoginId could potentially be put in a table, I'm not too worried about it because users don't have direct access to the database.

    Thoughts?  Think we'd get a big performance gain?  We're pretty sluggish currently.

    Thanks.

    You could first start by running the following:

    SELECT * 
    FROM sys.dm_db_index_usage_stats
    ORDER BY user_updates DESC

    You'll want to compare the user_updates with the user_seeks + user_scans. This will help you understand how often they updating vs being used. Since these are constraints you may also want to take system_seeks, system_scans, and system_updates into consideration.

    From the SQL documentation (Books Online): "The user_updates counter indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view. You can use this view to determine which indexes are used only lightly by your applications. You can also use the view to determine which indexes are incurring maintenance overhead. You may want to consider dropping indexes that incur maintenance overhead, but are not used for queries, or are only infrequently used for queries."

    I can't say for certain what will happen because I don't know your entire workload and if these columns or tables are ever joined but for inserts updates and deletes you would think it would speed up the environment.

    Have you looked at your waitstats (sys.dm_os_wait_stats) or sys.dm_exec_query_stats?

    Here's an example of what an FK will do. Notice the query does not look at Person.StateProvince at all; yet, the plan has the object and the clustered index is incremented as a seek. The FK constraint will not show under sys.dm_db_index_usage_stats but you can correlate the data between the tables like you see below.

    As you can see every index on Person.Address was modified to allow for the insert. The clustered index on StateProvince was also seeked which incremented the value by 1.

    To fully answer the question: FK's can at times slow performance but when fully trusted can also increase performance for certain queries. Too many indexes can harm performance for any DML statement.

    I hope that helps!!


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)




    Tuesday, August 25, 2015 8:11 PM
  • It may gain a bit performance but the big issue for your case is that you use GUID as PK in table Login and as FK in your 500+ tables. I would suggest to add an IDENTITY int or bigint column to the Login table and than use that column as FK for your 500+ tables. You would gain a big performance.

    A Fan of SSIS, SSRS and SSAS

    Tuesday, August 25, 2015 8:13 PM

All replies

  • We have a database with hundreds of tables, each with "CreatedByLoginId" and "ModifiedByLoginId" FK columns back to the Login table.  This is all fine and well, but 500+ tables all link back to Login table every time a record is inserted or updated.

    For strictly performance reasons, what do you think of us REMOVING the FK constraints on all of our tables?  While this does mean that a GUID that is not a valid LoginId could potentially be put in a table, I'm not too worried about it because users don't have direct access to the database.

    Thoughts?  Think we'd get a big performance gain?  We're pretty sluggish currently.

    Thanks.

    You could first start by running the following:

    SELECT * 
    FROM sys.dm_db_index_usage_stats
    ORDER BY user_updates DESC

    You'll want to compare the user_updates with the user_seeks + user_scans. This will help you understand how often they updating vs being used. Since these are constraints you may also want to take system_seeks, system_scans, and system_updates into consideration.

    From the SQL documentation (Books Online): "The user_updates counter indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view. You can use this view to determine which indexes are used only lightly by your applications. You can also use the view to determine which indexes are incurring maintenance overhead. You may want to consider dropping indexes that incur maintenance overhead, but are not used for queries, or are only infrequently used for queries."

    I can't say for certain what will happen because I don't know your entire workload and if these columns or tables are ever joined but for inserts updates and deletes you would think it would speed up the environment.

    Have you looked at your waitstats (sys.dm_os_wait_stats) or sys.dm_exec_query_stats?

    Here's an example of what an FK will do. Notice the query does not look at Person.StateProvince at all; yet, the plan has the object and the clustered index is incremented as a seek. The FK constraint will not show under sys.dm_db_index_usage_stats but you can correlate the data between the tables like you see below.

    As you can see every index on Person.Address was modified to allow for the insert. The clustered index on StateProvince was also seeked which incremented the value by 1.

    To fully answer the question: FK's can at times slow performance but when fully trusted can also increase performance for certain queries. Too many indexes can harm performance for any DML statement.

    I hope that helps!!


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)




    Tuesday, August 25, 2015 8:11 PM
  • It may gain a bit performance but the big issue for your case is that you use GUID as PK in table Login and as FK in your 500+ tables. I would suggest to add an IDENTITY int or bigint column to the Login table and than use that column as FK for your 500+ tables. You would gain a big performance.

    A Fan of SSIS, SSRS and SSAS

    Tuesday, August 25, 2015 8:13 PM
  • So having a FK on these tables only gives you an ability to check if the login is removed you need to delete all its  data in all the tables?

    In order to provide you with the accurate suggestion, please tell us what kind of queries you are running? Do you have indexes on FK columns?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, August 26, 2015 5:55 AM