Solución de problemas de rendimiento lento o memoria baja causados por concesiones de memoria en SQL Server
¿Qué son las concesiones de memoria?
Las concesiones de memoria, también conocidas como reservas de ejecución de consultas (QE), memoria de ejecución de consultas, memoria del área de trabajo y reservas de memoria, describen el uso de la memoria en tiempo de ejecución de la consulta. SQL Server asigna esta memoria durante la ejecución de la consulta para uno o varios de los siguientes fines:
- Operaciones de ordenación
- Operaciones hash
- Operaciones de copia masiva (no es un problema común)
- Creación de índices, incluida la inserción en índices COLUMNSTORE porque los diccionarios o tablas hash se usan en tiempo de ejecución para la creación de índices (no es un problema común)
Para proporcionar algún contexto, durante su duración, una consulta puede solicitar memoria de distintos asignadores o empleados de memoria en función de lo que tenga que hacer. Por ejemplo, cuando una consulta se analiza y compila inicialmente, consume memoria de compilación. Una vez compilada la consulta, esa memoria se libera y el plan de consulta resultante se almacena en la memoria caché del plan. Una vez que se almacena en caché un plan, la consulta está lista para su ejecución. Si la consulta realiza operaciones de ordenación, operaciones de coincidencia hash (JOIN o agregados) o inserciones en índices COLUMNSTORE, usa memoria del asignador de ejecución de consultas. Inicialmente, la consulta solicita esa memoria de ejecución y, más adelante, si se concede esta memoria, la consulta usa toda o parte de la memoria para ordenar los resultados o los cubos hash. Esta memoria asignada durante la ejecución de la consulta es lo que se conoce como concesiones de memoria. Como puede imaginar, una vez completada la operación de ejecución de la consulta, la concesión de memoria se libera de vuelta a SQL Server para su uso en otro trabajo. Por lo tanto, las asignaciones de concesión de memoria son temporales por naturaleza, pero pueden durar mucho tiempo. Por ejemplo, si una ejecución de consulta realiza una operación de ordenación en un conjunto de filas muy grande en la memoria, la ordenación puede tardar muchos segundos o minutos y la memoria concedida se usa para la duración de la consulta.
Ejemplo de una consulta con una concesión de memoria
Este es un ejemplo de una consulta que usa memoria de ejecución y su plan de consulta que muestra la concesión:
SELECT *
FROM sys.messages
ORDER BY message_id
Esta consulta selecciona un conjunto de filas de más de 300 000 filas y lo ordena. La operación de ordenación induce una solicitud de concesión de memoria. Si ejecuta esta consulta en SSMS, puede ver su plan de consulta. Al seleccionar el operador más SELECT a la izquierda del plan de consulta, puede ver la información de concesión de memoria de la consulta (presione F4 para mostrar propiedades):
Además, si hace clic con el botón derecho en el espacio en blanco del plan de consulta, puede elegir Mostrar XML del plan de ejecución... y buscar un elemento XML que muestre la misma información de concesión de memoria.
<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="41232" RequiredMemory="5248" DesiredMemory="46016" RequestedMemory="46016" GrantWaitTime="0" GrantedMemory="46016" MaxUsedMemory="45816" MaxQueryMemory="277688" LastRequestedMemory="0" IsMemoryGrantFeedbackAdjusted="No: First Execution" />
Varios términos necesitan explicación aquí. Una consulta puede desear una cantidad determinada de memoria de ejecución (DesiredMemory) y normalmente solicitaría esa cantidad (RequestedMemory). En tiempo de ejecución, SQL Server concede la totalidad o parte de la memoria solicitada en función de la disponibilidad (GrantedMemory). Al final, la consulta puede usar más o menos de la memoria solicitada inicialmente (MaxUsedMemory). Si el optimizador de consultas ha sobreestimado la cantidad de memoria necesaria, usa menos que el tamaño solicitado. Pero esa memoria se desperdicia, ya que podría haber sido utilizada por otra solicitud. Por otro lado, si el optimizador ha subestimado el tamaño de la memoria necesaria, el exceso de filas se puede derramar en el disco para realizar el trabajo en tiempo de ejecución. En lugar de asignar más memoria que el tamaño solicitado inicialmente, SQL Server inserta las filas adicionales en el disco y la usa como área de trabajo temporal. Para obtener más información, vea Archivos de trabajo y tablas de trabajo en Consideraciones de concesión de memoria.
Terminología
Vamos a revisar los diferentes términos que puede encontrar con respecto a este consumidor de memoria. De nuevo, todos estos conceptos describen conceptos relacionados con las mismas asignaciones de memoria.
Memoria de ejecución de consultas (memoria QE): Este término se usa para resaltar el hecho de que se usa la memoria hash o de ordenación durante la ejecución de una consulta. Normalmente, la memoria QE es el mayor consumidor de memoria durante la vida útil de una consulta.
Reservas de ejecución de consultas (QE) o reservas de memoria: Cuando una consulta necesita memoria para operaciones de ordenación o hash, realiza una solicitud de reserva de memoria. Esa solicitud de reserva se calcula en tiempo de compilación en función de la cardinalidad estimada. Más adelante, cuando se ejecuta la consulta, SQL Server concede esa solicitud parcial o totalmente en función de la disponibilidad de la memoria. Al final, la consulta puede usar un porcentaje de la memoria concedida. Hay un empleado de memoria (contable de memoria) denominado "MEMORYCLERK_SQLQERESERVATIONS" que realiza un seguimiento de estas asignaciones de memoria (consulte DBCC MEMORYSTATUS o sys.dm_os_memory_clerks).
Concesiones de memoria: Cuando SQL Server concede la memoria solicitada a una consulta en ejecución, se dice que se ha producido una concesión de memoria. Hay algunos contadores de rendimiento que usan el término "conceder". Estos contadores,
Memory Grants OutstandingyMemory Grants Pending, muestran el recuento de concesiones de memoria satisfechas o en espera. No tienen en cuenta el tamaño de concesión de memoria. Una sola consulta podría haber consumido, por ejemplo, 4 GB de memoria para realizar una ordenación, pero eso no se refleja en ninguno de estos contadores.Memoria del área de trabajo es otro término que describe la misma memoria. A menudo, puede ver este término en el contador
Granted Workspace Memory (KB)Perfmon , que refleja la cantidad total de memoria que se usa actualmente para las operaciones de ordenación, hash, copia masiva y creación de índices, expresada en KB. ElMaximum Workspace Memory (KB), otro contador, representa la cantidad máxima de memoria del área de trabajo disponible para las solicitudes que puedan necesitar realizar dichas operaciones de hash, ordenación, copia masiva y creación de índices. El término Memoria del área de trabajo se encuentra con poca frecuencia fuera de estos dos contadores.
Impacto en el rendimiento del uso de memoria de QE grande
En la mayoría de los casos, cuando un subproceso solicita memoria dentro de SQL Server para realizar algo y la memoria no está disponible, la solicitud produce un error de memoria insuficiente. Sin embargo, hay un par de escenarios de excepción en los que el subproceso no produce errores, pero espera hasta que la memoria esté disponible. Uno de esos escenarios son las concesiones de memoria y el otro es la memoria de compilación de consultas. SQL Server usa un objeto de sincronización de subprocesos denominado semáforo para realizar un seguimiento de la cantidad de memoria que se ha concedido para la ejecución de consultas. Si SQL Server se queda sin el área de trabajo de QE predefinida, en lugar de producir errores en la consulta con un error de memoria insuficiente, hace que la consulta espere. Dado que la memoria del área de trabajo puede tomar un porcentaje significativo de la memoria general SQL Server, esperar la memoria en este espacio tiene graves implicaciones de rendimiento. Un gran número de consultas simultáneas han solicitado memoria de ejecución y, juntos, han agotado el grupo de memoria de QE o algunas consultas simultáneas han solicitado concesiones muy grandes. En cualquier caso, los problemas de rendimiento resultantes pueden tener los siguientes síntomas:
- Es probable que las páginas de datos e índices de una memoria caché de búfer se hayan vaciado para hacer espacio para las solicitudes de concesión de memoria de gran tamaño. Esto significa que las lecturas de página procedentes de solicitudes de consulta deben satisfacerse desde el disco (una operación significativamente más lenta).
- Las solicitudes de otras asignaciones de memoria pueden producir errores de memoria insuficiente porque el recurso está vinculado con operaciones de ordenación, hash o creación de índices.
- Las solicitudes que necesitan memoria de ejecución están esperando a que el recurso esté disponible y tardan mucho tiempo en completarse. En otras palabras, para el usuario final, estas consultas son lentas.
Por lo tanto, si observa esperas en la memoria de ejecución de consultas en Perfmon, vistas de administración dinámica (DMV) o DBCC MEMORYSTATUS, debes actuar para resolver este problema, especialmente si el problema se produce con frecuencia. Para obtener más información, consulte ¿Qué puede hacer un desarrollador sobre las operaciones de ordenación y hash?
Identificación de esperas para la memoria de ejecución de consultas
Hay varias maneras de determinar las esperas para las reservas de QE. Elija los que mejor le sirvan para ver la imagen más grande en el nivel de servidor. Es posible que algunas de estas herramientas no estén disponibles (por ejemplo, Perfmon no está disponible en Azure SQL Database). Una vez que identifique el problema, debe explorar en profundidad el nivel de consulta individual para ver qué consultas necesitan ajuste o reescritura.
En el nivel de servidor, use los métodos siguientes:
- DMV de semáforo de recursos sys.dm_exec_query_resource_semaphores Para obtener más información, consulte sys.dm_exec_query_resource_semaphores.
- Monitor de rendimiento contadores Para obtener más información, consulte SQL Server objeto del Administrador de memoria.
- DBCC MEMORYSTATUS Para obtener más información, vea DBCC MEMORYSTATUS.
- DMV de los distribuidores de memoria sys.dm_os_memory_clerks Para obtener más información, consulte sys.dm_os_memory_clerks.
- Identificación de concesiones de memoria mediante eventos extendidos (XEvents) Para obtener más información, vea Eventos extendidos (XEvents).
En el nivel de consulta individual, use los métodos siguientes:
- Identificar consultas específicas con sys.dm_exec_query_memory_grants: consultas actualmente en ejecución. Para obtener más información, consulte sys.dm_exec_query_memory_grants.
- Identificar consultas específicas con sys.dm_exec_requests: consultas actualmente en ejecución. Para obtener más información, consulte sys.dm_exec_requests.
- Identificar consultas específicas con sys.dm_exec_query_stats: Estadísticas históricas de consultas. Para obtener más información, consulte sys.dm_exec_query_stats.
- Identifique consultas específicas mediante Almacén de consultas (QDS) con sys.query_store_runtime_stats: Estadísticas históricas de consultas con QDS. Para obtener más información, consulte sys.query_store_runtime_stats.
Estadísticas de uso de memoria agregadas
DMV de semáforo de recursos sys.dm_exec_query_resource_semaphores
Esta DMV desglosa la memoria de reserva de consultas por grupo de recursos (interno, predeterminado y creado por el usuario) y resource_semaphore (solicitudes de consulta normales y pequeñas). Una consulta útil puede ser:
SELECT
pool_id
,total_memory_kb
,available_memory_kb
,granted_memory_kb
,used_memory_kb
,grantee_count, waiter_count
,resource_semaphore_id
FROM sys.dm_exec_query_resource_semaphores rs
En la salida de ejemplo siguiente se muestra que 22 solicitudes usan alrededor de 900 MB de memoria de ejecución de consultas y 3 más están esperando. Esto tiene lugar en el grupo predeterminado (pool_id = 2) y el semáforo de consulta normal (resource_semaphore_id = 0).
pool_id total_memory_kb available_memory_kb granted_memory_kb used_memory_kb grantee_count waiter_count resource_semaphore_id
------- --------------- ------------------- ----------------- -------------- ------------- ------------ ---------------------
1 30880 30880 0 0 0 0 0
1 5120 5120 0 0 0 0 1
2 907104 0 907104 898656 22 3 0
2 40960 40960 0 0 0 0 1
(4 rows affected)
contadores de Monitor de rendimiento
Hay información similar disponible a través de Monitor de rendimiento contadores, donde puede observar las solicitudes actualmente concedidas (Memory Grants Outstanding), las solicitudes de concesión en espera (Memory Grants Pending) y la cantidad de memoria usada por las concesiones de memoria (Granted Workspace Memory (KB)). En la siguiente imagen, las concesiones pendientes son 18, las concesiones pendientes son 2 y la memoria del área de trabajo concedida es de 828 288 KB. El Memory Grants Pending contador Perfmon con un valor distinto de cero indica que se ha agotado la memoria.
Para obtener más información, vea SQL Server objeto del Administrador de memoria.
- SQLServer, Administrador de memoria: memoria máxima del área de trabajo (KB)
- SQLServer, Administrador de memoria: concesiones de memoria pendientes
- SQLServer, Administrador de memoria: concesiones de memoria pendientes
- SQLServer, Administrador de memoria: Memoria del área de trabajo concedida (KB)
DBCC MEMORYSTATUS
Otro lugar donde puede ver detalles sobre la memoria de reserva de consultas es DBCC MEMORYSTATUS (sección Objetos de memoria de consulta). Puede ver la Query Memory Objects (default) salida de las consultas de usuario. Si ha habilitado Resource Governor con un grupo de recursos denominado PoolAdmin, por ejemplo, puede ver tanto como Query Memory Objects (default)Query Memory Objects (PoolAdmin).
Este es un resultado de ejemplo de un sistema en el que se ha concedido memoria de ejecución de consultas a 18 solicitudes y 2 solicitudes están esperando memoria. El contador disponible es cero, lo que indica que no hay más memoria del área de trabajo disponible. Este hecho explica las dos solicitudes en espera. Wait Time muestra el tiempo transcurrido en milisegundos desde que se puso una solicitud en la cola de espera. Para obtener más información sobre estos contadores, consulte Consulta de objetos de memoria.
Query Memory Objects (default) Value
------------------------------------------------------------------------ -----------
Grants 18
Waiting 2
Available 0
Current Max 103536
Future Max 97527
Physical Max 139137
Next Request 5752
Waiting For 8628
Cost 16
Timeout 401
Wait Time 2750
(11 rows affected)
Small Query Memory Objects (default) Value
------------------------------------------------------------------------ -----------
Grants 0
Waiting 0
Available 5133
Current Max 5133
Future Max 5133
DBCC MEMORYSTATUS también muestra información sobre el empleado de memoria que realiza un seguimiento de la memoria de ejecución de consultas. La salida siguiente muestra que las páginas asignadas para las reservas de ejecución de consultas (QE) superan los 800 MB.
MEMORYCLERK_SQLQERESERVATIONS (node 0) KB
------------------------------------------------------------------------ -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 824640
DMV de los distribuidores de memoria sys.dm_os_memory_clerks
Si necesita más de un conjunto de resultados tabular, diferente del basado en DBCC MEMORYSTATUSsecciones, puede usar sys.dm_os_memory_clerks para obtener información similar. Busque al empleado de MEMORYCLERK_SQLQERESERVATIONS memoria. Sin embargo, los objetos de memoria de consulta no están disponibles en esta DMV.
SELECT type, memory_node_id, pages_kb
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLQERESERVATIONS'
Esta es una salida de ejemplo:
type memory_node_id pages_kb
----------------------------------------------- -------------- --------------
MEMORYCLERK_SQLQERESERVATIONS 0 824640
MEMORYCLERK_SQLQERESERVATIONS 64 0
Identificación de concesiones de memoria mediante eventos extendidos (XEvents)
Hay varios eventos extendidos que proporcionan información de concesión de memoria y le permiten capturar esta información a través de un seguimiento:
- sqlserver.additional_memory_grant: se produce cuando una consulta intenta obtener más concesión de memoria durante la ejecución. El error al obtener esta concesión de memoria adicional puede provocar la ralentización de la consulta.
- sqlserver.query_memory_grant_blocking: se produce cuando una consulta bloquea otras consultas mientras espera una concesión de memoria.
- sqlserver.query_memory_grant_info_sampling: se produce al final de las consultas muestreadas aleatoriamente que proporcionan información de concesión de memoria (se puede usar, por ejemplo, para telemetría).
- sqlserver.query_memory_grant_resource_semaphores: se produce a intervalos de cinco minutos para cada grupo de recursos del regulador de recursos.
- sqlserver.query_memory_grant_usage: se produce al final del procesamiento de consultas con concesiones de memoria de más de 5 MB para informar a los usuarios sobre las imprecisiones de concesión de memoria.
- sqlserver.query_memory_grants: se produce a intervalos de cinco minutos para cada consulta con una concesión de memoria.
Eventos extendidos de comentarios de concesión de memoria
Para obtener información sobre las características de comentarios de concesión de memoria de procesamiento de consultas, consulte Comentarios de concesión de memoria.
- sqlserver.memory_grant_feedback_loop_disabled: se produce cuando se deshabilita el bucle de comentarios de concesión de memoria.
- sqlserver.memory_grant_updated_by_feedback: se produce cuando los comentarios actualizan la concesión de memoria.
Advertencias de ejecución de consultas relacionadas con concesiones de memoria
- sqlserver.execution_warning: se produce cuando una instrucción T-SQL o un procedimiento almacenado espera más de un segundo por una concesión de memoria o cuando se produce un error en el intento inicial de obtener memoria. Use este evento en combinación con eventos que identifiquen las esperas para solucionar problemas de contención que afectan al rendimiento.
- sqlserver.hash_spill_details: se produce al final del procesamiento hash si no hay memoria suficiente para procesar la entrada de compilación de una combinación hash. Use este evento junto con cualquiera de los
query_pre_execution_showplaneventos oquery_post_execution_showplanpara determinar qué operación del plan generado está causando el derrame de hash. - sqlserver.hash_warning: se produce cuando no hay memoria suficiente para procesar la entrada de compilación de una combinación hash. Esto da como resultado una recursividad hash cuando la entrada de compilación está particionada o un rescate hash cuando la partición de la entrada de compilación supera el nivel máximo de recursividad. Use este evento junto con cualquiera de los
query_pre_execution_showplaneventos oquery_post_execution_showplanpara determinar qué operación del plan generado está causando la advertencia hash. - sqlserver.sort_warning: se produce cuando la operación de ordenación de una consulta en ejecución no cabe en la memoria. Este evento no se genera para operaciones de ordenación causadas por la creación de índices, solo para operaciones de ordenación en una consulta. (Por ejemplo, en
Order ByunaSelectinstrucción ). Use este evento para identificar las consultas que se realizan lentamente debido a la operación de ordenación, especialmente cuando =warning_type2, que indica que se necesitan varias pasadas sobre los datos para ordenar.
Planear la generación de eventos que contienen información de concesión de memoria
El siguiente plan de consulta que genera eventos extendidos contiene campos granted_memory_kb y ideal_memory_kb de forma predeterminada:
- sqlserver.query_plan_profile
- sqlserver.query_post_execution_plan_profile
- sqlserver.query_post_execution_showplan
- sqlserver.query_pre_execution_showplan
Creación de índices de almacén de columnas
Una de las áreas cubiertas a través de XEvents es la memoria de ejecución que se usa durante la compilación del almacén de columnas. Esta es una lista de eventos disponibles:
- sqlserver.column_store_index_build_low_memory: El motor de almacenamiento detectó una condición de memoria baja y se redujo el tamaño del grupo de filas. Hay varias columnas de interés aquí.
- sqlserver.column_store_index_build_memory_trace: Seguimiento del uso de memoria durante la compilación del índice.
- sqlserver.column_store_index_build_memory_usage_scale_down: Motor de almacenamiento escalado verticalmente.
- sqlserver.column_store_index_memory_estimation: muestra el resultado de la estimación de memoria durante la compilación del grupo de filas COLUMNSTORE.
Identificación de consultas específicas
Hay dos tipos de consultas que puede encontrar al examinar el nivel de solicitud individual. Las consultas que consumen una gran cantidad de memoria de ejecución de consultas y las que esperan la misma memoria. Este último grupo puede constar de solicitudes con necesidades modestas de concesiones de memoria y, si es así, puede centrar su atención en otro lugar. Pero también podrían ser los culpables si solicitan grandes tamaños de memoria. Céntrese en ellos si considera que es así. Puede ser común encontrar que una consulta determinada es el delincuente, pero se generan muchas instancias de ella. Esas instancias que obtienen las concesiones de memoria hacen que otras instancias de la misma consulta esperen a la concesión. Independientemente de circunstancias específicas, en última instancia, debe identificar las consultas y el tamaño de la memoria de ejecución solicitada.
Identificar consultas específicas con sys.dm_exec_query_memory_grants
Para ver las solicitudes individuales y el tamaño de memoria que han solicitado y se han concedido, puede consultar la sys.dm_exec_query_memory_grants vista de administración dinámica. Esta DMV muestra información sobre la ejecución de consultas actualmente, no información histórica.
La instrucción siguiente obtiene datos de la DMV y también captura el texto de la consulta y el plan de consulta como resultado:
SELECT
session_id
,requested_memory_kb
,granted_memory_kb
,used_memory_kb
,queue_id
,wait_order
,wait_time_ms
,is_next_candidate
,pool_id
,text
,query_plan
FROM sys.dm_exec_query_memory_grants
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
Esta es una salida de ejemplo abreviada de la consulta durante el consumo de memoria de QE activo. La mayoría de las consultas tienen su memoria concedida, como se muestra en granted_memory_kb valores used_memory_kb numéricos que no son NULL. Las consultas que no han concedido su solicitud están esperando la memoria de ejecución y .granted_memory_kb = NULL Además, se colocan en una cola de espera con un queue_id = 6. Su wait_time_ms indica unos 37 segundos de espera. La sesión 72 es la siguiente en línea para obtener una concesión como se indica wait_order en = 1, mientras que la sesión 74 viene después de ella con wait_order = 2.
session_id requested_memory_kb granted_memory_kb used_memory_kb queue_id wait_order wait_time_ms is_next_candidate pool_id
---------- -------------------- -------------------- -------------------- -------- ----------- -------------------- ----------------- -------
80 41232 41232 40848 NULL NULL NULL NULL 2
83 41232 41232 40848 NULL NULL NULL NULL 2
84 41232 41232 40848 NULL NULL NULL NULL 2
74 41232 NULL NULL 6 2 37438 0 2
78 41232 41232 40848 NULL NULL NULL NULL 2
81 41232 41232 40848 NULL NULL NULL NULL 2
71 41232 41232 40848 NULL NULL NULL NULL 2
75 41232 NULL NULL 6 0 37438 1 2
82 41232 41232 40848 NULL NULL NULL NULL 2
76 41232 41232 40848 NULL NULL NULL NULL 2
79 41232 41232 40848 NULL NULL NULL NULL 2
85 41232 41232 40848 NULL NULL NULL NULL 2
70 41232 41232 40848 NULL NULL NULL NULL 2
55 41232 41232 40848 NULL NULL NULL NULL 2
59 41232 NULL NULL 6 3 37438 0 2
62 41232 41232 40848 NULL NULL NULL NULL 2
54 41232 41232 40848 NULL NULL NULL NULL 2
77 41232 41232 40848 NULL NULL NULL NULL 2
52 41232 41232 40848 NULL NULL NULL NULL 2
72 41232 NULL NULL 6 1 37438 0 2
69 41232 41232 40848 NULL NULL NULL NULL 2
73 41232 41232 40848 NULL NULL NULL NULL 2
66 41232 NULL NULL 6 4 37438 0 2
68 41232 41232 40848 NULL NULL NULL NULL 2
63 41232 41232 40848 NULL NULL NULL NULL 2
Identificar consultas específicas con sys.dm_exec_requests
Hay un tipo de espera en SQL Server que indica que una consulta está esperando la concesión RESOURCE_SEMAPHOREde memoria . Puede observar este tipo de espera en sys.dm_exec_requests para solicitudes individuales. Esta última DMV es el mejor punto de partida para identificar qué consultas son víctimas de memoria de concesión insuficiente. También puede observar la RESOURCE_SEMAPHORE espera en sys.dm_os_wait_stats como puntos de datos agregados en el nivel de SQL Server. Este tipo de espera se muestra cuando no se puede conceder una solicitud de memoria de consulta debido a que otras consultas simultáneas han agotado la memoria. Un gran número de solicitudes en espera y tiempos de espera largos indican un número excesivo de consultas simultáneas mediante memoria de ejecución o tamaños de solicitud de memoria grandes.
Nota:
El tiempo de espera de las concesiones de memoria es finito. Después de una espera excesiva (por ejemplo, más de 20 minutos), SQL Server agota el tiempo de espera de la consulta y genera el error 8645, "Se ha producido un tiempo de espera mientras se esperaba que los recursos de memoria ejecutaran la consulta. Vuelva a ejecutar la consulta." Puede ver el valor de tiempo de espera establecido en el nivel de servidor examinando timeout_sec en sys.dm_exec_query_memory_grants. El valor de tiempo de espera puede variar ligeramente entre SQL Server versiones.
Con el uso de sys.dm_exec_requests, puede ver qué consultas se han concedido memoria y el tamaño de esa concesión. Además, puede identificar qué consultas están esperando actualmente una concesión de memoria si busca el RESOURCE_SEMAPHORE tipo de espera. Esta es una consulta que muestra las solicitudes concedidas y las solicitudes en espera:
SELECT session_id, wait_type, wait_time, granted_query_memory, text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE granted_query_memory > 0
OR wait_type = 'RESOURCE_SEMAPHORE'
Una salida de ejemplo muestra que se ha concedido memoria a dos solicitudes y otras dos docenas esperan concesiones. La granted_query_memory columna notifica el tamaño en páginas de 8 KB. Por ejemplo, un valor de 34 709 significa 34 709 * 8 KB = 277 672 KB de memoria concedida.
session_id wait_type wait_time granted_query_memory text
---------- ----------------------------------- -------------------- -------------------------------------------------------------------
65 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
66 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
67 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
68 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
69 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
70 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
71 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
72 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
73 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
74 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
75 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
76 ASYNC_NETWORK_IO 11 34709 select * from sys.messages order by message_id option (maxdop 1)
77 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
78 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
79 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
80 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
81 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
82 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
83 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
84 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
85 ASYNC_NETWORK_IO 14 34709 select * from sys.messages order by message_id option (maxdop 1)
86 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
87 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
88 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
89 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
Identificar consultas específicas con sys.dm_exec_query_stats
Si el problema de concesión de memoria no se está produciendo en este momento, pero le gustaría identificar las consultas infractores, puede examinar los datos históricos de las consultas a través de sys.dm_exec_query_stats. La duración de los datos está asociada al plan de consulta de cada consulta. Cuando se quita un plan de la memoria caché del plan, las filas correspondientes se eliminan de esta vista. En otras palabras, la DMV mantiene las estadísticas en memoria que no se conservan después de un reinicio de SQL Server o después de que la presión de memoria provoque una liberación de caché del plan. Dicho esto, puede encontrar la información aquí valiosa, especialmente para las estadísticas de consulta agregadas. Es posible que alguien haya informado recientemente de que ha visto concesiones de memoria grandes de consultas, pero al examinar la carga de trabajo del servidor, es posible que descubra que el problema ha desaparecido. En esta situación, sys.dm_exec_query_stats puede proporcionar la información que otros DVM no pueden. Esta es una consulta de ejemplo que puede ayudarle a encontrar las 20 instrucciones principales que consumieron las mayores cantidades de memoria de ejecución. Esta salida muestra instrucciones individuales incluso si su estructura de consulta es la misma. Por ejemplo, SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 5 es una fila independiente de SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 100 (solo el valor del predicado de filtro varía). La consulta obtiene las 20 instrucciones principales con un tamaño de concesión máximo superior a 5 MB.
SELECT TOP 20
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
,CONVERT(DECIMAL (10,2), max_grant_kb /1024.0) AS max_grant_mb
,CONVERT(DECIMAL (10,2), min_grant_kb /1024.0) AS min_grant_mb
,CONVERT(DECIMAL (10,2), (total_grant_kb / execution_count) /1024.0) AS avg_grant_mb
,CONVERT(DECIMAL (10,2), max_used_grant_kb /1024.0) AS max_grant_used_mb
,CONVERT(DECIMAL (10,2), min_used_grant_kb /1024.0) AS min_grant_used_mb
,CONVERT(DECIMAL (10,2), (total_used_grant_kb/ execution_count) /1024.0) AS avg_grant_used_mb
,CONVERT(DECIMAL (10,2), (total_ideal_grant_kb/ execution_count) /1024.0) AS avg_ideal_grant_mb
,CONVERT(DECIMAL (10,2), (total_ideal_grant_kb/ 1024.0)) AS total_grant_for_all_executions_mb
,execution_count
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
WHERE max_grant_kb > 5120 -- greater than 5 MB
ORDER BY max_grant_kb DESC
Se puede obtener información aún más eficaz examinando las consultas agregadas por query_hash. En este ejemplo se muestra cómo buscar los tamaños de concesión promedio, máximo y mínimo de una instrucción de consulta en todas sus instancias desde que el plan de consulta se almacenara por primera vez en caché.
SELECT TOP 20
MAX(SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1)) AS sample_statement_text
,CONVERT(DECIMAL (10,2), SUM(max_grant_kb) /1024.0) AS max_grant_mb
,CONVERT(DECIMAL (10,2), SUM(min_grant_kb) /1024.0) AS min_grant_mb
,CONVERT(DECIMAL (10,2), (SUM(total_grant_kb) / SUM(execution_count)) /1024.0) AS avg_grant_mb
,CONVERT(DECIMAL (10,2), SUM(max_used_grant_kb) /1024.0) AS max_grant_used_mb
,CONVERT(DECIMAL (10,2), SUM(min_used_grant_kb) /1024.0) AS min_grant_used_mb
,CONVERT(DECIMAL (10,2), (SUM(total_used_grant_kb)/ SUM(execution_count)) /1024.0) AS avg_grant_used_mb
,CONVERT(DECIMAL (10,2), (SUM(total_ideal_grant_kb)/ SUM(execution_count)) /1024.0) AS avg_ideal_grant_mb
,CONVERT(DECIMAL (10,2), SUM(total_grant_kb) /1024.0) AS total_grant_all_executions_mb
,SUM(execution_count) AS execution_count
,query_hash
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
GROUP BY query_hash
HAVING SUM(max_grant_kb) > 5120 -- greater than 5 MB
ORDER BY SUM(max_grant_kb) DESC
OPTION (MAX_GRANT_PERCENT = 5)
La Sample_Statement_Text columna muestra un ejemplo de la estructura de consulta que coincide con el hash de consulta, pero debe leerse sin tener en cuenta valores específicos de la instrucción. Por ejemplo, si una instrucción contiene WHERE Id = 5, puede leerla en su forma más genérica: WHERE Id = @any_value.
Esta es una salida de ejemplo abreviada de la consulta con solo las columnas seleccionadas mostradas:
sample_statement_text max_grant_mb avg_grant_mb max_grant_used_mb avg_grant_used_mb avg_ideal_grant_mb total_grant_all_executions_mb execution_count
----------------------------------------- ------------ ------------- ----------------- ----------------- ------------------ ----------------------------- ----------------
select de.ObjectName,de.CounterName,d 282.45 282.45 6.50 6.50 282.45 282.45 1
SELECT SCHEMA_NAME(udf.schema_id) AS [Sch 33.86 8.55 7.80 1.97 8.55 42.74 5
insert into #tmpCounterDateTime (CounterD 32.45 32.45 3.11 3.11 32.45 32.45 1
select db_id() dbid, db_name() dbname, * 20.80 1.30 5.75 0.36 1.30 20.80 16
SELECT SCHEMA_NAME(obj.schema_id) AS [Sch 20.55 5.19 5.13 1.28 5.19 25.93 5
SELECT xmlplan FROM (SELECT ROW_NUMBER() 19.69 1.97 1.09 0.11 1.97 19.69 10
if ( select max(cast(countervalue as floa 16.39 8.20 0.77 0.38 8.20 16.39 2
SELECT udf.name AS [Name], udf.object_id 11.36 5.08 1.66 0.83 5.08 20.33 4
select --* Database_I 10.94 5.47 1.98 0.99 5.47 10.94 2
IF (select max(cast(dat.countervalue as f 8.00 1.00 0.00 0.00 0.53 8.00 8
insert into #tmpCounterDateTime (CounterD 5.72 2.86 1.98 0.99 2.86 5.72 2
INSERT INTO #tmp (CounterDateTime, Counte 5.39 1.08 1.64 0.33 1.08 6.47 6
Identificar consultas específicas mediante Almacén de consultas (QDS) con sys.query_store_runtime_stats
Si tiene Almacén de consultas habilitado, puede aprovechar sus estadísticas históricas persistentes. Al contrario que los datos de sys.dm_exec_query_stats, estas estadísticas sobreviven a un reinicio SQL Server o a la presión de memoria porque se almacenan en una base de datos. QDS también tiene límites de tamaño y una directiva de retención. Para obtener más información, consulte las secciones Establecer el modo de captura de Almacén de consultas óptimo y Mantener los datos más relevantes en Almacén de consultas secciones de Procedimientos recomendados para administrar el Almacén de consultas.
Identifique si las bases de datos tienen Almacén de consultas habilitadas mediante esta consulta:
SELECT name, is_query_store_on FROM sys.databases WHERE is_query_store_on = 1Ejecute la siguiente consulta de diagnóstico en el contexto de una base de datos específica que quiera investigar:
SELECT MAX(qtxt.query_sql_text) AS sample_sql_text ,CONVERT(DECIMAL(10,2), SUM(rts.avg_query_max_used_memory) / 128) AS avg_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.min_query_max_used_memory) / 128) AS min_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.max_query_max_used_memory) / 128) AS max_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.stdev_query_max_used_memory) / 128) AS stdev_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.last_query_max_used_memory) / 128) AS last_mem_grant_used_mb ,SUM(count_executions) AS count_query_executions FROM sys.query_store_runtime_stats rts JOIN sys.query_store_plan p ON p.plan_id = rts.plan_id JOIN sys.query_store_query q ON p.query_id = q.query_id LEFT OUTER JOIN sys.query_store_query_text qtxt ON q.query_text_id = qtxt.query_text_id GROUP BY q.query_hash HAVING SUM(rts.avg_query_max_used_memory) /128 > 5 -- greater than 5 MB ORDER BY SUM(avg_query_max_used_memory) DESC OPTION (MAX_GRANT_PERCENT = 5)Los principios aquí son los mismos
sys.dm_exec_query_statsque ; verá estadísticas agregadas para las instrucciones . Sin embargo, una diferencia es que, con QDS, solo se examinan las consultas en el ámbito de esta base de datos, no en toda la SQL Server. Por lo tanto, puede que necesite conocer la base de datos en la que se ejecutó una solicitud de concesión de memoria determinada. De lo contrario, ejecute esta consulta de diagnóstico en varias bases de datos hasta que encuentre las concesiones de memoria considerables.Esta es una salida de ejemplo abreviada:
sample_sql_text avg_mem_grant_used_mb min_mem_grant_used_mb max_mem_grant_used_mb stdev_mem_grant_used_mb last_mem_grant_used_mb count_query_executions ----------------------------------------- ---------------------- ---------------------- ---------------------- ------------------------ ----------------------- ---------------------- SELECT qtxt.query_sql_text ,CONVERT(D 550.16 550.00 550.00 0.00 550.00 1 SELECT qtxt.query_sql_text ,rts.avg_q 61.00 36.00 65.00 10.87 51.00 14 SELECT qtxt.query_sql_text ,q.* ,rts 25.46 25.00 25.00 0.00 25.00 2 insert into #tmpStats select 5 'Database 13.69 13.00 13.00 0.03 13.00 16 SELECT q.* ,rts 11.93 11.00 12.00 0.23 12.00 2 SELECT * ,rts.avg_query_max_used_memory 9.70 9.00 9.00 0.00 9.00 1 SELECT qtxt.query_sql_text ,rts.avg_q 9.32 9.00 9.00 0.00 9.00 1 select db_id() dbid, db_name() dbname, * 7.33 7.00 7.00 0.00 7.00 9 SELECT q.* ,rts.avg_query_max_used_memo 6.65 6.00 6.00 0.00 6.00 1 (@_msparam_0 nvarchar(4000),@_msparam_1 5.17 4.00 5.00 0.68 4.00 2
Una consulta de diagnóstico personalizada
Esta es una consulta que combina datos de varias vistas, incluidas las tres enumeradas anteriormente. Proporciona una vista más exhaustiva de las sesiones y sus concesiones a través sys.dm_exec_requests de y sys.dm_exec_query_memory_grants, además de las estadísticas de nivel de servidor proporcionadas por sys.dm_exec_query_resource_semaphores.
Nota:
Esta consulta devolvería dos filas por sesión debido al uso de sys.dm_exec_query_resource_semaphores (una fila para el semáforo de recursos normal y otra para el semáforo de recursos de consulta pequeña).
SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime
, r.session_id
, r.wait_time
, r.wait_type
, mg.request_time
, mg.grant_time
, mg.requested_memory_kb
/ 1024 requested_memory_mb
, mg.granted_memory_kb
/ 1024 AS granted_memory_mb
, mg.required_memory_kb
/ 1024 AS required_memory_mb
, max_used_memory_kb
/ 1024 AS max_used_memory_mb
, rs.pool_id as resource_pool_id
, mg.query_cost
, mg.timeout_sec
, mg.resource_semaphore_id
, mg.wait_time_ms AS memory_grant_wait_time_ms
, CASE mg.is_next_candidate
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Memory has been granted'
END AS 'Next Candidate for Memory Grant'
, r.command
, ltrim(rtrim(replace(replace (substring (q.text, 1, 1000), char(10), ' '), char(13), ' '))) [text]
, rs.target_memory_kb
/ 1024 AS server_target_grant_memory_mb
, rs.max_target_memory_kb
/ 1024 AS server_max_target_grant_memory_mb
, rs.total_memory_kb
/ 1024 AS server_total_resource_semaphore_memory_mb
, rs.available_memory_kb
/ 1024 AS server_available_memory_for_grants_mb
, rs.granted_memory_kb
/ 1024 AS server_total_granted_memory_mb
, rs.used_memory_kb
/ 1024 AS server_used_granted_memory_mb
, rs.grantee_count AS successful_grantee_count
, rs.waiter_count AS grant_waiters_count
, rs.timeout_error_count
, rs.forced_grant_count
, mg.dop
, r.blocking_session_id
, r.cpu_time
, r.total_elapsed_time
, r.reads
, r.writes
, r.logical_reads
, r.row_count
, s.login_time
, d.name
, s.login_name
, s.host_name
, s.nt_domain
, s.nt_user_name
, s.status
, c.client_net_address
, s.program_name
, s.client_interface_name
, s.last_request_start_time
, s.last_request_end_time
, c.connect_time
, c.last_read
, c.last_write
, qp.query_plan
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_connections c
ON r.connection_id = c.connection_id
INNER JOIN sys.dm_exec_sessions s
ON c.session_id = s.session_id
INNER JOIN sys.databases d
ON r.database_id = d.database_id
INNER JOIN sys.dm_exec_query_memory_grants mg
ON s.session_id = mg.session_id
INNER JOIN sys.dm_exec_query_resource_semaphores rs
ON mg.resource_semaphore_id = rs.resource_semaphore_id
CROSS APPLY sys.dm_exec_sql_text (r.sql_handle ) AS q
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) qp
OPTION (MAXDOP 1, LOOP JOIN )
Nota:
La LOOP JOIN sugerencia se usa en esta consulta de diagnóstico para evitar una concesión de memoria por la propia consulta y no se usa ninguna ORDER BY cláusula. Si la consulta de diagnóstico termina esperando una concesión en sí, su propósito de diagnosticar concesiones de memoria se eliminaría. La LOOP JOIN sugerencia podría hacer que la consulta de diagnóstico sea más lenta, pero en este caso, es más importante obtener los resultados de diagnóstico.
Esta es una salida de ejemplo abreviada de esta consulta de diagnóstico con solo las columnas seleccionadas.
| session_id | wait_time | wait_type | requested_memory_mb | granted_memory_mb | required_memory_mb | max_used_memory_mb | resource_pool_id |
|---|---|---|---|---|---|---|---|
| 60 | 0 | NULL | 9 | 9 | 7 | 1 | 1 |
| 60 | 0 | NULL | 9 | 9 | 7 | 1 | 2 |
| 75 | 1310085 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 1 |
| 75 | 1310085 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 2 |
| 86 | 1310129 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 1 |
| 86 | 1310129 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 2 |
La salida de ejemplo muestra claramente cómo una consulta enviada por session_id = 60 obtuvo correctamente la concesión de memoria de 9 MB que solicitó, pero solo se necesitaron 7 MB para iniciar correctamente la ejecución de la consulta. Al final, la consulta solo usó 1 MB de los 9 MB que recibió del servidor. La salida también muestra que las sesiones 75 y 86 están esperando concesiones de memoria, por lo que .RESOURCE_SEMAPHOREwait_type Su tiempo de espera ha superado los 1.300 segundos (21 minutos) y su granted_memory_mb valor es NULL.
Esta consulta de diagnóstico es un ejemplo, así que no dude en modificarla de cualquier manera que se adapte a sus necesidades. También se usa una versión de esta consulta en las herramientas de diagnóstico que microsoft SQL Server admite.
Herramientas de diagnóstico
Hay herramientas de diagnóstico que Microsoft SQL Server soporte técnico usa para recopilar registros y solucionar problemas de forma más eficaz. Sql LogScout y Pssdiag Configuration Manager (junto con SQLDiag) recopilan salidas de las DMV descritas anteriormente y contadores de Monitor de rendimiento que pueden ayudarle a diagnosticar problemas de concesión de memoria.
Si ejecuta SQL LogScout con escenarios de LightPerf, GeneralPerf o DetailedPerf , la herramienta recopila los registros necesarios. A continuación, puede examinar manualmente el YourServer_PerfStats.out y buscar -- dm_exec_query_resource_semaphores -- y -- dm_exec_query_memory_grants -- salidas. O bien, en lugar de un examen manual, puede usar SQL Nexus para importar la salida procedente de SQL LogScout o PSSDIAG en una base de datos de SQL Server. SQL Nexus crea dos tablas, tbl_dm_exec_query_resource_semaphores y tbl_dm_exec_query_memory_grants, que contienen la información necesaria para diagnosticar concesiones de memoria. SQL LogScout y PSSDIAG también recopilan registros de Perfmon en forma de . Archivos BLG, que se pueden usar para revisar los contadores de rendimiento descritos en la sección Monitor de rendimiento contadores.
¿Por qué son importantes las concesiones de memoria para un desarrollador o DBA?
En función de la experiencia de soporte técnico de Microsoft, los problemas de concesión de memoria tienden a ser algunos de los problemas más comunes relacionados con la memoria. Las aplicaciones suelen ejecutar consultas aparentemente sencillas que pueden terminar causando problemas de rendimiento en el SQL Server debido a operaciones de ordenación o hash enormes. Estas consultas no solo consumen una gran cantidad de SQL Server memoria, sino que también hacen que otras consultas esperen a que la memoria esté disponible, por lo que el cuello de botella de rendimiento.
Con las herramientas descritas aquí (DMV, contadores perfmon y planes de consulta reales), puede identificar qué consultas son consumidores de concesión grande. A continuación, puede ajustar o reescribir estas consultas para resolver o reducir el uso de memoria del área de trabajo.
¿Qué puede hacer un desarrollador sobre las operaciones de ordenación y hash?
Una vez que identifique consultas específicas que consumen una gran cantidad de memoria de reserva de consultas, puede realizar pasos para reducir las concesiones de memoria mediante el rediseño de estas consultas.
¿Qué provoca las operaciones de ordenación y hash en las consultas?
El primer paso es tener en cuenta qué operaciones de una consulta pueden dar lugar a concesiones de memoria.
Motivos por los que una consulta usaría un operador SORT:
ORDER BY (T-SQL) conduce a que las filas se ordene antes de transmitirse como resultado final.
GROUP BY (T-SQL) puede introducir un operador de ordenación en un plan de consulta antes de la agrupación si no hay un índice subyacente que ordene las columnas agrupadas.
DISTINCT (T-SQL) se comporta de forma similar a
GROUP BY. Para identificar filas distintas, se ordenan los resultados intermedios y, a continuación, se quitan los duplicados. El optimizador usa unSortoperador anterior a este operador si los datos aún no están ordenados debido a una búsqueda o examen de índice ordenado.El operador Merge Join , cuando lo selecciona el optimizador de consultas, requiere que ambas entradas unidas estén ordenadas. SQL Server puede desencadenar una ordenación si un índice clúster no está disponible en la columna de combinación de una de las tablas.
Motivos por los que una consulta usaría un operador de plan de consulta HASH:
Esta lista no es exhaustiva, pero incluye los motivos más comunes de las operaciones hash. Analice el plan de consulta para identificar las operaciones de coincidencia hash.
JOIN (T-SQL): al combinar tablas, SQL Server tiene una opción entre tres operadores físicos,
Nested Loop,Merge JoinyHash Join. Si SQL Server termina eligiendo una combinación hash, necesita memoria QE para almacenar y procesar resultados intermedios. Normalmente, la falta de índices buenos puede dar lugar a este operador de combinación más costoso de recursos,Hash Join. Para examinar el plan de consulta para identificarHash Match, consulte Referencia de operadores lógicos y físicos.DISTINCT (T-SQL): se podría usar un
Hash Aggregateoperador para eliminar duplicados en un conjunto de filas. Para buscar (Hash MatchAggregate) en el plan de consulta, consulte Referencia de operadores lógicos y físicos.UNION (T-SQL): es similar a
DISTINCT.Hash AggregateSe podría usar para quitar los duplicados de este operador.SUM/AVG/MAX/MIN (T-SQL): cualquier operación de agregado podría realizarse potencialmente como .
Hash AggregatePara buscar (Hash MatchAggregate) en el plan de consulta, consulte Referencia de operadores lógicos y físicos.
Conocer estas razones comunes puede ayudarle a eliminar, en la medida de lo posible, las solicitudes de concesión de memoria de gran tamaño que llegan a SQL Server.
Formas de reducir las operaciones de ordenación y hash o el tamaño de concesión
- Mantenga las estadísticas actualizadas . Este paso fundamental, que mejora el rendimiento de las consultas en muchos niveles, garantiza que el optimizador de consultas tenga la información más precisa al seleccionar planes de consulta. SQL Server determina el tamaño que se va a solicitar para su concesión de memoria en función de las estadísticas. Las estadísticas obsoletas pueden provocar una sobreestimación o una infravaloración de la solicitud de concesión y, por tanto, conducir a una solicitud de concesión innecesariamente alta o a derramar resultados en el disco, respectivamente. Asegúrese de que las estadísticas de actualización automática están habilitadas en las bases de datos o mantenga estáticas actualizadas con UPDATE STATISTICS o sp_updatestats.
- Reduzca el número de filas procedentes de tablas. Si usa un filtro WHERE más restrictivo o join y reduce el número de filas, una ordenación posterior en el plan de consulta puede ordenar o agregar un conjunto de resultados más pequeño. Un conjunto de resultados intermedios más pequeño requiere menos memoria del conjunto de trabajo. Se trata de una regla general que los desarrolladores pueden seguir no solo para guardar la memoria del conjunto de trabajo, sino también para reducir la CPU y la E/S (este paso no siempre es posible). Si ya hay consultas bien escritas y eficaces en los recursos, se ha cumplido esta guía.
- Cree índices en columnas de combinación para ayudar a combinar combinaciones. Las operaciones intermedias de un plan de consulta se ven afectadas por los índices de la tabla subyacente. Por ejemplo, si una tabla no tiene ningún índice en una columna de combinación y se detecta que una combinación de combinación es el operador de combinación más rentable, todas las filas de esa tabla deben ordenarse antes de que se realice la combinación. Si, en su lugar, existe un índice en la columna, se puede eliminar una operación de ordenación.
- Cree índices para ayudar a evitar las operaciones hash. Normalmente, el ajuste básico de consultas comienza con la comprobación de si las consultas tienen índices adecuados para ayudarles a reducir las lecturas y a minimizar o eliminar grandes ordenaciones o operaciones hash siempre que sea posible. Normalmente, las combinaciones hash se seleccionan para procesar entradas grandes, no ordenadas y no indizadas. La creación de índices puede cambiar esta estrategia del optimizador y acelerar la recuperación de datos. Para obtener ayuda en la creación de índices, consulte Asistente para la optimización de motor de base de datos y Ajuste de índices no agrupados con sugerencias de índices que faltan.
- Use índices COLUMNSTORE cuando corresponda para las consultas de agregación que usan
GROUP BY. Las consultas de análisis que se ocupan de conjuntos de filas muy grandes y suelen realizar agregaciones "agrupadas por" pueden necesitar fragmentos de memoria grandes para realizar el trabajo. Si un índice no está disponible que proporciona resultados ordenados, se introduce automáticamente una ordenación en el plan de consulta. Un resultado muy grande puede dar lugar a una costosa concesión de memoria. - Quite si
ORDER BYno lo necesita. En los casos en los que los resultados se transmiten a una aplicación que ordena los resultados a su manera o permite al usuario modificar el orden de los datos vistos, no es necesario realizar una ordenación en el lado SQL Server. Simplemente transmita los datos a la aplicación en el orden en que el servidor los genera y deje que el usuario final los ordene por su cuenta. Las aplicaciones de informes como Power BI o Reporting Services son ejemplos de tales aplicaciones que permiten a los usuarios finales ordenar sus datos. - Tenga en cuenta, aunque con precaución, el uso de una sugerencia LOOP JOIN cuando existen combinaciones en una consulta T-SQL. Esta técnica puede evitar combinaciones hash o de combinación que usan concesiones de memoria. Sin embargo, esta opción solo se sugiere como último recurso porque forzar una combinación podría dar lugar a una consulta significativamente más lenta. Pruebe el estrés de la carga de trabajo para asegurarse de que se trata de una opción. En algunos casos, es posible que una combinación de bucle anidado ni siquiera sea una opción. En este caso, SQL Server puede producir un error MSSQLSERVER_8622, "El procesador de consultas no pudo generar un plan de consulta debido a las sugerencias definidas en esta consulta".
Sugerencia de consulta de concesión de memoria
Desde SQL Server SP3 de 2012, existe una sugerencia de consulta que permite controlar el tamaño de la concesión de memoria por consulta. Este es un ejemplo de cómo puede usar esta sugerencia:
SELECT Column1, Column2
FROM Table1
ORDER BY Column1
OPTION (MIN_GRANT_PERCENT = 3, MAX_GRANT_PERCENT = 5 )
Se recomienda usar valores conservadores aquí, especialmente en los casos en los que espera que muchas instancias de la consulta se ejecuten simultáneamente. Asegúrese de probar la carga de trabajo para que coincida con el entorno de producción y determine qué valores usar.
Para obtener más información, consulte MAX_GRANT_PERCENT y MIN_GRANT_PERCENT.
Resource Governor
Memoria QE es la memoria que Resource Governor limita realmente cuando se usan los valores de MIN_MEMORY_PERCENT y MAX_MEMORY_PERCENT. Una vez que identifique las consultas que causan concesiones de memoria grandes, puede limitar la memoria que usan las sesiones o aplicaciones. Vale la pena mencionar que el default grupo de cargas de trabajo permite que una consulta tome hasta el 25 % de la memoria que se puede conceder en una instancia de SQL Server. Para obtener más información, consulte Resource Governor grupos de recursos y CREATE WORKLOAD GROUP.
Procesamiento de consultas adaptable y comentarios de concesión de memoria
SQL Server 2017 introdujo la característica de comentarios de concesión de memoria. Permite al motor de ejecución de consultas ajustar la concesión dada a la consulta en función del historial anterior. El objetivo es reducir el tamaño de la concesión siempre que sea posible o aumentarla cuando se necesite más memoria. Esta característica se ha publicado en tres oleadas:
- Comentarios de concesión de memoria en modo por lotes en SQL Server 2017
- Comentarios de concesión de memoria en modo de fila en SQL Server 2019
- Comentarios de concesión de memoria sobre la persistencia en disco mediante la concesión de Almacén de consultas y percentil en SQL Server 2022
Para obtener más información, vea Comentarios sobre la concesión de memoria. La característica de concesión de memoria puede reducir el tamaño de las concesiones de memoria para las consultas en tiempo de ejecución y, por tanto, reducir los problemas derivados de solicitudes de concesión de gran tamaño. Con esta característica en vigor, especialmente en SQL Server 2019 y versiones posteriores, donde el procesamiento adaptable del modo de fila está disponible, es posible que ni siquiera observe ningún problema de memoria procedente de la ejecución de consultas. Sin embargo, si tiene esta característica en vigor (activada de forma predeterminada) y sigue viendo un consumo de memoria QE grande, aplique los pasos descritos anteriormente para volver a escribir consultas.
Aumento de SQL Server o memoria del sistema operativo
Después de realizar los pasos necesarios para reducir las concesiones de memoria innecesarias para las consultas, si sigue experimentando problemas relacionados con la memoria baja, es probable que la carga de trabajo requiera más memoria. Por lo tanto, considere la posibilidad de aumentar la memoria de SQL Server mediante la max server memory configuración si hay suficiente memoria física en el sistema para hacerlo. Siga las recomendaciones sobre cómo dejar aproximadamente el 25 % de la memoria para el sistema operativo y otras necesidades. Para obtener más información, consulte Opciones de configuración de memoria del servidor. Si no hay suficiente memoria disponible en el sistema, considere la posibilidad de agregar RAM física o, si es una máquina virtual, aumente la RAM dedicada para la máquina virtual.
Interno de concesión de memoria
Para obtener más información sobre algunos aspectos internos de la memoria de ejecución de consultas, consulte la entrada de blog Descripción de la concesión de memoria de SQL Server .
Creación de un escenario de rendimiento con uso intensivo de concesión de memoria
Por último, en el ejemplo siguiente se muestra cómo simular un gran consumo de memoria de ejecución de consultas e introducir consultas que esperan en RESOURCE_SEMAPHORE. Puede hacerlo para aprender a usar las herramientas y técnicas de diagnóstico que se describen en este artículo.
Advertencia
No lo use en un sistema de producción. Esta simulación se proporciona para ayudarle a comprender el concepto y ayudarle a aprender mejor.
En un servidor de prueba, instale RML Utilities y SQL Server.
Use una aplicación cliente como SQL Server Management Studio para reducir el valor máximo de memoria del servidor de la SQL Server a 1500 MB:
EXEC sp_configure 'max server memory', 1500 RECONFIGUREAbra un símbolo del sistema y cambie el directorio a la carpeta utilidades rml:
cd C:\Program Files\Microsoft Corporation\RMLUtilsUse ostress.exe para generar varias solicitudes simultáneas en el SQL Server de prueba. En este ejemplo se usan 30 sesiones simultáneas, pero puede cambiar ese valor:
ostress.exe -E -S. -Q"select * from sys.messages order by message_id option (maxdop 1)" -n30Use las herramientas de diagnóstico descritas anteriormente para identificar los problemas de concesión de memoria.
Resumen de las formas de tratar las concesiones de memoria grandes
- Vuelva a escribir consultas.
- Actualice las estadísticas y manténgalos actualizados periódicamente.
- Cree los índices adecuados para la consulta o las consultas identificadas. Los índices pueden reducir el gran número de filas procesadas, cambiando así los
JOINalgoritmos y reduciendo el tamaño de las concesiones o eliminándolas por completo. - Use la
OPTIONsugerencia (min_grant_percent = XX, max_grant_percent = XX). - Use Resource Governor.
- SQL Server 2017 y 2019 usan el procesamiento de consultas adaptables, lo que permite al mecanismo de comentarios de concesión de memoria ajustar el tamaño de concesión de memoria dinámicamente en tiempo de ejecución. Esta característica puede evitar problemas de concesión de memoria en primer lugar.
- Aumentar SQL Server o la memoria del sistema operativo.
Comentarios
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea: https://aka.ms/ContentUserFeedback.
Enviar y ver comentarios de