SQL Server: le nombre maximal de lignes dans la table

Je développe un logiciel qui stocke beaucoup de données dans l'une de ses tables de base de données (SQL Server version 8, 9 ou 10). Disons, environ 100 000 enregistrements sont insérés dans cette table par jour. C'est environ 36 millions d'enregistrements par an. De peur de perdre en performance, j'ai décidé de créer une nouvelle table tous les jours (une table avec la date actuelle dans son nom) pour réduire le nombre d'enregistrements par table.

Pourriez-vous me dire, si c'était une bonne idée? Est-il une limite d'enregistrement pour SQL des tables de serveur? Ou savez-vous combien d'enregistrements (plus ou moins) peuvent être stockés dans une table avant que les performances soient considérablement réduites?

62
demandé sur A-Sharabiani 2009-04-17 10:20:46

12 réponses

, Il est difficile de donner une réponse générique à cela. Cela dépend vraiment du nombre de facteurs:

  • Quelle est la taille de votre ligne
  • Quel type de données vous stockez (chaînes, blobs, nombres)
  • Que faites-vous avec vos données (Gardez-les simplement comme archive, interrogez-les régulièrement)
  • Avez - vous des index sur votre table-combien
  • Quelles sont les spécifications de votre serveur

Etc.

Comme répondu ailleurs ici, 100 000 par jour et donc par table est exagéré-je suggère mensuellement ou hebdomadaire peut-être même trimestriel. Plus vous avez de tables, plus le cauchemar de maintenance/requête sera grand.

31
répondu Rashack 2009-04-17 07:04:09

Voici quelques-unes des spécifications de capacité maximale pour SQL Server 2008 R2

  • Taille de la base de données: 524 272 téraoctets
  • bases de données par instance de SQL Server: 32 767
  • groupes de fichiers par base de données: 32 767
  • fichiers par base de données: 32 767
  • Taille du Fichier (données): 16 téraoctets
  • Taille du fichier (journal): 2 téraoctets
  • lignes par table: limité par le stockage disponible
  • Tables par base de données: limité par le nombre d'objets base de données
79
répondu Malak Gerges 2011-03-22 13:34:30

J'ai une table à trois colonnes avec un peu plus de 6 milliards de lignes dans SQL Server 2008 R2.

Nous l'interrogeons tous les jours pour créer des graphiques d'analyse système minute par minute pour nos clients. Je n'ai pas remarqué de performances de base de données (bien que le fait qu'il augmente ~1 GB chaque jour rend la gestion des sauvegardes un peu plus impliquée que je ne le voudrais).

Mise À Jour En Juillet 2016

Le nombre de lignes

Nous avons atteint ~24,5 milliards de lignes avant que les sauvegardes ne deviennent assez grand pour nous de décider de tronquer les enregistrements de plus de deux ans (~700 GO stockés dans plusieurs sauvegardes, y compris sur des bandes coûteuses). Il convient de noter que le rendement n'a pas été un facteur de motivation important dans cette décision (c.-à-d., il fonctionnait toujours très bien).

Pour tous ceux qui essaient de supprimer 20 milliards de lignes de SQL Server, je recommande fortement cet article . Code pertinent dans le cas où le lien meurt (lire l'article pour un explication):

ALTER DATABASE DeleteRecord SET RECOVERY SIMPLE;
GO

BEGIN TRY
    BEGIN TRANSACTION
        -- Bulk logged 
        SELECT  *
        INTO    dbo.bigtable_intermediate
        FROM    dbo.bigtable
        WHERE   Id % 2 = 0;

        -- minimal logged because DDL-Operation 
        TRUNCATE TABLE dbo.bigtable;  

        -- Bulk logged because target table is exclusivly locked! 
        SET IDENTITY_INSERT dbo.bigTable ON;
        INSERT INTO dbo.bigtable WITH (TABLOCK) (Id, c1, c2, c3)
        SELECT Id, c1, c2, c3 FROM dbo.bigtable_intermediate ORDER BY Id;
        SET IDENTITY_INSERT dbo.bigtable OFF;
    COMMIT
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK
END CATCH

ALTER DATABASE DeleteRecord SET RECOVERY FULL;
GO

Mise À Jour Novembre 2016

Si vous prévoyez de stocker autant de données dans une seule table: ne le faites pas. je vous recommande fortement d'envisager le partitionnement de table (manuellement ou avec les fonctionnalités intégrées si vous utilisez Enterprise edition). Cela rend la suppression d'anciennes données aussi facile que de tronquer une table une fois par semaine (Semaine / Mois/etc.). Si vous N'avez pas Enterprise (ce que nous n'avons pas), vous pouvez simplement écrire un script qui s'exécute une fois par mois, supprime des tables de plus de 2 ans, crée la table du mois prochain et régénère une vue dynamique qui réunit toutes les tables de partition pour faciliter l'interrogation. Évidemment, "une fois par mois" et "plus de 2 ans" devraient être définis par vous en fonction de ce qui est logique pour votre cas d'utilisation. Supprimer directement d'une table avec des dizaines de milliards de lignes de données a) prendra énormément de temps et b) remplira le journal des transactions des centaines ou des milliers de fois.

28
répondu Dan Bechard 2016-11-23 13:59:32

Je ne connais pas de limite de ligne, mais je connais des tables avec plus de 170 millions de lignes. Vous pouvez l'accélérer en utilisant des tables partitionnées (2005+) ou des vues qui connectent plusieurs tables.

18
répondu Sascha 2009-04-17 06:27:32

Je ne connais pas MSSQL spécifiquement, mais 36 millions de lignes ne sont pas grandes pour une base de données d'entreprise - travailler avec des bases de données mainframe, 100 000 lignes ressemble à une table de configuration pour moi :-).

Bien que je ne sois pas un grand fan de Certains des logiciels de Microsoft, ce N'est pas L'accès dont nous parlons ici: je suppose qu'ils peuvent gérer des tailles de base de données assez substantielles avec leur SGBD d'entreprise.

Je soupçonne que les jours ont été une résolution trop fine pour le diviser, si en effet il les besoins en divisant à tous.

15
répondu paxdiablo 2012-03-14 00:55:28

Nous avons des tables dans SQL Server 2005 et 2008 avec plus de 1 milliard de lignes (30 millions ajoutés quotidiennement). Je ne peux pas imaginer descendre le nid de rats de diviser cela en une nouvelle table chaque jour.

Beaucoup moins cher d'ajouter l'espace disque approprié (dont vous avez besoin de toute façon) et de la RAM.

5
répondu NotMe 2010-10-06 21:01:09

Cela dépend, mais je dirais qu'il est préférable de tout garder dans une table par souci de simplicité.

100 000 lignes par jour, ce n'est pas vraiment énorme. (En fonction du matériel de votre serveur). J'ai personnellement vu MSSQL gérer JUSQU'à 100m de lignes dans une seule table sans aucun problème. Tant que vous gardez vos index dans l'ordre, tout devrait être bon. La clé est d'avoir heaps de mémoire afin que les index ne doivent pas être échangés sur le disque.

Sur le d'autre part, cela dépend de la façon dont vous utilisez les données, si vous avez besoin de faire beaucoup de requêtes, et ses données improbables seront nécessaires qui s'étendent sur plusieurs jours (donc vous n'aurez pas besoin de joindre les tables) il sera plus rapide de les séparer en plusieurs tables. Ceci est souvent utilisé dans des applications telles que le contrôle de processus industriel où vous pouvez lire la valeur sur disons 50 000 instruments toutes les 10 Secondes. Dans ce cas, la vitesse est extrêmement importante, mais la simplicité ne l'est pas.

3
répondu Nathan 2009-04-22 09:02:42

Nous avons débordé une clé primaire entière une fois (qui est de ~2,4 milliards de lignes) sur une table. S'il y a une limite de ligne, vous n'êtes pas susceptible de l'atteindre à seulement 36 millions de lignes par an.

3
répondu Mark 2010-10-06 21:11:53

Vous pouvez remplir la table jusqu'à ce que vous ayez suffisamment d'espace disque. Pour de meilleures performances, vous pouvez essayer la migration vers SQL Server 2005, puis partitionner la table et placer des pièces sur différents disques (si vous avez une configuration RAID qui pourrait vraiment vous aider). Le partitionnement n'est possible que dans la version enterprise de SQL Server 2005. Vous pouvez regarder l'exemple de partitionnement à ce lien: http://technet.microsoft.com/en-us/magazine/cc162478.aspx

Vous pouvez également essayer de créer des vues pour la plupart partie de données utilisées, qui est également l'une des solutions.

Espère que cela a aidé...

2
répondu 2009-04-22 08:25:36

La plus grande table que J'ai rencontrée sur SQL Server 8 sur Windows2003 était de 799 millions avec 5 colonnes. Mais si oui ou non c'est une bonne volonté doit être mesurée par rapport au SLA et au cas d'utilisation - par exemple charger 50-100,000,000 enregistrements et voir si cela fonctionne toujours.

0
répondu jpj 2012-08-10 21:49:55
SELECT Top 1 sysobjects.[name], max(sysindexes.[rows]) AS TableRows, 
  CAST( 
    CASE max(sysindexes.[rows]) 
      WHEN 0 THEN -0 
      ELSE LOG10(max(sysindexes.[rows])) 
    END 
    AS NUMERIC(5,2)) 
  AS L10_TableRows 
FROM sysindexes INNER JOIN sysobjects ON sysindexes.[id] = sysobjects.[id] 
WHERE sysobjects.xtype = 'U' 
GROUP BY sysobjects.[name] 
ORDER BY max(rows) DESC
-1
répondu ravi 2011-12-30 23:21:56

Partitionner la table mensuellement.C'est la meilleure façon de gérer les tables avec un afflux quotidien important ,que ce soit oracle ou MSSQL.

-3
répondu Sameer 2012-09-07 18:10:55