Deux instances distinctes de SQL Server exécutant un plan d'explication différent

En voici une dont j'ai besoin de l'aide des administrateurs SQL. J'ai deux instances SQL Server distinctes sur Amazon EC2. L'un est notre environnement de mise en scène, et l'autre est notre environnement de production, mais ils sont configurés exactement de la même manière (engendrés à partir de la même image).

Nous avions une database que nous avons copiée de la mise en scène dans notre environnement de production la semaine dernière. La façon dont nous copions un db en production est que nous en sauvegardons une copy sur notre site de transfert, et restaurez la sauvegarde en production. Quoi qu'il en soit, nous avons constaté qu'en production, une requête complexe particulière expirait au bout d'une heure, mais cette requête exacte dans notre environnement de transfert s'est achevée en 10 minutes.

Le plan d'explication sur les deux était presque le même, sauf dans un server, il effectuait un scan PK sur une grande table (8M lignes), et sur l'autre table il faisait une search d'index. Nous supposons que c'était la différence. Donc, un server faisait beaucoup d'E / S sur disque, et l'autre ne l'était pas.

Donc ma question est, quelles sont les raisons pour lesquelles une installation de SQL Server déciderait d'utiliser un index, alors qu'un autre l'ignore – en supposant que les mêmes versions de SQL Server, et même set de données? Encore mieux, quels sont les meilleurs moyens de savoir pourquoi SQL ignore un index?

SQL Server utilise des statistics pour déterminer le plan d'exécution de la requête.

Normalement, ils doivent être identiques sur les mêmes jeux de données, mais il y a un risque de statistics obsolètes sur l'une des machines.

Utilisez sp_updatestats pour mettre à jour les statistics sur les deux ordinateurs.

En outre, je ne suis pas familier avec Amazon EC2 , mais il peut y avoir une chance que les machines exécutant les deux instances aient un nombre différent de CPU installé (ou rendu disponible pour une utilisation par SQL Server ). Ceci est également pris en count par l'optimiseur.

Paramètre reniflant?

Un SP utilisera le plan de requête jugé le plus approprié en fonction des parameters qui lui ont été transmis lors de son exécution (et ainsi compilé) pour la première fois.

La restauration d'une database efface le cache du plan; Si le SP sur la copy de la database a été exécuté avec des parameters qui favorisent une search d'index, alors c'est ce qui sera ensuite utilisé.

Vous pouvez vérifier cela en sp_recomstack les deux et en les sp_recomstack avec des parameters identiques.

C'était notre erreur.

Après de nombreuses searchs, nous avons découvert que l'un de nos développeurs avait ajouté quelques index supplémentaires à la production db après le transfert. Il s'agissait d'un cas où les index supplémentaires ont effectivement amené l'optimiseur de requête à choisir une route less efficace dans l'environnement de production.

La suppression de ces index supplémentaires semble avoir résolu le problème de performance de la requête en question, et les deux expliquent que les plans sont maintenant les mêmes.