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