many - sql server varchar length

SQL Server, conversión de NTEXT a NVARCHAR(MAX)

Question

Tengo una base de datos con una gran cantidad de campos que actualmente son NTEXT.

Después de haber actualizado a SQL 2005, hemos realizado algunas pruebas de rendimiento al convertirlas a NVARCHAR (MAX).

Si lees este artículo:

http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx

Esto explica que una simple ALTER COLUMN no reorganice los datos en filas.

Experimento esto con mis datos. De hecho, tenemos un rendimiento mucho peor en algunas áreas si solo ejecutamos ALTER COLUMN. Sin embargo, si ejecuto una Columna SET de tabla UPDATE = Columna para todos estos campos, obtenemos un aumento de rendimiento extremadamente grande.

El problema que tengo es que la base de datos consiste en cientos de estas columnas con millones de registros. Una prueba simple (en una máquina virtual de bajo rendimiento) tenía una tabla con una sola columna NTEXT que contenía 7 millones de registros y tardó 5 horas en actualizarse.

¿Alguien puede ofrecer alguna sugerencia sobre cómo puedo actualizar los datos de una manera más eficiente que minimiza el tiempo de inactividad y bloqueos?

EDITAR: Mi solución de copia de seguridad es simplemente actualizar los datos en bloques a lo largo del tiempo, sin embargo, con nuestros datos esto da como resultado un peor rendimiento hasta que todos los registros se hayan actualizado y esta vez sea más corta, así que todavía estoy buscando una solución más rápida. forma de actualizar.

Answer 1

¿Qué hay de ejecutar la actualización en lotes? Actualice 1000 filas a la vez.

Utilizaría un ciclo while que incrementa un contador, que corresponde al ID de las filas que se actualizarán en cada iteración de la consulta de actualización. Esto puede no acelerar la cantidad de tiempo que lleva actualizar los 7 millones de registros, pero debería hacer que sea mucho menos probable que los usuarios experimenten un error debido al bloqueo de registros.

Answer 2

Ejecutar una prueba de base de datos en una máquina virtual de bajo rendimiento no es realmente indicativo del rendimiento de la producción, el IO pesado involucrado requerirá una matriz de disco rápida, que acelerará la virtualización.

Answer 3

Si no puede obtener el tiempo de inactividad programado ...

crear dos nuevas columnas: nvarchar (max) processedflag INT DEFAULT 0

Crear un índice no agrupado en el indicador procesado

Tiene UPDATE TOP disponible (desea actualizar top ordenado por la clave principal).

Simplemente configure el indicador procesado en 1 durante la actualización para que la próxima actualización solo se actualice cuando el indicador procesado aún esté 0

Puede usar @@ rowcount después de la actualización para ver si puede salir de un bucle.

Sugiero usar WAITFOR durante unos segundos después de cada consulta de actualización para dar a otras consultas la oportunidad de adquirir bloqueos en la tabla y no sobrecargar el uso del disco.

Answer 4

Si puede obtener un tiempo de inactividad programado:

1. Haga una copia de seguridad de la base
2. Cambiar el modelo de recuperación a simple
3. Elimine todos los índices de la tabla que está actualizando
4. Agregue un indicador de mantenimiento de columna (INT DEFAULT 0) con un índice no agrupado
5. Ejecutar: UPDATE TOP 1000 tablename SET nvarchar desde ntext, maintenanceflag = 1 WHERE maintenanceflag = 0

Múltiples veces según sea necesario (dentro de un ciclo con un retraso).

Una vez completado, haga otra copia de seguridad, luego cambie el modelo de recuperación al que estaba originalmente y agregue índices antiguos.

Recuerde que cada índice o desencadenante en esa tabla causa E / S de disco adicional y que el modo de recuperación simple minimiza la E / S del archivo de registro.

Answer 5

También puede considerar probar para ver si un paquete SSIS puede hacer esto de manera más eficiente.

Hagas lo que hagas, conviértelo en un proceso automatizado que se puede programar y ejecutar fuera de horas. Para los usuarios más pequeños que intenta acceder a los datos, más rápido irá todo. Si es posible, seleccione los tres o cuatro más críticos para cambiar y tome la base de datos como mantenimiento (durante un tiempo de apagado normal) y hágalo en modo de usuario único. Una vez que obtienes los más críticos, los otros se pueden programar uno o dos por noche.