SQL choosing to use the worst possible index

  • If they're local variables, it's what Grant said. SQL can't sniff the values and so probably has an incorrect row estimation, one where it thinks that the index it's choosing is the most efficient (clustered index is the largest index and most expensive to use)

    The solutions to lack of parameter sniffing are recompile, optimise for or to convert the variables into parameters, either by nesting a call to a procedure or using parameterised dynamic SQL.

    It's not about the complexity of the query, it's about how the cardinality estimator works. Without parameters that it can sniff it's limited to a fairly rough guess of rows affected based on the average distribution of data in the table. If it's a BETWEEN, the estimation will be 9% of the rows in the table.

    p.s. It's not stats, it's definitely not fragmentation (optimiser isn't aware of fragmentation), it's going to be down to cardinality estimation.

    p.p.s check that the column you're searching on really is the leading column of the clustered index and that someone hasn't moved/changed the clustered index without you being aware.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ricardo_chicas (3/16/2015)


    Yes, those are local variables

    The option(recompile) or the optimIze hint work, but, MS SQL shouldn't be doing this, I have a clustered index for a reason, I need to trust that it will work as expected not with this random behavior,

    I left an update stats of all the tables running since rebuilding all the indexes at that table didn't work, I will check the effects in a couple hour.

    Sorry I can't share and exec plan or the query, I am bound by contract not to release the smallest piece of code, but the example I provided is very close to the actual thing, the only thing I didn't is that those variables are varchar(23).

    Since it seems that I can't trust the optimizer anymore even in a very simple query as this, is there a way to look for this behavior by using the dmv tables?

    Just so we're clear, if those are local variables, then everything the optimizer is doing is 100% correct. That is how it works. It's not acting in a crazy fashion. Local variables can only be sniffed in a recompile situation. That's why I suggest making them into parameters. All of these behaviors come down to the statistics and how the optimizer works with them. As Gail says, it's the cardinality that it can define that drives these decisions. You can trust the optimizer to behave in exactly this fashion when working with local variables.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thanks

    What is really annoying is that I even trimmed the query as to return the same column where I have the clustered index on..

    I know that the solutions provided will work in this case, but, how many others do I have? Is there a way to know that? I handle more than 1500,large databases, with tons of code, is there an easy way to look for them?

    This is a very huge bug

  • ricardo_chicas (3/16/2015)


    Thanks

    What is really annoying is that I even trimmed the query as to return the same column where I have the clustered index on..

    I know that the solutions provided will work in this case, but, how many others do I have? Is there a way to know that? I handle more than 1500,large databases, with tons of code, is there an easy way to look for them?

    This is a very huge bug

    It's not a bug. It's how local variables work. They aren't sniffed, except during a recompile, so average values are used instead of specific values. Because of this behavior, some people actually use local variables as a way to "fix" bad parameter sniffing.

    You can search your queries to see where local variables are in use. But, the problem you're hitting is that in some cases an average value will work well. In other cases, an average value won't work well. Knowing which, when, is down to the data and the query. There's no automatic way to look for that.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thanks again

    I will see what I can find with the stored metrics.

    But I still consider this a bug, we create indexes, statistics, keep maintenance's plans and the optimizer just don't work as expected, and not only that, it will choose to do the worst possible thing. And that happens at both scenarios, of the variables came from a parameter then it is not a bug it is bad parameter sniffing, bit if it a local variable then it is something else. When any software behaves in away that prevent proper results under the same circumstances then it is a bug. And it is worst of that is a known behavior and nothing is being done as to correct it.

    If things are done I this way, then why do we even keep statistics?

    Don't get me wrong, I appreciate all your inputs bit I am really mad at MS right now

  • ricardo_chicas (3/16/2015)


    Thanks

    What is really annoying is that I even trimmed the query as to return the same column where I have the clustered index on..

    I know that the solutions provided will work in this case, but, how many others do I have? Is there a way to know that? I handle more than 1500,large databases, with tons of code, is there an easy way to look for them?

    This is a very huge bug

    There is a DMV called sys.dm_db_index_usage_stats that will give you stats on what indexes are being used and how. It will show number of seeks, scans and lookups. This may help identify tables that are getting a lot of scans. Getting to the queries doing the scans is more difficult but this may at least give you an idea if there is a issue. Scans are not always bad and what you are seeing is this not considered a bug by most performance folks. Depending on what you a query is doing SQL can decide a scan is more efficient than seeks & lookups. If SQL did everything perfectly the DBA's role would be pretty boring.

  • It's not a bug.

    A bug is a behaviour that is contrary to the way the software was designed to work. What you're seeing is a fully expected (and documented) behaviour of the cardinality estimator. The optimiser may not be working as *you* expect, that doesn't mean it's a bug.

    When a query has parameters there is a possibility for bad parameter sniffing when the values that the optimiser sniffs to generate the execution plan are not typical. The plan is cached and hence the parameters used on the first call result in a plan that is used for multiple queries. If the values used on the first call weren't typical it can result in a plan that is not optimal for the majority of uses.

    Local variables are completely the opposite. The optimiser can't sniff them so it has no idea what the actual values are, so the plan generated is for an average case. This will be good in some circumstances and bad in others.

    The solutions have already been posted. Recompile, optimise for (value or unknown) or parameters. Test this scenario and see which works better. No one will work everywhere (if it did, we'd be giving you one solution not multiple options)

    I know you can't post actual code, but can you mock up a repro with generic tables that has the same behaviour? If so it may help us narrow down the exact cause and a good solution for this particular case.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I have the same concerns. SQL should be better at using the clustered index when it applies ... we went to the trouble to get the best clustered index key, and now SQL won't use it when it should, very frustrating!

    Be sure that you don't have just sampled stats on the clustered index, rather than full stats. You can use DBCC SHOW_STATISTICS to verify that.

    If I let the query run forever since it is scanning the whole table

    Hmm, isn't it scanning the nonclustered index rather than the whole table (clustered 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".

  • If I let the query run forever since it is scanning the whole table

    Hmm, isn't it scanning the nonclustered index rather than the whole table (clustered index)?

    Yeah just the nonclustered, but due to the side of it the effect is very similar... that was out of frustration,

    I gave up, so I am now looking for all the similar cases across my many databases, some can argue this is not a bug, but then why it is not behaving as expected?....

    thanks to all

  • ricardo_chicas (3/16/2015)


    If I let the query run forever since it is scanning the whole table

    Hmm, isn't it scanning the nonclustered index rather than the whole table (clustered index)?

    Yeah just the nonclustered, but due to the side of it the effect is very similar... that was out of frustration,

    I gave up, so I am now looking for all the similar cases across my many databases, some can argue this is not a bug, but then why it is not behaving as expected?....

    thanks to all

    Again, be sure to verify that the stats on your clustered index are based on a fullscan and not just a sampling.

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

  • yes, those are based on a full scan

Viewing 11 posts - 16 through 25 (of 25 total)

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