visualizar ver plan optimizacion interpretar formas ejecución ejecucion consultas sql sql-server performance tsql sql-execution-plan

optimizacion - plan de ejecucion sql server



¿Cómo obtengo un plan de ejecución de consultas? (9)

Suponiendo que está utilizando Microsoft SQL Server Management Studio

  • Para el Plan de consulta estimado , puede presionar Ctrl + L o el siguiente botón.

  • Para el plan de consulta real , puede presionar Ctrl + M o el siguiente botón antes de ejecutar la consulta.

  • Para el plan de consulta en vivo (solo en SSMS 2016) use el siguiente botón antes de ejecutar la consulta.

En Microsoft SQL Server, ¿cómo puedo obtener un plan de ejecución de consultas para una consulta / procedimiento almacenado?

A partir de SQL Server 2016+, se introdujo la función Query Store para monitorear el rendimiento. Proporciona información sobre la elección del plan de consulta y el rendimiento. No es un reemplazo completo del seguimiento o eventos extendidos, pero a medida que evoluciona de una versión a otra, podríamos obtener un almacén de consultas completamente funcional en futuras versiones de SQL Server. El flujo primario de Query Store

  1. Los componentes existentes de SQL Server interactúan con el almacén de consultas utilizando Query Store Manager.
  2. Query Store Manager determina qué tienda se debe usar y luego pasa la ejecución a esa tienda (Plan o Estadísticas de tiempo de ejecución o Estadísticas de espera de consulta)
    • Almacén del plan - Persistiendo la información del plan de ejecución
    • Runtime Stats Store - Persistiendo la información de estadísticas de ejecución
    • Consulta de estadísticas de espera en espera: información de estadísticas de espera persistente.
  3. El plan, las estadísticas de tiempo de ejecución y el almacén de espera utilizan el almacén de consultas como una extensión de SQL Server.

  1. Habilitación del Almacén de consultas : el Almacén de consultas funciona en el nivel de la base de datos en el servidor.

    • Query Store no está activo para nuevas bases de datos de forma predeterminada.
    • No puede habilitar el almacén de consultas para la base de datos master o tempdb .
    • DMV disponible

      sys.database_query_store_options (Transact-SQL)

  2. Recopilar información en el almacén de consultas : Recopilamos toda la información disponible de las tres tiendas utilizando el DMV (vistas de gestión de datos) de Query Store.

    • Almacén del plan de consultas: persiste la información del plan de ejecución y es responsable de capturar toda la información relacionada con la compilación de consultas.

      sys.query_store_query (Transact-SQL) sys.query_store_plan (Transact-SQL) sys.query_store_query_text (Transact-SQL)

    • Almacén de estadísticas de tiempo de ejecución: persiste la información de estadísticas de ejecución y es probablemente la tienda que se actualiza con más frecuencia. Estas estadísticas representan datos de ejecución de consulta.

      sys.query_store_runtime_stats (Transact-SQL)

    • Almacén de estadísticas de espera de consulta: persistencia y captura de información de estadísticas de espera.

      sys.query_store_wait_stats (Transact-SQL)

NOTA: el almacén de estadísticas de espera de consulta solo está disponible en SQL Server 2017+

Además de la respuesta completa que ya se ha publicado, a veces es útil poder acceder al plan de ejecución de manera programática para extraer información. Código de ejemplo para esto está abajo.

DECLARE @TraceID INT EXEC StartCapture @@SPID, @TraceID OUTPUT EXEC sp_help ''sys.objects'' /*<-- Call your stored proc of interest here.*/ EXEC StopCapture @TraceID

Ejemplo de definición de StartCapture

CREATE PROCEDURE StartCapture @Spid INT, @TraceID INT OUTPUT AS DECLARE @maxfilesize BIGINT = 5 DECLARE @filepath NVARCHAR(200) = N''C:/trace_'' + LEFT(NEWID(),36) EXEC sp_trace_create @TraceID OUTPUT, 0, @filepath, @maxfilesize, NULL exec sp_trace_setevent @TraceID, 122, 1, 1 exec sp_trace_setevent @TraceID, 122, 22, 1 exec sp_trace_setevent @TraceID, 122, 34, 1 exec sp_trace_setevent @TraceID, 122, 51, 1 exec sp_trace_setevent @TraceID, 122, 12, 1 -- filter for spid EXEC sp_trace_setfilter @TraceID, 12, 0, 0, @Spid -- start the trace EXEC sp_trace_setstatus @TraceID, 1

Ejemplo de definición de StopCapture

CREATE PROCEDURE StopCapture @TraceID INT AS WITH XMLNAMESPACES (''http://schemas.microsoft.com/sqlserver/2004/07/showplan'' as sql), CTE as (SELECT CAST(TextData AS VARCHAR(MAX)) AS TextData, ObjectID, ObjectName, EventSequence, /*costs accumulate up the tree so the MAX should be the root*/ MAX(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost FROM fn_trace_getinfo(@TraceID) fn CROSS APPLY fn_trace_gettable(CAST(value AS NVARCHAR(200)), 1) CROSS APPLY (SELECT CAST(TextData AS XML) AS xPlan) x CROSS APPLY (SELECT T.relop.value(''@EstimatedTotalSubtreeCost'', ''float'') AS EstimatedTotalSubtreeCost FROM xPlan.nodes(''//sql:RelOp'') T(relop)) ca WHERE property = 2 AND TextData IS NOT NULL AND ObjectName not in ( ''StopCapture'', ''fn_trace_getinfo'' ) GROUP BY CAST(TextData AS VARCHAR(MAX)), ObjectID, ObjectName, EventSequence) SELECT ObjectName, SUM(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost FROM CTE GROUP BY ObjectID, ObjectName -- Stop the trace EXEC sp_trace_setstatus @TraceID, 0 -- Close and delete the trace EXEC sp_trace_setstatus @TraceID, 2 GO

Además de los métodos descritos en las respuestas anteriores, también puede utilizar un visor de plan de ejecución gratuito y la herramienta de optimización de consultas ApexSQL Plan (con el que me he topado recientemente).

Puede instalar e integrar ApexSQL Plan en SQL Server Management Studio, por lo que los planes de ejecución se pueden ver directamente desde SSMS.

Visualización de planes de ejecución estimados en el plan ApexSQL

  1. Haga clic en el botón Nueva consulta en SSMS y pegue el texto de consulta en la ventana de texto de consulta. Haga clic derecho y seleccione la opción "Mostrar el plan de ejecución estimado" en el menú contextual.

  1. Los diagramas del plan de ejecución se mostrarán en la pestaña Plan de ejecución en la sección de resultados. A continuación, haga clic con el botón derecho en el plan de ejecución y en el menú contextual, seleccione la opción "Abrir en el plan ApexSQL".

  1. El plan de ejecución estimado se abrirá en el plan ApexSQL y se puede analizar para la optimización de consultas.

Visualización de los planes de ejecución reales en el plan ApexSQL

Para ver el plan de ejecución real de una consulta, continúe desde el segundo paso mencionado anteriormente, pero ahora, una vez que se muestre el plan estimado, haga clic en el botón "Actual" en la barra de la barra principal en el plan ApexSQL.

Una vez que se haga clic en el botón "Actual", se mostrará el plan de ejecución real con una vista previa detallada de los parámetros de costo junto con otros datos del plan de ejecución.

Puede encontrar más información sobre cómo ver los planes de ejecución siguiendo este enlace .

Al igual que con SQL Server Management Studio (ya explicado), también es posible con Datagrip como se explica here .

  1. Haga clic con el botón derecho en una declaración SQL y seleccione Explicar plan.
  2. En el panel Salida, haga clic en Plan.
  3. De forma predeterminada, se ve la representación de árbol de la consulta. Para ver el plan de consulta, haga clic en el icono Mostrar visualización o presione Ctrl + Mayús + Alt + U

Aquí hay una cosa importante que se debe saber, además de todo lo dicho antes.

Los planes de consulta a menudo son demasiado complejos para ser representados por el tipo de columna XML incorporado que tiene una limitación de 127 niveles de elementos anidados. Esa es una de las razones por las que sys.dm_exec_query_plan puede devolver NULL o incluso lanzar un error en versiones anteriores de MS SQL, por lo que generalmente es más seguro usar sys.dm_exec_text_query_plan . Este último también tiene una característica de bonificación útil para seleccionar un plan para una declaración particular en lugar de todo el lote. A continuación, le indicamos cómo lo usa para ver los planes de las declaraciones actualmente en ejecución:

SELECT p.query_plan FROM sys.dm_exec_requests AS r OUTER APPLY sys.dm_exec_text_query_plan( r.plan_handle, r.statement_start_offset, r.statement_end_offset) AS p

Sin embargo, la columna de texto en la tabla resultante no es muy útil en comparación con una columna XML. Para poder hacer clic en el resultado que se abrirá en una pestaña separada como un diagrama, sin tener que guardar su contenido en un archivo, puede usar un pequeño truco (recuerde que no puede usar CAST(... AS XML) ) , aunque esto solo funcionará para una sola fila:

SELECT Tag = 1, Parent = NULL, [ShowPlanXML!1!!XMLTEXT] = query_plan FROM sys.dm_exec_text_query_plan( -- set these variables or copy values -- from the results of the above query @plan_handle, @statement_start_offset, @statement_end_offset) FOR XML EXPLICIT

Existen varios métodos para obtener un plan de ejecución, el cual usar dependerá de sus circunstancias. Por lo general, puede usar SQL Server Management Studio para obtener un plan, sin embargo, si por alguna razón no puede ejecutar su consulta en SQL Server Management Studio, puede ser útil obtener un plan a través del Analizador de SQL Server o mediante la inspección el caché del plan.

Método 1: uso de SQL Server Management Studio

SQL Server viene con un par de características que facilitan la captura de un plan de ejecución, simplemente asegúrese de que el elemento del menú "Incluir el plan de ejecución real" (que se encuentra en el menú "Consulta") esté marcado y ejecute su consulta normalmente .

Si está tratando de obtener el plan de ejecución para las sentencias en un procedimiento almacenado, debe ejecutar el procedimiento almacenado, de esta manera:

exec p_Example 42

Cuando se complete la consulta, debería ver una pestaña adicional titulada "Plan de ejecución" que aparece en el panel de resultados. Si ejecutó muchas declaraciones, es posible que vea muchos planes que se muestran en esta pestaña.

Desde aquí puede inspeccionar el plan de ejecución en SQL Server Management Studio, o hacer clic derecho en el plan y seleccionar "Guardar el plan de ejecución como ..." para guardar el plan en un archivo en formato XML.

Método 2 - Usando las opciones de SHOWPLAN

Este método es muy similar al método 1 (de hecho, esto es lo que hace internamente SQL Server Management Studio), sin embargo, lo he incluido para completar o si no tiene disponible SQL Server Management Studio.

Antes de ejecutar su consulta, ejecute una de las siguientes declaraciones. La declaración debe ser la única instrucción en el lote, es decir, no puede ejecutar otra instrucción al mismo tiempo:

SET SHOWPLAN_TEXT ON SET SHOWPLAN_ALL ON SET SHOWPLAN_XML ON SET STATISTICS PROFILE ON SET STATISTICS XML ON -- The is the recommended option to use

Estas son opciones de conexión y, por lo tanto, solo necesita ejecutar esto una vez por conexión. A partir de este momento, todas las declaraciones ejecutadas se acompañarán de un conjunto de resultados adicional que contendrá su plan de ejecución en el formato deseado, simplemente ejecute su consulta como lo haría normalmente para ver el plan.

Una vez que haya terminado, puede desactivar esta opción con la siguiente declaración:

SET <<option>> OFF

Comparación de formatos de plan de ejecución.

A menos que tenga una fuerte preferencia, mi recomendación es usar la opción STATISTICS XML . Esta opción es equivalente a la opción "Incluir plan de ejecución real" en SQL Server Management Studio y proporciona la mayor información en el formato más conveniente.

  • SHOWPLAN_TEXT : muestra un plan de ejecución estimado basado en texto básico, sin ejecutar la consulta
  • SHOWPLAN_ALL - Muestra un plan de ejecución estimado basado en texto con estimaciones de costos, sin ejecutar la consulta
  • SHOWPLAN_XML : muestra un plan de ejecución estimado basado en XML con estimaciones de costos, sin ejecutar la consulta. Esto es equivalente a la opción "Mostrar el plan de ejecución estimado ..." en SQL Server Management Studio.
  • STATISTICS PROFILE : ejecuta la consulta y muestra un plan de ejecución real basado en texto.
  • STATISTICS XML : ejecuta la consulta y muestra un plan de ejecución real basado en XML. Esto es equivalente a la opción "Incluir plan de ejecución real" en SQL Server Management Studio.

Método 3 - Usando el Analizador de SQL Server

Si no puede ejecutar la consulta directamente (o la consulta no se ejecuta lentamente cuando la ejecuta directamente; recuerde que queremos que el plan de la consulta funcione mal), puede capturar un plan utilizando una traza del Analizador de SQL Server. La idea es ejecutar su consulta mientras se ejecuta una traza que captura uno de los eventos "Showplan".

Tenga en cuenta que, dependiendo de la carga, puede utilizar este método en un entorno de producción, sin embargo, obviamente debe tener cuidado. Los mecanismos de creación de perfiles de SQL Server están diseñados para minimizar el impacto en la base de datos, pero esto no significa que no habrá ningún impacto en el rendimiento. También puede tener problemas para filtrar e identificar el plan correcto en su rastreo si su base de datos está bajo uso intensivo. ¡Obviamente, debe consultar con su DBA para ver si están contentos con que haga esto en su preciosa base de datos!

  1. Abra el Analizador de SQL Server y cree una nueva traza que se conecta a la base de datos deseada en la que desea registrar la traza.
  2. Bajo la pestaña "Selección de eventos", marque "Mostrar todos los eventos", verifique la fila "Rendimiento" -> "Showplan XML" y ejecute la traza.
  3. Mientras se ejecuta la traza, haga lo que sea que necesite hacer para ejecutar la consulta de ejecución lenta.
  4. Espere a que la consulta se complete y detenga la traza.
  5. Para guardar la traza, haga clic con el botón derecho en el xml del plan en el Analizador de SQL Server y seleccione "Extraer datos de eventos ..." para guardar el plan en un archivo en formato XML.

El plan que obtiene es equivalente a la opción "Incluir plan de ejecución real" en SQL Server Management Studio.

Método 4 - Inspeccionar el caché de consulta

Si no puede ejecutar su consulta directamente y tampoco puede capturar una traza del generador de perfiles, puede obtener un plan estimado inspeccionando el caché del plan de consulta SQL.

Inspeccionamos el caché del plan consultando los DMVs SQL Server. La siguiente es una consulta básica que mostrará una lista de todos los planes de consulta en caché (como xml) junto con su texto SQL. En la mayoría de las bases de datos, también deberá agregar cláusulas de filtrado adicionales para filtrar los resultados a los planes que le interesan.

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) CROSS APPLY sys.dm_exec_query_plan(plan_handle)

Ejecute esta consulta y haga clic en el plan XML para abrir el plan en una nueva ventana: haga clic derecho y seleccione "Guardar plan de ejecución como ..." para guardar el plan en un archivo en formato XML.

Notas:

Debido a que hay muchos factores involucrados (que van desde la tabla y el esquema del índice hasta los datos almacenados y las estadísticas de la tabla), siempre debe intentar obtener un plan de ejecución de la base de datos en la que está interesado (normalmente el que está experimentando un rendimiento). problema).

No puede capturar un plan de ejecución para procedimientos almacenados cifrados.

Planes de ejecución "reales" vs "estimados"

Un plan de ejecución real es uno donde SQL Server realmente ejecuta la consulta, mientras que un plan de ejecución estimado SQL Server resuelve lo que haría sin ejecutar la consulta. Aunque es lógicamente equivalente, un plan de ejecución real es mucho más útil, ya que contiene detalles y estadísticas adicionales sobre lo que realmente sucedió al ejecutar la consulta. Esto es esencial cuando se diagnostican problemas cuando las estimaciones de los servidores SQL están desactivadas (como cuando las estadísticas no están actualizadas).

¿Cómo interpreto un plan de ejecución de una consulta?

Este es un tema suficientemente valioso para un book (gratuito) por derecho propio.

Ver también:

Los planes de consulta se pueden obtener de una sesión de Extended Events a través del evento query_post_execution_showplan . Aquí hay una sesión XEvent de muestra:

/* Generated via "Query Detail Tracking" template. */ CREATE EVENT SESSION [GetExecutionPlan] ON SERVER ADD EVENT sqlserver.query_post_execution_showplan( ACTION(package0.event_sequence,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)), /* Remove any of the following events (or include additional events) as desired. */ ADD EVENT sqlserver.error_reported( ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))), ADD EVENT sqlserver.module_end(SET collect_statement=(1) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))), ADD EVENT sqlserver.rpc_completed( ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))), ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))), ADD EVENT sqlserver.sql_batch_completed( ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))), ADD EVENT sqlserver.sql_statement_completed( ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))) ADD TARGET package0.ring_buffer WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF) GO

Después de crear la sesión, (en SSMS) vaya al Explorador de objetos y profundice en Administración | Eventos Extendidos | Sesiones Haga clic derecho en la sesión "GetExecutionPlan" e inícielo. Haz clic derecho nuevamente y selecciona "Ver datos en vivo".

A continuación, abra una nueva ventana de consulta y ejecute una o más consultas. Aquí hay uno para AdventureWorks:

USE AdventureWorks; GO SELECT p.Name AS ProductName, NonDiscountSales = (OrderQty * UnitPrice), Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount) FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID ORDER BY ProductName DESC; GO

Después de un momento o dos, debería ver algunos resultados en la pestaña "GetExecutionPlan: Live Data". Haga clic en uno de los eventos de query_post_execution_showplan en la cuadrícula y luego haga clic en la pestaña "Plan de consulta" debajo de la cuadrícula. Debería verse similar a esto:

EDITAR : El código XEvent y la captura de pantalla se generaron a partir de SQL / SSMS 2012 con SP2. Si está utilizando SQL 2008 / R2, es posible que pueda modificar la secuencia de comandos para que se ejecute. Pero esa versión no tiene una GUI, por lo que tendría que extraer el XML del plan de presentación, guardarlo como un archivo * .sqlplan y abrirlo en SSMS. Eso es engorroso. XEvents no existía en SQL 2005 o anterior. Por lo tanto, si no está en SQL 2012 o posterior, sugeriría encarecidamente una de las otras respuestas publicadas aquí.

Mi herramienta favorita para obtener y analizar en profundidad los planes de ejecución de consultas es SQL Sentry Plan Explorer . Es mucho más fácil de usar, conveniente y completo para el análisis detallado y la visualización de los planes de ejecución que el SSMS.

Aquí hay una captura de pantalla de muestra para que tenga una idea de qué funcionalidad ofrece la herramienta:

Es solo una de las vistas disponibles en la herramienta. Observe un conjunto de pestañas en la parte inferior de la ventana de la aplicación, que le permite obtener diferentes tipos de representación de su plan de ejecución e información adicional útil también.

Además, no he notado ninguna limitación de su edición gratuita que impida su uso diario o te obligue a comprar la versión Pro con el tiempo. Entonces, si prefieres quedarte con la edición gratuita, nada te prohíbe hacerlo.

ACTUALIZACIÓN: (Gracias a Martin Smith ) ¡Plan Explorer ahora es gratis! Consulte http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view para obtener más información.