Solución de problemas de rendimiento lento o poca memoria 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 memoria en tiempo de ejecución de consultas. SQL Server asigna esta memoria durante la ejecución de consultas para uno o varios de los siguientes propósitos:
- Operaciones de ordenación
- Operaciones hash
- Operaciones de copia masiva (no un problema común)
- Creación de índices, incluida la inserción en índices COLUMNSTORE porque se usan diccionarios o tablas hash en tiempo de ejecución para la creación de índices (no un problema común)
Para proporcionar algún contexto, durante su duración, una consulta puede solicitar memoria de distintos asignadores de memoria o distribuidores en función de lo que necesite 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 de 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 todo o parte de la memoria para ordenar los resultados o cubos hash. Esta memoria asignada durante la ejecución de consultas es lo que se conoce como concesiones de memoria. Como puede imaginar, una vez completada la operación de ejecución de consultas, la concesión de memoria se libera de vuelta a SQL Server para usarla para 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 memoria, la ordenación puede tardar muchos segundos o minutos y la memoria concedida se usa durante la vigencia 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 de SELECT 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" />
Aquí hay varios términos que necesitan una explicación. Una consulta puede querer una cierta cantidad de memoria de ejecución (DesiredMemory) y normalmente solicitaría esa cantidad (RequestedMemory). En tiempo de ejecución, SQL Server concede toda 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 superado la cantidad de memoria necesaria, usa menos del tamaño solicitado. Pero esa memoria se desperdicia como 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 que el trabajo se realice 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 un área de trabajo temporal. Para obtener más información, vea Workfiles and Worktables in Memory Grant Considerations( Consideraciones sobre concesión de memoria).
Terminología
Vamos a revisar los distintos términos que puede encontrar con respecto a este consumidor de memoria. De nuevo, todos estos conceptos describen los mismos 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 de ordenación o hash 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 para la 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 parcialmente o totalmente en función de la disponibilidad de memoria. Al final, la consulta puede usar un porcentaje de la memoria concedida. Hay un distribuidor 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 "grant". Estos contadores y
Memory Grants Pending, muestran el recuento de concesiones de memoria satisfechosMemory Grants Outstandingo 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 e creación de índices, expresadas en KB. ElMaximum Workspace Memory (KB), otro contador, tiene en cuenta la cantidad máxima de memoria del área de trabajo disponible para las solicitudes que pueden necesitar realizar tales operaciones hash, ordenar, copiar de forma masiva y crear í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 QE grande
En la mayoría de los casos, cuando un subproceso solicita memoria dentro de SQL Server para hacer 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 un error, pero espera hasta que la memoria esté disponible. Uno de esos escenarios es la concesión 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 predefinida de QE, en lugar de producir un error en la consulta con un error de memoria insuficiente, hace que la consulta espere. Dado que se permite que la memoria del área de trabajo tome un porcentaje significativo de la memoria general de SQL Server, esperar a 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, juntas, 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 los datos y las páginas de índice de una caché de búfer se hayan vaciado para hacer espacio para las solicitudes de concesión de memoria grandes. Esto significa que las lecturas de página procedentes de las solicitudes de consulta deben cumplirse 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á asociado 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, debe 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 de memoria de ejecución de consultas
Hay varias maneras de determinar las esperas de las reservas de QE. Elija las 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 en el nivel de consulta individual para ver qué consultas necesitan ajuste o reescrituras.
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.
- contadores de Monitor de rendimiento Para obtener más información, vea Objeto del Administrador de memoria de SQL Server.
- DBCC MEMORYSTATUS Para obtener más información, vea DBCC MEMORYSTATUS.
- DMV de distribuidores de memoria sys.dm_os_memory_clerks Para obtener más información, consulte sys.dm_os_memory_clerks.
- Identificar 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: actualmente en ejecución de consultas. Para obtener más información, consulte sys.dm_exec_query_memory_grants.
- Identificar consultas específicas con sys.dm_exec_requests: actualmente ejecutando consultas. 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 en las consultas. Para obtener más información, consulte sys.dm_exec_query_stats.
- Identificar 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
Sys.dm_exec_query_resource_semaphores de DMV de semáforo de recursos
Esta DMV desglosa la memoria de reserva de consultas por grupo de recursos (interna, predeterminada y creada 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 alrededor de 900 MB de memoria de ejecución de consultas se usa en 22 solicitudes 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 Performance Monitor
Hay información similar disponible a través de contadores de Monitor de rendimiento, donde puede observar las solicitudes concedidas actualmente (), las solicitudes de concesión en espera (Memory Grants OutstandingMemory Grants Pending) y la cantidad de memoria usada por 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 Objeto del Administrador de memoria de SQL Server.
- 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 examinar la salida de las Query Memory Objects (default) consultas de usuario. Si ha habilitado Resource Governor con un grupo de recursos denominado PoolAdmin, por ejemplo, puede examinar y Query Memory Objects (default) Query Memory Objects (PoolAdmin).
Esta es una salida de ejemplo de un sistema en el que se han concedido 18 solicitudes a la memoria de ejecución de consultas y dos 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, ya que una solicitud se puso en la cola de espera. Para obtener más información sobre estos contadores, vea 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 distribuidor de memoria que realiza un seguimiento de la memoria de ejecución de consultas. En la salida siguiente se 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 distribuidores de memoria sys.dm_os_memory_clerks
Si necesita más de un conjunto de resultados tabulares, diferente del basado en DBCC MEMORYSTATUSsecciones , puede usar sys.dm_os_memory_clerks para obtener información similar. Busque el distribuidor 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'
Este 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 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. Si no se obtiene 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 (por ejemplo, para la 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 para las 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 en 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 el bucle de comentarios de concesión de memoria está deshabilitado.
- 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 para 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 identifican 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 desbordamiento 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 se particiona o un rescate hash cuando la creación de particiones 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 en una consulta en ejecución no cabe en la memoria. Este evento no se genera para las 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, lo que indica que se requerían varios pasos 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 granted_memory_kb y campos 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: el motor de almacenamiento se ha 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 están esperando la misma memoria. Este último grupo puede constar de solicitudes con necesidades modestas para 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 tamaños de memoria enormes. Céntrese en ellos si encuentra que es el caso. Puede ser habitual encontrar que una consulta determinada es el infractor, 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.
Identificación de 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 siguiente instrucción 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 QE activo. La mayoría de las consultas tienen concedida su memoria, como se muestra en granted_memory_kb valores used_memory_kb numéricos que no son NULL. Las consultas que no han obtenido su solicitud concedida están esperando memoria de ejecución y granted_memory_kb = NULL. Además, se colocan en una cola de espera con un queue_id valor = 6. Indica wait_time_ms aproximadamente 37 segundos de espera. La sesión 72 es la siguiente línea para obtener una concesión como se indica en wait_order = 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
Identificación de 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 usado 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 para 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 espera a que los recursos de memoria ejecuten 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 las versiones de SQL Server.
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 buscando el tipo de RESOURCE_SEMAPHORE 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 han concedido dos solicitudes de memoria y dos docenas de otras están esperando concesiones. La granted_query_memory columna informa del 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)
Identificación de 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 incorrectas, puede examinar los datos de consulta históricos a través de sys.dm_exec_query_stats. La duración de los datos está vinculada 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 la memoria que no se conservan después de un reinicio de SQL Server o después de la presión de memoria provoca 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 notificado recientemente que ve concesiones de memoria de gran tamaño de las consultas, pero al examinar la carga de trabajo del servidor, puede detectar que el problema ha desaparecido. En esta situación, sys.dm_exec_query_stats puede proporcionar la información que otros DVMs no pueden. Esta es una consulta de ejemplo que puede ayudarle a encontrar las 20 primeras instrucciones 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 varía el valor del predicado de filtro). La consulta obtiene las 20 instrucciones principales con un tamaño máximo de concesión 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 para una instrucción de consulta en todas sus instancias, ya que el plan de consulta se almacena en caché por primera vez.
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 los 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 que se muestran:
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
Identificación de 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 sobrevive a una presión de memoria o reinicio de SQL Server 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 óptimo Almacén de consultas y Mantener los datos más relevantes en Almacén de consultas secciones de Procedimientos recomendados para administrar la Almacén de consultas.
Identifique si las bases de datos tienen Almacén de consultas habilitada 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 desea 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 que
sys.dm_exec_query_stats; 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 instancia de SQL Server. Por lo tanto, es posible que tenga que 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, incluidos los tres enumerados 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 normales 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 propiamente dicha, se derrotaría su propósito de diagnosticar concesiones de memoria. 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 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 requerían 7 MB para iniciar correctamente la ejecución de la consulta. Al final, la consulta usó solo 1 MB del 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_SEMAPHORE wait_type Su tiempo de espera ha sido superior a 1300 segundos (21 minutos) y su granted_memory_mb 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 usa el soporte técnico de Microsoft SQL Server 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 los contadores de Monitor de rendimiento que pueden ayudarle a diagnosticar problemas de concesión de memoria.
Si ejecuta SQL LogScout con escenarios LightPerf, GeneralPerf o DetailedPerf, la herramienta recopila los registros necesarios. A continuación, puede examinar manualmente la 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 y tbl_dm_exec_query_resource_semaphores 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 las concesiones de memoria importantes 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 acabar causando problemas de rendimiento en SQL Server debido a operaciones de ordenación o hash enormes. Estas consultas no solo consumen mucha memoria de SQL Server, 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 que se describen aquí (DMV, contadores de Perfmon y planes de consulta reales), puede identificar qué consultas son consumidores de concesión de gran tamaño. A continuación, puede ajustar o volver a escribir 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é causa las operaciones de ordenación y hash en las consultas?
El primer paso consiste en conocer las operaciones de una consulta que pueden dar lugar a concesiones de memoria.
Motivos por los que una consulta usaría un operador SORT:
ORDER BY (T-SQL) hace que las filas se ordenan antes de transmitirse como resultado final.
GROUP BY (T-SQL) puede introducir un operador de ordenación en un plan de consulta antes de agrupar si un índice subyacente no está presente que ordena 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 antes de este operador si los datos aún no están ordenados debido a una búsqueda o examen de índice ordenados.El operador Merge Join , cuando lo selecciona el optimizador de consultas, requiere que se ordenen ambas entradas combinadas. SQL Server puede desencadenar una ordenación si un índice agrupado no está disponible en la columna de combinación en 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 para las operaciones hash. Analice el plan de consulta para identificar las operaciones de coincidencia de 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 que los resultados intermedios se almacenen y procesen. Normalmente, una 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 unaHash Match(Aggregate) 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 como .
Hash AggregatePara buscar unaHash Match(Aggregate) en el plan de consulta, consulte Referencia de operadores lógicos y físicos.
Conocer estas razones comunes puede ayudarle a eliminar, tanto como sea posible, las solicitudes de concesión de memoria grandes que llegan a SQL Server.
Formas de reducir las operaciones de ordenación y hash o el tamaño de concesión
- Mantenga actualizadas las estadísticas . 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 sobrestimación o infraestimación de la solicitud de concesión y, por tanto, provocar una solicitud de concesión innecesariamente alta o desbordar 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 obtiene para ordenar o agregar un conjunto de resultados más pequeño. Un conjunto de resultados intermedio 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 eficientes en 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 encuentra 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 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 minimizar o eliminar grandes operaciones de ordenación o hash siempre que sea posible. Las combinaciones hash suelen seleccionarse para procesar entradas grandes, no ordenadas y no indexadas. La creación de índices puede cambiar esta estrategia del optimizador y acelerar la recuperación de datos. Para obtener ayuda para crear índices, consulte Motor de base de datos Asistente para la optimización y Optimización de índices no agrupados con sugerencias de índice 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 que normalmente realizan agregaciones "agrupar 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 tipo de resultado muy grande puede dar lugar a una concesión de memoria costosa. - Quite si
ORDER BYno lo necesita. En los casos en los que los resultados se transmiten a una aplicación que ordena los resultados de forma propia o permite al usuario modificar el orden de los datos vistos, no es necesario realizar una ordenación en el lado de SQL Server. Simplemente transmita los datos a la aplicación en el orden en que el servidor lo genere y deje que el usuario final lo ordene por su cuenta. Las aplicaciones de informes como Power BI o Reporting Services son ejemplos de estas 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 de 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, ya que forzar una combinación podría dar lugar a una consulta significativamente más lenta. Pruebe la carga de trabajo para asegurarse de que esta es una opción. En algunos casos, es posible que una combinación de bucle anidada no sea incluso 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 2012 SP3, existe una sugerencia de consulta que le 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 se ejecuten simultáneamente muchas instancias de la consulta. Asegúrese de probar el esfuerzo de 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.
Regulador de recursos
La memoria QE es la memoria que el regulador de recursos limita realmente cuando se usan la configuración de MIN_MEMORY_PERCENT y MAX_MEMORY_PERCENT . Una vez que identifique las consultas que provocan concesiones de memoria grandes, puede limitar la memoria usada por sesiones o aplicaciones. Cabe mencionar que el default grupo de cargas de trabajo permite que una consulta tarde hasta el 25 % de la memoria que se puede conceder en una instancia de SQL Server. Para más información, consulte Grupos de recursos de Resource Governor y CREATE WORKLOAD GROUP.
Comentarios de concesión de memoria y procesamiento de consultas adaptables
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 cuando sea posible o aumentarlo cuando se necesite más memoria. Esta característica se ha lanzado 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 más información, consulte Comentarios de 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 problemas de memoria procedentes de la ejecución de consultas. Sin embargo, si tiene esta característica en su lugar (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 la memoria de SQL Server o 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 poca memoria, 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 para 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.
Elementos internos de concesión de memoria
Para más información sobre algunos elementos internos en 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 en espera de RESOURCE_SEMAPHORE. Puede hacerlo para aprender a usar las herramientas y técnicas de diagnóstico descritas 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 a ayudarle a aprender mejor.
En un servidor de prueba, instale utilidades RML y SQL Server.
Use una aplicación cliente como SQL Server Management Studio para reducir la configuración de memoria máxima del servidor de 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 de RML:
cd C:\Program Files\Microsoft Corporation\RMLUtilsUse ostress.exe para generar varias solicitudes simultáneas en la prueba de SQL Server. 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 formas de tratar con concesiones de memoria grandes
- Reescritura de consultas.
- Actualice las estadísticas y manténgalas actualizadas periódicamente.
- Cree í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 eliminando 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 que el mecanismo de comentarios de concesión de memoria ajuste el tamaño de concesión de memoria dinámicamente en tiempo de ejecución. Esta característica puede impedir problemas de concesión de memoria en primer lugar.
- Aumente la memoria de SQL Server o del sistema operativo.