Quelles sont les meilleures performances de SQL Server, les techniques d'optimisation?
J'ai toujours pris l'approche de la première déploiement de la base de données avec un ensemble minimal de l'index, puis ajouter/modifier des indices de performance dicte.
Cette approche fonctionne raisonnablement bien. Toutefois, cela ne fonctionne toujours pas me dire où je pouvais améliorer les performances. Il ne me dit où la performance est tellement mauvais que les utilisateurs s'en plaignent.
Actuellement, je suis dans le processus de refactoring objets de base de données sur un grand nombre de nos applications.
Dois-je donc pas la peine de chercher des améliorations de performances depuis "l'optimisation prématurée est la racine de tout mal"?
Lors d'un refactoring du code de l'application, le développeur est constamment à la recherche de moyens pour améliorer la qualité du code. Est-il un moyen d'être constamment à la recherche pour l'amélioration des performances des bases de données ainsi? Si oui, quels sont les outils et les techniques avez-vous trouvé les plus utiles?
J'ai brièvement joué un peu avec le "paramétrage du moteur de Base", mais ne trouve pas qu'il soit utile à tous. Peut-être que j'ai juste besoin de plus d'expérience de l'interprétation des résultats.
Vous devez vous connecter pour publier un commentaire.
Ma démarche est de recueillir des commandes sur le serveur ou base de données dans une table en utilisant le générateur de profils SQL Server. Une fois que vous avez cela, vous pouvez requête basée sur le max et avg temps d'exécution, max et avg temps de calcul, et (très important) le nombre de fois que la requête a été exécutée.
Depuis que j'ai essayer de mettre tous les accès à la base de code des procédures stockées, il est facile pour moi de sortir des requêtes. Si vous utilisez inline SQL, il peut être plus difficile, depuis une modification d'une valeur dans la requête serait de la faire ressembler à une requête différente. Vous pouvez essayer de contourner ce problème en utilisant l'opérateur LIKE pour mettre les mêmes types de requêtes dans le même seaux pour le calcul des agrégats (max, avg, count).
Une fois que vous avez un "top 10" liste de problèmes potentiels, vous pouvez commencer à les regarder individuellement pour voir si la requête peut être retravaillé, un indice qui pourrait l'aider, ou de faire une mineure modification de l'architecture est dans l'ordre. À venir avec le top 10, essayer de regarder les données de différentes façons: avg * nombre total des coûts au cours de la période, max pour le pire délinquant, tout simplement avg, etc.
Enfin, veillez à le suivre sur différentes périodes de temps si nécessaire. L'utilisation de base de données peut être différente dans le matin, quand tout le monde est dans et à l'exécution de leurs rapports quotidiens que c'est à midi, lorsque les utilisateurs sont de la saisie de nouvelles données. Vous pouvez également décider que, même si certains soirs processus prend plus de temps que n'importe quelle autre requête, il n'a pas d'importance puisqu'il est lancé pendant les heures creuses.
Bonne chance!
"l'optimisation prématurée est la racine de tout mal"
En termes de programmation de base de données, je pense que cette citation est un non-sens. Il est extrêmement coûteux de le ré-écrire l'ensemble de votre demande parce que les développeurs ne se soucient pas écrire du code efficace la première fois. Tous les code t-sql doit être pensé en termes de la façon dont il aura une incidence sur les performances de la base de la deuxième (l'intégrité des données est, bien sûr, d'abord). Performance l'emporte sur tout, sauf de l'intégrité des données.
Oui, il y a de l'optimisation des choses que vous ne devriez pas le faire jusqu'à ce que vous avez des problèmes, mais certaines choses doivent être faites, comme une question de cours et de ne pas fixée ultérieurement. Il ne faut pas plus de temps pour écrire du code qui a plus de chance d'être efficace que du code qui ne sera pas une fois que vous comprenez comment vous êtes qui affectent l'efficacité avec le mauvais code. Cervo discussion du code de curseur en est un exemple. Basées sur des actions sont presque toujours beaucoup plus rapide que le curseur de solutions, afin de curseurs ne devrait jamais être écrite initialement, quand un jeu basé sur la solution sera de le faire. C'est presque toujours me prend moins de temps pour écrire une série basée sur la solution qu'il aurait à écrire un curseur, mais la seule façon de le faire est de ne jamais écrire des curseurs.
Et il n'y a aucune raison de jamais utiliser select * au lieu de préciser vos noms de domaine. Dans MSSQL vous pouvez faire glisser les noms de plus à partir de l'explorateur d'objets, de sorte que vous ne pouvez pas me dire qu'il est trop difficile de le faire. Mais par specyfying uniquement les champs dont vous avez réellement besoin, vous économisez les ressources du réseau et de la base de données de ressources de serveur web et de serveur de ressources. Alors pourquoi un programmeur jamais prendre le paresseux option de sélectionner * et l'inquiétude au sujet de l'optimisation plus tard?
La même chose avec les index. Vous dites que vous faites un ensemble minimal d'indices. Selon la façon dont vous définissez minimes, qui pourrait être ok, mais il est essentiel d'avoir des indices sur toutes les clés étrangères et je ne veux pas pousser à une base de données qui n'ont pas d'index sur les quelques domaines qui sont les plus souvent dans les clauses where. Si vos utilisateurs sont des clients de l'extérieur et non intérieur, ils ne vont pas se plaindre de la lenteur de votre site, ils vont ailleurs. Il ne fait que busness sens au plan pour l'efficacité de la base de données access à partir du début.
L'une de mes principales préoccupations au sujet de ne pas envisager d'efficacité depuis le début, c'est que le premier couple de fois que les choses sont trop lents, les entreprises ont tendance à simplement jeter plus de matériel à la question plutôt que d'optimisation des performances. Par le temps que les gens commencent à performacne tuning vous avez une plusieurs gigaoctets ou plus de la base de données avec de nombreux clients mécontents qui sont l'obtention de délais d'attente plus de résultats. À ce point, souvent, presque tout dans la base de données doit être ré-écrit et en attendant, vous sont en train de perdre des clients. Je me souviens de soutien à une entreprise avec une application commerciale qu'il a littéralement pris dix minutes pour les représentants du service client pour passer d'un écran à l'autre alors qu'ils tentaient d'aider déjà la colère des clients sur le téléphone. Vous pouvez imaginer combien de clients de la société a perdu en raison de la mauvaise conception des requêtes de base de données dans le produit commercial qu'on ne pouvait pas changer.
D'Exécution de SQL Server Plan!!! Rendez-vous ici: http://dbalink.wordpress.com/2008/08/08/dissecting-sql-server-execution-plans-free-ebook/
Après votre profil, mettre les requêtes de vous voir aussi gênant dans l'Analyseur de Requêtes SQL et afficher le plan d'exécution. Identifier les parties des requêtes qui sont coûteux d'effectuer les analyses de la table et ré-indexer les tables de minimiser ce coût.
Essayez ces références:
L'optimisation de SQL
Comment Optimiser les Requêtes
profil de vos requêtes, pas les plus évidents, mais le complexe qui a accès à différentes tables, vues, etc et/ou ceux qui reviennent beaucoup de lignes de différentes tables
Qui vous dira exactement où vous devez vous concentrer
profilage est la clé, mais lors de l'utilisation d'un profilage série, vous DEVEZ être sûr que c'est un test précis de données, sinon, les outils de réglage ne sera pas en mesure de vous obtenir un résultat précis, ce qui est nécessaire.
Également la gestion des objets avec la fragmentation de l'un des rapports d'utilisation en 2005 sont très utiles!
Bien sûr, vous avez le profil de vos requêtes et de regarder le plan d'exécution. Mais les deux principales choses qui reviennent encore et encore filtrant sont autant d'éléments que vous pouvez dès que vous le pouvez et essayez d'éviter les curseurs.
J'ai vu une application où quelqu'un a téléchargé un ensemble de table de base de données d'événements pour un client et ensuite à travers chaque ligne une par une de filtrage en fonction de certains critères. Il y a une ÉNORME augmentation de la performance, en passant, les critères de filtre à la base de données et avoir la requête appliquer les critères dans une clause where. C'est évident pour les gens qui travaillent avec des bases de données, mais j'ai vu des choses similaires cultures. Aussi, certaines personnes ont des requêtes stocker un tas de tables temp plein de lignes qu'ils n'ont pas besoin, qui sont ensuite éliminés en finale de la jointure des tables temporaires. Fondamentalement, si vous éliminez de requêtes qui peuplent les tables temporaires puis il y a moins de données pour le reste de la requête et de l'ensemble de la requête s'exécute plus rapidement.
Les curseurs sont évidentes. Si vous avez un million de lignes et aller ligne par ligne, puis il va prendre une éternité. Faisant quelques tests, si vous vous connectez à une base de données même avec un "lent" dynamique du langage comme Perl et effectue ligne par ligne de commande sur un jeu de données, la vitesse sera toujours beaucoup plus que d'un curseur dans la base de données. Le faire avec quelque chose comme Java/C/C++ et de la différence de vitesse est encore plus grand. Si vous pouvez trouver/éliminer un curseur dans le code de base de données, il s'exécute beaucoup plus rapidement... Si vous devez utiliser un curseur, la réécriture de la partie dans tout langage de programmation et le sortir de la base de données sera probablement générer de considérables augmentations de rendement.
Une note plus sur les curseurs, méfiez-vous d'un code comme SELECT @col1 = col1, @col2 = col2, @col3 = col3 where id = @currentid dans une boucle qui passe par Id, puis exécute les instructions sur chaque colonne. Fondamentalement, c'est un curseur ainsi. Non seulement cela, mais l'utilisation réelle des curseurs est souvent plus rapidement, surtout statique et forward_only. Si vous pouvez modifier le fonctionnement du jeu de base, il sera beaucoup plus rapide.....Cela étant dit, les curseurs ont un endroit pour certaines choses....mais à partir d'un point de vue des performances il y a une pénalité à l'aide de leur rapport aux approches fondées.
Aussi se méfier de l'exécution du plan. Parfois, il les estimations des opérations qui prennent quelques secondes à être très coûteux et les opérations que prendre quelques minutes pour être très bon marché. Lors de la visualisation d'un plan d'exécution assurez-vous de vérifier tout ce qui peut l'insertion de certains SÉLECTIONNEZ 'À cette région", GETDATE() dans votre code.
Mon avis est que "l'optimisation prématurée est la racine de tout mal" dans ce contexte est absoulte non-sens.
À mon avis, son tout sur la conception, vous devez penser à la simultanéité, les points chauds, d'indexation, de mise à l'échelle et des modes d'utilisation lorsque vous êtes la conception votre schéma de données.
Si vous ne savez pas de quels indices vous ont besoin et comment ils doivent être configurés droit au large de la chauve-souris, sans faire de profilage vous avez déjà échoué.
Il y a des millions de façons d'optimiser l'exécution de la requête qui sont tous bien, mais à la fin de la journée, les données des terres où vous dites qu'il.
Appliquer une bonne indexation dans les colonnes de la table dans la base de données
Cela permettra d'assurer que chaque table a un index cluster créé (et, par conséquent, les pages correspondantes de la table sont physiquement triés sur le disque dur selon le champ de clé primaire). Ainsi, toute opération de récupération de données à partir de la table à l'aide de la clé primaire, ou de toute opération de tri sur le champ de clé primaire ou une plage de valeurs de clé primaire spécifié dans le où clause de récupérer des données à partir de la table très rapide.
Créer des index non cluster sur les colonnes qui sont
Fréquemment utilisés dans les critères de recherche.
Utilisé pour se joindre à d'autres tables.
Utilisés comme champs de clé étrangère.
D'avoir une haute sélectivité (colonne qui renvoie un faible pourcentage (de 0 à 5%) de lignes à partir d'un nombre total de lignes sur une valeur particulière).
Utilisé dans le COMMANDE PAR la clause.
Ne pas utiliser "SELECT*" dans une requête SQL
Les colonnes inutiles peuvent obtenir récupérés qui va ajouter de la charge de la récupération des données.
Le moteur de base de données ne peut pas bénéficier de l'aide d' "Recouvert " Index" et, partant, la requête s'exécute lentement.
Exemple:
Au lieu de:
Essayez d'éviter d'AVOIR de la Clause dans les instructions Select
Clause HAVING est utilisée pour filtrer les lignes après que toutes les lignes sont sélectionnées et est utilisé comme un filtre. Essayez de ne pas utiliser la clause HAVING pour d'autres fins.
Exemple:
Au lieu de:
Essayer de minimiser le nombre de sous-requête des blocs à l'intérieur d'une requête
Parfois on peut avoir plus d'une sous-requête dans notre requête principale. Nous devrions essayer de minimiser le nombre de sous-requête bloc dans notre requête.
Exemple:
Au lieu de:
Éviter les colonnes dans la liste de sélection et inutile tables dans des conditions de jointure
Sélectionnant les colonnes inutiles dans une requête Select ajoute de la surcharge de la requête réelle, surtout si les colonnes inutiles sont de LOB types.
Y compris l'inutile de tables dans des conditions de jointure des forces moteur de base de données pour récupérer et récupérer les données inutiles et augmente le temps d'exécution requête.
Ne pas utiliser la fonction COUNT() regroupées dans une sous-requête pour faire une existence vérifier
Lorsque vous utilisez des COUNT(), SQL Server ne sais pas ce que vous faites une existence vérifier. Il compte toutes les valeurs correspondantes, soit en effectuant une analyse de table ou en scannant le plus petit index non ordonné en clusters.
Lorsque vous utilisez des EXISTE, SQL Server sait que vous faites une existence vérifier. Lorsqu'il trouve le premier correspondant à la valeur, elle renvoie TRUE et s'arrête à la recherche.
Essayez d'éviter les joindre entre les deux types de colonnes
Lors de la jointure entre les deux colonnes de types de données différents, l'une des colonnes doit être converti dans le type de l'autre. La colonne dont le type est plus faible est celui qui est converti.
Si vous êtes à la jointure de tables avec des types incompatibles, l'un d'eux peut utiliser un index, mais l'optimiseur de requête ne peut pas choisir un index sur la colonne qu'il convertit.
Essayez de ne pas utiliser COUNT(*) pour obtenir le nombre d'enregistrements dans une table
Pour obtenir le total du nombre de lignes dans une table, on utilise en général la suivante Sélectionnez déclaration:
Cette requête va effectuer un full table scan pour obtenir le nombre de lignes.
La requête suivante ne nécessiterait pas un full table scan. (Veuillez noter que cela ne peut pas vous donner 100% de résultats parfaits toujours, mais c'est pratique que si vous n'avez pas besoin d'un parfait comte.)
Essayez d'utiliser des opérateurs comme il EXISTE, DANS et REJOINT de façon appropriée dans votre requête
Essayez d'éviter de SQL dynamique
Sauf si vraiment nécessaire, essayez d'éviter l'utilisation de SQL dynamique parce que:
Le SQL dynamique est difficile à déboguer et résoudre les problèmes.
Si l'utilisateur fournit les données d'entrée du SQL dynamique, alors il ya une possibilité d'attaques par injection SQL.
Essayez d'éviter l'utilisation de tables temporaires
Sauf si vraiment nécessaire, essayez d'éviter l'utilisation de tables temporaires. Plutôt d'utiliser les variables de table.
Dans 99% des cas, les variables de table résident en mémoire, il est donc beaucoup plus rapide. Les tables temporaires de résider dans la base de données TempDb. Afin d'exploitation sur les tables temporaires nécessitent entre la base de données de communication, et donc sera plus lent.
Au lieu de COMME recherche, de l'utilisation de la recherche plein texte pour la recherche de données textuelles
La recherche en texte intégral sont toujours plus COMME les recherches.
La recherche en texte intégral vous permettra de mettre en œuvre des critères de recherche complexes qui ne peuvent pas être mises en œuvre à l'aide d'une recherche, comme la recherche sur un seul mot ou une phrase (et, éventuellement, le classement de l'ensemble de résultats), une recherche sur un mot ou une expression proche d'un autre mot ou une expression, ou de la recherche sur les formes synonymes d'un mot spécifique.
La mise en œuvre de la recherche plein texte est plus facile à mettre en œuvre que COMME la recherche (en particulier dans le cas des complexes exigences de la recherche).
Essayez d'utiliser de l'UNION pour mettre en œuvre un "OU" l'opération
Essayez de ne pas utiliser "OU" dans une requête. Au lieu d'utiliser "l'UNION" de combiner l'ensemble de résultats de deux éminents requêtes. Cela permettra d'améliorer les performances des requêtes.
Mieux utiliser de l'UNION de TOUS les si distingué résultat n'est pas nécessaire. l'UNION de TOUS les est plus rapide que UNION de ne pas avoir à trier le jeu de résultats pour trouver le distingue des valeurs.
Mettre en œuvre un paresseux stratégie de chargement pour les objets volumineux
Stocker de Grandes colonnes d'Objet (comme VARCHAR(MAX), Image Texte etc.) dans une autre table que la table principale, et de mettre une référence à l'objet de grande taille dans la table principale.
Récupérer toutes les principales données de la table dans une requête, et si un objet de grande taille est nécessaire pour être chargé, de récupérer les données d'objets volumineux à partir de la grande table d'objet uniquement lorsque cela est requis.
Mettre en œuvre les bonnes pratiques suivantes en Fonctions Définies par l'Utilisateur
Ne pas appeler des fonctions à plusieurs reprises dans vos Procédures Stockées, des déclencheurs, des fonctions et des lots. Par exemple, vous pourriez avoir besoin de la longueur d'une variable de chaîne en de nombreux endroits de votre procédure, mais ne l'appelez pas le LEN fonction chaque fois que c'est nécessaire; au lieu de cela, appelez le LEN fonction qu'une seule fois, et stocker le résultat dans une variable pour l'utiliser plus tard.
Mettre en œuvre les bonnes pratiques suivantes dans les Déclencheurs
Il semble que vous parlez de MS SQL.
Démarrer le générateur de profils et d'enregistrer tehe la plupart des questions courantes que vous exécutez sur la base de données.
Puis exécuter ces requêtes avec le Plan d'Exécution allumé et vous verrez ce que (le cas échéant) est en train de ralentir vos requêtes vers le bas. Vous pourrez alors vous rendre et d'optimiser les requêtes ou d'ajouter des index sur les champs.
SQL Livres vous donnera un bon aperçu de profilage et d'analyse de la demande de fonctionnalité.
Vous pourriez vouloir vérifier internes et externes framentation de les indices actuels et soit le supprimer et de re-créer ou re de les organiser.
Assurez-vous de profilage à l'aide de volumes de production en termes de nombre de lignes et charge. Les requêtes et leurs plans se comportent différemment selon la charge/volume scénarios
Généralement, les conseils ici:
http://www.sql-server-performance.com/
ont été de haute qualité et utile pour moi dans le passé.
Mon conseil serait de commencer avec des techniques applicables à toutes les bases de données et essayez de celles spécifiques aux MsSQL.
L'optimisation de SQL est difficile, et il n'existe pas de règles strictes et rapides. Il y a très peu de lignes directrices génériques que vous pouvez suivre, tels que:
Mais le point clé est que vous devez toujours appliquer la règle des 80-20. Ce qui signifie que dans tout système, vous avez besoin d'ajuster 20% (souvent beaucoup moins) de votre code pour les plus grands gains de performance. C'est là que les outils fournis par les fournisseurs généralement échouer, comme ils ne peuvent généralement pas se deviner la demande ou de l'entreprise contexte d'exécution.