SQL server index recommendations different from one environment to another

Question

Post reply

SQL server index recommendations different from one environment to another

Siten0308

SSCarpal Tunnel

Points: 4404
More actions
August 28, 2020 at 7:37 pm
Go to Answer
#3784515

Hello,
happy friday, SO i have 3 servers, each runs same application, same version, same database, BUT, i use Brent ozar BlitzIndex to help me out on index recommendations, when i run it on 1 environment, it gives me recommendations from changing index, or adding a new, or removing.
When i run it on the other server, which is the same, i get slightly different recommendations, such as different indexing on different indexes to be created or removed, edited etc...
just wondering why that is?
doug.brown

SSCertifiable

Points: 5608
More actions
August 29, 2020 at 5:42 pm
Answer
#3784630

Whenever I hear, "these things are identical but giving different results," the results are telling me things are actually not identical. Either the query is different, or the data, or the indexes, or the statistics, or the versions of SQL, or compatibility modes, etc. etc. Something is different.

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

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Look inside the code and find out what it uses as grounds for its recommendations.

Then you'll be able to figure out what's different in your environments.

P.S. Professional racers never use auto gear boxes.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

I've never used Brent's BlitzIndex but... you say that everything is the "same" on your boxes... is the data identical, as well? Probably not.

And I love Sergiy's "P.S.". Great example. I'll back that up by saying that there is currently no panacea code that will handle proper recommendations for most indexes never mind all indexes. Even if there was, there are queries that just can't be fixed with an index because the query is bad to begin with.

To put it in the terms of Sergiy's "P.S.", if you want to build really good indexes and code, you're gonna have to learn how to drive. 😉

Sadly, not many people even try to do that anymore/still.

--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)

ScottPletcher SSC Guru Points: 101120 More actions · Answer 3

There was very likely different activity on the two servers. That could cause SQL's missing index recommendations to change, which would in turn likely cause BlitzIndex's recommendations to change. (Note: I am not familiar with BlitzIndex in any detail at all. I've only run it twice. I don't think it's intended as a DBA-level tool, except perhaps for an accidental DBA.)

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".