why no suggested indexes in estimated exec plan?

Question

why no suggested indexes in estimated exec plan?

stan

Ten Centuries

Points: 1021
More actions
January 7, 2025 at 8:39 pm

#4511646

hi im looking at a query for a friend that has to be lacking in indexes. I'm in ssms.
i ran an estimated exec plan and dont see the familiar (from another life) green suggestion for at least one index. i generated exec plan xml and dont see missing indexes.
is there a property in either ssms or the server db itself that allows such suggestions to show in ssms? im going to start looking at costs in the plan but sure would have liked to see the poor man's suggestions as well.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101131 More actions · Answer 1

If you select the query code, right-click on it, and then select "Display Estimated Execution Plan", you should see any recommended index

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

frederico_fonseca SSCoach Points: 16171 More actions · Answer 2

many times there is no missing index even if you think it should have - the engine determines that and may decide a existing index is good enough to be used, or that a table/clustered index scan is better/faster.

and "missing indexes" should NEVER be taken as a absolute need - its more a pointer that something may need to be changed to improve the performance - but I've seen plenty of times indexes being added based on such suggestions only to have NO impact on the performance of the query - and with the added negative impact on inserts/deletes (and potentially updates) of all other operations.

when looking at an explain plan you should be looking more for high volumes of data from table(s), sort steps, tempdb spool, filtering done only after data being retrieved from multiple joins, and a few other bits - and address those issues - the fix could be a filtering index, could be moving part of the query to a individual query into a temp table and so on.

Adi Cohn SSC-Addicted Points: 425 More actions · Answer 3

If the query plan is a trivial plan, then no optimization is being done and SQL Server will not suggest any new index. You can check in the plan's properties if it is a trivial plan.

Adi

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 4

Shifting gears to the dark side, it may be that the query was written in such a fashion that the optimize just can't figure out if an index would help or not. In such cases, I'll break the query up for testing purposes to let SQL Server have a better chance of helping me.

Sometimes, I leave it split up because "Set Based" does NOT mean "All in one query" and a little "Divide'n'Conquer" can make a really big difference. Of course, you need to test such mods and make sure that you're coming up with the same result set(s).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)