¿Cómo agregar un índice en un sistema cargado 24/7 sin tiempo de inactividad?

Amigos, a fines de enero, comenzaremos un nuevo curso llamado "MS SQL Server Developer ". En anticipación a su lanzamiento, le pedimos a la maestra del curso, Kristina Kucherova , que preparara un artículo de autor. Este artículo le será útil si tiene una tabla muy popular en el producto con acceso 24/7 y de repente se da cuenta de que necesita agregar un índice con urgencia y no romper nada en el proceso.

Entonces que hacer? El método tradicional CREAR ÍNDICE CON (EN LÍNEA = ENCENDIDO) no es adecuado para usted, porque, por ejemplo, causa un bloqueo del sistema y un ataque cardíaco a su DBA, todas las partes superiores monitorean de cerca el tiempo de respuesta de su sistema y, si aumenta, acuden a usted y a su DBA para hablar. con respecto a las cifras exageradas de su compensación por el trabajo.

Se utilizaron scripts y técnicas descritas en un sistema con una carga de 400 K solicitudes por minuto, versiones de SQL Server 2012 y 2016 (Enterprise).

Existen dos enfoques muy diferentes para crear un índice, que se utilizan según el tamaño de la tabla.

Caso No. 1. Una mesa pequeña pero muy popular

Una tabla de 50 mil registros (pequeños), pero muy popular (varios miles de visitas por minuto). Necesita un nuevo índice y un tiempo de inactividad mínimo y bloqueos en la mesa.
En la aplicación, todo el acceso a la base de datos es solo a través de procedimientos.

Si se produce un error, la aplicación volverá a intentar acceder a la tabla.



¿Cuál es el problema de aplicar este índice simplemente, preguntas? Con la oración WITH ONLINE = ON (sí, tuvimos suerte, y esta fue Enterprise).

El hecho es que con un acceso tan activo, lleva algún tiempo obtener un bloqueo (incluso el mínimo que se necesita con la opción Online = ON). En el proceso de espera, se ponen en cola nuevas solicitudes, la cola se acumula, la CPU está creciendo, el DBA está sudando y entrecerrando los ojos nerviosamente hacia los desarrolladores, mientras que en los gráficos de monitoreo de la aplicación su tiempo de respuesta comienza a aumentar sin problemas, pero inevitablemente. Su Vicepresidente de Ingeniería está muy interesado en saber si, debido a este aumento en el tiempo de respuesta, habrá algún tipo de tiempo de inactividad del sistema, que al final del año la disponibilidad de la aplicación se estimará no 5 nueves (99,999), sino menor. Y luego la compañía tiene contratos, obligaciones y fuertes multas en caso de disponibilidad reducida, y, por supuesto, no nos olvidaremos de las pérdidas de reputación.

¿Qué hemos hecho para evitar esta desafortunada situación?
El sistema todavía necesita un índice.
Tomaron los derechos de todos, excepto la sesión actual en esta tabla.
Aplica el índice.

Sí, la solución tiene un signo negativo: todos los que acudieron a la mesa en estos segundos recibirán Acceso denegado. Si su aplicación normalmente maneja tal situación y repite la consulta a la base de datos, entonces debería mirar esta opción. En el caso de nuestro proyecto, este método funcionó bien. De nuevo, puede eliminar de forma segura ONLINE = ON, ya que sabemos que solo la sesión tendrá acceso a la tabla durante la creación del índice.

Código para aplicar el índice:

REVOKE EXECUTE ON [dbo].[spUserLogin] TO [User1] REVOKE EXECUTE ON [dbo].[spUserLogin] TO [User2] REVOKE EXECUTE ON [dbo].[spUserCreate] TO [User1] REVOKE EXECUTE ON [dbo].[spUserCreate] TO [User2] CREATE NONCLUSTERED INDEX IX_Users_Email_Status ON [dbo].[Users] ([Email],[Status]); GRANT EXECUTE ON [dbo].[spUserCreate] TO [User1] GRANT EXECUTE ON [dbo].[spUserCreate] TO [User2] GRANT EXECUTE ON [dbo].[spUserLogin] TO [User1] GRANT EXECUTE ON [dbo].[spUserLogin] TO [User2] 

Programación del tiempo de respuesta y porcentaje de errores durante las pruebas bajo carga.



El método se puede aplicar si, como en el caso descrito, tiene una tabla pequeña y sabe que sin carga, el índice se creará en segundos (o en un tiempo aceptable para usted). Al mismo tiempo, como puede ver en el gráfico anterior, el tiempo de respuesta de la aplicación no aumentará, aunque se puede ver que la tasa de error en segundos sin acceso a la tabla fue mayor.

Caso No. 2. Mesa grande

Si tiene una tabla grande y necesita cambiar los índices, entonces la forma más sencilla de vender es crear una tabla al lado con el índice correcto y transferir gradualmente los datos a una nueva tabla.

Hay 2 formas:

1. Si tiene un procedimiento especial para cambiar una tabla, simplemente cambie el código del procedimiento para que los datos nuevos se inserten solo en la nueva tabla, la eliminación sea de ambos, la actualización también se aplicó a ambos y la selección se realizó desde dos tablas con UNION ALL.
2. Si tiene muchas partes diferentes del código donde puede cambiar los datos en la tabla, entonces hay dos trucos populares: ver con disparadores o reescribir todas las partes del código para insertar datos en una nueva tabla, eliminar de ambas y actualizar ambas tablas. Una vista con disparadores es una opción cuando crea una vista con dos tablas y le cambia el nombre, cambia el nombre de su tabla actual a TableOld y la vista a Tabla. Luego, obtiene automáticamente todas las llamadas de la tabla a la vista, aquí con el cambio de nombre también puede haber un problema, porque SchemaLock es necesario, pero el cambio de nombre pasa muy rápido.

Una versión un poco más detallada sobre la reescritura de llamadas a una nueva tabla:

1. Tiene la tabla Pedidos, cree una nueva tabla PedidosNuevo con el mismo esquema, pero con el índice deseado. Al mismo tiempo, si usa Indentity, debe establecer el primer valor de identidad en la nueva tabla para que sea igual al valor máximo en la tabla anterior + el paso de cambio o la brecha que puede permitirse para desviarse del valor máximo en Órdenes.
2. Cree una vista de pedidos, dentro de la cual una selección de pedidos UNION TODOS los pedidosNuevo
3. Cambie todos los procedimientos / llamadas para seleccionar datos de la vista, insértelos en OrdersNew, elimine y modifique ambas tablas.
4. Migre datos de la tabla anterior a la nueva, por ejemplo, así:
   
   

    DECLARE @rowcount INT, @batchsize INT = 4999; SET IDENTITY_INSERT dbo.OrdersNew ON; SET @rowcount = @batchsize; WHILE @rowcount = @batchsize BEGIN BEGIN TRY DELETE TOP (@batchsize) FROM dbo.Orders OUTPUT deleted.Id ,deleted.Column1 ,deleted.Column2 ,deleted.Column3 INTO dbo.OrdersNew (Id ,Column1 ,Column2 ,Column3); SET @rowcount = @@ROWCOUNT; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage; THROW; END CATCH; END; SET IDENTITY_INSERT dbo.OrdersNew OFF; 

   
   
5. Devuelva todos los procedimientos a la versión antes de la migración, con una tabla. Esto se puede hacer a través de la modificación o la eliminación y creación de procedimientos (luego no se olvide de los derechos), y puede cambiar el nombre de la nueva tabla a Pedidos, eliminando la tabla y la vista vacías.

En el paso 2, era posible, si la carga lo permitía, cambiar el nombre de la tabla principal Pedidos -> OrdersOld, y OrdersView -> Orders y la vista en sí a OrdersOld UNION ALL OrdersNew, entonces no es necesario cambiar todos los lugares donde hay una selección de la tabla.

Al mover bloques de una tabla a otra, los datos se fragmentarán.
Si la tabla que se está cambiando se usa activamente para la lectura, pero los datos en ella rara vez cambian, puede volver a usar disparadores: escriba una copia de todos los cambios en la tercera tabla, transfiera los datos de la tabla a través de bcp out y bcp in (o insert masivo) a una nueva tabla , cree índices después de la transferencia de datos y luego aplique los cambios de la tabla con el registro de cambios, y cambie una tabla a otra, la actual, renombrándola a TableOld y la nueva de TableNew a Table.

La probabilidad de errores en esta situación es ligeramente mayor, así que pruebe la aplicación de cambios y diferentes casos de conmutación en este caso.

Las opciones descritas no son las únicas. Los utilicé en una base de datos de SQL Server muy cargada y no causaron problemas durante la aplicación, lo que agradó a nuestro equipo de DBA. Tal rebote generalmente no es necesario para bases con un modo de carga más tranquilo, cuando puede aplicar cambios de manera segura en las horas de menor actividad. Los usuarios del proyecto que utilizaron los enfoques descritos se encuentran en los EE. UU. Y Europa y utilizan activamente la aplicación entre semana y fines de semana, y las tablas en las que se aplicaron los cambios se usan constantemente en el trabajo. Los objetos más "silenciosos" generalmente se cambiaron mediante scripts automáticos generados a través de Redgate Toolkit después de que el desarrollador y uno de los DBA revisaron los scripts.

Bueno para todos! ¡Comparta en los comentarios si utilizó alguno de estos métodos o describa su método! También lo invitamos a una lección abierta y una jornada de puertas abiertas de nuestro nuevo curso "MS SQL Server Developer"