SQL Q & A: El tamaño sí importa

  • Artículo

Tamaño de la base de datos, fragmentación del índice y disponibilidad después de una conmutación por error; estos son algunos de los problemas que penan a los administradores de SQL este mes.

Paul S. Randal

Miedo a la fragmentación

P. He leído algunos blogs que parecen dar a entender que no necesitamos estar preocupados por la fragmentación del índice si nuestras bases de datos están alojados en el almacenamiento de estado sólido, la teoría de que ser estado sólido (SSD) las unidades son mucho más rápidas que girar discos. ¿Entiendo que se reduciría la degradación de la performance, pero podemos ignorar simplemente completamente fragmentación del índice?

**R.**Si está utilizando discos de hilado o .ssds., es necesario prestar atención a la fragmentación del índice. Fragmentación del índice abarca dos fenómenos: problemas de densidad de página y las páginas de índice fuera de orden. El ex evita lectura eficiente por delante durante los análisis de rango de índice y éste reduce la densidad de datos.

Es cierto que las latencias de lectura/escritura con SSD son muy pequeñas. En consecuencia, la necesidad de realizar operaciones de E/s más frecuentes, más pequeñas de lectura anticipada cuando alcance un índice de fragmentación de análisis no tendrá en cualquier lugar cercano como mucho de un efecto de rendimiento como en la misma situación cuando se utilizan discos de hilado.

Sin embargo, la reducción en la densidad de datos de la fragmentación del índice puede ser un gran problema. La mayoría de fragmentación de índice se produce de una operación llamada un "divide página." Esto ocurre cuando el espacio libre se crea en una página en un índice moviendo la mitad de las filas de índice a una página nueva. Esto deja a las viejas y nuevas páginas con aproximadamente 50 por ciento de espacio vacío. Con un índice muy fragmentado, no es raro ver índices con una densidad promedio de página del 70 por ciento o menos (con espacio libre de 30 por ciento).

Con esto en mente, si un gran número de índices en las bases de datos almacenados en sus SSDs tiene página baja densidad, esto significa que su costoso SSD podría almacenar una gran cantidad de espacio vacío. Claramente, esto no es una situación óptima. También, a pesar de las E/s adicionales necesarios para leer en las páginas de baja densidad tendrá bajas latencias en el SSD, que te ocupan más espacio en el búfer de SQL Server (la caché de páginas del archivo de datos en memoria). Esto también significa que su memoria de servidor preciosos no está siendo utilizada de forma óptima.

La otra cosa a tener en cuenta además de la fragmentación del índice propio es la causa de la fragmentación: se divide la página. Estas son operaciones costosas que generan una gran cantidad de registros de transacciones (echa un vistazo a mi blog post para ver lo malo que puede ser). Estos registros adicionales significan procesamiento adicional por cualquier cosa que se lee el registro de transacciones (tales como la replicación transaccional, copias de seguridad de bases de datos espejado, trasvase de registros). Esto puede causar la degradación de la performance de esos mismos procesos. Para no ignorar la fragmentación del índice sólo porque utilizas SSD.

No mirar el espejo

P. Nos estamos rediseñando nuestra estrategia de disponibilidad, pero han atorarse sobre cómo hacer un par de replicación transaccional de bases de datos de suscripción más altamente disponible. Nosotros no podíamos utilizar espejado de base de datos en SQL Server 2005 porque tendríamos que reinicializar la suscripción después de un failover. ¿Hay una solución mejor ahora que estamos en SQL Server 2008 R2?

**R.**Usted es correcto afirmar que sólo el reflejo de una base de datos de suscripción en SQL Server 2005 proporciona una copia redundante de los datos que ya se ha visto reflejadas en la copia de espejo de la base de datos de suscripción. No hay forma para recrear la suscripción de replicación sin una reinicialización completa. Obviamente, esto hace de base de datos, reflejo de una mala decisión en SQL Server 2005.

Con SQL Server 2008, hubo un nuevo mecanismo que se introdujo en la replicación transaccional que permite la reinicialización parcial de una suscripción. La opción se llama "inicializar desde lsn". Se especifica que el parámetro @ sync_type al llamar a sp_addsubscription.

Peer-to-peer replicación transaccional en SQL Server 2008 fue mejorada que permite agregar y quitar nodos en una topología de peer-to-peer, sin tener que hacer toda la actividad de la topología completamente quieto en primer lugar. Esto fue un gran impulso a la topología de peer-to-peer de disponibilidad de datos proporciona. Se agregó la opción de "inicialización de lsn" como estas mejoras estaban en el lugar.

Espejado de bases de datos, no hay ningún soporte adicional para el espejado de bases de datos de suscripción (como existe en el agente de lector del registro para el espejado de bases de datos de publicación). Sin embargo, puede utilizar el método de "inicialización de lsn" para proporcionar un rápido reinicialización de una suscripción después de un failover espejado.

Esta metodología se basa en determinar el número de secuencia de registro (LSN — un número único que identifica un registro de transacciones) de los más recientes replican operación aplicada a la base de datos de suscripción antes de que ocurriera el failover espejado. Llamaremos a este LSN2.

Algunas de estas operaciones también habrán visto reflejado en la copia de espejo de la base de datos antes de que ocurriera la conmutación por error. Esto podría subir a LSN3, por ejemplo, un poco más atrás en el tiempo de LSN2. También habrá algunas operaciones que aún no se ha aplicado a la base de datos de suscripción a todos. Estos son más recientes en el tiempo que LSN2 o LSN3. Llamaremos a los LSN1.

Todas las operaciones hasta LSN2 se aplicaron a la base de datos principal de suscripción. Todas las operaciones hasta LSN3 se aplica a la base de datos principal de suscripción y reflejadas a la base de datos de suscripción de espejo. Para realizar una inicialización "inicialización de lsn" de una nueva suscripción después de un failover espejado, la llamada a sp_addsubscription debe usar LSN3 como punto de partida.

La retención de distribución período de tiempo también se debe establecer por lo que las operaciones se mantienen en la base de datos de distribución durante algún tiempo después de que han sido aplicados a la base de datos de suscripción. En definitiva, ahora puede utilizar espejado de bases de datos para proporcionar una mayor disponibilidad de una base de datos de suscripción con sólo una reinicialización mínima requerida después de un failover espejado. Para una explicación más detallada sobre esto, descargue el libro blanco, "replicación de SQL Server: Ofrecer un espejado de alta disponibilidad utilizando base de datos. "

Demasiado grande para asa

P. Nuestra base de datos principal ha llegado a casi 9 TB. Estamos encontrando que simplemente no tenemos la capacidad para realizar tareas de mantenimiento sin afectar seriamente nuestras cargas de trabajo regulares. Estamos más preocupados por ser capaz de realizar copias de seguridad de la base de datos para permitir la recuperación ante desastres. ¿Tienes algún Consejo?

**R.**Este es un caso donde dividir la base de datos en trozos más manejables sería beneficioso. Puede hacerlo de varias maneras, la más común utiliza el SQL Server/índice de tabla partición característica (Enterprise Edition) o manualmente divide las cosas en tablas separadas.

En cualquier caso, el punto crucial es crear varios grupos de archivos en la base de datos. Con la partición, cada partición de los índices y tablas más grandes reside en un grupo de archivos independiente. Con separación manual, cada tabla grande reside en un grupo de archivos separado (posiblemente con todos sus índices así).

Mediante el uso de grupos de archivos separados, tiene unidades más granulares de la base de datos que se puede realizar copias de seguridad y restaurar. No tendrás que operan en la 9 TB todo cada vez. Si tuviera una base de datos de venta, por ejemplo, con datos de 2012 hasta 2008, usted podría particionar varias tablas por rango de datos en particiones de año. Cada partición de año estaría en un grupo de archivos independiente.

Con sólo los 2012 filegroup en cambios, usted podría respaldarla con frecuencia. Podría respaldar otros grupos de archivos que no cambian mucho menos frecuentes. Esto ahorra espacio de almacenamiento de copia de seguridad y la cantidad de tiempo que se incurre en la I/O adicional sobrecarga de realizar la copia de seguridad en el sistema de producción.

Con esa disposición, recuperación ante desastres también se vuelve más rápido (mediante Enterprise Edition). Sólo necesita restaurar rápidamente los grupos de archivos necesarios para llevar la parte de procesamiento de transacciones en línea (OLTP) de la carga de trabajo en línea. Puede hacer esto con una restauración parcial y, a continuación, utilice disponibilidad parcial de bases de datos para poner en línea la base de datos. Puede restaurar los grupos de archivos que contiene los datos más antiguos más adelante mediante restauración gradual en línea, mientras que la actividad OLTP se está produciendo en los grupos de archivos en línea ya.

Puede leer que más acerca de este enfoque en estos white papers:

Bajo presión

P. Una cosa que confunde nuestro equipo DBA es cómo saber si está siendo presionado el pool de buffer. Hay un montón de información contradictoria sobre qué contadores PerfMon y qué umbrales a utilizar. La mayor parte de lo que he leido dice que use Page la esperanza de vida (PLE) y 300 como un umbral. ¿Puede arrojar alguna luz sobre esto?

**R.**No estás solo en tu confusión. El número 300 en primer lugar se hace referencia en un documento de Microsoft publicado hace cinco años. Es ahora muy anticuado.

PLE es el contador adecuado utilizar, pero tienes que entender lo que significa y cuándo se trate. Ese número proporciona una medida instantánea de la agresividad del pool de buffer es hacer espacio para las páginas de archivo de datos requeridos ser leídos desde el disco en la memoria. No es un promedio móvil. Es el tiempo en segundos que lee una página del disco estancia en memoria antes de que es necesario vaciar fuera tan otra página puede tendrá su lugar esperado.

Como tal, mirando un único valor PLE no decir mucho. Es necesario mirar las tendencias del valor. Es enteramente posible para las operaciones de SQL Server válidas causar el PLE a caer drásticamente. A menudo, a continuación, se recuperará a su valor anterior. Si PLE desciende y permanece baja, es un motivo de preocupación.

El umbral para cuando se trate no es un valor fijo, como muchas personas describen. Los medios de 300 valor el búfer completo está siendo sustituido cada 300 segundos. Si tienes un buffer de 100 GB, por ejemplo, esto significa que 100 GB de datos nuevos se están leyendo en la memoria cada cinco minutos. Eso es claramente un problema de rendimiento. Sin embargo, se convierte en una forma de problema de rendimiento masivo antes PLE hits 300. Puede calcular un uso más razonable de valor (un búfer de memoria en GB / 4) * 300, como se explica en esta entrada en el blog.

También debe ser consciente de la configuración de acceso (NUMA) de memoria no uniforme del servidor. El contador PLE en el objeto de rendimiento del administrador de búfer es realmente la media de las PLEs para cada nodo NUMA, si tienes NUMA configurado. Esto significa vigilar el búfer administrar PLE no es un verdadero indicador de presión de la memoria en el servidor. En ese caso, debe medir el contador PLE en cada uno de los objetos de rendimiento de la partición de la memoria intermedia. Puede leer más sobre PLE y NUMA en esta entrada en el blog.

PLE es el contador derecho a supervisar, pero sólo debería estar preocupado si el valor disminuye significativamente por debajo de la normal y permanece allí por un largo tiempo. Es una pauta general, pero lamentablemente no hay ninguna información específica que se aplican a todas las situaciones.

Paul S. Randal

**Paul S. Randal**es el director de SQLskills.com, un director regional de Microsoft y un MVP de SQL Server. Trabajó en el equipo de motor de almacenamiento de SQL Server en Microsoft desde 1999 a 2007. Escribió DBCC CHECKDB y reparación para SQL Server 2005 y fue responsable por el motor de almacenamiento básico durante el desarrollo de SQL Server 2008. Randal es un experto en recuperación ante desastres, alta disponibilidad y mantenimiento de base de datos y es un presentador regular en conferencias en todo el mundo. Blogs de él en SQLskills.com/blogs/paul, y se le puede encontrar en Twitter en twitter.com/PaulRandal.

Contenido relacionado