why no suggested indexes in estimated exec plan?

Question

why no suggested indexes in estimated exec plan?

stan

Ten Centuries

Points: 1025
More actions
January 7, 2025 at 8:39 pm
Go to Answer
#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.
frederico_fonseca

SSCoach

Points: 16172
More actions
January 8, 2025 at 12:12 am
Answer
#4511724

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.

Viewing 11 posts - 1 through 10 (of 10 total)

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

ScottPletcher SSC Guru Points: 101134 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".

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

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: 1004436 More actions · Answer 3

Shifting gears to the dark side, it may be that the query was written in such a fashion that the optimizer 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)

stan Ten Centuries Points: 1025 More actions · Answer 4

thx scott that approach gives the same results (ie no suggestion) i got clicking the "display estimated execution plan" icon upper left in ssms.

stan Ten Centuries Points: 1025 More actions · Answer 5

thx frederico. i never take those suggestions seriously until i should.

i profiled the volumes on each table and view next looking for a silver bullet.

i went to the plan next and noticed 66% in a single plan lookup alone. That's huge in a 6 minute query. from what i could tell, that lookup went into one of many nested loop join objects in the plan. seemed kind of odd so i went on to the next step after noticing a full table scan on the biggest table which has 6 million records.

i did my "divide and conquer" thing on the query. this is where thru skinnied down temp tables i reduce the cardinality of the joins. i got it from 6 minutes down to 19 seconds. 10 of those seconds are because of an avg function and its group by clause.

i turned it over in this state to the peer who asked.

anyway, i'm gathering that its not because of some property i didnt set that im not seeing suggestions.

This reply was modified 13 hours, 22 minutes ago by stan.

stan Ten Centuries Points: 1025 More actions · Answer 6

thx adi, didnt find the word trivial in the xml version of the plan.

ScottPletcher SSC Guru Points: 101134 More actions · Answer 7

stan wrote:

that lookup went into one of many nested loop join objects in the plan

(Nested) loop joins are a concern, particularly if SQL doesn't properly estimate the number of rows coming into the join.

You might try using hint(s) to force hash join(s) rather than loop join(s).