Cluster vs Non-groupés

Question

Le bas de mon niveau de connaissances de SQL (Serveur 2008) est limitée, et est maintenant challanged par nos Administrateurs de bases de données. Laissez-moi vous expliquer (je l'ai mentionné évident états dans l'espoir que je suis de droite, mais si vous voyez quelque chose de mal, s'il vous plaît dites-moi), le scénario:

Nous avons un tableau qui contient 'Ordonnances de la Cour" pour les personnes. Quand j'ai créé la table, (Nom: CourtOrder), je l'ai créé comme:

CREATE TABLE dbo.CourtOrder
(
  CourtOrderID INT NOT NULL IDENTITY(1,1), (Primary Key)
  PersonId INT NOT NULL,
  + around 20 other fields of different types.
)

J'ai ensuite appliqué un index non-cluster de la clé primaire (pour l'efficacité). Mes raisons, c'est que c'est un domaine unique (clé primaire), et doivent être indexés, principalement à des fins de sélection, comme nous l'avons souvent Select from table where primary key = ...

J'ai ensuite appliqué un index CLUSTER sur PersonId. La raison était de grouper les commandes pour une personne en particulier physiquement, comme la grande majorité des travaux est d'obtenir des commandes pour une personne. Donc, select from mytable where personId = ...

J'ai été tiré sur cette maintenant. J'ai dit que nous devrions mettre l'index cluster sur la clé primaire, et l'indice normal sur le personId. Cela semble très étrange pour moi. Tout d'abord, pourquoi voudriez-vous mettre un index cluster sur une colonne unique? qu'est-ce clustering? Certes, c'est un gaspillage de l'index cluster? J'aurais cru un indice normal serait utilisé sur une colonne unique. Aussi, le regroupement de l'indice voudrait dire qu'on ne peut pas en cluster une autre colonne (Un par table, à droite?).

Le raisonnement me dit que je l'avais fait une erreur, c'est qu'ils croient en mettant un index cluster sur la PersonId ferait insère lent. Pour le gain de 5% en vitesse d'un select, nous prendrions à 95% de la dégradation de la vitesse sur les insertions et mises à jour. Est-ce correct et valide?

Ils disent que, parce que nous cluster de la personId, SQL Server a pour réorganiser les données lorsque nous insérer ou de faire un changement à la PersonId.

Alors j'ai demandé, pourquoi serait-SQL, le concept d'un INDEX CLUSTER, si c'est si lent? Est-il aussi lent qu'ils disent? Comment dois-je configurer mon index pour obtenir une performance optimale? J'aurais pensé SELECT est utilisé plus que d'INSÉRER... mais ils disent que nous allons avoir des problèmes de verrouillage sur les plaquettes...

J'espère que quelqu'un pourra m'aider.

Answer 1

La distinction entre un cluster vs index non-cluster est que l'index cluster détermine l'ordre physique des lignes dans la base de données. En d'autres termes, l'application de l'index cluster pour PersonId signifie que les lignes seront physiquement triés par PersonId dans le tableau, ce qui permet une recherche d'indice sur ce à aller directement à la ligne (plutôt que d'un index non-cluster, qui serait de vous diriger vers la ligne de l'emplacement, l'ajout d'une étape supplémentaire).

Cela dit, il est inhabituel pour la clé primaire de ne pas être de l'index cluster, mais pas inconnue. De la question avec votre scénario est en fait le contraire de ce que vous êtes en supposant que: vous voulez unique de valeurs dans un index cluster, pas de doublons. Parce que l'index cluster détermine l'ordre physique de la ligne, si l'index est sur un non-unique colonne, puis le serveur doit ajouter une valeur d'arrière-plan pour les lignes qui ont une valeur de clé en double (dans votre cas, toutes les lignes avec le même PersonId), de sorte que la valeur combinée (clé + valeur d'arrière-plan) est unique.

La seule chose que je suggère, c'est de ne pas à l'aide d'une clé de substitution (votre CourtOrderId) de la colonne comme clé primaire, mais au lieu d'utiliser un composé de la clé primaire de l' PersonId et certains autres unique d'identification de la colonne ou un ensemble de colonnes. Si ce n'est pas possible (ou pas), même si, ensuite, mettre l'index cluster sur CourtOrderId.

Answer 2

Je ne suis pas un SQL Expert...alors prendre cela comme un développeur de vue plutôt qu'un DBA vue..

Inserts en cluster (physiquement commandé) indices qui ne sont pas dans l'ordre séquentiel entraîner un surcroît de travail pour les insertions et mises à jour. Aussi, si vous avez de nombreux inserts de passe à la fois et ils sont tous survenus dans le même endroit, vous vous retrouvez avec contention. Votre performance spécifique varie en fonction de vos données et la façon d'y accéder. La règle générale est de construire votre index cluster unique, la plus étroite de la valeur à votre table (généralement le PK)

Je suis en supposant que votre PersonId ne change, donc les Mises à jour ne viennent pas en jeu ici. Mais imaginez un aperçu de quelques lignes avec PersonId de 1 2 3 3 4 5 6 7 8 8

Insérez maintenant 20 nouvelles lignes pour PersonId de 3. Tout d'abord, puisque ce n'est pas une clé unique, le serveur ajoute quelques octets supplémentaires à votre valeur (en coulisse) pour le rendre unique (qui ajoute également un espace supplémentaire) et ensuite l'emplacement où ces résidera doit être modifiée. Comparez cela à l'insertion d'une auto-incrémentation PK où les inserts arriver à la fin. La non explication technique serait probablement parvenu à cela: il y a moins de " feuille de brassage de travail à faire si c'est tout naturellement progression des valeurs plus élevées à la fin de la table et le remaniement de l'emplacement des éléments existants à cet emplacement lors de l'insertion de vos articles.

Maintenant, si vous rencontrez des problèmes avec Inserts alors vous êtes probablement l'insertion d'une bande de la même (ou similaire) PersonId valeurs à la fois ce qui est à l'origine de ce travail supplémentaire dans divers endroits à travers la table et de la fragmentation est de vous tuer. L'inconvénient de passer à l'PK étant regroupés dans votre cas, si vous rencontrez insérer des questions d'aujourd'hui sur PersonIds qui varient en fonction de la valeur de la propagation dans tout le tableau, si vous passez votre index cluster de la PK et de tous les insère désormais se produire dans un lieu de votre problème peut effectivement s'aggraver en raison de l'augmentation de contention de la concentration. (Sur le revers de la médaille, si vos plaquettes aujourd'hui, ne sont pas réparties sur tout, mais ils sont tous généralement regroupés dans des domaines similaires, alors votre problème sera probablement facilité par commutation votre index cluster loin de PersonId à votre PK parce que vous allez être de réduire la fragmentation.)

Vos problèmes de performances doivent être analysés à votre situation et de prendre ces types de réponses que de lignes directrices générales. Votre meilleur pari est de s'appuyer sur un DBA qui peut valider exactement où se situent les problèmes. On dirait que vous avez les conflits de ressources qui peuvent être au-delà d'un simple indice de tweak. Ce pourrait être un symptôme d'un problème beaucoup plus vaste. (Probablement des problèmes de conception...sinon limitations de ressources.)

En tout cas, bonne chance!

Answer 3

Certains auteurs suggèrent de ne pas "perdre" de l' CI sur identity colonne si il existe une alternative qui serait à l'avantage de la gamme des requêtes.

À partir de MSDN Index Cluster lignes Directrices sur la Conception de la clé doit être choisi selon les critères suivants

1. Peut être utilisé pour les requêtes fréquemment utilisées.
2. Fournir un degré élevé de spécificité.
3. Peut être utilisé dans la gamme des requêtes.

Votre CourtOrderID colonne correspond 2. Votre PersonId satisfait 1 et 3. Comme la plupart des lignes jusqu'à la fin avec l' uniqueifier ajouté de toute façon, vous pourriez tout aussi bien déclarer que unique et utiliser PersonId,CourtOrderID que ce sera la même largeur, mais plus utile que la clé d'index cluster est ajouté à tous les NCIs comme le localisateur de ligne et cela leur permettra de couvrir plus de requêtes.

Le principal problème avec l'aide d' PersonId,CourtOrderID que l'IC est que la logique de la fragmentation sera probablement s'ensuivre (et cela concerne en particulier la gamme requêtes que vous essayez d'aider) de sorte que vous besoin de surveiller le facteur de remplissage, et des niveaux de fragmentation et d'effectuer la maintenance des index plus souvent.

Answer 4

Quelques db avec quelque méchant sélectionne, jointures dans une procédure stockée - seule diffrence est l’index

-Les index non clusters cluster vs