Je n'ai pas résolu cette question en utilisant la procédure stockée, mais nous avons décidé de surpasser le SP et d'exécuter simplement la plaine ol 'SQL em> forte> forte> S'il vous plaît voir le schéma de table étendu ci-dessous strong> J'ai deux indices sur une table: p> et j'ai la pièce de code suivante: p> ceci exécute simplement comme il devrait le faire; Il pointe vers l'index correct et tout ce qu'il fait est de numériser l'index. p> Maintenant, je crée une procédure stockée: p> exécuter la chose: p>
Edit 2: Mise à jour de l'index (pour ne pas utiliser actiegroep plus longtemps) em>
nb. SQL Server 2005 Enterprise 9.00.4035.00 EM>
nb2. Semble lié à http://www.sqlservercenter.com/forums/topic781451-338- 1.aspx em> p>
@foreignId bigint,
@maand int, --month
@jaar int --year
10 Réponses :
Le message d'erreur que vous avez reçu peut être généré lorsqu'il y a des indemnités de requête en conflit dans votre requête.
Pouvez-vous exécuter la requête, y compris l'indice, en dehors de la procédure stockée?
Un train de pensée alternatif, avez-vous été testé / exécuté votre procédure stockée avec une valeur de paramètre différente? Il est possible que la valeur de paramètre utilisé pour créer le plan d'exécution d'origine n'est pas un candidat approprié pour toutes les activités. Vous voudrez peut-être envisager de recompiler votre procédure stockée pour voir si un plan d'exécution différent est produit entre différentes exécutions avec différents paramètres.
Si vous souhaitez vous assurer qu'un nouveau plan de requête est calculé pour chaque exécution de votre procédure stockée, vous pouvez utiliser la clause de recompilation. Cela devrait être l'exception et non la norme. Validez le comportement de votre procédure et sa génération de plan via des tests.
Oui, je peux réellement exécuter la requête, avec l'indice, en dehors de la procédure stockée. Et même avec 'avec recompilation', je ne peux pas obtenir le sugineux de travailler. Même erreur.
Recompiler ne traitera pas de la question avec votre indice de requête. Recompilation d'une procédure stockée, à chaque exécution, garantira qu'un nouveau plan d'exécution est généré à l'écoute des paramètres spécifiques fournis pour l'exécution donnée.
Cela pourrait être un paramètre renifler, alors essayez quelque chose comme ceci: En savoir plus Paramètre anout reniflant:
http://omnibuzz-sql.blogspot.com/ 2006/11 / paramètre-renifler-stores-profonces.html
Les paramètres reniflant peuvent être effectivement éteints par recompilation à la fin de la SQL appelant.
Quel type de données est étranger dans la table? Si c'est INT, vous obtenez probablement une conversion implicite qui empêche l'Index cherche. Si le type de données dans la table est INT, redéfinissez le paramètre pour être également et que vous devriez obtenir une recherche d'index (pas une analyse d'index) pour cette requête.
Grande idée Gail, je n'avais pas pensé à cela. :-)
C'est la seule chose que je puisse penser que SQL Scannerait un index lorsque l'indice revient et avec un prédicat d'égalité directement sur la clé d'index. Ne devriez pas être le paramètre reniflant, le plan Exec pour une requête aussi simple est la même quel que soit le nombre de lignes, car il n'y a pas besoin de recherches sur les signets.
Oui, ça ne semblait pas comme le renifler par le paramètre non plus.
étranger est un Bigint, j'ai vu cela dans une version antérieure de la procédure et l'a modifié à l'époque.
Vous savez, le schéma complet est un enfer de beaucoup plus complexe que vous l'avez initialement décrit ...
Essayez ceci et nous dit le résultat:
DBCC FLUSHPROCINDB: Utilisé pour effacer le cache de procédure stocké pour une base de données spécifique sur un serveur SQL, et non l'ensemble du serveur SQL. Le numéro d'identification de la base de données à affecter doit être entré dans le cadre de la commande.
Vous pouvez utiliser cette commande avant de tester pour que les plans de procédure stockées précédents n'affectent pas négativement les résultats des tests.
Exemple:
Déclarer @Indbid Entere Set @Indbid = (Sélectionnez dbid à partir de maître.dbo.sysysdatabases où nom = 'Nom_sube_Name') DBCC FLUSHPROCINDB (@Indbid)
N'aide pas, je pense que recompiler ferait la même chose.
Lorsque vous passez dans le paramètre, combien de lignes de la table correspondent à la jointure par rapport au nombre total de lignes de la table? SQL Server choisit un index en utilisant entre autres, un ratio de lignes correspondantes renvoyées par la jointure au nombre total de lignes de la table. S'il y a un grand nombre de lignes renvoyées par rapport au nombre total dans le tableau, l'index peut être ignoré sous forme d'index de préférences SQL Server lorsque le nombre de lignes correspondantes est inférieur au total.
Donc, si votre sélection SELECT et votre procédure stockée a utilisé différentes valeurs pour @FID, vous pouvez parfois utiliser l'index et d'autres fois non. Si cela ressemble à votre problème, jetez un coup d'œil au "ratio de sélectivité" dans Google.
bonne chance!
Eh bien à la fois le non-SP et le SP utilisent les mêmes valeurs et j'ai ajouté une optimisation pour l'indice dans la procédure stockée avec une indice supplémentaire à utiliser ceetrayide.
Et le nombre de lignes retournées est d'environ 10 500 000
Le problème persisté-non utilisé-utilisé ci-dessous ne se produit qu'avec actiegroep / actieid sur mon système (SQL 2008). Mais il est possible que le même problème puisse se produire sur votre système SQL 2005 avec les colonnes Dagnumger / Datum. Si même cela se produit, cela expliquerait le comportement que vous voyez, car une analyse d'index en cluster serait nécessaire pour filtrer les valeurs de Datum. Pour diagnostiquer si cela se produit effectivement, ajoutez simplement la colonne Datum comme une colonne Inclure-D à votre index, comme celui-ci: si le problème disparaît avec cette révision de l'index , alors vous savez que Dagnummer est le problème, vous pouvez probablement même supprimer Dagnummer de l'index puisque SQL ne l'utilise pas de toute façon. En outre, la révocation de votre index pour ajouter ACTIEID est une bonne idée puisque elle échappe à la question indiquée ci-dessous. Mais dans le processus, vous devez également laisser la colonne AANTAL dans l'index, de sorte que votre index soit un Index de couverture pour cette requête. Sinon, SQL devra lire votre index en cluster pour obtenir la valeur de cette colonne. Cela ralentira votre requête depuis que les recherches dans l'indice en cluster sont assez lentes. Voici une bande d'idées qui peut vous aider à résoudre ce problème, avec les choses les plus probables / les plus faciles en premier: < ul>
Lorsque j'ai essayé de reproduire votre utilisation de schéma et de requêtes (avec de fausses données générées), je vois que votre colonne calculée de colonne ACTIEGROEP est répandue au moment de l'exécution au lieu de la valeur persistante utilisée. Cela ressemble à un bogue dans l'optimiseur SQL Server. Étant donné que la valeur de colonne sous-jacente ACTIEGROEP n'est pas présente dans votre indice de couverture Le type de données de la colonne calculée ACTIEGROEP est une chaîne mais vous comparez-la aux entiers (par exemple dans la (1,2,3)) dans votre clause et des déclarations de cas. Si SQL décide de convertir la colonne au lieu de la constante, celle-ci fera mal à la requête perf et peut rendre le problème de l'expansion de colonne calculé (décrit ci-dessus) plus probable. Je suggère fortement de changer votre définition de colonne calculée à un type intégré, par exemple.
Vous faites un groupe par une colonne qui n'a qu'une seule valeur possible. Par conséquent, le groupe par est inutile. Espérons que l'optimiseur serait assez intelligente pour le savoir, mais vous ne pouvez jamais être sûr.
Essayez d'utiliser une optimisation pour l'indice plutôt que de forcer directement les index, cela peut contourner l'erreur que vous obtenez avec votre indice
Craig Freedman's Post http://blogs.msdn.com/craigfr/archive/2009/04/28/Implied-predicates-and-query-hintS.aspx décrit des causes courantes du message d'erreur associé à un indice que vous 'revenir lorsque Recompiler est utilisé. Vous voudrez peut-être examiner cet article et vous assurer d'exécuter les dernières mises à jour de SQL Server.
Je suis sûr que vous avez déjà fait cela, mais vous voudrez peut-être construire une version "chambre propre" de vos données, en faisant ce que nous faisons: créer une nouvelle dB, utilisez le DDL dans Votre question pour créer les tables, puis remplir les tables avec des données. Si les résultats que vous obtenez sont différents, regarde Closley au schéma dans votre vraie table et vos index et voyez s'ils sont différents.
Si rien de cela ne fonctionne, et je peux suggérer des idées plus sauvages. : -) En outre, veuillez ajouter la version exacte et le niveau de mise à jour de SQL Server à votre question! ix_stat_statistieken_1 Index (seule la colonne calculée est là), si SQL Server décide de récupérer cette colonne supplémentaire, SQL peut envisager un index en cluster. Pour être moins cher que d'utiliser votre index non clusterné, puis recherchez ACTIEID pour chaque ligne correspondante de l'indice de cluster. En effet, les recherches d'index en cluster sont très coûteuses par rapport aux E / S séquentielles, de sorte que tout plan nécessite plus de quelques pour cent des lignes à rechercher est probablement moins cher à faire avec une analyse. En tout état de cause, s'il s'agit bien du problème que vous voyez, ajoutez ACTIEGROEP comme une colonne Include-D de votre ix_stat_statistieken_1 doit contourner le problème. Comme ceci:
crée index non clustered [ix_stat_statistieken_1] sur [DBO]. [Stat_statistieken]
(
[étranger] Desc,
[secondaire] ASC,
[ACTIEGROEP] ASC,
[Dagnummer] Desc,
[Aantatal] ASC
) Inclure (actialier) sur [primaire]
cas lorsque ACTIEID comprise entre 0 et 9 puis ACTIEID
Quand Actieid entre 10 et 99, ACTIEID / 10
Quand Actieid entre 100 et 999, ACTIEID / 100
Quand Actieid entre 1000 et 9999, ACTIEID / 1000
Quand Actieid entre 10000 et 99999, ACTIEID / 10000
Quand Actieid entre 100000 et 999999, ACTIEID / 100000
Quand Actieid entre 1000000 et 9999999, ACTIEID / 1000000
Sinon acetieid / 10000000 fin
D'accord. J'ai supprimé les déclarations Actitiegroep des requêtes, en utilisant uniquement Actieid, qui est un véritable domaine Int. J'ai supprimé le groupe, qui était en effet inutile, a ajouté optimiser les notes pour le stress étranger et la journée. Aucune aide. SQL Server version est SQL Server 2005 Enterprise 9.00.4035.00
Merci pour l'information supplémentaire. J'ai édité ma réponse pour proposer une autre solution possible qui correspond exactement au comportement que vous voyez. Voir le haut de la réponse ci-dessus. Bonne chance résolvant cela ... c'est un problème intéressant!
D'accord. Je ne pouvais pas résoudre ce problème, pourtant vous accorder la prime de venir avec les sources les plus viables :-) J'ai fini par dépasser la procédure stockée et exécuter le SQL moi-même.
Merci! Celui-ci était vraiment un tremper. Je suis toujours curieux de savoir ce qui se passait réellement. Avez-vous eu la chance d'essayer d'inclure la colonne Datum de votre index? Ok sinon, je me demandais juste. Quoi qu'il en soit, content que vous ayez pu trouver une solution de contournement.
select AU.*
FROM SYS.Allocation_units AS AU
INNER JOIN SYS.Partitions AS P
ON AU.Container_id = P.Partition_id
WHERE Object_ID = object_id('STAT_Statistieken')
Try this and check if the NON CLUSTERED Index Has more pages than the CLUSTERED INDEX (THIS WOULD MEAN THAT IS CHEAPER TO READ THE CLUSTERED INDEX)
Une recherche sur le non-clustere battrait toujours une analyse du regroupement.
HMMM NOPE, l'index non clustered Ayez la clé d'index en clustere sur son niveau de feuille, ce qui signifie que si vous sélectionnez avec un index qui ne couvre pas l'index de sélection (appelé Index de la couverture), le SELECT ira pour l'index non en cluster et après. Cela cherchera d'autres champs dans l'indice en cluster. (Votre index ne couvre pas votre sélection car le champ Actiebroep n'est pas inclus dans l'index)
Je ne reçois aucun résultat de la requête ci-dessus (sur le maître je suppose?); Juste pas de lignes que ce soit.
La table n'existe pas sur Master, utilisez la requête sur votre base de données.
Premièrement, je devrais dire que les index que vous avez créés ne sont pas optimaux, car ils ne peuvent être utilisés que pour filtrer sur donc votre condition sur maintenant, à votre indice actuel. Je viens de créer vos tables et de les remplir avec Les données aléatoires utilisant ce script: , et il utilise Je vous recommanderais de créer un index supplémentaire avec Si vous Vous souhaitez toujours utiliser votre index actuel, pourriez-vous exécuter ces commandes: chaque commande produira trois résultatssets. Pourriez-vous s'il vous plaît poster les résultats ( code> 1 et étranger . SQL Server n'est pas Capable de faire Skip Scan et que vous avez un secondaireID dans votre index qui n'est pas filtré avec une condition de plage. étranger, actiegroep, dagnummer ne donne pas de nombre limité de gammes et n'est pas complètement sargable. Il ne peut filtrer que sur étranger , pas sur l'ensemble de l'ensemble. indexer la recherche quoi que ce soit, lequel signifie probablement que le problème est avec vos données Distribution. secondaireID supprimé, comme celui-ci: 2 à partir de chaque commande, et trois lignes de Resultins 3 avec la valeur de range_hi juste ci-dessous, juste en dessous et égal à 873926 ?
L'élément Microsoft Connect sur le Skip Scan: connect.microsoft .Com / SQLServer / Feedback / Détails / 695044 / ...
J'ai vu un comportement similaire avant, où il fait l'allusion d'index et faire quelque chose de pire avec elle (index non filtrée analyse avec recherche de signet).
L'un de ces quatre devrait aider:
1) Append; -T4102; -T4118 aux paramètres de démarrage SQL Server 2005 (peut appliquer à SQL Server 2008). Note: Cela ramène la mauvaise gestion SQL 2000 IN et NOT IN requêtes dans SQL 2005 .
2) STATISTIQUES DE MISE À JOUR [DBO]. [STAT_Statistieken] AVEC FULLSCAN
3) OPTION (MAXDOP 1) - parfois le parallélisme provoque des requêtes vraiment stupides à générer
4) Assurez-vous de l'indice est en ligne.
Notez également que si vous créez un index sur une table créée dans une procédure stockée, cet indice n'existe pas lors de la compilation des requêtes de procédure stockée de sorte qu'il ne sera pas utilisé. Étant donné que votre table est créée de manière globale dbo Je suppose que c'est pas le cas ici.
EDIT: parfois je souhaite qu'il y ait un vrai forceplan où vous pourriez clé dans le plan directement et tout éventuel plan sera exécuté. Sorte d'un semblable langage d'assemblage pour la DB
Mise à jour des statistiques, a ajouté l'option à la procédure stockée, mais toujours une balayage de la table complète; L'indice est en ligne. À l'extérieur SP, la requête est bien performée (et bien plus vite).
Il semble que votre avec l'index ne correspond pas au nom de l'index. Je commence à penser que ce n'était pas une erreur de pâte de copie dans Stackoverflow.
Nah, a changé l'index et le nom pour résoudre un autre problème que nous avions. Pourtant, cela ne change rien. J'ai copié tout le contenu de la procédure stockée dans une nouvelle fenêtre et je peux exécuter avec l'utilisation de l'index et avoir exactement la même information.
Essayez de créer votre index comme ceci: et recréer votre procédure ensuite
J'ai changé mon indice et la procédure stockée pour ne plus utiliser l'actiegroep, mais le problème reste toujours.
Est-ce que le point de la SPROC pour déterminer si un lien existe sur cette autre table, de Stat_Statistieken, ou combien de ces liens existe?
Non, ceci est juste une version abstraite du problème. Je veux juste faire un choix sur la table avec l'utilisation de l'index. La table contient uniquement un tas de grossications
@Jan: Quelle est la période d'exécution de votre requête lors de l'utilisation du clustered et de l'index régulier?
Clustered: 28 sec. Non regroupé: <1 sec.
@Jan: Probablement un point discutable, mais avez-vous mis à jour vos statistiques, défragmenté vos index (plus particulièrement vos indices en cluster), ...?
Si je imite le schéma avec les informations limitées, je n'ai aucun problème que cela cherche comme prévu. Les problèmes concernant la requête cache / plan de requête Hit / Paramètre Sniffing sont déjà affichés, donc si cela ne la résoudra pas, pouvez-vous fournir une version et un schéma moins abstraites, vous pourrez peut-être réduire les informations nécessaires pour l'aider à l'abstraction.
Ajouté le schéma complet dans le poteau.
Salut Jan - Toute nouvelle sur cette question? Je pense que j'ai pu comprendre ce qui causait le problème (un bogue d'optimiseur SQL Server avec des colonnes calculées persistantes). Voir ma réponse ci-dessous. Laissez-moi savoir si cela fonctionne!
Sont
-1 ,1356 et8455 les valeurs les plus proches de873924 ?Et pourriez-vous poster les plans
xml générés par la requête et par la procédure stockée? Il suffit d'exécuterSET WORSPLAN_XML ON WO Sélectionnez ... etSET WOWPLAN_XML ON EXECUTE _SP_TEMP ...Bonjour Jan - merci pour les informations supplémentaires ... très utile. Je viens de réviser ma réponse avec une suggestion supplémentaire pour ce qui pourrait causer cela et comment le résoudre. Voir ci-dessous. De plus, la suggestion de Quassnoi à poster un plan XML est une bonne. Je vais certainement nous aider à mieux diagnostiquer votre problème.