Gérer de grandes quantités de données dans SQL

Je viens de prendre en charge un projet au travail, et mon patron m'a demandé de le faire tourner plus vite. Génial.

J'ai donc identifié l'un des principaux goulots d'étranglement à searchr dans une table particulière de notre server SQL, ce qui peut prendre jusqu'à une minute , parfois plus, pour une requête de sélection avec des filters à exécuter. Voici le SQL généré par C # Entity Framework (less toutes les instructions GO ):

 CREATE TABLE [dbo].[MachineryReading]( [Id] [int] IDENTITY(1,1) NOT NULL, [Location] [geometry] NULL, [Latitude] [float] NOT NULL, [Longitude] [float] NOT NULL, [Altitude] [float] NULL, [Odometer] [int] NULL, [Speed] [float] NULL, [BatteryLevel] [int] NULL, [PinFlags] [bigint] NOT NULL, -- Deprecated field, this is now stored in a separate table [DateRecorded] [datetime] NOT NULL, [DateReceived] [datetime] NOT NULL, [Satellites] [int] NOT NULL, [HDOP] [float] NOT NULL, [MachineryId] [int] NOT NULL, [TrackerId] [int] NOT NULL, [ReportType] [nvarchar](1) NULL, [FixStatus] [int] NOT NULL, [AlarmStatus] [int] NOT NULL, [OperationalSeconds] [int] NOT NULL, CONSTRAINT [PK_dbo.MachineryReading] PRIMARY KEY NONCLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ) ALTER TABLE [dbo].[MachineryReading] ADD DEFAULT ((0)) FOR [FixStatus] ALTER TABLE [dbo].[MachineryReading] ADD DEFAULT ((0)) FOR [AlarmStatus] ALTER TABLE [dbo].[MachineryReading] ADD DEFAULT ((0)) FOR [OperationalSeconds] ALTER TABLE [dbo].[MachineryReading] WITH CHECK ADD CONSTRAINT [FK_dbo.MachineryReading_dbo.Machinery_MachineryId] FOREIGN KEY([MachineryId]) REFERENCES [dbo].[Machinery] ([Id]) ON DELETE CASCADE ALTER TABLE [dbo].[MachineryReading] CHECK CONSTRAINT [FK_dbo.MachineryReading_dbo.Machinery_MachineryId] ALTER TABLE [dbo].[MachineryReading] WITH CHECK ADD CONSTRAINT [FK_dbo.MachineryReading_dbo.Tracker_TrackerId] FOREIGN KEY([TrackerId]) REFERENCES [dbo].[Tracker] ([Id]) ON DELETE CASCADE ALTER TABLE [dbo].[MachineryReading] CHECK CONSTRAINT [FK_dbo.MachineryReading_dbo.Tracker_TrackerId]

La table a des index sur MachineryId , TrackerId et DateRecorded :

 CREATE NONCLUSTERED INDEX [IX_MachineryId] ON [dbo].[MachineryReading] ( [MachineryId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) CREATE NONCLUSTERED INDEX [IX_MachineryId_DateRecorded] ON [dbo].[MachineryReading] ( [MachineryId] ASC, [DateRecorded] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) CREATE NONCLUSTERED INDEX [IX_TrackerId] ON [dbo].[MachineryReading] ( [TrackerId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

Lorsque nous sélectionnons à partir de ce tableau, nous sums presque toujours intéressés par une machine ou un tracker, sur une plage de dates donnée:

 SELECT * FROM MachineryReading WHERE MachineryId = 2127 AND DateRecorded > '2016-12-08 00:00:10.009' AND DateRecorded < '2016-12-11 18:32:41.734'

Comme vous pouvez le voir, c'est une configuration assez basique. Le principal problème est la quantité de données que nous y mettons – environ une ligne toutes les dix secondes par tracker, et nous avons plus d'une centaine de trackers pour le moment. Nous sums actuellement assis quelque part entre 10 et 15 millions de lignes. Donc, cela me laisse avec deux questions.

Suis-je en train de battre la database si j'insère 10 lignes par seconde (sans les mélanger)?
Étant donné qu'il s'agit de données historiques, une fois inséré, cela ne changera jamais. Y a-t-il quelque chose que je puisse faire pour accélérer l'access en lecture?

Vous avez trop d'index non clusterisés sur la table – ce qui augmentera la taille de la database.

Si vous avez un index sur MachineryId et DateRecorded – vous n'avez pas vraiment besoin d'un index séparé sur MachineryId .

Avec 3 de vos index non clusterisés – il y a 3 copys supplémentaires des données

Clustered VS non clusterisé

Non Inclure dans l'index non clusterisé

Lorsque SQL Server exécute votre SQL, il search d'abord dans l'index non clusterisé datatables requirejses, puis il returnne à la table d'origine ( bookmark lookup ) Link et récupère le rest des colonnes comme vous le faites, mais l'index non groupé n'a pas toutes les colonnes (c'est ce que je pense qui se passe – ne peut pas vraiment dire sans le plan de requête)

Inclure les colonnes dans l'index non cluster: https://stackoverflow.com/a/1308325/1910735

Vous devez gérer vos index en créant un plan de maintenance pour vérifier la fragmentation et rebuild ou réorganiser vos index sur une base hebdomadaire.
Je pense vraiment que vous devriez avoir un index clusterisé sur votre MachineryId et DateRecordred au lieu d'un index non cluster. Une table ne peut avoir qu'un seul index clusterisé (datatables de la command sont stockées sur le disque dur) – car la plupart de vos requêtes seront dans les commands DateRecordred et MachineryId – il vaudra mieux les stocker de cette façon,

En outre, si vous effectuez une search avec TrackerId dans une requête, essayez de l'append au même index clusterisé

NOTE IMPORTANTE: EFFACER L'INDEX NON GRAPPÉ dans l'environnement TEST avant de passer en LIVE

Créez un index cluster au lieu de votre index non cluster, exécutez différentes requêtes – Vérifiez la performance en comparant les Query Plans et les IO / IO STATISTICS

Certaines ressources pour l'aide Index et SQL Query:

Abonnez-vous à la newsletter ici et téléchargez le kit first responder: https://www.brentozar.com/?s=first+responder

Il est maintenant open source – mais je ne sais pas s'il a le file PDF actuel et les files d'aide (Abonnez-vous dans le lien ci-dessus de toute façon – pour les articles hebdomadaires / tutoriels)

https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit

Tuning est par requête, mais en tout cas –
Je vois que vous n'avez aucune partition et aucun index, ce qui signifie, peu importe ce que vous faites. il en résulte toujours un balayage de table complet.

Pour votre requête spécifique –

 create index MachineryReading_ix_MachineryReading_DateRecorded on (MachineryReading,DateRecorded)

Tout d'abord, 10 inserts par seconde est très faisable dans presque toutes les circonstances raisonnables.

Deuxièmement, vous avez besoin d'un index. Pour cette requête:

 SELECT * FROM MachineryReading WHERE MachineryId = 2127 AND DateRecorded > '2016-12-08 00:00:10.009' AND DateRecorded < '2016-12-11 18:32:41.734';

Vous avez besoin d'un index sur MachineryReading(MachineryId, DateRecorded) . Cela va probablement résoudre votre problème de performance.

Si vous avez des requêtes similaires pour tracker, alors vous voulez un index sur MachineryReading(TrackerId, DateRecorded) .

Ceux-ci entraveront légèrement la progression des inserts . Mais l'amélioration globale devrait être si grande, que tout sera une grande victoire.