SQL Server ne choisit pas d'utiliser l'index bien que tout semble le suggérer

Quelque chose ne va pas ici et je ne comprends pas quoi. Il vaut la peine de mentionner, la valeur recherchée n'est pas dans la table, pour une valeur existante il n'y a pas de problème. Cependant, pourquoi la première requête nécessite-t-elle une search de key en cluster pour la key primaire qui n'est même pas utilisée dans la requête, alors que la seconde peut s'exécuter directement sur l'index. Forcer la requête à utiliser l'index WITH (INDEX (indexname)) fonctionne, mais pourquoi l'optimiseur ne choisit-il pas de l'utiliser lui-même.

La colonne PIECE_NUM n'est dans aucun autre index et n'est pas non plus la key primaire.

SET STATISTICS IO ON DECLARE @vchEventNum VARCHAR(50) SET @vchEventNum = '54235DDS28KC1F5SJQMWZ' SELECT TOP 1 fwt.WEIGHT, fwt.TEST_RESULT FROM FIN_WEIGHT_TESTS fwt WITH(NOLOCK) WHERE fwt.PIECE_NUM LIKE @vchEventNum + '%' ORDER BY fwt.DTTM_INSERT DESC SELECT TOP 1 fwt.WEIGHT, fwt.TEST_RESULT FROM FIN_WEIGHT_TESTS fwt WITH(NOLOCK) WHERE fwt.PIECE_NUM LIKE '54235DDS28KC1F5SJQMWZ' + '%' ORDER BY fwt.DTTM_INSERT DESC SET STATISTICS IO OFF

Je laisse les deux requêtes s'exécuter dans un lot:

Rapport statistique IO:

Requête 1: lectures logiques 16244910

Requête 2: lectures logiques 5

 Table 'FIN_WEIGHT_TESTS'. Scan count 1, logical reads 16244910, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'FIN_WEIGHT_TESTS'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

La table a un index non cluster sur PIECE_NUM INCLUANT les trois autres colonnes de la requête.

Voici les plans d'exécution des requêtes (avec un peu d'édition pour supprimer les noms réels): entrez la description de l'image ici

J'ai remarqué le convert_implicit, mais c'est juste dû à la conversion du paramètre varchar en colonne nvarchar. La modification du type de paramètre n'a pas modifié le comportement de la requête.

Pourquoi la requête avec le paramètre n'utilise pas l'index en remplaçant le paramètre par sa valeur?

La première requête va être analysée car vous utilisez une variable locale. L'optimiseur considère cela comme une valeur «anonyme» et ne peut donc pas utiliser les statistics pour créer un bon plan de requête.

La deuxième requête cherche parce que c'est une valeur littérale et SQL peut regarder dans ses statistics et sait beaucoup mieux combien de lignes estimées il finda avec cette valeur.

Si vous exécutez votre première requête comme suit, j'imagine que vous le verrez utiliser le meilleur plan:

 DECLARE @vchEventNum VARCHAR(50) SET @vchEventNum = '54235DDS28KC1F5SJQMWZ' SELECT TOP 1 fwt.WEIGHT, fwt.TEST_RESULT FROM FIN_WEIGHT_TESTS fwt WITH(NOLOCK) WHERE fwt.PIECE_NUM LIKE @vchEventNum + '%' ORDER BY fwt.DTTM_INSERT DESC OPTION(RECOMPILE)

Je suggère d'utiliser une procédure paramétrée pour exécuter ce code afin de s'assurer qu'il utilise un plan mis en cache. L'utilisation de l'indicateur RECOMPILE a ses propres inconvénients car l'optimiseur devra rebuild le plan chaque fois qu'il s'exécute. Donc, si vous utilisez ce code très souvent, j'éviterais cet indice.

Vous pouvez lire sur les variables locales ici: https://www.brentozar.com/archive/2014/06/tuning-stored-procedures-local-variables-problems/

Je pense que la cause de ce qui est arrivé est à la fois l'utilisation de @variable et ORDER BY dans votre requête. Pour tester ma supposition, supprimez l' order by de votre requête et cela peut conduire à des plans égaux dans les deux cas (cette fois-ci avec un nombre de lignes estimé différent dans select).

Comme mentionné dans la réponse précédente, les variables locales ne peuvent pas être ignorées au moment de la compilation car le lot est considéré comme un tout, et seule l'option recomstack permet au server de connaître la valeur d'une variable au moment de la compilation. atsortingbué. Cela conduit à "estimer pour inconnue" dans le premier cas, c'est-à-dire que les statistics ne peuvent pas être utilisées car nous ne connaissons pas la valeur dans le filter, plus de lignes dans la sortie sont estimées.

Mais la requête a top + order by dedans. Cela signifie que si nous attendons beaucoup de lignes, pour n'en get qu'une seule mais la première ordonnée par DTTM_INSERT DESC nous devons sort toutes les lignes filtrées. En fait, si vous regardez le deuxième plan, vous verrez que l'opérateur SORT coûte le plus cher. Mais lorsque vous utilisez la constante, SQL Server utilise les statistics et découvre qu'il n'y aura qu'une seule ligne returnnée, ce qui peut permettre de sortinger le résultat.

En cas de nombreuses lignes attendues, il décide d'utiliser l'index déjà commandé par DTTM_INSERT . C'est seulement ma conjecture parce que vous n'avez pas posté ici les scripts de création pour vos index mais à partir du plan, je vois que le premier plan va sûrement à l'index clusterisé pour récupérer les champs manquants dans l'index non cluster, cela signifie que ce n'est pas le Même non clusterisé qui est utilisé dans le second cas, mais je suis sûr que l'index choisi dans le premier cas a la THE LEADING KEY COLUMN DTTM_INSERT . Ce faisant, le server élimine le sort que nous voyons dans le deuxième plan