Pourquoi est-ce un Indice de Scan et pas une recherche d'Index?

Voici la requête:

SELECT      top 100 a.LocationId, b.SearchQuery, b.SearchRank
FROM        dbo.Locations a
INNER JOIN  dbo.LocationCache b ON a.LocationId = b.LocationId
WHERE       a.CountryId = 2
AND         a.Type = 7

Emplacement D'Index:

PK_Locations:

LocationId

IX_Locations_CountryId_Type:

CountryId, Type

LocationCache Index:

PK_LocationCache:

LocationId

IX_LocationCache_LocationId_Searchquery_searchrank:

LocationId, SearchQuery, SearchRank

Plan D'Exécution:

Pourquoi est-ce un Indice de Scan et pas une recherche d'Index?

Donc, c'est de faire un de recherche d'Index sur les Lieux, à l'aide de l'index de couverture, c'est cool.

Mais pourquoi il est en train de faire un Index Scan sur le LocationCache index de couverture?

Que l'index de couverture a LocationId, SearchQuery, SearchRank dans l'index (et non comme des "colonnes Incluses").

Passez la souris sur l'analyse d'index:

Pourquoi est-ce un Indice de Scan et pas une recherche d'Index?

Cette requête doit aller dans une vue indexée servi par un Serveur SQL server FTS catalogue, consommé par une saisie semi-automatique du plugin, il se doit donc d'être à 100% optimisé.

Au moment de la requête ci-dessus est à prendre 3 secondes. Il convient de < 0.

Des idées?

Probablement pas lié, mais je suis curieux, pourquoi ne pas avoir une ordonnance par tout en utilisant top 100
De l'intérêt (mais ne vise pas à être un correctif de toute sorte) modification de l' INNER JOIN à INNER LOOP JOIN accélérer ou ralentir?
A - qu'est ce qu'une JOINTURE de BOUCLE? Que fait l'instant, et se tourna de l'Analyse d'Index dans une recherche d'Index!
INNER JOIN feuilles de la cueillette de la stratégie de jointure (BOUCLE, de FUSION, de HACHAGE) jusqu'à l'optimiseur, qui sera un choix fait sur la base des statistiques sur les table(s).
Au moins à l'emplacement de la cache mais je vous recommande les deux tables. Le SGBD doit réaliser le bon nombre de lignes à faire la jointure. Je suppose parce qu'il ne pensait qu'à 1420, il fait une mauvaise décision sur le type de rejoindre il doit utiliser. Rejoignez conseils peuvent être utiles, mais elles peuvent devenir échéant, au fil du temps. Si les stats est correctement mise à jour de sql server doit toujours choisir le meilleur plan.

OriginalL'auteur RPM1984 | 2011-06-30

  1. 8

    Tout en gardant à l'esprit qu'il en résultera une requête qui peut effectuer de mal et lorsque des modifications sont apportées, à l'aide d'un INNER LOOP JOIN doit forcer l'index de couverture pour être utilisé sur dbo.LocationCache.

    SELECT      top 100 a.LocationId, b.SearchQuery, b.SearchRank
FROM        dbo.Locations a
INNER LOOP JOIN dbo.LocationCache b ON a.LocationId = b.LocationId
WHERE       a.CountryId = 2
AND         a.Type = 7
    S'avère que je ne peux pas l'utiliser de toute façon, comme il a besoin d'aller dans une vue indexée (parce que FTS catalogues besoin de ça). mais les vues indexées avec un cluster PK ne peut pas avoir de conseils. 🙁
    Un autre chaton vient de mourir :~(
    J'espère que c'était un mal chaton intention sur la domination du monde.
    darn. Je vais essayer quelques solutions de rechange plus tard vs. certains se moquaient de données pour refléter vos tables et, espérons-le, il y a une vue indexée une solution compatible.
    remercie de votre aide! +1 car je n'ai pas mentionné que j'ai exigé qu'il soit indexé, donc c'est encore viable non indexables solution.

    OriginalL'auteur Will A

  2. 31

    C'est à l'aide d'une Analyse d'Index, principalement parce qu'il est aussi à l'aide d'une Jointure de Fusion. L'opérateur Merge Join nécessite deux flux d'entrée qui sont à la fois triées dans un ordre qui est compatible avec les conditions de Jointure.

    Et c'est à l'aide de l'opérateur Merge Join à la réalisation de votre INNER JOIN parce qu'il croit que ce sera plus rapide que le plus typique de la Jointure de Boucle Imbriquée de l'opérateur. Et c'est probablement à droite (c'est souvent le cas), en utilisant les deux indices qu'il a choisi, il a les flux d'entrée qui sont à la fois pré-triés en fonction de votre condition de jointure (LocationID). Lorsque les flux d'entrée sont pré-triés comme cela, alors les Jointures de Fusion sont presque toujours plus rapide que les deux autres (en Boucle et les Jointures de Hachage).

    L'inconvénient, c'est ce que vous avez remarqué: il semble être l'analyse de la totalité de l'index, alors, comment cela peut-il être plus rapide si elle est la lecture de plusieurs records ne peut jamais être utilisé? La réponse est que les Scans (en raison de leur nature séquentielle) peut lire n'importe où de 10 à 100 fois le nombre de dossiers/seconde cherche.

    Cherche maintenant généralement gagnent parce qu'ils sont sélectifs: ils ne obtenir les lignes que vous demandez, alors que les Scans sont non-sélectifs: ils doivent retourner chaque ligne de la plage. Mais parce que les Scans ont un beaucoup hausse des taux de lecture, ils peuvent souvent battu Cherche aussi longtemps que le ratio de la Jetée Lignes à Lignes correspondantes est inférieur que le rapport de Scan lignes/sec VS. Chercher les lignes/sec.

    Questions?

    OK, j'ai été invité à expliquer la dernière phrase plus:

    Un "Jeté Ligne" est un que le le Scan lit (parce qu'il a tout lire dans l'index), mais qui sera rejetée par l'opérateur Merge Join, car il ne dispose pas d'un match sur l'autre côté, peut-être parce que la clause where condition a déjà exclu.

    "Entre les Lignes" sont ceux qu'il a lu, qui sont en réalité correspondait à quelque chose dans la Jointure de Fusion. Ce sont les mêmes lignes que celles qui auraient été lu par un Demander si l'Analyse a été remplacé par un Cache.

    Vous pouvez comprendre ce qu'il y a en regardant les statistiques dans le Plan de Requête. Voir cette énorme graisse flèche à gauche de l'Index Scan? Qui représente le nombre de lignes de l'optimiseur pense qu'il sera lu avec le Scan. La zone de statistiques de l'Analyse d'Index que vous avez posté indique le nombre de Lignes retournées est d'environ 5,4 M (5,394,402). Ce qui est égal à:

    TotalScanRows = (MatchingRows + DiscardedRows)

    (Dans mes termes, de toute façon). Pour obtenir les Lignes correspondantes, regardez les "Lignes Réelles" rapporté par l'opérateur Merge Join (vous pourriez avoir à enlever le TOP 100 pour obtenir cette précision). Une fois que vous savez cela, vous pouvez obtenir les rebuts de lignes par:

    DiscardedRows = (TotalScanRows - MatchingRows)

    Et maintenant vous pouvez calculer le ratio.

    esprit == soufflé (au mauvais sens du terme). Des Questions? yep. citation: "le ratio de déchets de Lignes pour les Lignes Correspondant est inférieur au ratio de Balayage de lignes/sec VS. Chercher les lignes/sec". <- u peut donner des précisions sur ce .. et COMMENT nous pouvons l'utiliser pour vérifier cette statistique, etc..
    +1 c'est un outil très utile de répondre. Après avoir regardé mes numéros, c'est de faire un Index Scan sur les 400 000 lignes, mais seulement de correspondance 201 dans un Jointure de Hachage. Donc je suppose a Jointure de Boucle serait beaucoup plus efficace basée sur les ratios.
    votre message semble très significative. Pouvez-vous svp me guider, comment calculer le ratio de Scan lignes/sec vs ** Chercher des lignes/sec**
    Il n'y a pas de réponse simple à cette et une réponse complète est beaucoup trop pour ces commentaires. Fondamentalement, c'est la différence entre le séquentiel IO débit (scan) de vos disques contre leurs aléatoire IOs par seconde (cherche). C'est la façon dont nous avons utilisé pour calculer dans les années 70, mais aujourd'hui, il ya tellement de nombreuses améliorations de performances qui affectent ce (RAID, des Miroirs, des caches à tous les niveaux, etc.) qu'il est difficile à mesurer et encore plus difficile à prévoir. Je crois que l'optimiseur SQL suppose un certain rapport de recherche de lignes à analyser les lignes de comme 5%-10%.
    Ce doit être la accepté de répondre.

    OriginalL'auteur RBarryYoung

  3. 4

    Avez-vous essayé de mettre à jour vos statistiques?

    UPDATE STATISTICS dbo.LocationCache

    Ici sont un couple de bonnes références sur ce qui fait et pourquoi l'optimiseur choisit un balayage sur une recherche.

    http://social.msdn.microsoft.com/Forums/en-CA/sqldatabaseengine/thread/82f49db8-0c77-4bce-b26c-1ad0a4af693b

    Résumé

    Il y a plusieurs choses à prendre en
    prise en compte ici. Tout d'abord, lorsque SQL
    qui décide de la meilleure façon (assez bon)
    plan à utiliser, il ressemble à la requête,
    et puis aussi regarde les statistiques
    qu'il stocke sur les tables
    impliqués.

    Il décide ensuite s'il est plus
    efficace pour rechercher en bas de l'index, ou
    de numériser l'ensemble de niveau de feuille de l'index
    (dans ce cas, il consiste à se toucher
    chaque page dans la table, parce que c'est
    un index cluster) Il le fait en
    à la recherche à un certain nombre de choses.
    Tout d'abord, il devine combien
    lignes/pages qu'il faut analyser. Cette
    est appelé le point de bascule, et est un
    pourcentage plus bas que vous pouvez penser.
    Voir cette grande Kimberly Tripp blog
    http://www.sqlskills.com/BLOGS/KIMBERLY/category/The-Tipping-Point.aspx

    Si vous êtes dans les limites de la
    point de bascule, il se peut que votre
    les statistiques ne sont pas à jour, ou de votre
    l'indice est fortement fragmenté.

    Il est possible de forcer SQL pour rechercher une
    index à l'aide de la requête FORCESEEK
    indice, mais s'il vous plaît utiliser avec
    attention, comme, en général, de vous les fournir
    garder le tout bien entretenu, SQL
    est assez bon à décider de ce que l'
    le plus efficace sera!!

    yep, j'ai essayé que, comme dans les commentaires ci-dessus.
    Je n'avais pas remarqué qu'il y a plus de 13 commentaires
    +1 pour le lien vers Kimberly Tripp est un excellent blog posts

    OriginalL'auteur cordsen

  4. 2

    En Bref: Vous n'avez pas de filtre sur LocationCache, l'ensemble du contenu de la table doit être retourné. Vous avez entièrement index de couverture. ANALYSE d'Index (une fois) est le moins cher de l'opération, et l'optimiseur de requête récupère.

    À optimiser:
    Vous vous joignez à l'ensemble des tables, et plus tard seulement 100 meilleurs résultats. Je ne sais pas en quoi sont-ils, mais essayez de la sous-requête [Lieux] table CountryId, Type puis rejoindre juste le résultat avec [LocationCache]. Sera waaaay plus vite si vous avez plus de 1000 lignes.
    Aussi, essayez d'ajouter un peu plus restrictive des filtres avant de les jointures si possible.

    Index Scan:
    Depuis un scan touche à chaque ligne dans la table si oui ou non il est admissible, le coût est proportionnel au nombre total de lignes dans la table. Ainsi, une analyse est une stratégie efficace si la table est petite ou si la plupart des lignes qualifier pour le prédicat.

    De Recherche D'Index:
    Depuis une recherche ne touche que les lignes éligibles et les pages qui contiennent ces lignes de qualification, le coût est proportionnel au nombre de lignes éligibles et des pages plutôt que le nombre total de lignes dans la table.

    Si il existe un index sur une table, et si la requête est de toucher une plus grande quantité de données, ce qui signifie que la requête est en train de récupérer plus de 50% ou 90% de données, puis de l'optimiseur aurait suffit de scanner toutes les pages de données pour récupérer les lignes de données.

    source

    OriginalL'auteur d.popov

  5. 0

    J'ai fait un test rapide et est venu avec la suite de

    CREATE TABLE #Locations
(LocationID INT NOT NULL ,
CountryID INT NOT NULL ,
[Type] INT NOT NULL 
CONSTRAINT PK_Locations
PRIMARY KEY CLUSTERED ( LocationID ASC )
)
CREATE NONCLUSTERED INDEX [LocationsIndex01] ON #Locations
(
CountryID ASC,
[Type] ASC
)
CREATE TABLE #LocationCache
(LocationID INT NOT NULL ,
SearchQuery VARCHAR(50) NULL ,
SearchRank INT NOT NULL 
CONSTRAINT PK_LocationCache
PRIMARY KEY CLUSTERED ( LocationID ASC )
)
CREATE NONCLUSTERED INDEX [LocationCacheIndex01] ON #LocationCache
(
LocationID ASC,
SearchQuery ASC,
SearchRank ASC
)
INSERT INTO #Locations
SELECT 1,1,1 UNION
SELECT 2,1,4 UNION
SELECT 3,2,7 UNION
SELECT 4,2,7 UNION
SELECT 5,1,1 UNION
SELECT 6,1,4 UNION
SELECT 7,2,7 UNION
SELECT 8,2,7 --UNION
INSERT INTO #LocationCache
SELECT 4,'BlahA',10 UNION
SELECT 3,'BlahB',9 UNION
SELECT 2,'BlahC',8 UNION
SELECT 1,'BlahD',7 UNION
SELECT 8,'BlahE',6 UNION
SELECT 7,'BlahF',5 UNION
SELECT 6,'BlahG',4 UNION
SELECT 5,'BlahH',3 --UNION
SELECT * FROM #Locations
SELECT * FROM #LocationCache
SELECT      top 3 a.LocationId, b.SearchQuery, b.SearchRank
FROM        #Locations a
INNER JOIN  #LocationCache b ON a.LocationId = b.LocationId
WHERE       a.CountryId = 2
AND         a.[Type] = 7
DROP TABLE #Locations
DROP TABLE #LocationCache

    Pour moi, le plan de requête montre pour cherche avec une boucle imbriquée jointure interne. Si vous exécutez ce code, vous obtenez à la fois cherche? Si vous le faites, alors faites un test sur votre système et de créer une copie de vos Emplacements et LocationCache de la table et de les appeler, dire Locations2 et LocationCache2 avec tous les indices et la copie de vos données. Alors tentez votre requête frapper les nouvelles tables?

    C'est probablement parce que vous n'avez pas de 5,4 M de lignes dans vos tables.
    lol bon, la raison pour laquelle je mentionne essayer de nouvelles tables, j'ai eu un problème similaire il y a un moment sur les deux tableaux avec 34 millions de lignes, je n'ai rien de le faire fonctionner comme il le devrait. Mais sur un louches serveur de test, il a travaillé très bien et rapide. Après, j'ai créé de nouvelles tables et copié les données, il était tout heureux. Je n'ai toujours aucune idée de ce qui a causé le problème, la statistique peut-être que je ne sais pas.
    Ce qui est frustrant .. c'est quand j'ai le même problème .. et utiliser la mise à JOUR des STATISTIQUES ... je ne suis pas sûr que cela aide 🙁

    OriginalL'auteur John Petrak