Disons que vous avez une table avec environ 5 millions d'enregistrements et une colonne nvarchar (max) rempli de grandes données de texte. Vous voulez définir cette colonne sur NULL si SomeOtherColumn 1 de la manière la plus rapide possible. La mise à jour de la force brute ne fonctionne pas très bien ici, car elle va créer de grandes transactions implicites et prendre une éternité. Faire des mises à jour dans de petits lots de 50K enregistre à la fois des travaux mais ses toujours en prenant 47 heures pour terminer sur beefy 32 core64GB serveur. Existe-t-il un moyen de faire cette mise à jour plus rapidement? Existe-t-il des options de tables de conseils de requête magique qui sacrifie quelque chose d'autre (comme la concurrence) en échange de la vitesse REMARQUE: Créer une table temporaire ou une colonne temporaire n'est pas une option car cette colonne nvarchar De données et donc consomme beaucoup d'espace PS: Oui, SomeOtherColumn est déjà indexé. Je suis d'accord, nous faisons régulièrement des mises à jour comme celle-ci sur des tables avec 50Million ou même 500Million enregistrements et cela arrive en quelques secondes. Je suppose que le plan de requête sélectionné n'est pas très optimal et prendre beaucoup de temps. J'ai eu ceci arrivé à moi quand il ya une contrainte étrangère de clef sur une autre table sur une colonne non indexée. Après avoir regardé le plan de requête, nous avons réalisé qu'il devait scanner l'autre table pour chaque suppression qui était le coupable. Cela avait 23 millions de lignes, l'indexation de l'autre table a ramené à moins de 5 secondes. Ndash Cobusve Jun 7 10 at 10:46 De tout ce que je peux voir, il ne ressemble pas à vos problèmes sont liés aux index. La clé semble être dans le fait que votre champ nvarchar (max) contient beaucoup de données. Pensez à ce que SQL doit faire pour effectuer cette mise à jour. Étant donné que la colonne que vous mettez à jour est probablement plus de 8000 caractères, il est stocké hors page, ce qui implique un effort supplémentaire dans la lecture de cette colonne quand il n'est pas NULL. Lorsque vous exécutez un lot de 50000 mises à jour SQL doit placer cela dans une transaction implicite afin de permettre de revenir en arrière en cas de tout problème. Pour revenir en arrière il doit stocker la valeur d'origine de la colonne dans le journal des transactions. En supposant que chaque colonne contient en moyenne 10 000 octets de données, cela signifie que 50 000 lignes contiendront environ 500 Mo de données, qui doivent être stockées temporairement (en mode de récupération simple) ou permanentes (en mode de récupération complète). Il n'existe aucun moyen de désactiver les journaux car cela compromettra l'intégrité de la base de données. J'ai couru un test rapide sur mon bureau de chien lent, et les lots en cours de même 10 000 devient prohibitivement lent, mais porter la taille à 1000 lignes, ce qui implique une taille de journal temporaire d'environ 10 Mo, a fonctionné très bien. J'ai chargé une table avec 350.000 lignes et marqué 50.000 d'entre eux pour la mise à jour. Cela a été complété en environ 4 minutes, et comme il s'échelonne linéairement, vous devriez être en mesure de mettre à jour vos 5 millions de lignes entières sur mon bureau lent chien en environ 6 heures sur mon bureau de 1 processeur 2 Go, donc je m'attends à quelque chose de bien meilleur sur votre serveur costaud soutenu Par SAN ou quelque chose. Vous pouvez exécuter votre instruction de mise à jour en tant que sélection, en sélectionnant uniquement la clé primaire et la grande colonne nvarchar, et assurez-vous que cela fonctionne aussi rapidement que prévu. Bien sûr, le goulot d'étranglement peut être d'autres utilisateurs verrouiller les choses ou la contention sur votre stockage ou la mémoire sur le serveur, mais comme vous n'avez pas mentionné d'autres utilisateurs, je suppose que vous avez le DB en mode utilisateur unique pour cela. En tant qu'optimisation, vous devez vous assurer que les journaux de transactions se trouvent sur un autre groupe de disques physiques que les données pour minimiser les temps de recherche. Cela m'a vraiment aidé. Je suis passé de 2 heures à 20 minutes avec cela. D'après mon expérience, travailler dans MSSQL 2005, déplacer quotidiennement (automatiquement) 4 millions de dossiers de 46 octets (pas de nvarchar (max) cependant) d'une table dans une base de données à une autre table dans une base de données différente prend environ 20 minutes dans un QuadCore 8GB , 2Ghz serveur et il doesnt blesser la performance de l'application. En passant, je veux dire INSERT INTO SELECT, puis DELETE. L'utilisation du processeur ne dépasse jamais 30, même lorsque la table supprimée a 28M enregistrements et il constamment fait autour de 4K insert par minute, mais pas de mises à jour. Eh bien, thats mon cas, il peut varier en fonction de votre charge de serveur. Spécifie que les instructions (vos mises à jour) peuvent lire les lignes qui ont été modifiées par d'autres transactions mais qui n'ont pas encore été validées. Dans mon cas, les enregistrements sont en lecture seule. Je ne sais pas ce que rg-tsql signifie, mais ici vous trouverez des informations sur les niveaux d'isolement des transactions dans MSSQL. Soyez toujours prudent et assurez-vous de comprendre les implications de la lecture des transactions non sollicitées. Oui, votre processus n'aura pas à attendre que les transactions ouvertes s'engagent avant de supprimer des éléments, mais bien sûr si la transaction n'est pas engagée après tout cela signifierait que vous supprimiez la ligne incorrectement. Ndash Cobusve Jun 7 10 at 10:43 Si vous exécutez un environnement de production avec pas assez d'espace pour dupliquer toutes vos tables, je crois que vous êtes à la recherche de problèmes tôt ou tard. Si vous fournissez quelques informations sur le nombre de lignes avec SomeOtherColumn1, peut-être pouvons-nous penser autrement, mais je suggère: 0) Sauvegardez votre table 1) Index de la colonne flag 2) Définissez l'option table à aucune log tranctions. Si possible 3) écrire une procédure stockée pour exécuter les mises à jour répondu Jun 2 10 à 3:17 BTW. Vous allez avoir besoin d'exécuter cette procédure plus d'une fois dans une vie ndash Dr. belisarius Jun 2 10 at 3:24 Comment pouvez-vous mettre l'option de table à quotno log tranctionsquot ndash User356004 Jun 7 10 at 9:56 Your Answer 2017 Stack Exchange , IncIntroduction Avec la libération de SQL Server 2016 Service Pack 1, la technologie ColumnStore en mémoire est désormais disponible dans les éditions Standard, Web et même Express et LocalDB. Outre le bénéfice d'un seul codebase à maintenir, cette modification de la politique deviendra également un économiseur d'espace de stockage de disque clair en raison de ses taux élevés de déduplication et de compression de données et, last but not least, son également une performance de requête ad hoc graves Booster La principale différence entre les flavours SQL est la quantité de puissance du processeur et la mémoire est alloué à des tâches telles que (reconstruction) de l'index Clustered ColumnStore. Par exemple: avec l'édition standard, un noyau unique (maximum 100 processeurs du processus sqlservr) est utilisé et l'interrogation d'un CCI se fait avec un maximum de 2 processeurs (MAXDOP2) par rapport à l'utilisation de toutes les CPU disponibles dans Enterprise Edition. Créer un CCI avec SQL Server 2016 Standard Edition: Construire un CCI avec les 4 cœurs disponibles avec SQL Server 2016 Enterprise Edition: Les horaires de base pour le chargement de 7,2 Go 60 millions de lignes à partir d'un seul TPCH lineItem fichiers ne montre pas beaucoup de Une différence entre les saveurs lorsque Bulk insérer les données directement dans une table de tas ou une table avec un CCI la différence devient claire lorsque nous comparons le temps nécessaire pour construire un CCI sur une table de tas ou de reconstruire un CCI: La manière la plus rapide d'avoir des données disponibles dans une table avec un Index de la colonne ColumnStore est de: charger dans la construction de tas avec la SQL 2016 Ent. Ed. Chargement direct dans CCI Pour les tables disposant d'un index ColumnStore en grappe déjà créé, assurez-vous de diffuser directement dans les groupes de lignes compressées afin de maximiser le débit. Pour ce faire, la taille du lot d'insertion doit être égale ou supérieure à 100K lignes (102400 pour être précis). Les lots plus petits seront écrits dans des tables comprimées delta stocker d'abord avant d'être tuplé déplacé dans ses segments de groupe de lignes comprimés final, ce qui signifie que SQL Server doit toucher les données deux fois: Il ya plusieurs options pour charger des données et nous passerons sur les plus fréquemment utilisés Comme la commande Bulk Insert, BCP et SSIS. Permet de voir ce qui est nécessaire pour obtenir les meilleures performances et comment surveiller 1) Insertion en bloc T-SQL Commençons par la commande BULK INSERT: Vérification du chargement des données Pour vérifier le nombre de lignes déjà chargées dans le CCI, L'option de verrouillage de table est utilisée, interrogez un nouveau dmv appelé sys. dmdbcolumnstorerowgroupphysicalstats: Cette DMV révélera également les états possibles du groupe de ressources en plus de détails lors du chargement. Il existe quatre états de groupe de lignes possibles pendant le chargement des données. Lorsque vous voyez l'état INVISBILE comme dans l'image ci-dessous signifie que les données sont compressées en un RowGroup. 1: OPEN160160160160160160160 (RowGroup accepte de nouveaux enregistrements) 2: CLOSED160160160 (RowGroup est rempli, mais pas encore compressé par le processus de déplacement de tuple) 3: COMPRESSED160 (RowGroup est en cours de construction à partir de données dans la mémoire delta) RowGroup est rempli et compressé). 4 TOMBSTONE160 (RowGroup est prêt à être récupéré et supprimé) En spécifiant la taille du lot avec une valeur de 102400 ou plus vous obtiendrez des performances maximales et les données seront transmises en continu et compressées directement dans son RG final ce comportement apparaîtra COMPRESSÉ. Vous pouvez également vérifier un DMV qui a été introduit avec SQL2014 pour vérifier sur l'état RowGroup, qui est le sys. columnstorerowgroups DMV: Résultat de test Bulk insérer des données dans une table avec CCI via la commande Bulk Insert peut légèrement être améliorée en ajoutant le Batchsize102400 et TABLOCK options. Ceci améliore l'amélioration du débit. 2) BCP. exe L'utilitaire BCP est encore très utilisé dans de nombreux environnements de production, ce qui vaut la peine de le vérifier rapidement: par défaut, le BCP invoque 1000 lignes au moment de SQL Server. Le temps nécessaire pour charger 7,2 Go de données via BCP: 530 secondes. Or160 113K rowssec L'état RowGroup affiche NVISIBLE, ce qui signifie que, avec les paramètres par défaut, Delta Store est utilisé. Pour s'assurer que la commande BCP transmet les données directement dans les RG compressés, vous devez ajouter l'option batchsize b d'une valeur d'au moins 102400. J'ai exécuté divers tests avec de plus grandes tailles de lot: jusqu'à 1048576, mais le 102400 m'a donné le meilleur résultat. BCP DB. dbo. LINEITEMCCI dans F: TPCHlineitem. tbl S. - c - T - tquotquot - b 102400 h tablock L'état RowGroup affiche maintenant COMPRESSED, ce qui signifie que nous contournons le Delta Store et les flux de données dans les RG compressés: Résultat: le BCP Terminé en 457 secondes ou 133K lignes par seconde ou Au cours des tests, j'ai remarqué que les paramètres par défaut du SSIS 2016 utilisent des tailles de mémoire tampon qui peuvent également potentiellement limiter la taille du lot à devenir moins de 100K lignes. Dans l'exemple ci-dessous, vous voyez que les données sont stockées dans les magasins delta: les états RG sont fermés et les champs deltastorehobtid sont remplis, ce qui signifie que les magasins delta sont exploités. C'était le moment de communiquer avec mes collègues qui ont heureusement remarqué cela et une solution est déjà là (voir: la capacité de calibrage automatique du flux de données à capacité tampon permet de charger les données dans CCI). Pour exploiter pleinement les capacités de diffusion en continu de la CCI, vous devez augmenter la mémoire par défaut BufferSize amp Paramètres MaxRows: Changez ces valeurs en 10x: 8211 DefaultMaxBufferRows de 10000 à 1024000 et le plus important: 8211 DefaultBufferSize de 10485760 à 104857600. Remarque: le nouveau paramètre AutoAdjustBufferSize doit être défini sur True lorsque vous chargez des lignes de données très larges. Modifiez également les valeurs de l'adaptateur Destination: 8211 Lignes par lot: 160 de none vers 102400 8211 Maximum Insérer la taille de validation: de 2147483647 à 102400 La parité de fonctionnalité introduite avec SQL Server 2016 SP1 ouvre une toute nouvelle gamme de possibilités pour bénéficier de Hopefully Les explications ci-dessus vous aident à maximiser les performances d'insertion en bloc, de BCP et de SSIS lors du chargement de données dans un index ColumnStore en grappe. Quel sera le moyen le plus rapide absolue de charger des données à partir d'un fichier plat dans une table dans SQL Server 2016 Beaucoup a changé depuis mon initial Post sur ce sujet il ya de nombreuses années, ike l'introduction de la mémoire en optimisé tables et Updateable Columnstore table indexes. De plus, la liste des véhicules de transport de données à choisir est en croissance: outre BCP, la commande T-SQL Bulk Insert, SSIS comme outil ETL et PowerShell, il y en a de nouveaux ajoutés, comme PolyBase, External R Script ou ADF. Dans ce post, je vais commencer par vérifier combien plus rapide le nouvel ampli durable non durables dans les tables en mémoire sont la configuration de la ligne de base pour ces tests Im utilisant un Azure DS4V2 Standard VM avec 8 cores28 Go de RAM et 2 HDD Volumes avec cache de l'hôte RW activé. (Les deux Luns fournissent 275 Mo de débit RW, bien que l'interface graphique indique une limite de 60 Mo). J'ai généré un seul 60 millions de lignes 7,2 Gigabyte TPCH lineitem fichier plat comme des données à charger. Comme ligne de base à utiliser pour la comparaison, nous allons utiliser le temps qu'il faut pour charger le fichier dans une table Heap: Cette commande ordinaire Bulk Insert se termine en 7 minutes avec une moyenne de 143K rowssec. Activation de la base de données de test pour les tables optimisées en mémoire Les tables introduites en mémoire (dans SQL20142016 Enterprise amp développement) ont été conçues pour OLTP très rapide avec de nombreuses petites transactions et une forte simultanéité, un type de charge de travail complètement différent de l'insertion en masse. Hors de curiositylets lui donner un essai Il ya 2 types de tableaux en mémoire: tables durables et non durables. Les durables persisteront les données sur le disque, les non-durables l'habitude. Pour activer cette option, nous devons faire quelques tâches ménagères et attribuer un volume de disque rapide pour l'hébergement de ces fichiers. Tout d'abord, modifiez la base de données pour activer l'option Contains MEMORYOPTIMIZEDDATA suivie de l'ajout d'un emplacement de fichier et d'un groupe de fichiers qui contiendra les tables Memory-Optimized: La troisième chose à faire est d'ajouter un pool de mémoire séparé à l'instance de SQL Server Les données que nous chargerons dans des tables en mémoire séparées de leur pool de mémoire par défaut: Liaison d'une base de données à un pool de mémoire Les étapes pour définir un pool de mémoire séparé et pour lier une base de données à celui-ci sont indiquées ci-dessous: Gouverneur de ressources SQL. La quatrième et dernière étape consiste à lier la base de données de test au nouveau pool de mémoire avec la commande sys. spxtpbinddbresourcepool.160 Pour que la liaison devienne effective, nous devons la déconnecter et la mettre en ligne. Une fois lié, nous pouvons changer dynamiquement la quantité de mémoire affectée à son pool via la commande ALTER RESOURCE POOL PoolHk WITH (MAXMEMORYPERCENT 80). Insertion en bloc dans la table en mémoire durable Maintenant, nous sommes tous réglés avec l'option en mémoire activée, nous pouvons créer une table en mémoire. Chaque table optimisée pour la mémoire doit comporter au moins un index (soit un index Range ou Hash) qui sont complètement (ré) composés en mémoire et ne sont jamais stockés sur le disque. Une table durable doit avoir une clé primaire déclarée, qui pourrait alors être prise en charge par l'index requis. Pour prendre en charge une clé primaire, j'ai ajouté une colonne ROWID1 à la table: La spécification d'une taille de lot de 1 (jusqu'à 5) millions de lignes sur la commande d'insertion groupée permet de persister les données sur le disque alors que l'insertion en bloc est continue Tout cela à la fin) ce faisant minimise la pression de mémoire sur le pool de mémoire PookHK que nous avons créé. La charge de données dans la table en mémoire durable se termine en 5 minutes 28 secondes, ou 183K Rowssec. C'est un bon moment, mais pas beaucoup plus vite que notre base. En regardant le sys. dmoswaitstats montre que le waitstat n ° 1 est IMPPROVIOWAIT qui se produit lorsque SQL Server attend une IO de chargement en bloc pour terminer. En regardant le compteur de performance Bulk Copy Rowssec et Disk Write Bytessec montre le flushing sur les pointes de disque de 275 MBsec une fois qu'un lot est entré (les pointes vertes). C'est le maximum de ce que le disque peut offrir, mais ne l'explique pas tout. Étant donné le gain mineur, nous allons garer celui-ci pour une enquête future. Surveiller le pool de mémoire via le sys. dmresourcegovernorresourcepools dmv pouvons-nous vérifier si notre table en mémoire exploite le pool de mémoire PoolHK nouvellement créé: La sortie montre que c'est le cas du 7.2GB (un extra pour le Rowid) obtenu non compressé chargé dans la mémoire PoolHk pool: Si vous essayez de charger plus de données que vous avez de mémoire disponible pour la piscine, vous obtiendrez un message correct comme celui-ci: La déclaration a été résiliée. Msg 701, niveau 17, état 103, ligne 5 Il n'y a pas assez de mémoire système dans le pool de ressources 8216PookHK pour exécuter cette requête. Pour afficher un niveau plus profond de l'allocation d'espace mémoire sur une table par table en mémoire, vous pouvez exécuter la requête suivante (prise à partir du document SQL Server In Memory OLTP Internals pour SQL Server 2016): Les données que nous venons de charger sont stockées en tant que Varheap structure avec un index de hachage: Jusqu'à présent, si bon Maintenant, laissez passer et vérifiez comment la mise en scène dans une table non durable effectue Bulk Insérer dans Non-Durable Table en mémoire Pour IMND tables, nous n'avons pas besoin d'une clé primaire, nous avons juste Ajoutez et index de hachage non clusterisé et définissez DURABILITY SCHEMAONLY. Le chargement de données en vrac dans la table non durable se termine dans les 3 minutes avec un débit de 335K rowssec (vs 7 minutes) Ceci est 2.3x plus rapide puis l'insertion dans une table de tas. Traditionnellement, SSIS est le moyen le plus rapide de charger rapidement un fichier dans SQL Server car SSIS gère toutes les données de prétraitement de sorte que le moteur SQL Server peut Dépenser son CPU tics sur la persistance des données sur le disque. Est-ce toujours le cas lors de l'insertion des données dans un tableau non durable En dessous d'un résumé des tests que j'ai courus avec SSIS pour ce post: l'option Fastparse SSIS et 160 les paramètres DefaultBufferMaxRows et DefaultBufferSize sont les amplificateurs de performances principaux. Également le fournisseur OLE DB natif (SQLOLEDB.1) effectue légèrement mieux que le client natif SQL (SQLNCLI11.1). Lorsque vous exécutez SSIS et SQL Server côte à côte, l'augmentation de la taille du paquet réseau n'est pas nécessaire.160160 Résultat net: un package SSIS de base qui lit une source de fichier plat et écrit les données directement à la table non durable via une destination OLE DB Effectue de la même manière que l'instruction Bulk Insert dans une table IMND: les lignes 60 Millions sont chargées en 2 minutes 59secondes ou 335K lignes, identique à la commande Insertion groupée. SSIS avec le distributeur équilibré de données Mais wait8230160 les tables en mémoire sont conçues pour fonctionner verrouillage sans verrouillage libre de sorte que cela signifie que nous pouvons charger des données aussi par l'intermédiaire de plusieurs flux Qui est facile à réaliser avec SSIS le distributeur équilibré de données apportera juste cela (le BDD Est listé dans la section Commune de la boîte à outils SSIS) Ajouter le composant BDD et insérer les données dans la même table Non durable avec 3 flux fournit le meilleur débit: nous sommes maintenant jusqu'à 526000 Rowssec En regardant cette ligne très plate, avec seulement 160 du temps CPU utilisé par SQLServer, il semble que nous frappons un goulot d'étranglement: J'ai rapidement essayé d'être créatif en utilisant la fonction modulo et ajouté 2 flux de données plus dans le paquet (chaque traitement de 13 des données) 160, mais ce n'est pas améliorer (1 min52sec) donc un grand sujet à étudier pour un futur post160160 L'option table en mémoire non durable permet d'améliorer sérieusement les performances pour la mise en scène des données de chargement 1.5x plus rapidement avec un insert en vrac régulier et jusqu'à 3.6 fois plus rapide Avec SSIS. Cette option, principalement conçue pour accélérer OLTP, peut également faire une énorme différence pour réduire votre fenêtre de lot rapidement (A suivre)
No comments:
Post a Comment