sql-server - tablas - tamaño maximo de una tabla en sql server

SQL Server: el número máximo de filas en la tabla

Question

Desarrollo software que almacena una gran cantidad de datos en una de sus tablas de bases de datos (SQL Server versión 8, 9 o 10). Digamos que aproximadamente 100,000 registros se insertan en esa tabla por día. Esto es alrededor de 36 millones de registros por año. Por temor a perder en el rendimiento, decidí crear una nueva mesa todos los días (una tabla con la fecha actual en su nombre) para reducir el número de registros por mesa.

¿Podría decirme por favor si fue una buena idea? ¿Hay un límite de registro para las tablas del servidor SQL? ¿O sabe cuántos registros (más o menos) se pueden almacenar en una tabla antes de que el rendimiento se reduzca significativamente?

Answer 1

Depende, pero yo diría que es mejor mantener todo en una sola tabla por simplicidad.

100.000 filas por día no es realmente una cantidad tan enorme. (Dependiendo del hardware de su servidor). Personalmente, he visto que MSSQL maneja hasta 100M filas en una sola tabla sin ningún problema. Mientras mantenga sus índices en orden, debería estar todo bien. La clave es tener montones de memoria para que los índices no tengan que ser intercambiados al disco.

Por otro lado, depende de cómo esté usando los datos, si necesita realizar muchas consultas y es poco probable que se necesiten datos que abarquen varios días (para que no tenga que unir las tablas) será más rápido para separarlo en múltiples tablas. Esto se usa a menudo en aplicaciones como el control de procesos industriales, donde puede estar leyendo el valor en, por ejemplo, 50,000 instrumentos cada 10 segundos. En este caso, la velocidad es extremadamente importante, pero la simplicidad no lo es.

Answer 2

Desbordamos una clave primaria entera una vez (que es ~ 2.4 mil millones de filas) en una tabla. Si hay un límite de filas, es probable que nunca lo golpees en solo 36 millones de filas por año.

Answer 3

Es difícil dar una respuesta genérica a esto. Realmente depende del número de factores:

• de qué tamaño es tu fila
• qué tipo de datos almacena (cadenas, blobs, números)
• qué haces con tus datos (solo guárdalo como archivo, búscalo regularmente)
• ¿Tiene índices en su mesa? ¿Cuántos
• cuales son tus especificaciones de servidor

etc.

Como se respondió en otro lugar aquí, 100.000 por día y, por lo tanto, por mesa son exagerados, sugiero mensual o semanal quizás incluso trimestralmente. Cuantas más tablas tenga, más grande será la pesadilla de mantenimiento / consulta.

Answer 4

Estas son algunas de las especificaciones de capacidad máxima para SQL Server 2008 R2

• Tamaño de la base de datos: 524,272 terabytes
• Bases de datos por instancia de SQL Server: 32.767
• Grupos de archivos por base de datos: 32.767
• Archivos por base de datos: 32.767
• Tamaño del archivo (datos): 16 terabytes
• Tamaño del archivo (registro): 2 terabytes
• Filas por tabla: Limitado por el almacenamiento disponible
• Tablas por base de datos: Limitado por el número de objetos en una base de datos

Answer 5

La tabla más grande que he encontrado en SQL Server 8 en Windows2003 era 799 millones con 5 columnas. Pero si es bueno o no es medido en función del SLA y el caso de uso, por ejemplo, cargue 50-100,000,000 de registros y vea si todavía funciona.

Answer 6

No conozco MSSQL específicamente, pero 36 millones de filas no son grandes para una base de datos empresarial. Al trabajar con bases de datos de mainframe, 100.000 filas me suenan como una tabla de configuración :-).

Si bien no soy un gran admirador de algunos de los programas de Microsoft, no nos referimos a Access aquí: supongo que pueden manejar tamaños de bases de datos bastante importantes con su DBMS empresarial.

Sospecho que los días pueden haber sido una resolución demasiado fina para dividirlos, si es que realmente necesitan dividirse.

Answer 7

No sé de un límite de fila, pero sé tablas con más de 170 millones de filas. Puede acelerarlo usando tablas particionadas (2005+) o vistas que conecten múltiples tablas.

Answer 8

Partición de la tabla mensualmente. Esa es la mejor manera de manejar tablas con gran afluencia diaria, ya sea Oracle o MSSQL.

Answer 9

Puede completar la tabla hasta que tenga suficiente espacio en el disco. Para un mejor rendimiento, puede probar la migración a SQL Server 2005 y luego particionar la tabla y poner partes en diferentes discos (si tiene una configuración RAID que realmente podría ayudarlo). El particionamiento solo es posible en la versión empresarial de SQL Server 2005. Puede buscar ejemplos de particiones en este enlace: http://technet.microsoft.com/en-us/magazine/cc162478.aspx

También puede intentar crear vistas para la porción de datos más utilizada, que también es una de las soluciones.

Espero que esto haya ayudado ...

Answer 10

Tenemos tablas en SQL Server 2005 y 2008 con más de 1 mil millones de filas (30 millones agregadas diariamente). No me puedo imaginar yendo por el nido de ratas de dividir eso en una nueva mesa cada día.

Es mucho más barato agregar el espacio de disco adecuado (que de todos modos necesita) y la RAM.

Answer 11

Tengo una tabla de tres columnas con poco más de 6 mil millones de filas en SQL Server 2008 R2.

Lo consultamos todos los días para crear gráficos de análisis de sistema minuto a minuto para nuestros clientes. No he notado ningún impacto en el rendimiento de la base de datos (aunque el hecho de que crezca ~ 1 GB por día hace que la administración de las copias de seguridad sea un poco más complicada de lo que me gustaría).

Actualización julio de 2016

Llegamos a ~ 24.5 mil millones de filas antes de que las copias de seguridad fueran lo suficientemente grandes como para que podamos decidir truncar registros anteriores a dos años (~ 700 GB almacenados en copias de seguridad múltiples, incluso en cintas costosas). Vale la pena señalar que el rendimiento no fue un motivador significativo en esta decisión (es decir, todavía funcionaba de maravilla).

Para cualquiera que intente eliminar 20 mil millones de filas de SQL Server, recomiendo este artículo . Código relevante en caso de que el enlace muera (lea el artículo para una explicación completa):

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

Actualización noviembre de 2016

Si planea almacenar esta cantidad de datos en una sola tabla, no lo haga. Le recomiendo que considere la partición de tablas (ya sea manualmente o con las funciones incorporadas si está ejecutando la edición Enterprise). Esto hace que eliminar datos antiguos sea tan fácil como truncar una tabla una vez a (semana / mes / etc.). Si no tiene Enterprise (que nosotros no), puede simplemente escribir un script que se ejecuta una vez al mes, descarta tablas de más de 2 años, crea la tabla del próximo mes y regenera una vista dinámica que une toda la partición tablas juntas para facilitar la consulta. Obviamente, debe definir "una vez al mes" y "más de 2 años" en función de lo que tenga sentido para su caso de uso. Eliminar directamente de una tabla con decenas de miles de millones de filas de datos a) requerirá una GRAN cantidad de tiempo yb) completará el registro de transacciones cientos o miles de veces.

Answer 12

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