Estimated query plan missing indexes and spid blocked by same spid
Estimated execution query plan showed me I’m missing indexes and the missing index impact is 32.497. I created the indexes as mentioned in the estimated execution plan. First the index creation query took a long time to complete and I found out it was blocked by the same spid. Second, it is still showing me I’m missing indexes even after creating the indexes. So, the questions are: why the blocking by the same spid and why after creating the indexes I still have missing indexes problem? Thanks
Within a SPID you can have multiple threads executing. Each thread will be doing a piece of the work. The first thread is used to manage the others and it can be prevented from completing because it is waiting on other parts of the threads to complete. This can then show up as a SPID blocking itself. It is not an uncommon event to happen. You can look at sysprocesses to see all the threads associated with a SPID and see what each is waiting on which ultimately causes it to show up as blocking. As regards the missing indexes. Firstly, do not create missing indexes based upon those recommendations, there are issues whereby it can incorrectly have you create indexes that already exist. You also should evaluate the necessity of an index prior to creating it to be sure that it makes sense for your environment (there is overhead with keeping indexes up to date when data is added, updated or deleted from a table). Paul Randal has a good write up as regards a [DMV bug which may show missing indexes even when they are not]. :
A lot of factors go into the missing index you see and you should never blindly take its recommendation - know why it's recommending the index and make sure its appropriate to your query load (not necessarily just this individual query). You may have an existing index the query could use but statistics are out of date, so it thinks it's not a good fit and recommends a new one. You may have an existing index that it very similar and you could just add a column to it to satisfy your query - the missing index recommendation doesn't look at other existing indexes to see if they might be close I believe it also only shows a single missing index, though the XML plan may have more, so you could cascade through several "missing" indexes before it thinks you don't need anymore. If you want a more complete indexing picture, you can run the query - or it's entire related workload - through the DTA. However, just like the missing index in the query plan, don't blindly apply the DTA recommendations. Know what it's recommending and understand how it may or may not be appropriate for your workload.