SQL Server: Minimizar la E/S de disco
Consulta e indexación de sintonización es una manera eficaz de reducir la E/S de disco físico y lógico.
Extraído de "SQL Server DMV Starter Pack", publicado por libros de puerta roja (2010).
Glenn Berry, Louis Davidson y Tim Ford
Hay una persistente necesidad de minimizar I/O lógicos y físicos. La colección de objetos de administración de base de datos de / O-relacionadas (OGD) ayuda a investigar, específicamente, entrada-salida física tienen lugar en el sistema, cuando los datos se escriben y se leen del disco.
La OGD en esta categoría proporciona una imagen explícita de E/S de disco desde el punto de vista del subsistema de disco. Nos muestran, por ejemplo, cómo la I/O se distribuye en varios archivos en el disco, lugares donde I/O es convertirse en un cuello de botella y resultando en puestos de I/O y así sucesivamente. Puede utilizar esta información para optimizar la arquitectura del subsistema de disco. También puede recopilar datos y utilizarla para apoyar peticiones a los líderes de unidad de negocio de más capacidad de almacenamiento.
Naturalmente, algunos entrada-salida física es inevitable. SQL Server debe escribir datos de aplicaciones en disco. También debe escribir en el registro de transacciones para cada insert, update y delete e incluso para operaciones masivas. Sin embargo, antes de saltar a la conclusión de que simplemente necesita más potencia de disco, recuerde que hay mucho que puede hacer en términos de consulta sintonización e indexación para minimizar I/O lógicos y físicos innecesarios.
Debe tener en cuenta la información de E/S que se deriva de la OGD cubierto aquí (todos que comienzan con "sys.dm_io_"), así como datos de otras vistas de administración dinámicas (DMV) que hacen referencia a rendimiento de E/S de alguna manera, incluyendo:
- Sys.dm_exec_query_stats-i/os que ha costado una consulta concreta sobre las veces que se ha ejecutado
- Sys.dm_exec_connections-i/os que ha tenido lugar en esa conexión
- Sys.dm_exec_sessions – i/os que ha tomado lugar durante ese período de sesiones
- Sys.dm_os_workers – E/S pendientes para un subproceso de trabajo determinado
Todas las consultas en esta sección trabajan con SQL Server 2005, 2008 y 2008 R2, y todos requieren el permiso View Server State.
Investigar los cuellos de botella de disco a través de puestos de la entrada-salida
El DMV que usaremos aquí es sys.dm_io_virtual_file_stats, que describe los libros en pantalla de SQL Server como: "Devuelve las estadísticas de E/S de archivos de datos y de registro. Este punto de vista de administración dinámica reemplaza la función de fn_virtualfilestats."
Esta DMV acepta dos argumentos: database_id y file_id. Puede especificar NULL para cualquiera. En ese caso, devolverá información sobre todas las bases de datos o todos los archivos.
Tenga en cuenta que esta DMV es acumulativa. En otras palabras, los valores de las columnas de datos incrementan continuamente desde el punto cuando modificada se reinicia el servidor. Esto significa que necesita tomar una medición de la línea de base, seguida de la medida real. Luego reste los dos, para que usted pueda ver donde está acumulando I/O.
Este script le permite ver el número de lecturas y escrituras en cada archivo de datos y de registro para cada base de datos en una instancia de SQL Server. Se ordena por medio tiempo de parada de E/S, en milisegundos:
-- Calculates average stalls per read, per write, and per total input/output -- for each database file. SELECT DB_NAME(database_id) AS [Database Name] , file_id , io_stall_read_ms , num_of_reads , CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [avg_read_stall_ms] , io_stall_write_ms , num_of_writes , CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms] , io_stall_read_ms + io_stall_write_ms AS [io_stalls] , num_of_reads + num_of_writes AS [total_io] , CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]FROM sys.dm_io_virtual_file_stats(NULL, NULL)ORDER BY avg_io_stall_ms DESC ;
Esta consulta le mostrará los archivos de la más larga la espera de E/S de disco. Puede ayudarle a decidir donde localizar archivos individuales basados en sus recursos de disco disponible. También puede utilizar para ayudar a convencer a alguien como un SAN Ingeniero SQL Server está viendo disco cuellos de botella para determinados archivos.
Investigar los cuellos de botella de disco a través de pendientes de entrada-salida
Esto toma un enfoque ligeramente diferente a la investigación de los cuellos de botella de E/S de disco. Utilice el sys.dm_io_pending_io_requests DMV, que describe los libros en pantalla de SQL Server como: "SQL Server devuelve una fila para cada solicitud de E/S pendiente."
Los datos en el DMV proporcionan una instantánea de "puntual" de solicitudes de E/S pendientes en el sistema, justo en el momento de que ejecutar el script:
-- Look at pending I/O requests by file SELECT DB_NAME(mf.database_id) AS [Database] , mf.physical_name ,r.io_pending , r.io_pending_ms_ticks , r.io_type , fs. num_of_reads , fs. num_of_writesFROM sys.dm_io_pending_io_requests AS r INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs ON r.io_handle = fs.file_handle INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.file_id = mf.file_idORDER BY r.io_pending , r.io_pending_ms_ticks DESC ;
Porque estos datos representan una instantánea del punto en el tiempo de actividad, desea ejecutar esta consulta varias veces para ver si los mismos archivos (y las mismas letras de unidad) aparecen constantemente en la parte superior de la lista. Si eso ocurre, es una prueba de cuellos de botella de I/O para esa letra de unidad o archivo particular. Podrían utilizar esto para ayudar a convencer a su SAN Ingeniero el sistema estaba experimentando problemas de I/O para un LUN particular.
Las dos últimas columnas de la consulta devuelven el número acumulado de lecturas y escrituras del archivo desde que se inició SQL Server (o desde que se creó el archivo — lo que era más corto). Esta información es útil cuando se trata de decidir qué nivel RAID para una letra de unidad particular. Por ejemplo, archivos con más actividad de escritura generalmente funcionará mejor en un RAID 10 LUN que lo harán en un RAID 5 LUN.
Saber la proporción relativa de lectura/escritura para cada archivo puede ayudarle a colocar los archivos de base de datos en un LUN apropiado. Esto, a su vez, le ayudará sintonizar sus consultas para una mayor eficacia.
.png)
.png)
.png)
Glenn Berry trabaja como arquitecto de base de datos en NewsGator Technologies en Denver, Colorado Él es un MVP de SQL Server y tiene una colección entera de certificaciones de Microsoft, incluyendo MCITP, MCTS, lo que demuestra que le gusta tomar pruebas, MCDBA, MCSE, MCSD y MCAD.
Louis Davidson ha estado en la industria de TI durante 16 años como un arquitecto y desarrollador de base de datos corporativa. Ha sido MVP de Microsoft SQL Server durante seis años y ha escrito cuatro libros sobre diseño de base de datos. Actualmente es el arquitecto de datos y a veces DBA para la Christian Broadcasting Network, apoyo a las oficinas en Virginia Beach, va. y Nashville, Tenn.
Timothy Ford mes un MVP de SQL Server y ha estado trabajando con SQL Server para más de 10 años. Es el principal DBA y experto en materia para la plataforma de SQL Server para la salud de espectro. Ha sido escrito acerca de la tecnología desde 2007 para una variedad de sitios Web y mantiene su propio blog en thesqlagentman.com, cubriendo SQL como temas de desarrollo así como teletrabajo y profesional.**
Conozca más acerca de "SQL Server DMV Starter Pack" en red-gate.com/our-company/about/book-store.