Query Optimization 03 – Users statistics (individual user) [StackOverflow]

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:-
stats-01
Below is the Actual Execution Plan for query:-
Execution Plan for Users statistics (individual user)
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.

Below code can be used to check the foreign key references of dbo.Users and dbo.Posts tables.
foreign-keys using sp_fkeys

After adding foreign keys, let’s add appropriate indexes supporting these FK columns.

Find Indexes of tables using sp_helpindex

That’s it. After creating foreign keys, and putting appropriate indexes on them, our query executed within 100 milli seconds. Below are the stats and new Actual Execution Plan for the same query.

Statistics for Query After Adding Foreign Keys & Indexes

New Execution Plan for Query

Conclusion: A good database design in terms of foreign keys for implementing relationship, putting indexes on those keys may add significant performance improvement in the system. Happy Coding 🙂

Please feel free to correct me by comments. Mention your approach if it differs from mine. If you like the article, do Like & Share.

Contributor: personalloandial.com

Leave a Reply