Compartir a través de

Solución de problemas de diferencia de rendimiento de consultas entre la aplicación de base de datos y SSMS

  • Artículo

Al ejecutar una consulta en una aplicación de base de datos, se ejecuta más lentamente que la misma consulta en una aplicación como SQL Server Management Studio (SSMS), Azure Data Studio o SQLCMD.

Este problema puede producirse por los siguientes motivos:

  • Las consultas usan parámetros o variables diferentes.

  • Las consultas se envían al servidor a través de diferentes redes o hay una diferencia en la forma en que las aplicaciones procesan los datos.

  • Las opciones SET de la aplicación de base de datos y SSMS son diferentes.

Para solucionar el problema, siga estos pasos:

Paso 1: Comprobar que las consultas se envían con los mismos parámetros o variables

Para comparar estas consultas y asegurarse de que son idénticas en todos los sentidos, siga estos pasos:

  1. Abra SSMS y conéctelo al motor de base de datos que usa.

  2. Ejecute los comandos siguientes para crear una sesión de eventos extendidos:

    CREATE EVENT SESSION <EventSessionName> ON SERVER
ADD EVENT sqlserver.existing_connection(SET collect_options_text=(1)),
ADD EVENT sqlserver.login(SET collect_options_text=(1)
    ACTION(sqlserver.client_app_name)),
ADD EVENT sqlserver.rpc_completed,
ADD EVENT sqlserver.sp_statement_completed(
    ACTION(sqlserver.client_app_name)),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_app_name)),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name))
ADD TARGET package0.event_file(SET filename=N'<FilePath>')
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=OFF,STARTUP_STATE=OFF)
GO

    Nota:

    Reemplace los marcadores <de posición EventSessionName> y <FilePath> por los que desea crear.

  3. Ejecute los siguientes comandos para iniciar la sesión EventSessionName:

    ALTER EVENT SESSION <EventSessionName> ON SERVER
STATE = START

  4. Ejecute las consultas para reproducir el problema.

  5. Use uno de los métodos siguientes para analizar los datos recopilados:

    • Abra el Explorador de Windows, busque el archivo .xel de destino y haga doble clic en él. El archivo se abrirá en otra ventana de SSMS que puede usar para ver y analizar.

    • En Explorador de objetos, expandaEventoSessionName desesiones de eventos>extendidos> de administración>, haga clic con el botón derecho en package0.event_file y, a continuación, seleccione Ver datos de destino....

    • Busque la ubicación de los archivos .xel y lea este archivo mediante la función sys.fn_xe_file_target_read_file.

  6. Compare la instrucción Field comprobando los eventos siguientes:

    • sp_statement_completed
    • sql_batch_completed
    • sql_statement_completed
    • rpc_completed

Para obtener más información sobre las consultas idénticas, consulte los ejemplos siguientes:

  • Si los procedimientos almacenados o las funciones tienen valores de parámetro diferentes, los tiempos de consulta pueden ser diferentes:

    • SpUserProc @p1 = 100

    • SpUserProc @p1 = 270

  • Las siguientes consultas son diferentes. La primera consulta usa average density del histograma para la estimación de cardinalidad, mientras que la segunda consulta usa el paso histograma para la estimación de cardinalidad:

    • declare @variable1 = 123
select * from table where c1 = @variable1

    • select * from table where c1 = 123

Por el mismo motivo que antes, comparar la ejecución de un procedimiento almacenado con la ejecución de la consulta ad hoc equivalente (mediante variables locales) puede ser diferente. Hay que comparar las instrucciones idénticas.

Paso 2: Medir el tiempo de ejecución en el servidor

Para una comparación precisa de las duraciones de la consulta, puede excluir el tiempo de latencia de red o el tiempo de procesamiento de datos específico de la aplicación. Use uno de los métodos siguientes para medir solo el tiempo de ejecución en el SQL Server:

  • Ejecute la consulta mediante SET STATISTICS TIME:

    SET STATISTICS TIME ON
<YourQuery>
SET STATISTICS TIME OFF

  • Use XEvent del paso 1 para examinar la duración o el tiempo transcurrido de una consulta (clase SQL:StmtCompletedde evento , SQL:BatchCompletedo RPC:Completed).

En algunos casos, la diferencia de tiempo entre las consultas podría deberse a una aplicación que se ejecuta en una red diferente o a la propia aplicación. Al comparar la ejecución en el servidor, se compara cuánto tiempo tardaron las consultas en ejecutarse en el servidor.

Paso 3: Comprobar las opciones set para cada conexión

Hay opciones SET que afectan al plan de consulta, lo que significa que pueden cambiar la elección del plan de consulta. Por lo tanto, si una aplicación de base de datos usa opciones de conjunto diferentes de SSMS, cada opción de conjunto puede obtener un plan de consulta diferente. Por ejemplo, ARITHABORT, NUMERIC_ROUNDABORT, ROWCOUNT, FORCEPLAN y ANSI_NULLS. La diferencia más común observada entre las aplicaciones SSMS y .NET es la opción SET ARITHABORT . De forma predeterminada, la opción se establece en ON en SSMS, pero se establece en OFF en la mayoría de las aplicaciones de base de datos. En función de las necesidades de la aplicación, establezca ARITHABORT en la misma configuración tanto en SSMS como en la aplicación para una comparación válida entre los dos.

Advertencia

La configuración predeterminada de ARITHABORT para SQL Server Management Studio es ON. Las aplicaciones cliente que establecen ARITHABORT en OFF pueden recibir planes de consulta diferentes, lo que dificulta la solución de problemas de consultas con un rendimiento deficiente. Es decir, la misma consulta podría ejecutarse rápidamente en Management Studio, pero lenta en la aplicación. Al solucionar problemas de consultas con Management Studio, siempre coincide con la configuración de ARITHABORT del cliente.

Para obtener una lista de todas las opciones que afectan al plan, consulte Establecer opciones.

Para asegurarse de que las opciones SET de SSMS y la aplicación son las mismas para poder realizar una comparación válida, siga estos pasos:

  1. Use los datos recopilados en el paso 1.

  2. Compare las opciones establecidas comprobando los eventos login y existing_connection, en concreto las options_text columnas y options.