Hi Friends, this is my 3rd blog post of series Query Optimization. In last blog post Questions and Answers per Month, we took optimization decision by analyzing warnings suggested by Execution Plan operators.
In this post again, we are going to optimize another query from data.stackexchange.com. Below is the query to optimize-
Query execution took 12 minutes to execute with DOP = 3 (as limited by Instance DOP settings) and max server memory = 4 GB. Below are the stats from Statistics Parser:-
Below is the Actual Execution Plan for query:-
We can see that are 3 tables involved in query, but we only see Clustered Indexes appearing in Execution Plan. This query is a perfect scenario suggesting for Foreign key relationships along with non-clustered indexes on tables. Oh yes, SQL Server does not create index on foreign keys as this is most common myth. A foreign key is a constraint, a relationship between two tables – that has nothing to do with an index. But many times it makes a lot of sense to index all the columns that are part of any foreign key relationship, because through a FK-relationship, one will often need to lookup a relating table and extract certain rows based on a single value or a range of values. So it makes good sense to index any columns involved in a FK, but a FK is not an index in itself. Check this Stackoverflow question. Also, check out Kimberly Tripp’s excellent article “When did SQL Server stop putting indexes on Foreign Key columns?”.
In my case, I downloaded [StackOverflow] database from Brent Ozar’s Torrent file. The downloaded copy has only Clustered Indexes on tables. So at first, let’s create Foreign keys on involved tables.
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.