SQL Server – Quelles sont les meilleures pratiques pour la maintenance des index?

Je lis des index pour optimiser les performances des bases de données et je voudrais rassembler les meilleures pratiques pour différentes situations de database en termes d'application d'index.

Prenez une database totalement non indexée avec une quantité non-sortingviale de tables et de lignes, quelles règles utilisez-vous pour déterminer quel index est appliqué à quelle colonne de quel type pour atteindre une performance maximale? Quelle requête parsing des astuces à utiliser?

Pour commencer j'ai eu: (sur http://asptutorials.net/SQL-Server/tutorial-on-indexes/ )

Pour les tables principales ou "en-tête" telles qu'une table de factures, créez un index en cluster sur la key primaire de la table.
Pour les tables secondaires ou "détaillées" telles que "invoice_row", créez un index clusterisé sur la key étrangère qui regroupe les loggings enfants (dans cet exemple, "invoice_id"). Cela est dû au fait que la majorité des requêtes sur la table invoice_row seront effectuées dans l'ordre invoice_id plutôt que dans l'ordre invoice_row_id.
Pour toutes les tables, créez un index non cluster sur chacune des foreign keys de la table. Ne vous préoccupez pas de couvrir les index à ce stade. Pensez aux requêtes de sélection que vous allez effectuer sur la table. Quel genre de "où" et "ordre par" les déclarations utiliserez-vous? Faites un index non cluster sur ces colonnes.
Il est maintenant time de commencer à chronométrer vos requêtes typiques et de chercher celles qui sont lentes. Si vous en identifiez un particulièrement lent, voyez s'il existe un moyen d'append des colonnes non-keys supplémentaires à un index afin qu'il devienne un index de couverture pour cette requête.

Quelles autres «règles empiriques» pouvons-nous collecter? Quels outils utiliser?

J'essaie de créer l'index dont j'ai besoin et j'utilise le script suivant dans l'environnement de test ou de production pour optimiser les index. J'ai aussi quelques autres procédures, mais celle-ci est un bon début. Rappelez-vous que vous devez sélectionner le meilleur ordre des colonnes par vous-même.

Cette question est similaire à cette question où j'ai posté d'autres procédures stockées que j'utilise pour régler les index.

CREATE PROCEDURE [ADMIN].[spMissingIndexes] AS SELECT mid.statement, mid.equality_columns, mid.inequality_columns, mid.included_columns, migs.user_seeks, migs.user_scans, migs.last_user_seek, migs.avg_user_impact, user_scans, avg_total_user_cost, avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) AS [weight] FROM sys.dm_db_missing_index_group_stats AS migs INNER JOIN sys.dm_db_missing_index_groups AS mig ON (migs.group_handle = mig.index_group_handle) INNER JOIN sys.dm_db_missing_index_details AS mid ON (mig.index_handle = mid.index_handle) ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) DESC ; GO

Numéro 1 – parsingz votre SQL.

Vous devez regarder le SQL étant lancé à la DB pour déterminer quels index pourraient être utiles. Vous ne pouvez pas déterminer ce qui est nécessaire simplement en regardant l'parsingur de plan.

Numéro 2 – Les balayages d'espace de table sont très souvent de bonnes choses! Si la table est "petite" ou si vous accédez à 30% ou plus des lignes, une parsing est souvent le path d'access le plus efficace.

Numéro 3 – Indexer les «petites» tables est inutile.

Numéro 4 – Les index sont utiles pour la récupération aussi. Coller les valeurs généralement récupérées à l'arrière d'un index – souvent le DB choisira simplement les valeurs de l'index et ne prendra pas la peine de reprendre la ligne réelle. par exemple si un SQL commun est "select max (item_no) où invoice =?" Si vous créez un index avec facture et item_no, la requête peut être satisfaite sans aucun access à la table réelle.

La valeur actuelle de "small" est d'environ 2000 lignes.

Rappelez-vous toujours qu'il y a une pénalité de performance sévère sur INSERT pour chaque index que vous construisez.

Une fois que vous avez défini vos index de key primaire et étrangère, vous devez parsingr l'activité transactionnelle de votre database afin de déterminer une stratégie d'indexing appropriée.

SQL Server suggère en fait des index que vous souhaiterez peut-être incorporer dans votre database de différentes manières:

Les index manquants Dynamic Management Views . Suggère des index qui peuvent manquer dans vos bases de données en examinant les plans d'exécution qui ont été générés sur votre plateforme et qui auraient pu bénéficier de l'existence d'un index.
L' assistant d'optimization du moteur de database . Créez une trace SQL Server pour créer un file de charge de travail, puis exécutez-le via le DTA afin de générer des recommandations.

Je vous suggère d'effectuer des tests pour simuler la charge de travail transactionnelle que vous attendez afin de concevoir et d'ajuster avec précision votre database.