Méthodes de performance des disques SQL Server Partie 2 autres mesures importantes de la performance des disques Dans la partie précédente de la série de métriques de performance SQL Server, nous avons présenté les métriques de performance de disque les plus importantes et les plus utiles. Maintenant, montrez bien d'autres mesures de performances de disque importantes Longueur de file d'attente de disque actuelle Indique le nombre de demandes de disque en attente ainsi que les demandes en cours d'entretien. Sous réserve de variations importantes à moins que la charge de travail n'ait atteint un état stable et que vous ayez recueilli un nombre suffisant d'échantillons pour établir un modèle. 1 La métrique indique combien d'opérations IO attendent d'être écrites ou lues sur le disque dur et combien sont actuellement traitées. Si le disque dur n'est pas disponible, ces opérations sont mises en file d'attente et seront traitées lorsque le disque devient disponible. Le sous-système de disque entier a une seule file d'attente La métrique de Longueur de file d'attente de disque actuelle dans l'Analyseur de performances Windows est disponible pour le disque physique et logique. Dans certaines versions antérieures de l'Analyseur de performances, ce compteur a été nommé Longueur de file d'attente de disque La valeur de longueur de file d'attente de disque doit être inférieure à 2 par broche de disque. Notez que ce n'est pas par logique, mais par disque physique. S'il est plus grand, cela indique un goulot d'étranglement potentiel sur le disque, de sorte qu'il est recommandé de poursuivre l'enquête et de surveiller d'autres mesures de disque. Commencez par surveiller le temps disque (expliqué ci-dessous). Les pics fréquents doivent également être étudiés Les systèmes de disques RAID ou SAN disposent d'un grand nombre de disques et de contrôleurs, ce qui rend les files d'attente sur ces systèmes plus courtes. Étant donné que la métrique n'indique pas la mise en file d'attente par disque, mais pour l'ensemble du tableau, certains DBA considèrent que la surveillance de la longueur de la file d'attente de disque sur les baies de disques n'est pas nécessaire. Il sera signalé comme étant mis en file d'attente pour l'écriture et ainsi la valeur de la longueur de la file d'attente du disque actuel sera supérieure à la longueur moyenne de la file d'attente de disque La mesure moyenne de la longueur de la file d'attente de disque affiche les informations semblables à la longueur de la file d'attente de disque courante. Seule la valeur n'est pas actuelle mais moyenne sur une période de temps spécifique. Le seuil est le même que pour la métrique précédente jusqu'à 2 par disque. Pour les systèmes à disques, la valeur recommandée est inférieure à 2 par unité de disque individuelle dans un tableau. Par exemple, dans une matrice de 6 disques, la valeur de Longueur de file d'attente de disque courante de 12 signifie que la file d'attente est 2 par disque. Il y a deux autres mesures semblables à Longueur moyenne de la file d'attente 8211. Comme leurs noms indiquent qu'ils indiquent la longueur moyenne de la file d'attente pour les opérations en attente de lecture ou d'écriture du disque. Disk Time Ce compteur indique un problème de disque, mais doit être observé conjointement avec le compteur de longueur de file d'attente pour être vraiment informatif. Rappelez-vous également que le disque pourrait être un goulot d'étranglement avant que la durée du disque n'atteigne 100 2 La mesure Disk Time indique à quel point le disque est occupé par les requêtes de lecture et d'écriture, mais comme indiqué plus haut, Peut être normale alors theres un grave problème de performance disque. Sa valeur est la valeur moyenne de longueur de file d'attente de disque représentée en pourcentages (c'est-à-dire multipliée par 100). Si la longueur moyenne de la file d'attente de disque est 1, la durée du disque est 100 Ce qui peut être déroutant, c'est que les valeurs de temps disque peuvent dépasser 100, ce qui n'est pas logique. Cela se produit si la longueur moyenne de la longueur de la file d'attente est supérieure à 1. Si la longueur moyenne de la file d'attente est de 3, la durée du disque est de 300, ce qui ne signifie pas que les processus utilisent 3 fois plus de temps disponible que le disponible. Disposent d'un tableau de disques durs, la durée totale du disque pour tous les disques est indiquée, sans indication du nombre de disques disponibles et du disque ayant le temps de disque le plus élevé. Par exemple, le temps de disque égal à 500 peut indiquer de bonnes performances (au cas où vous avez 6 disques), ou extrêmement mauvais (dans le cas où vous avez seulement 1 disque). Vous ne pouvez pas dire sans connaître le matériel de la machine Comme ce compteur peut être trompeur, certains DBA ne l'utilisent pas car il existe d'autres mesures plus simples et indicatives qui montrent les performances du disque Si la valeur est supérieure à 90 par disque, Tout d'abord, vérifiez la valeur Longueur de la file d'attente du disque. Si son supérieur au seuil (2 par disque physique), surveiller si les valeurs élevées se produisent fréquemment. Si l'ordinateur n'est pas utilisé uniquement pour SQL Server, d'autres applications nécessitant beaucoup de ressources peuvent provoquer des goulots d'étranglement de disque, de sorte que la performance de SQL Server souffrira. Si tel est le cas, envisagez de déplacer ces applications vers une autre machine et d'utiliser une machine dédiée pour SQL Server uniquement. Si ce n'est pas le cas, ou si vous ne pouvez pas le faire, envisagez de déplacer certains fichiers vers d'autres bases de données, Les sauvegardes du journal des transactions, l'utilisation d'un disque plus rapide ou l'ajout de disques supplémentaires à un disque Durée de lecture du disque et temps d'écriture du disque Les mesures du temps de lecture du disque et du temps d'écriture du disque sont similaires à la durée du disque. Montrant simplement les opérations lues ou écrites sur le disque, respectivement. Il s'agit en fait des valeurs moyennes de longueur de file d'attente de lecture de disque et de longueur moyenne de file d'attente d'écriture de disque présentées en pourcentages. Les valeurs que ces métriques montrent peuvent être tout aussi trompeuses que Disk Time Sur un système à trois disques, si un disque lit 50 fois (Disk Read Time 50), l'autre lit 85 fois et le troisième est inactif, Le temps de lecture du disque est de 135 et la longueur moyenne de la file de lecture du disque est de 1,35. À première vue, le temps de lecture de disque égal à 135 ressemble à un problème, mais ce n'est pas le cas. Cela ne signifie pas que les disques sont occupés 135 fois. Pour obtenir une valeur réelle, vous devez diviser la valeur avec le nombre de disques et vous obtiendrez 1363 45, ce qui indique la performance normale Idle Time Le disque est inactif quand il ne traite pas les requêtes de lecture et d'écriture Ce paramètre mesure le pourcentage de temps pendant lequel le disque était inactif Pendant l'intervalle d'échantillonnage. Si ce compteur tombe en dessous de 20%, le système de disque est saturé. Vous pouvez envisager de remplacer le système de disque actuel par un système de disque plus rapide. 3 Si la valeur est inférieure à 20, le disque n'est pas en mesure de traiter toutes les requêtes de lecture et d'écriture en temps voulu. Avant d'opter pour le remplacement du disque, vérifiez s'il est possible de supprimer certaines applications sur un autre ordinateur Espace libre Outre le moniteur de performances Windows, cette métrique est disponible dans l'Explorateur Windows dans l'ordinateur et les onglets Propriétés du disque. Bien que l'Analyseur de performances affiche le pourcentage d'espace disque disponible, l'Explorateur Windows affiche la quantité en GB Cette option mesure le pourcentage d'espace libre sur le lecteur de disque logique sélectionné. Prenez note si cela tombe en dessous de 15 pour cent, comme vous risquez de manquer d'espace libre pour l'OS pour stocker des fichiers critiques. Une solution évidente ici est d'ajouter plus d'espace disque. 3 Si la valeur affiche des pics soudains sans raisons évidentes, des recherches plus poussées sont nécessaires Contrairement à la plupart des métriques de performance de la mémoire et du processeur SQL Server, les métriques de disque peuvent être très trompeuses. Ils ne peuvent pas clairement indiquer un problème de performance leurs valeurs pourraient être OK, quand il ya effectivement un grave problème de disque, alors que leurs valeurs étrangement élevé peut afficher des performances normales, car ils montrent des valeurs pour un tableau de disques. Ensuite, il s'agit de métriques de réseau, la connaissance de la configuration matérielle est nécessaire pour les lire correctement. En dépit de ces inconvénients de métrique de disque, ils sont nécessaires pour la résolution de problèmes de SQL Server la plupart des gens sont familiers avec la phrase, quotthis tuera deux oiseaux avec un stonequot. Si vous ne le faites pas, la phase se réfère à une approche qui aborde deux objectifs dans une action. (Malheureusement, l'expression elle-même est plutôt désagréable, car la plupart d'entre nous ne veulent pas jeter des pierres à des animaux innocents). Aujourd'hui, je vais couvrir quelques notions de base sur deux grandes fonctionnalités de SQL Server: l'index Columnstore (disponible uniquement dans SQL Server Enterprise) Le magasin de requêtes SQL. Microsoft a effectivement implémenté l'index Columnstore dans SQL 2012 Enterprise, bien qu'ils l'aient amélioré dans les deux dernières versions de SQL Server. Microsoft a introduit Query Store dans SQL Server 2016. Alors, quelles sont ces fonctionnalités et pourquoi sont-elles importantes? Bien, j'ai une démo qui présentera les deux fonctionnalités et montrera comment elles peuvent nous aider. Avant d'aller plus loin, je traite également de ce (et d'autres fonctionnalités SQL 2016) dans mon article CODE Magazine sur les nouvelles fonctionnalités SQL 2016. Comme introduction de base, l'index Columnstore peut aider à accélérer les requêtes qui scanaggregate sur de grandes quantités de données et Le magasin de requêtes suit les exécutions de requêtes, les plans d'exécution et les statistiques d'exécution que vous devez habituellement collecter manuellement. Croyez-moi quand je dis, ce sont de grandes caractéristiques. Pour cette démo, j'utiliserai la base de données de démonstration Microsoft Contoso Retail Data Warehouse. Franchement parler, Contoso DW est comme quota vraiment grand AdventureWorksquot, avec des tables contenant des millions de lignes. (La plus grande table d'AdventureWorks contient environ 100 000 lignes au maximum). Vous pouvez télécharger la base de données Contoso DW ici: microsoften-usdownloaddetails. aspxid18279. Contoso DW fonctionne très bien lorsque vous souhaitez tester les performances sur les requêtes sur des tables plus grandes. Contoso DW contient une table de données d'entrepôt de données standard appelée FactOnLineSales, avec 12,6 millions de lignes. Ce n'est certainement pas la plus grande table d'entrepôt de données au monde, mais elle ne joue pas non plus. Supposez que je veux résumer le montant des ventes de produits pour 2009 et classer les produits. Je pourrais interroger la table de faits et joindre à la table de dimension de produit et utiliser une fonction RANK, comme suit: Here39s un ensemble de résultat partiel des 10 rangées supérieures, par les ventes totales. Sur mon ordinateur portable (i7, 16 Go de RAM), la requête prend de 3-4 secondes à s'exécuter. Cela pourrait ne pas sembler la fin du monde, mais certains utilisateurs pourraient s'attendre à des résultats presque instantanés (la façon dont vous pourriez voir des résultats presque instantanés lors de l'utilisation d'Excel contre un cube OLAP). Le seul index que j'ai actuellement sur cette table est un index clusterisé sur une clé de ventes. Si je regarde le plan d'exécution, SQL Server propose une suggestion pour ajouter un index couvrant à la table: Maintenant, juste parce que SQL Server suggère un index, doesn39t signifie que vous devez aveuglément créer des index sur chaque quotmissing indexquot message. Toutefois, dans ce cas, SQL Server détecte que nous filtrons en fonction de l'année et en utilisant la clé de produit et le montant des ventes. Ainsi, SQL Server suggère un indice couvrant, avec la DateKey comme le champ de clé d'index. La raison pour laquelle nous appelons cela un quotcoveringquot index est parce que SQL Server quotbring le long des champs non-clé nous avons utilisé dans la requête, quotfor the ridequot. De cette façon, SQL Server n'a pas besoin d'utiliser la table ou l'index clusterisé à tout le moteur de base de données peut simplement utiliser l'index couvrant pour la requête. Les index de couverture sont populaires dans certains scénarios de base de données et d'entreposage de données, bien qu'ils aient un coût de maintenance du moteur de base de données. Remarque: Les index de couverture existent depuis longtemps, donc je n'ai pas encore couvert l'index Columnstore et le magasin de requêtes. Donc, je vais ajouter l'indice de couverture: Si je re-exécuter la même requête que j'ai couru il ya un moment (celle qui a agrégé le montant des ventes pour chaque produit), la requête semble parfois exécuter environ une seconde plus rapide, et je reçois un Plan d'exécution différent, qui utilise une recherche d'index au lieu d'une analyse d'index (en utilisant la clé de date sur l'index couvrant pour récupérer les ventes pour 2009). Ainsi, avant l'index Columnstore, cela pourrait être une façon d'optimiser cette requête dans les versions beaucoup plus anciennes de SQL Server. Il fonctionne un peu plus vite que le premier, et je reçois un plan d'exécution avec un Index Seek au lieu d'un Index Scan. Cependant, il y a quelques problèmes: Les deux opérateurs d'exécution quotIndex Seekquot et quotHash Match (Aggregate) quot fonctionnent essentiellement quotrow par rowquot. Imaginez cela dans une table avec des centaines de millions de lignes. En relation, pensez au contenu d'une table de faits: dans ce cas, une seule valeur de clé de date et / ou une valeur de clé de produit unique peuvent être répétées à travers des centaines de milliers de lignes (rappelez-vous, la table de faits a également des clés pour la géographie, la promotion, le vendeur , Etc.). Ainsi, lorsque le quotIndex Seekquot et le quotHash Matchquot travaillent ligne par ligne, ils le font sur des valeurs qui peuvent être répétées à travers plusieurs autres lignes. C'est normalement là où I39d suit à l'index Columnstore de SQL Server, qui offre un scénario pour améliorer la performance de cette requête de manière étonnante. Mais avant de le faire, retournons dans le temps. Let's revenir à l'année 2010, lorsque Microsoft a introduit un add-in pour Excel connu sous le nom PowerPivot. Beaucoup de gens se souviennent probablement avoir vu des démos de PowerPivot pour Excel, où un utilisateur pouvait lire des millions de lignes d'une source de données externe dans Excel. PowerPivot compresserait les données et fournirait un moteur pour créer des tableaux croisés et des tableaux croisés qui fonctionnaient à des vitesses étonnantes par rapport aux données compressées. PowerPivot a utilisé une technologie en mémoire que Microsoft a appelée quotVertiPaqquot. Cette technologie en mémoire dans PowerPivot prendrait essentiellement dupliquer les clés clés d'entreprise et les compresser vers un seul vecteur. La technologie en mémoire analyserait également ces valeurs en parallèle, en blocs de plusieurs centaines à la fois. Le résultat final est que Microsoft a cuit une grande quantité d'améliorations de la performance dans la fonction en mémoire de VertiPaq pour que nous puissions l'utiliser, à droite de la boîte proverbiale. Pourquoi est-ce que je prends cette petite promenade en bas de la voie de mémoire Puisque dans SQL Server 2012, Microsoft a implémenté une des caractéristiques les plus importantes dans l'histoire de leur moteur de base de données: l'index de Columnstore. L'index est vraiment un index dans le nom uniquement: c'est un moyen de prendre une table SQL Server et de créer un compressed, en colonne columnstore qui compresse les valeurs de clé étrangère en double jusqu'à valeurs vectorielles simples. Microsoft a également créé un nouveau pool de mémoire tampon pour lire ces valeurs vectorielles compressées en parallèle, créant ainsi le potentiel d'énormes gains de performance. Ainsi, je vais créer un index de colonne sur la table, et je verrai combien mieux (et plus efficacement) la requête s'exécute, par rapport à la requête qui s'exécute contre l'indice de couverture. Ainsi, je créerai une copie de FactOnlineSales (I39ll l'appeler FactOnlineSalesDetailNCCS), et I39ll créeront un index de columnstore sur la table dupliquée de cette façon que je won39t interférer avec la table originale et l'indice couvrant en aucune façon. Ensuite, je créerai un index de colonne sur la nouvelle table: Notez plusieurs choses: J'ai spécifié plusieurs colonnes de clés étrangères, ainsi que le montant des ventes. Rappelez-vous qu'un index columnstore n'est pas comme un index traditionnel de magasin de rangée. Il n'y a pas de quotkeyquot. Nous indiquons simplement quelles colonnes SQL Server doit compresser et placer dans un magasin de colonnes en mémoire. Pour utiliser l'analogie de PowerPivot pour Excel lors de la création d'un index de colonne, nous demandons à SQL Server de faire essentiellement la même chose que PowerPivot lorsque nous avons importé 20 millions de lignes dans Excel à l'aide de PowerPivot. La table duplicée FactOnlineSalesDetailNCCS qui contient l'index columnstore. Cette requête s'exécute instantanément en moins d'une seconde. Et je peux aussi dire que même si la table avait des centaines de millions de rangées, il serait toujours courir à la quotbat proverbiale d'un cils. Nous pourrions examiner le plan d'exécution (et dans quelques instants, nous le ferons), mais maintenant il est temps de couvrir la fonctionnalité de magasin de requêtes. Imaginez un instant que nous avons exécuté les deux requêtes du jour au lendemain: la requête qui utilisait la table régulière FactOnlineSales (avec l'index de couverture), puis la requête qui utilisait la table dupliquée avec l'index Columnstore. Lorsque nous nous connectons le lendemain matin, nous aimerions voir le plan d'exécution pour les deux requêtes telles qu'elles ont eu lieu, ainsi que les statistiques d'exécution. En d'autres termes, nous aimerions voir les mêmes statistiques que nous pourrions voir si nous avons exécuté les deux requêtes interactivement dans SQL Management Studio, tourné dans TIME et IO Statistics, et vu le plan d'exécution juste après l'exécution de la requête. Eh bien, c'est ce que le magasin de requêtes nous permet de faire, nous pouvons activer (activer) Query Store pour une base de données, qui va déclencher SQL Server pour stocker l'exécution de la requête et planifier les statistiques afin que nous puissions les afficher plus tard. Donc, je vais activer le magasin de requêtes sur la base de données Contoso avec la commande suivante (et I39ll également effacer toute mise en cache): Puis I39ll exécuter les deux requêtes (et quotpretendquot que je les ai exécuté il ya quelques heures): Maintenant, depuis. Selon ce que j'ai dit, le Query Store va capturer les statistiques d'exécution. Alors, comment puis-je les voir Heureusement, c'est très facile. Si je développe la base de données Contoso DW, je verrai un dossier Query Store. Le Query Store a une fonctionnalité énorme et I39ll essayer de couvrir une grande partie de celui-ci dans les posts suivants. Mais pour le moment, je veux voir les statistiques d'exécution sur les deux requêtes, et examiner spécifiquement les opérateurs d'exécution pour l'index columnstore. Alors, cliquez avec le bouton droit de la souris sur les Requêtes de consommations de ressources principales et exécutez cette option. Cela me donne un graphique comme celui ci-dessous, où je peux voir la durée d'exécution (en millisecondes) pour toutes les requêtes qui ont été exécutées. Dans ce cas, la requête 1 était la requête par rapport à la table d'origine avec l'index de recouvrement et la requête 2 était contre la table avec l'index de la colonne. Les nombres ne sont pas l'indice columnstore surperformed l'indice initial tablecovering index par un facteur de presque 7 à 1. Je peux changer la métrique pour regarder la consommation de mémoire à la place. Dans ce cas, notez que la requête 2 (la requête d'index de colonne) a utilisé beaucoup plus de mémoire. Cela démontre clairement pourquoi l'index columnstore représente la technologie quotin-memoryquot. SQL Server charge l'index columnstore entier en mémoire et utilise un pool de mémoire tampon complètement différent avec des opérateurs d'exécution améliorés pour traiter l'index. Si vous cliquez sur la barre verticale pour la requête qui a utilisé l'index columnstore, vous verrez l'exécution Plan ci-dessous. La première chose que nous voyons est que SQL Server a effectué un scan d'index colonne, et qui représentait près de 100 du coût de la requête. Vous pourriez dire, quotEt attendre une minute, la première requête a utilisé un indice couvrant et a effectué une recherche d'indice ainsi comment peut-être une analyse d'index colonne est plus rapide. C'est une question légitime, et heureusement il y a une réponse. Même lorsque la première requête a effectué une recherche d'index, elle a toujours exécuté quotrow par rowquot. Si je passe la souris sur l'opérateur scan de l'index colonne, je vois une info-bulle (comme celle ci-dessous), avec un paramètre important: le mode d'exécution est BATCH (par opposition à ROW. Indice de couverture). Ce mode BATCH nous indique que SQL Server traite les vecteurs compressés (pour toutes les valeurs de clés étrangères qui sont dupliquées, telles que la clé de produit et la clé de date) dans des lots de près de 1000, en parallèle. Ainsi, SQL Server est toujours en mesure de traiter l'index columnstore beaucoup plus efficacement. De plus, si je place la souris sur la tâche Hash Match (Agrégat), je vois également que SQL Server agrège l'index columnstore en mode Batch (bien que l'opérateur lui-même représente un si petit pourcentage du coût de la requête) SQL Server compresse les valeurs dans les données, traite les valeurs en tant que vecteurs et les lit dans des blocs de près de mille valeurs en parallèle, mais ma requête ne voulait que des données pour 2009. Ainsi, SQL Server scanne Ensemble de données Une fois de plus, une bonne question. La réponse est, quotNot reallyquot. Heureusement pour nous, le nouveau pool de mémoire tampon d'index columnstore exécute une autre fonction appelée quotsegment eliminationquot. Basiquement, SQL Server examinera les valeurs vectorielles de la colonne de la clé de date dans l'index de la colonne et éliminera les segments qui ne sont pas dans la portée de l'année 2009. I39ll s'arrêtera ici. Dans les articles de blog suivants, I39ll couvre l'index columnstore et Query Store en détail. Essentiellement, ce que nous avons vu ici aujourd'hui est que l'index Columnstore peut considérablement accélérer les requêtes qui scanaggregate sur de grandes quantités de données, et le Query Store capture les exécutions de requête et nous permettent d'examiner les statistiques d'exécution et de performance plus tard. En fin de compte, nous aimerions produire un jeu de résultats qui montre ce qui suit. Notez trois choses: Les colonnes pivotent essentiellement toutes les raisons de retour possibles, après avoir indiqué le montant des ventes Le jeu de résultats contient des sous-totaux par la date de fin de semaine (dimanche) sur tous les clients (où le client est NULL) Ligne (où le client et la date sont tous les deux NULL) Tout d'abord, avant que j'obtienne dans la fin de SQL nous pourrions employer la capacité dynamique de pivotmatrix dans SSRS. Nous devrions simplement combiner les deux ensembles de résultats par une colonne et alors nous pourrions alimenter les résultats au contrôle de matrice de SSRS, qui écartera les raisons de retour à travers l'axe de colonnes du rapport. Cependant, tout le monde n'utilise pas SSRS (bien que la plupart des gens devraient). Mais même alors, parfois les développeurs ont besoin de consommer des ensembles de résultats dans autre chose qu'un outil de reporting. Donc, pour cet exemple, supposons que nous voulons générer le jeu de résultats pour une page de grille Web et éventuellement le développeur veut quotstrip outquot les lignes de sous-total (où j'ai une valeur ResultSetNum de 2 et 3) et les placer dans une grille de synthèse. Donc bottom line, nous devons générer la sortie ci-dessus directement à partir d'une procédure stockée. Et comme un twist ajouté la semaine prochaine il pourrait y avoir Retour Raison X et Y et Z. Donc nous don39t savoir combien de raisons de retour il pourrait y avoir. Nous voulons simplement que la requête pivote sur les valeurs distinctes possibles pour la Raison de retour. Voici où le T-SQL PIVOT a une restriction, nous devons lui fournir les valeurs possibles. Puisque nous ne savons pas que jusqu'à l'exécution, nous devons générer la chaîne de requête dynamiquement en utilisant le modèle SQL dynamique. Le pattern SQL dynamique implique de générer la syntaxe, pièce par pièce, de le stocker dans une chaîne, puis d'exécuter la chaîne à la fin. Dynamic SQL peut être délicat, car nous devons intégrer la syntaxe dans une chaîne. Mais dans ce cas, c'est notre seule option vraie si nous voulons gérer un nombre variable de raisons de retour. J'ai toujours trouvé que la meilleure façon de créer une solution SQL dynamique est de déterminer ce que le quotidealquot généré-interrogation serait à la fin (dans ce cas, étant donné les raisons de retour que nous connaissons) et puis l'ingénierie inverse en piecing Ensemble une partie à la fois. Et donc, voici le SQL dont nous avons besoin si nous savions que les raisons de retour (A à D) étaient statiques et ne changeraient pas. La requête fait ce qui suit: Combine les données de SalesData avec les données de ReturnData, où nous quothard-wire le mot Sales comme un type d'action de la table Sales, puis utiliser la raison de retour des données de retour dans la même colonne ActionType. Cela nous donnera une colonne ActionType propre sur laquelle pivoter. Nous combinons les deux instructions SELECT dans une expression de table commune (CTE), qui est fondamentalement une sous-requête de table dérivée que nous utilisons par la suite dans l'instruction suivante (à PIVOT) Une instruction PIVOT contre le CTE, qui somme les dollars pour le Type d'Action Étant dans l'une des valeurs de type d'action possibles. Notez que ce n'est pas le résultat final. Nous plaçons cela dans un CTE qui se lit du premier CTE. La raison en est que nous voulons faire plusieurs regroupements à la fin. L'instruction SELECT finale, qui lit à partir du PIVOTCTE et la combine avec une requête ultérieure sur le même PIVOTCTE, mais où nous mettons en œuvre deux regroupements dans la fonctionnalité GROUPING SETS dans SQL 2008: GROUPING par la date de fin de semaine (dbo. WeekEndingDate) GROUPAGE pour toutes les lignes () Donc, si nous savions avec certitude que nous n'aurions jamais plus de codes de raison de retour, alors ce serait la solution. Cependant, nous devons tenir compte d'autres codes de raison. Donc nous devons générer cette requête entière au-dessus comme une grande chaîne où nous construisons les raisons de retour possibles comme une liste séparée par des virgules. I39m va montrer l'ensemble du code T-SQL pour générer (et exécuter) la requête désirée. Et puis je le diviserai en parties et expliquerai chaque pas. Donc d'abord, voici le code entier pour générer dynamiquement ce que j'ai obtenu ci-dessus. Il ya essentiellement cinq étapes que nous devons couvrir. Étape 1 . Nous savons que quelque part dans le mixage, nous devons générer une chaîne pour cela dans la requête: SalesAmount, Raison A, Raison B, Raison C, Raison D0160016001600160 Ce que nous pouvons faire est construit une expression de table commune temporaire qui combine les quot Colonne avec la liste unique des codes de raison possible. Une fois que nous avons cela dans un CTE, nous pouvons utiliser le petit truc de FOR XML PATH (3939) pour réduire ces lignes en une seule chaîne, mettre une virgule devant chaque ligne que la requête lit, puis utiliser STUFF pour remplacer La première instance d'une virgule avec un espace vide. C'est un truc que vous pouvez trouver dans des centaines de blogs SQL. Donc, cette première partie construit une chaîne appelée ActionString que nous pouvons utiliser plus bas. Étape 2 . Nous savons aussi que nous souhaitons SOMME les colonnes de raison générées générées, ainsi que la colonne de vente standard. Nous aurons donc besoin d'une chaîne séparée pour cela, appelée SUMSTRING. I39ll utiliser simplement l'ActionString d'origine, puis REMPLACER les parenthèses externes avec la syntaxe SUM, plus les parenthèses d'origine. Étape 3: Maintenant le véritable travail commence. En utilisant cette requête originale en tant que modèle, nous voulons générer la requête originale (en commençant par l'UNION des deux tables), mais en remplaçant toute référence aux colonnes pivotées par les chaînes générées dynamiquement ci-dessus. De plus, bien que ce ne soit pas absolument nécessaire, nous avons également créé une variable pour tout simplement les combinaisons de flux de chariot à retourner que nous voulons intégrer dans la requête générée (pour la lisibilité). So we39ll construire la requête entière dans une variable appelée SQLPivotQuery. Étape 4 . Nous continuons à construire la requête à nouveau, en concaténant la syntaxe que nous pouvons quothard-wirequot avec l'ActionSelectString (que nous avons généré dynamiquement pour contenir toutes les valeurs de raison de retour possible) Étape 5. Enfin, nous générons la partie finale de la requête Pivot, qui lit à partir de la 2ème expression de table commune (PIVOTCTE, du modèle ci-dessus) et génère la SELECT finale à lire de la PIVOTCTE et la combine avec une 2ème lecture contre PIVOTCTE Mettre en œuvre les ensembles de regroupement. Enfin, nous pouvons citerexécuter la chaîne à l'aide du système SQL stocké proc spexecuteSQL Donc j'espère que vous pouvez voir que le processus de suivi pour ce type d'effort est Déterminer ce que la requête finale serait, en fonction de votre ensemble actuel de données et valeurs Un modèle de requête) Rédigez le code T-SQL nécessaire pour générer ce modèle de requête sous forme de chaîne. Peut-être la partie la plus importante est de déterminer l'ensemble unique de valeurs sur lesquelles youll PIVOT, puis de les effondrer en une seule chaîne à l'aide de la fonction STUFF et la trame FOR XML PATH (3939) Donc ce qui est dans mon esprit aujourd'hui Eh bien, au moins 13 articles Deux Il ya quelques années, j'ai écrit un projet de BDR qui a mis l'accent (en partie) sur le rôle de l'éducation et la valeur d'un bon art des arts libéraux non seulement pour l'industrie du logiciel, mais même pour d'autres industries ainsi. Un des thèmes de ce BDR particulier a souligné un point de vue pivot et éclairé de l'architecte de logiciel de renommée Allen Holub concernant les arts libéraux. Illustrant fidèlement son message: il met en évidence les parallèles entre la programmation et l'étude de l'histoire, en rappelant à tout le monde que l'histoire est la lecture et l'écriture, et le développement logiciel est aussi la lecture et l'écriture ). Et donc j'ai écrit un article d'opinion qui s'est concentré sur ceci et d'autres sujets connexes. Mais jusqu'à aujourd'hui, je n'ai jamais eu le temps de publier. De temps en temps, je pense à la réviser, et je me suis même asseoir pendant quelques minutes et faire quelques ajustements. Mais alors la vie en général obtiendrait dans la manière et l'Id ne finira jamais. Donc, ce qui a changé Il ya quelques semaines, collègues chroniqueur CoDe Magazine et leader de l'industrie Ted Neward a écrit un morceau dans sa colonne régulière, Managed Coder, qui a attiré mon attention. Le titre de l'article est On Liberal Arts. Et je recommande fortement que tout le monde le lire. Ted discute de la valeur d'un milieu des arts libéraux, de la fausse dichotomie entre les arts libéraux et le succès dans le développement de logiciels, et la nécessité de bien communiquer. Il parle de certaines de ses propres rencontres passées avec la gestion du personnel RH en ce qui concerne ses études. Il souligne également la nécessité d'accepter et de s'adapter aux changements dans notre industrie, ainsi que les caractéristiques d'un logiciel professionnel réussi (être fiable, planifier à l'avance et apprendre à passer le conflit initial avec les autres membres de l'équipe). Donc c'est une grande lecture, tout comme Teds d'autres articles CoDe et entrées de blog. Cela m'a également permis de réfléchir à mes points de vue à ce sujet (et à d'autres sujets), et finalement m'a motivé à terminer mon propre éditorial. Donc, mieux vaut tard que jamais, voici mes boulangers actuels Douzaine de réflexions: J'ai un dicton: L'eau gèle à 32 degrés. Si vous êtes dans un rôle trainingmentoring, vous pourriez penser que vous faites tout dans le monde pour aider quelqu'un quand en fait, theyre seulement sentir une température de 34 degrés et donc les choses arent solidifier pour eux. Parfois, il faut juste un peu plus d'effort ou un autre catalyseur ideachemical ou une nouvelle perspective qui signifie que ceux ayant une éducation préalable peut s'appuyer sur différentes sources. L'eau gèle à 32 degrés. Certaines personnes peuvent maintenir des niveaux élevés de concentration, même avec une salle pleine de gens bruyants. Je ne suis pas un d'entre eux à l'occasion, j'ai besoin d'une certaine intimité pour réfléchir à un problème critique. Certaines personnes décrivent cela comme vous devez apprendre à s'en éloigner. D'une autre façon, c'est une recherche de l'air raréfié. La semaine passée, j'ai passé des heures dans une pièce calme, à moitié éclairée, avec un tableau blanc, jusqu'à ce que je comprenne parfaitement un problème. C'est seulement à ce moment-là que je pouvais aller discuter avec d'autres développeurs d'une solution. Le message ici n'est pas de prêcher comment vous devriez aller sur votre entreprise de résoudre des problèmes, mais plutôt pour chacun de connaître leurs forces et ce qui fonctionne, et de les utiliser à votre avantage autant que possible. Certaines phrases sont comme des ongles sur un tableau pour moi. Utilisez-le comme un moment d'enseignement est un. (Pourquoi est-ce comme les ongles sur un tableau noir car si vous êtes dans un rôle de mentor, vous devriez généralement être en mode moment d'enseignement de toute façon, cependant subtilement). Heres un autre je ne peux pas vraiment l'expliquer en mots, mais je le comprends. Cela pourrait sembler un peu froid, mais si une personne vraiment ne peut pas expliquer quelque chose en mots, peut-être qu'ils ne comprennent pas. Bien sûr, une personne peut avoir un sens flou de la façon dont quelque chose fonctionne Je peux bluff ma façon de décrire comment fonctionne un appareil photo numérique, mais la vérité est que je ne comprends pas vraiment tout cela bien. Il existe un champ d'étude connu sous le nom d'épistémologie (l'étude de la connaissance). One of the fundamental bases of understanding whether its a camera or a design pattern - is the ability to establish context, to identify the chain of related events, the attributes of any components along the way, etc. Yes, understanding is sometimes very hard work, but diving into a topic and breaking it apart is worth the effort. Even those who eschew certification will acknowledge that the process of studying for certification tests will help to fill gaps in knowledge. A database manager is more likely to hire a database developer who can speak extemporaneously (and effortlessly) about transaction isolation levels and triggers, as opposed to someone who sort of knows about it but struggles to describe their usage. Theres another corollary here. Ted Neward recommends that developers take up public speaking, blogging, etc. I agree 100. The process of public speaking and blogging will practically force you to start thinking about topics and breaking down definitions that you might have otherwise taken for granted. A few years ago I thought I understood the T-SQL MERGE statement pretty well. But only after writing about it, speaking about, fielding questions from others who had perspectives that never occurred to me that my level of understanding increased exponentially. I know a story of a hiring manager who once interviewed an authordeveloper for a contract position. The hiring manager was contemptuous of publications in general, and barked at the applicant, So, if youre going to work here, would you rather be writing books or writing code Yes, Ill grant that in any industry there will be a few pure academics. But what the hiring manager missed was the opportunities for strengthening and sharpening skill sets. While cleaning out an old box of books, I came across a treasure from the 1980s: Programmers at Work. which contains interviews with a very young Bill Gates, Ray Ozzie, and other well-known names. Every interview and every insight is worth the price of the book. In my view, the most interesting interview was with Butler Lampson. who gave some powerful advice. To hell with computer literacy. Its absolutely ridiculous. Study mathematics. Learn to think. Read. Write. These things are of more enduring value. Learn how to prove theorems: A lot of evidence has accumulated over the centuries that suggests this skill is transferable to many other things. Butler speaks the truth . Ill add to that point learn how to play devils advocate against yourself. The more you can reality-check your own processes and work, the better off youll be. The great computer scientistauthor Allen Holub made the connection between software development and the liberal arts specifically, the subject of history. Here was his point: what is history Reading and writing. What is software development Among other things, reading and writing . I used to give my students T-SQL essay questions as practice tests. One student joked that I acted more like a law professor. Well, just like Coach Donny Haskins said in the movie Glory Road, my way is hard. I firmly believe in a strong intellectual foundation for any profession. Just like applications can benefit from frameworks, individuals and their thought processes can benefit from human frameworks as well. Thats the fundamental basis of scholarship. There is a story that back in the 1970s, IBM expanded their recruiting efforts in the major universities by focusing on the best and brightest of liberal arts graduates. Even then they recognized that the best readers and writers might someday become strong programmersystems analysts. (Feel free to use that story to any HR-type who insists that a candidate must have a computer science degree) And speaking of history: if for no other reason, its important to remember the history of product releases if Im doing work at a client site thats still using SQL Server 2008 or even (gasp) SQL Server 2005, I have to remember what features were implemented in the versions over time. Ever have a favorite doctor whom you liked because heshe explained things in plain English, gave you the straight truth, and earned your trust to operate on you Those are mad skills . and are the result of experience and HARD WORK that take years and even decades to cultivate. There are no guarantees of job success focus on the facts, take a few calculated risks when youre sure you can see your way to the finish line, let the chips fall where they may, and never lose sight of being just like that doctor who earned your trust. Even though some days I fall short, I try to treat my client and their data as a doctor would treat patients. Even though a doctor makes more money There are many clichs I detest but heres one I dont hate: There is no such thing as a bad question. As a former instructor, one thing that drew my ire was hearing someone criticize another person for asking a supposedly, stupid question. A question indicates a person acknowledges they have some gap in knowledge theyre looking to fill. Yes, some questions are better worded than others, and some questions require additional framing before they can be answered. But the journey from forming a question to an answer is likely to generate an active mental process in others. There are all GOOD things. Many good and fruitful discussions originate with a stupid question. I work across the board in SSIS, SSAS, SSRS, MDX, PPS, SharePoint, Power BI, DAX all the tools in the Microsoft BI stack. I still write some. NET code from time to time. But guess what I still spend so much time doing writing T-SQL code to profile data as part of the discovery process. All application developers should have good T-SQL chops. Ted Neward writes (correctly) about the need to adapt to technology changes. Ill add to that the need to adapt to clientemployer changes. Companies change business rules. Companies acquire other companies (or become the target of an acquisition). Companies make mistakes in communicating business requirements and specifications. Yes, we can sometimes play a role in helping to manage those changes and sometimes were the fly, not the windshield. These sometimes cause great pain for everyone, especially the I. T. people. This is why the term fact of life exists we have to deal with it. Just like no developer writes bug-free code every time, no I. T. person deals well with change every single time. One of the biggest struggles Ive had in my 28 years in this industry is showing patience and restraint when changes are flying from many different directions. Here is where my prior suggestion about searching for the rarified air can help. If you can manage to assimilate changes into your thought process, and without feeling overwhelmed, odds are youll be a significant asset. In the last 15 months Ive had to deal with a huge amount of professional change. Its been very difficult at times, but Ive resolved that change will be the norm and Ive tried to tweak my own habits as best I can to cope with frequent (and uncertain) change. Its hard, very hard. But as coach Jimmy Duggan said in the movie A League of Their Own: Of course its hard. If it wasnt hard, everyone would do it. The hard, is what makes it great . A powerful message. Theres been talk in the industry over the last few years about conduct at professional conferences (and conduct in the industry as a whole). Many respected writers have written very good editorials on the topic. Heres my input, for what its worth. Its a message to those individuals who have chosen to behave badly: Dude, it shouldnt be that hard to behave like an adult. A few years ago, CoDe Magazine Chief Editor Rod Paddock made some great points in an editorial about Codes of Conduct at conferences. Its definitely unfortunate to have to remind people of what they should expect out of themselves. But the problems go deeper. A few years ago I sat on a five-person panel (3 women, 2 men) at a community event on Women in Technology. The other male stated that men succeed in this industry because the Y chromosome gives men an advantage in areas of performance. The individual who made these remarks is a highly respected technology expert, and not some bozo making dongle remarks at a conference or sponsoring a programming contest where first prize is a date with a bikini model. Our world is becoming increasingly polarized (just watch the news for five minutes), sadly with emotion often winning over reason. Even in our industry, recently I heard someone in a position of responsibility bash software tool XYZ based on a ridiculous premise and then give false praise to a competing tool. So many opinions, so many arguments, but heres the key: before taking a stand, do your homework and get the facts . Sometimes both sides are partly rightor wrong. Theres only one way to determine: get the facts. As Robert Heinlein wrote, Facts are your single clue get the facts Of course, once you get the facts, the next step is to express them in a meaningful and even compelling way. Theres nothing wrong with using some emotion in an intellectual debate but it IS wrong to replace an intellectual debate with emotion and false agenda. A while back I faced resistance to SQL Server Analysis Services from someone who claimed the tool couldnt do feature XYZ. The specifics of XYZ dont matter here. I spent about two hours that evening working up a demo to cogently demonstrate the original claim was false. In that example, it worked. I cant swear it will always work, but to me thats the only way. Im old enough to remember life at a teen in the 1970s. Back then, when a person lost hisher job, (often) it was because the person just wasnt cutting the mustard. Fast-forward to today: a sad fact of life is that even talented people are now losing their jobs because of the changing economic conditions. Theres never a full-proof method for immunity, but now more than ever its critical to provide a high level of what I call the Three Vs (value, versatility, and velocity) for your employerclients. I might not always like working weekends or very late at night to do the proverbial work of two people but then I remember there are folks out there who would give anything to be working at 1 AM at night to feed their families and pay their bills. Always be yourselfyour BEST self. Some people need inspiration from time to time. Heres mine: the great sports movie, Glory Road. If youve never watched it, and even if youre not a sports fan I can almost guarantee youll be moved like never before. And Ill close with this. If you need some major motivation, Ill refer to a story from 2006. Jason McElwain, a high school student with autism, came off the bench to score twenty points in a high school basketball game in Rochester New York. Heres a great YouTube video. His mother said it all . This is the first moment Jason has ever succeeded and is proud of himself. I look at autism as the Berlin Wall. He cracked it. To anyone who wanted to attend my session at todays SQL Saturday event in DC I apologize that the session had to be cancelled. I hate to make excuses, but a combination of getting back late from Detroit (client trip), a car thats dead (blown head gasket), and some sudden health issues with my wife have made it impossible for me to attend. Back in August, I did the same session (ColumnStore Index) for PASS as a webinar. You can go to this link to access the video (itll be streamed, as all PASS videos are streamed) The link does require that you fill out your name and email address, but thats it. And then you can watch the video. Feel free to contact me if you have questions, at kgoffkevinsgoff. net November 15, 2013 Getting started with Windows Azure and creating SQL Databases in the cloud can be a bit daunting, especially if youve never tried out any of Microsofts cloud offerings. Fortunately, Ive created a webcast to help people get started. This is an absolute beginners guide to creating SQL Databases under Windows Azure. It assumes zero prior knowledge of Azure. You can go to the BDBI Webcasts of this website and check out my webcast (dated 11102013). Or you can just download the webcast videos right here: here is part 1 and here is part 2. You can also download the slide deck here. November 03, 2013 Topic this week: SQL Server Snapshot Isolation Levels, added in SQL Server 2005. To this day, there are still many SQL developers, many good SQL developers who either arent aware of this feature, or havent had time to look at it. Hopefully this information will help. Companion webcast will be uploaded in the next day look for it in the BDBI Webcasts section of this blog. October 26, 2013 Im going to start a weekly post of T-SQL tips, covering many different versions of SQL Server over the years Heres a challenge many developers face. Ill whittle it down to a very simple example, but one where the pattern applies to many situations. Suppose you have a stored procedure that receives a single vendor ID and updates the freight for all orders with that vendor id. create procedure dbo. UpdateVendorOrders update Purchasing. PurchaseOrderHeader set Freight Freight 1 where VendorID VendorID Now, suppose we need to run this for a set of vendor IDs. Today we might run it for three vendors, tomorrow for five vendors, the next day for 100 vendors. We want to pass in the vendor IDs. If youve worked with SQL Server, you can probably guess where Im going with this. The big question is how do we pass a variable number of Vendor IDs Or, stated more generally, how do we pass an array, or a table of keys, to a procedure Something along the lines of exec dbo. UpdateVendorOrders SomeListOfVendors Over the years, developers have come up with different methods: Going all the way back to SQL Server 2000, developers might create a comma-separated list of vendor keys, and pass the CSV list as a varchar to the procedure. The procedure would shred the CSV varchar variable into a table variable and then join the PurchaseOrderHeader table to that table variable (to update the Freight for just those vendors in the table). I wrote about this in CoDe Magazine back in early 2005 (code-magazinearticleprint. aspxquickid0503071ampprintmodetrue. Tip 3) In SQL Server 2005, you could actually create an XML string of the vendor IDs, pass the XML string to the procedure, and then use XQUERY to shred the XML as a table variable. I also wrote about this in CoDe Magazine back in 2007 (code-magazinearticleprint. aspxquickid0703041ampprintmodetrue. Tip 12)Also, some developers will populate a temp table ahead of time, and then reference the temp table inside the procedure. All of these certainly work, and developers have had to use these techniques before because for years there was NO WAY to directly pass a table to a SQL Server stored procedure. Until SQL Server 2008 when Microsoft implemented the table type. This FINALLY allowed developers to pass an actual table of rows to a stored procedure. Now, it does require a few steps. We cant just pass any old table to a procedure. It has to be a pre-defined type (a template). So lets suppose we always want to pass a set of integer keys to different procedures. One day it might be a list of vendor keys. Next day it might be a list of customer keys. So we can create a generic table type of keys, one that can be instantiated for customer keys, vendor keys, etc. CREATE TYPE IntKeysTT AS TABLE ( IntKey int NOT NULL ) So Ive created a Table Typecalled IntKeysTT . Its defined to have one column an IntKey. Nowsuppose I want to load it with Vendors who have a Credit Rating of 1..and then take that list of Vendor keys and pass it to a procedure: DECLARE VendorList IntKeysTT INSERT INTO VendorList SELECT BusinessEntityID from Purchasing. Vendor WHERE CreditRating 1 So, I now have a table type variable not just any table variable, but a table type variable (that I populated the same way I would populate a normal table variable). Its in server memory (unless it needs to spill to tempDB) and is therefore private to the connectionprocess. OK, can I pass it to the stored procedure now Well, not yet we need to modify the procedure to receive a table type. Heres the code: create procedure dbo. UpdateVendorOrdersFromTT IntKeysTT IntKeysTT READONLY update Purchasing. PurchaseOrderHeader set Freight Freight 1 FROM Purchasing. PurchaseOrderHeader JOIN IntKeysTT TempVendorList ON PurchaseOrderHeader. VendorID Te mpVendorList. IntKey Notice how the procedure receives the IntKeysTT table type as a Table Type (again, not just a regular table, but a table type). It also receives it as a READONLY parameter. You CANNOT modify the contents of this table type inside the procedure. Usually you wont want to you simply want to read from it. Well, now you can reference the table type as a parameter and then utilize it in the JOIN statement, as you would any other table variable. So there you have it. A bit of work to set up the table type, but in my view, definitely worth it. Additionally, if you pass values from. NET, youre in luck. You can pass an ADO. NET data table (with the same tablename property as the name of the Table Type) to the procedure. For. NET developers who have had to pass CSV lists, XML strings, etc. to a procedure in the past, this is a huge benefit. Finally I want to talk about another approach people have used over the years. SQL Server Cursors. At the risk of sounding dogmatic, I strongly advise against Cursors, unless there is just no other way. Cursors are expensive operations in the server, For instance, someone might use a cursor approach and implement the solution this way: DECLARE VendorID int DECLARE dbcursor CURSOR FASTFORWARD FOR SELECT BusinessEntityID from Purchasing. Vendor where CreditRating 1 FETCH NEXT FROM dbcursor INTO VendorID WHILE FETCHSTATUS 0 EXEC dbo. UpdateVendorOrders VendorID FETCH NEXT FROM dbcursor INTO VendorID The best thing Ill say about this is that it works. And yes, getting something to work is a milestone. But getting something to work and getting something to work acceptably are two different things. Even if this process only takes 5-10 seconds to run, in those 5-10 seconds the cursor utilizes SQL Server resources quite heavily. Thats not a good idea in a large production environment. Additionally, the more the of rows in the cursor to fetch and the more the number of executions of the procedure, the slower it will be. When I ran both processes (the cursor approach and then the table type approach) against a small sampling of vendors (5 vendors), the processing times where 260 ms and 60 ms, respectively. So the table type approach was roughly 4 times faster. But then when I ran the 2 scenarios against a much larger of vendors (84 vendors), the different was staggering 6701 ms versus 207 ms, respectively. So the table type approach was roughly 32 times faster. Again, the CURSOR approach is definitely the least attractive approach. Even in SQL Server 2005, it would have been better to create a CSV list or an XML string (providing the number of keys could be stored in a scalar variable). But now that there is a Table Type feature in SQL Server 2008, you can achieve the objective with a feature thats more closely modeled to the way developers are thinking specifically, how do we pass a table to a procedure Now we have an answer Hope you find this feature help. Feel free to post a comment.
No comments:
Post a Comment