MS SQL Server
  1. MS SQL Server
  3. Niveau de transaction, nolock / readpast et concurrency
Intereting Posts
Comment puis-je faire "SELECT" requête avec "WHERE" condition sur la colonne qui n'existe pas dans la table dans SQL Server Type de données non déterministe dans SQL Server Erreurs de troncature essayant d'importer à partir d'Excel Activation du debugging de procédure stockée SQL Server à partir de .Net Core Fonction SQL Server renvoyant des données dans une colonne incorrecte Problème d'autorisation SQL Server 2008 OPENROWSET Générer le numéro de list Requête SQL n'utilisant pas l'index requirejs dans SQL Server Calcul du pourcentage de participation sur plusieurs colonnes Stockage des documents Microsoft Word 97 dans la colonne SQL Server VBA Excel – Requête SQL via ADO renvoie un jeu d'loggings null infini Requête de sélection multiple de database, impossible de récupérer datatables souhaitées Qu'est-ce que l'erreur "Nouvelle transaction n'est pas autorisée" dans PHP et le pilote SQLSRV? mettre à jour la colonne à l'aide de ROW_NUMBER Plan de maintenance SQL Server 2008 R2 – autre chose?

Niveau de transaction, nolock / readpast et concurrency

Nous avons un système qui est simultanément inséré une grande quantité de données provenant de plusieurs stations tout en exposant une interface d'interrogation de données. Le schéma ressemble à ceci (désolé de la mauvaise mise en forme): 

[SyncTable] SyncID StationID MeasuringTime [DataTypeTable] TypeID TypeName [DataTable] SyncID TypeID DataColumns...

L'insertion de données se fait dans une "synchronisation" et va comme ceci (nous insérons seulement des données dans le système, nous ne mettons jamais à jour) 

 INSERT INTO SyncTable(StationID, MeasuringTime) VALUES (X,Y); SELECT @@IDENTITY INSERT INTO DataTable(SyncID, TypeID, DataColumns) VALUES (SyncIDJustInserted, InMemoryCachedTypeID, Data) ... lots (500) similar inserts into DataTable ...

Et les requêtes vont comme ça (pour une station donnée, measuringtime et type de données) 

 SELECT SyncID FROM SyncTable WHERE StationID = @StationID AND MeasuringTime = @MeasuringTime SELECT DataColumns FROM DataTable WHERE SyncID = @SyncIDJustSelected AND DataTypeID = @TypeID

Ma question est comment pouvons-nous combiner le niveau de transaction sur les insertions et les indications NOLOCK / READPAST sur les requêtes de sorte que:

  1. Nous maximisons la concurrency dans notre système tout en favorisant les insertions (nous avons besoin de stocker beaucoup de données, quelque chose de plus de 2000+ loggings par seconde)
  2. Les requêtes ne renvoient des données que de la synchronisation "validée" (nous ne voulons pas d'un set de résultats avec une synchronisation semi-insérée ou une synchronisation avec certaines inputs ignorées en raison d'un "lock-skipping")
  3. Nous ne nous soucions pas de savoir si datatables "les plus récentes" sont incluses dans la requête, nous nous soucions plus de cohérence et de réactivité que de données "live" et à jour

Cela peut être des objectives très contradictoires et peut nécessiter un niveau élevé d'isolation des transactions, mais je suis intéressé par toutes les astuces et optimizations pour atteindre une réactivité élevée sur les insertions et les sélections. Je serai heureux d'élaborer si plus de détails sont nécessaires pour débusquer plus de réglages et astuces.

MISE À JOUR: Ajout d'un peu plus d'informations pour les réponses futures. Nous exécutons SQL Server 2005 (2008 dans un timeout de six mois probablement) sur un réseau SAN avec initialement 5 To de stockage. Je ne suis pas sûr de quel type de RAID le SAn est configuré et précisément combien de disques nous avons disponibles.

  1. Quel type de système de disque utiliserez-vous? Si vous avez une grande masortingce RAID rayée, les écritures devraient bien fonctionner. Si vous pouvez estimer vos lectures et écritures requirejses par seconde, vous pouvez twigr ces nombres dans une formule et voir si votre sous-système de disque va continuer. Peut-être que vous n'avez aucun contrôle sur le matériel …

  2. N'envelopperiez-vous pas les insertions dans une transaction, ce qui les rendrait indisponibles aux lectures jusqu'à ce que l'insertion soit terminée?

  3. Cela devrait suivre si votre matériel est configuré correctement et que vous faites attention à votre code SQL – ce que vous semblez être.

Regardez dans les outils SQLIO.exe et SQL Stress:

SQLIOStress.exe SQLIOStress.exe simule différents templates de comportement d'E / S SQL Server 2000 pour garantir une security E / S rudimentaire.

L'utilitaire SQLIOStress peut être téléchargé à partir du site Web de Microsoft. Voir l'article suivant.

Comment utiliser l'utilitaire SQLIOStress pour souligner un sous-système de disque tel que SQL Server http://support.microsoft.com/default.aspx?scid=kb;en-us;231619

Important Le téléchargement contient un livre blanc complet avec des détails étendus sur l'utilitaire.

SQLIO.exe SQLIO.exe est un utilitaire d'E / S SQL Server 2000 utilisé pour établir des résultats de test de reference de base.

L'utilitaire SQLIO peut être téléchargé à partir du site Web de Microsoft. Voir ce qui suit: • devise de test de performance SQLIO (développement SQL) – Client disponible http://download.microsoft.com/download/f/3/f/f3f92f8b-b24e-4c2e-9e86-d66df1f6f83b/SQLIO.msi

Si vous exécutez SQL 2005 et versions ultérieures, searchz l'implémentation de l' isolation des instantanés . Vous ne serez pas en mesure d'get des résultats cohérents avec nolock.

Résoudre ceci sur SQL 2000 est beaucoup plus difficile.

C'est un excellent scénario pour la fonction de partitionnement de SQL Server 2005/2008 Enterprise. Vous pouvez créer une partition pour chaque StationID, et datatables de chaque StationID peuvent aller dans son propre groupe de files (si vous le souhaitez, cela peut ne pas être nécessaire en fonction de votre charge).

Cela vous achète quelques avantages avec la concurrency:

  • Si vous partitionnez par stationid, les users peuvent exécuter des requêtes select pour les stationid qui ne sont pas en cours de chargement, et ils ne rencontreront aucun problème de concurrency.
  • Si vous partitionnez par stationid, alors plusieurs stations peuvent insert des données simultanément sans problèmes de simultanéité (tant qu'elles se trouvent sur des groupes de files différents)
  • Si vous partitionnez par plage de synchronisation, vous pouvez mettre les anciennes données sur un stockage plus lent.
  • Si vous partitionnez par plage de synchronisation, ET si vos plages sont suffisamment petites (ce qui signifie pas une plage avec des milliers de syncides), vous pouvez effectuer des requêtes en même time que vos users interrogent sans rencontrer de problèmes de concurrency.

Le scénario que vous décrivez a beaucoup en commun avec les charges nocturnes de l'entrepôt de données. Microsoft a fait un projet de reference technique appelé Project Real que vous pourriez find intéressant. Ils l'ont publié comme standard, et vous pouvez lire les documents de design et le code de mise en œuvre afin de voir comment ils ont tiré des charges très rapides:

http://www.microsoft.com/technet/prodtechnol/sql/2005/projreal.mspx

Le partitionnement est encore meilleur dans SQL Server 2008, en particulier en ce qui concerne la concurrency. Ce n'est toujours pas une solution miracle – elle nécessite une design manuelle et une maintenance par un administrateur de bases de données qualifié. Il ne s'agit pas d'une fonctionnalité définie et oubliée, qui nécessite Enterprise Edition, ce qui coûte plus cher que l'édition Standard. Je l'aime, cependant – je l'ai utilisé plusieurs fois et cela a résolu des problèmes spécifiques pour moi.