3

I have a database on Microsoft SQL Server 2016, with a table of about one million rows, let's call it books. This query takes more than one minute, which is not acceptable:

select * from books where publisher_id in (857413, 857317, 857316)

There is a proper FK from publisher_id to the publishers table. I display the Estimated Execution Plan, and it tells me that 100% of the cost is in the "Clustered Index Scan (clustered)" on the primary key of the books table. And what worries me, it does not recommend an index at all. By looking at the query, it seems obvious that an index will help. And in fact, when I create the index the query returns results instantly. Did something go corrupt in my database, maybe statistics? Or do you believe I should nor, in general, trust what I read in the estimated execution plan?

7

There's a limitation where it will not give you missing indexes when the query plan is trivial, perhaps that's what you are encountering.

Right click on the SELECT operator and select Properties. Check if the Optimization Level is TRIVIAL.

enter image description here

I use the estimated execution plan a great deal when performance troubleshooting. It's rare that I'll have the actual plan. Regarding missing indexes, I don't just add what the plan thinks is missing. I test the index, variations of it, etc.

  • I know, FK are not indexes: most of the times you define an index where a FK is. In fact, I mentioned it to point out that this is a very clear-cut case: my query will obviously produce a table scan without the index. What surprises me is that the estimated execution plan did not see it, and I am wondering if I did something wrong with e.g. statistics. – carlo.borreo Jan 20 '17 at 15:46
  • @carlo.borreo, might be due to the query having a trivial plan. There's a limitation where it will not give you missing indexes when the plan is trivial. – Tara Kizer Jan 20 '17 at 15:53
  • I edited my reply since I misunderstood your question. – Tara Kizer Jan 20 '17 at 15:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.