Augmenter les performances de débit pour Azure SQL Database à partir d’Azure Stream AnalyticsIncrease throughput performance to Azure SQL Database from Azure Stream Analytics

Cet article donne des conseils pour améliorer les performances de débit d’écriture quand vous chargez des données dans Azure SQL Database à partir d’Azure Stream Analytics.This article discusses tips to achieve better write throughput performance when you're loading data into Azure SQL Database using Azure Stream Analytics.

La sortie SQL dans Azure Stream Analytics prend en charge l’option d’écriture en parallèle.SQL output in Azure Stream Analytics supports writing in parallel as an option. Cette option permet les topologies de travaux massivement parallèles, où plusieurs partitions de sortie sont écrites en parallèle dans la table de destination.This option allows for fully parallel job topologies, where multiple output partitions are writing to the destination table in parallel. Toutefois, l’activation de cette option dans Azure Stream Analytics ne permet pas toujours d’observer une hausse du débit, car le résultat dépend fortement de la configuration et du schéma de table de votre base de données.Enabling this option in Azure Stream Analytics however may not be sufficient to achieve higher throughputs, as it depends significantly on your database configuration and table schema. Les index, la clé de clustering, le facteur de remplissage d’index et la compression que vous choisissez ont un impact sur le temps de chargement des tables.The choice of indexes, clustering key, index fill factor, and compression have an impact on the time to load tables. Pour plus d’informations sur les moyens d’optimiser votre base de données afin d’améliorer les performances des requêtes et du chargement sur la base de tests d’évaluation internes, consultez les recommandations relatives aux performances de SQL Database.For more information about how to optimize your database to improve query and load performance based on internal benchmarks, see SQL Database performance guidance. La réorganisation des écritures n’est pas garantie lors de l’écriture en parallèle dans la base de données SQL.Ordering of writes is not guaranteed when writing in parallel to SQL Database.

Les configurations présentées ci-après pour chaque service peuvent vous aider à améliorer le débit global de votre solution.Here are some configurations within each service that can help improve overall throughput of your solution.

Azure Stream AnalyticsAzure Stream Analytics

  • Héritage du partitionnement – Cette option de configuration de la sortie SQL permet d’hériter le schéma de partitionnement de l’entrée ou l’étape de requête précédente.Inherit Partitioning – This SQL output configuration option enables inheriting the partitioning scheme of your previous query step or input. L’activation de cette option contribue à améliorer les débits lors de l’écriture dans une table sur disque et l’utilisation d’une topologie de travail massivement parallèle.With this enabled, writing to a disk-based table and having a fully parallel topology for your job, expect to see better throughputs. Ce partitionnement est déjà automatiquement activé pour beaucoup d’autres sorties.This partitioning already automatically happens for many other outputs. Le verrouillage de table (TABLOCK) est également désactivé pour les insertions en bloc effectuées avec cette option.Table locking (TABLOCK) is also disabled for bulk inserts made with this option.

Notes

Quand il y a plus de huit partitions d’entrée, l’héritage du schéma de partitionnement d’entrée n’est pas toujours une option appropriée.When there are more than 8 input partitions, inheriting the input partitioning scheme might not be an appropriate choice. Cette limite supérieure a été observée sur une table contenant une seule colonne d’identité et un index cluster.This upper limit was observed on a table with a single identity column and a clustered index. Dans ce cas, envisagez d’utiliser INTO 8 dans votre requête pour spécifier explicitement le nombre de générateurs de sortie.In this case, consider using INTO 8 in your query, to explicitly specify the number of output writers. Les résultats observés peuvent varier en fonction du schéma et des index choisis.Based on your schema and choice of indexes, your observations may vary.

  • Taille de lot – Avec cette option de configuration de la sortie SQL, vous pouvez spécifier la taille de lot maximale dans une sortie SQL d’Azure Stream Analytics en fonction de la nature de votre table de destination/charge de travail.Batch Size - SQL output configuration allows you to specify the maximum batch size in an Azure Stream Analytics SQL output based on the nature of your destination table/workload. La taille de lot correspond au nombre maximal d’enregistrements qui sont envoyés avec chaque opération d’insertion en bloc.Batch size is the maximum number of records that sent with every bulk insert transaction. Dans les index cluster columnstore, une taille de lot d’environ 100 000 permet d’optimiser la parallélisation, la journalisation minimale et le verrouillage.In clustered columnstore indexes, batch sizes around 100K allow for more parallelization, minimal logging, and locking optimizations. Dans les tables sur disque, une taille de 10 000 (valeur par défaut) ou moins peut être optimale pour votre solution, car des tailles de lot plus élevées risquent de déclencher une escalade de verrous durant les insertions en bloc.In disk-based tables, 10K (default) or lower may be optimal for your solution, as higher batch sizes may trigger lock escalation during bulk inserts.

  • Paramétrage des messages d’entrée – Si vous avez déjà optimisé les performances à l’aide de l’héritage du partitionnement et de la taille de lot, vous pouvez en plus augmenter le nombre d’événements d’entrée par message dans chaque partition pour améliorer encore le débit d’écriture.Input Message Tuning – If you've optimized using inherit partitioning and batch size, increasing the number of input events per message per partition helps further pushing up your write throughput. Le paramétrage des messages d’entrée permet d’augmenter les tailles de lot dans Azure Stream Analytics jusqu’à la taille de lot spécifié, ce qui améliore le débit.Input message tuning allows batch sizes within Azure Stream Analytics to be up to the specified Batch Size, thereby improving throughput. Cela est possible en utilisant la compression ou en augmentant la taille des messages d’entrée dans EventHub ou Blob.This can be achieved by using compression or increasing input message sizes in EventHub or Blob.

SQL AzureSQL Azure

  • Table et index partitionnés – L’utilisation d’une table SQL partitionnée et d’index partitionnés sur la table ayant la même colonne que votre clé de partition (par exemple, PartitionId) peut réduire considérablement les conflits entre partitions durant les opérations d’écriture.Partitioned Table and Indexes – Using a partitioned SQL table and partitioned indexes on the table with the same column as your partition key (for example, PartitionId) can significantly reduce contentions among partitions during writes. Pour utiliser une table partitionnée, vous devez créer une fonction de partition et un schéma de partition dans le groupe de fichiers PRIMARY.For a partitioned table, you'll need to create a partition function and a partition scheme on the PRIMARY filegroup. Cela améliorera également la disponibilité des données existantes quand de nouvelles données sont chargées.This will also increase availability of existing data while new data is being loaded. La limite des E/S de journal peut être atteinte en fonction du nombre de partitions, mais vous pouvez l’augmenter en mettant à niveau la référence SKU.Log IO limit may be hit based on number of partitions, which can be increased by upgrading the SKU.

  • Éviter les violations de clé unique – Si vous obtenez plusieurs messages d’avertissement de violation de clé dans le journal d’activité Azure Stream Analytics, assurez-vous que votre travail n’est pas impacté par des violations de contrainte unique qui sont susceptibles de se produire dans des scénarios de reprise d’activité.Avoid unique key violations – If you get multiple key violation warning messages in the Azure Stream Analytics Activity Log, ensure your job isn't impacted by unique constraint violations which are likely to happen during recovery cases. Pour éviter ce problème, définissez l’option IGNORE_DUP_KEY sur vos index.This can be avoided by setting the IGNORE_DUP_KEY option on your indexes.

Azure Data Factory et tables en mémoireAzure Data Factory and In-Memory Tables

  • Table en mémoire en tant que table temporaire – Les tables en mémoire permettent des chargements de données à très haut débit, mais les données doivent tenir dans la mémoire.In-Memory Table as temp tableIn-Memory tables allow for very high-speed data loads but data needs to fit in memory. Les tests d’évaluation montrent que le chargement en masse d’une table en mémoire vers une table sur disque est environ dix fois plus rapide que l’insertion en bloc directe à l’aide d’un seul writer dans la table sur disque ayant une colonne d’identité et un index cluster.Benchmarks show bulk loading from an in-memory table to a disk-based table is about 10 times faster than directly bulk inserting using a single writer into the disk-based table with an identity column and a clustered index. Pour améliorer ces performances d’insertion en bloc, créez un travail de copie avec Azure Data Factory qui copie les données de la table en mémoire vers la table sur disque.To leverage this bulk insert performance, set up a copy job using Azure Data Factory that copies data from the in-memory table to the disk-based table.

Éviter l’écueil des performancesAvoiding Performance Pitfalls

Insérer des données en bloc prend beaucoup moins de temps que de les charger par insertions multiples, car cela élimine les contraintes répétées liées au transfert des données, à l’analyse de l’instruction d’insertion, à l’exécution de l’instruction et à l’émission d’un enregistrement de transaction.Bulk inserting data is much faster than loading data with single inserts because the repeated overhead of transferring the data, parsing the insert statement, running the statement, and issuing a transaction record is avoided. En effet, le chemin emprunté par les données diffusées dans le moteur de stockage s’avère plus efficace.Instead, a more efficient path is used into the storage engine to stream the data. Le coût de configuration de ce chemin est cependant beaucoup plus élevé que celui d’une simple instruction d’insertion dans une table sur disque.The setup cost of this path is however much higher than a single insert statement in a disk-based table. Le point d’équilibre se situe généralement autour des 100 lignes. Au-delà, le chargement en masse est presque toujours plus efficace.The break-even point is typically around 100 rows, beyond which bulk loading is almost always more efficient.

Si le taux d’événements entrants est faible, des tailles de lot inférieures à 100 lignes peuvent alors être facilement créées, ce qui altère l’efficacité de l’insertion en masse et occupe trop d’espace disque.If the incoming events rate is low, it can easily create batch sizes lower than 100 rows, which makes bulk insert inefficient and uses too much disk space. Pour contourner cette limitation, vous pouvez prendre une des mesures suivantes :To work around this limitation, you can do one of these actions:

  • Créer un déclencheur INSTEAD OF pour utiliser une instruction simple pour chaque ligne.Create an INSTEAD OF trigger to use simple insert for every row.
  • Utilisez une table temporaire en mémoire, comme décrit dans la section précédente.Use an In-Memory temp table as described in the previous section.

Un autre scénario de ce type se produit quand l’écriture se produit dans un index columnstore non cluster (NCCI), où des insertions en masse plus petites peuvent créer un nombre de segments trop important, ce qui peut bloquer l’index.Another such scenario occurs when writing into a non-clustered columnstore index (NCCI), where smaller bulk inserts can create too many segments, that can crash the index. Dans ce cas, il est recommandé d’utiliser plutôt un index columstore cluster.In this case, the recommendation is to use a Clustered Columnstore index instead.

RésuméSummary

En résumé, avec la fonctionnalité de sortie partitionnée dans Azure Stream Analytics pour la sortie SQL, la parallélisation alignée de votre travail avec une table partitionnée dans SQL Azure doit en principe vous apporter des améliorations de débit significatives.In summary, with the partitioned output feature in Azure Stream Analytics for SQL output, aligned parallelization of your job with a partitioned table in SQL Azure should give you significant throughput improvements. L’utilisation d’Azure Data Factory pour orchestrer le déplacement des données d’une table en mémoire vers des tables sur disque contribue aussi à améliorer le débit.Leveraging Azure Data Factory for orchestrating data movement from an In-Memory table into Disk-based tables can give order of magnitude throughput gains. Quand cela est possible, l’optimisation de la densité des messages peut également être un facteur majeur de l’amélioration du débit global.If feasible, improving message density can also be a major factor in improving overall throughput.