I was entrusted with a new database for a critical application that was having general performance issue and the CPU usage constantly over 70%.
It turned out that I did not have to sweat much to quickly and significantly improve the database performance and also bring down CPU % way below the threshold.
The solution? For one thing, the database was missing many much needed indexes. After making sure that it was the right thing to do, I added the indexes that would give the biggest performance boost. It instantly and greatly improved the performance. I was the hero!
Secondly, the foreign key columns were not indexed and because of their relative small size, they would not easily be captured by the the famous sys.dm_db_missing_index_details DMV.
Lack of indexes in foreign keys was not only causing minutely noticeable performance impact but also leading to lock escalations that during heavy loads cause dead locks and exacerbating the situation.
So I wrote the below query to get list of all foreign keys in the database with no index on them. Among other things, it also lets me know if the CASCADE DELETE/UPDATES options are being used as those could be more cause of concern for performance.
AS ( SELECT OBJECT_NAME(fk.referenced_object_id) pk_table , OBJECT_NAME(fk.parent_object_id)fk_table , CASEWHEN i.object_id IS NOT NULL THEN 1 ELSE 0 END does_fk_has_index , i.is_primary_key is_fk_a_pk_also , i.is_unique is_index_on_fk_unique , INNER JOIN sys.foreign_key_columnsfkc ON fkc.constraint_object_id = fk.object_id INNER JOIN sys.columnsc ON c.object_id = fk.parent_object_id ANDc.column_id =fkc.parent_column_id LEFT JOIN sys.columns c2 ON c2.object_id = fk.referenced_object_id ANDc2.column_id =fkc.referenced_column_id LEFT JOIN sys.key_constraints kc ON kc.parent_object_id = fk.referenced_object_id AND kc.type = 'PK' LEFT JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id LEFT JOIN sys.indexes i ON i.object_id = ic.object_id AND i.index_id = ic.index_id LEFT JOIN sys.dm_db_partition_statsps on ps.object_id = fk_cte.parent_object_id andps.index_id <=1 WHERE does_fk_has_index =0 -- and fk_table = 'LineItems' ORDER BY used_page_count desc
Based on one of the questions in the comments section, I thought maybe it would be helpful to add an actual example that is reproducible with minimum efforts for all of us.
EXAMPLE 1 - How having index on a FK field can speed up deletes in PK table In the AdventureWorks2014 sample database sales.SalesOrderDetail table references Sales.SpecialOfferProduct (SpecialOfferID, ProductID). But currently there is no index on the FK fields. The following DELETE produces estimated execution plan with a suggestion to create a missing index on the FK table sales.SalesDetails
DELETEsales.SpecialOfferProduct WHERE ProductID = 764 I realize that the impact/improvement % is not that great on its face value (and that's also the reasons why the missing indexes DMV can easily miss it) but on a busy system and depending on the application design, it can produce very meaningful improvements.
So here I oblige to the recommendation and add index to the FK fields
CREATE NONCLUSTERED INDEXidx_FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID ON[Sales].[SalesOrderDetail] ([ProductID],[SpecialOfferID]) And here is the execution plan for the same DELETE statement after adding the index.
And here are the statistics IO numbers
Table 'SalesOrderDetail'.Scan count 1,logical reads 1246, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'SpecialOfferProduct'.Scan count 1,logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'SalesOrderDetail'.Scan count 1,logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'SpecialOfferProduct'.Scan count 1,logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
