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.

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!!

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.

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?