Solución de problemas de una consulta que muestra una diferencia significativa de rendimiento entre dos servidores
Se aplica a: SQL Server
En este artículo se proporcionan los pasos para solucionar un problema de rendimiento en el que una consulta se ejecuta más lentamente en un servidor que en otro.
Síntomas
Supongamos que hay dos servidores con SQL Server instalados. Una de las instancias de SQL Server contiene una copia de una base de datos en la otra instancia de SQL Server. Al ejecutar una consulta en las bases de datos de ambos servidores, la consulta se ejecuta más lentamente en un servidor que en el otro.
Los pasos siguientes pueden ayudar a solucionar este problema.
Paso 1: Determinar si se trata de un problema común con varias consultas
Use uno de los dos métodos siguientes para comparar el rendimiento de dos o más consultas en los dos servidores:
Pruebe manualmente las consultas en ambos servidores:
- Elija varias consultas para realizar pruebas con prioridad en las consultas que son:
- Significativamente más rápido en un servidor que en el otro.
- Importante para el usuario o la aplicación.
- Se ejecuta o se diseña con frecuencia para reproducir el problema a petición.
- Es lo suficientemente largo como para capturar datos (por ejemplo, en lugar de una consulta de 5 milisegundos, elija una consulta de 10 segundos).
- Ejecute las consultas en los dos servidores.
- Compare el tiempo transcurrido (duración) en dos servidores para cada consulta.
- Elija varias consultas para realizar pruebas con prioridad en las consultas que son:
Analice los datos de rendimiento con SQL Nexus.
- Recopile datos PSSDiag/SQLdiag o SQL LogScout para las consultas en los dos servidores.
- Importe los archivos de datos recopilados con SQL Nexus y compare las consultas de los dos servidores. Para obtener más información, vea Comparación de rendimiento entre dos recopilaciones de registros (slow y fast, por ejemplo).
Escenario 1: solo una sola consulta funciona de forma diferente en los dos servidores
Si solo una consulta funciona de forma diferente, es más probable que el problema sea más específico para la consulta individual que para el entorno. En este caso, vaya al Paso 2: Recopilar datos y determinar el tipo de problema de rendimiento.
Escenario 2: Varias consultas se realizan de forma diferente en los dos servidores
Si varias consultas se ejecutan más lentamente en un servidor que en el otro, la causa más probable son las diferencias en el servidor o el entorno de datos. Vaya a Diagnosticar diferencias de entorno y vea si la comparación entre los dos servidores es válida.
Paso 2: Recopilar datos y determinar el tipo de problema de rendimiento
Recopilar tiempo transcurrido, tiempo de CPU y lecturas lógicas
Para recopilar el tiempo transcurrido y el tiempo de CPU de la consulta en ambos servidores, use uno de los métodos siguientes que mejor se adapten a su situación:
Para las instrucciones que se ejecutan actualmente, compruebe las columnas total_elapsed_time y cpu_time en sys.dm_exec_requests. Ejecute la consulta siguiente para obtener los datos:
SELECT req.session_id , req.total_elapsed_time AS duration_ms , req.cpu_time AS cpu_time_ms , req.total_elapsed_time - req.cpu_time AS wait_time , req.logical_reads , SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE req.statement_end_offset END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text FROM sys.dm_exec_requests AS req CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST ORDER BY total_elapsed_time DESC;Para las ejecuciones anteriores de la consulta, compruebe las columnas last_elapsed_time y last_worker_time en sys.dm_exec_query_stats. Ejecute la consulta siguiente para obtener los datos:
SELECT t.text, (qs.total_elapsed_time/1000) / qs.execution_count AS avg_elapsed_time, (qs.total_worker_time/1000) / qs.execution_count AS avg_cpu_time, ((qs.total_elapsed_time/1000) / qs.execution_count ) - ((qs.total_worker_time/1000) / qs.execution_count) AS avg_wait_time, qs.total_logical_reads / qs.execution_count AS avg_logical_reads, qs.total_logical_writes / qs.execution_count AS avg_writes, (qs.total_elapsed_time/1000) AS cumulative_elapsed_time_all_executions FROM sys.dm_exec_query_stats qs CROSS apply sys.Dm_exec_sql_text (sql_handle) t WHERE t.text like '<Your Query>%' -- Replace <Your Query> with your query or the beginning part of your query. The special chars like '[','_','%','^' in the query should be escaped. ORDER BY (qs.total_elapsed_time / qs.execution_count) DESCNota:
Si
avg_wait_timemuestra un valor negativo, es una consulta paralela.Si puede ejecutar la consulta a petición en SQL Server Management Studio (SSMS) o Azure Data Studio, ejecútela con SET STATISTICS TIME
ONy SET STATISTICS IOON.SET STATISTICS TIME ON SET STATISTICS IO ON <YourQuery> SET STATISTICS IO OFF SET STATISTICS TIME OFFA continuación, en Mensajes, verá el tiempo de CPU, el tiempo transcurrido y las lecturas lógicas de la siguiente manera:
Table 'tblTest'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. SQL Server Execution Times: CPU time = 460 ms, elapsed time = 470 ms.Si puede recopilar un plan de consulta, compruebe los datos de las propiedades del plan de ejecución.
Ejecute la consulta con Incluir plan de ejecución real activado.
Seleccione el operador más a la izquierda en Plan de ejecución.
En Propiedades, expanda la propiedad QueryTimeStats .
Compruebe ElapsedTime y CpuTime.
Compare el tiempo transcurrido y el tiempo de CPU de la consulta para determinar el tipo de problema de ambos servidores.
Tipo 1: enlazado a la CPU (ejecutor)
Si el tiempo de CPU está cerca, igual o mayor que el tiempo transcurrido, puede tratarlo como una consulta enlazada a la CPU. Por ejemplo, si el tiempo transcurrido es de 3000 milisegundos (ms) y el tiempo de CPU es de 2900 ms, significa que la mayor parte del tiempo transcurrido se dedica a la CPU. A continuación, podemos decir que es una consulta enlazada a la CPU.
Ejemplos de consultas en ejecución (enlazadas a CPU):
| Tiempo transcurrido (ms) | Tiempo de CPU (ms) | Lecturas (lógicas) |
|---|---|---|
| 3200 | 3000 | 300000 |
| 1080 | 1000 | 20 |
Las lecturas lógicas (leer páginas de datos o índices en la memoria caché) son con más frecuencia los controladores del uso de CPU en SQL Server. Podría haber escenarios en los que el uso de CPU procede de otros orígenes: un bucle while (en T-SQL u otro código como XProcs o objetos CRL de SQL). En el segundo ejemplo de la tabla se muestra este escenario, donde la mayoría de la CPU no procede de lecturas.
Nota:
Si el tiempo de CPU es mayor que la duración, esto indica que se ejecuta una consulta paralela; varios subprocesos usan la CPU al mismo tiempo. Para obtener más información, consulte Consultas paralelas: ejecutor o camarero.
Tipo 2: Esperando un cuello de botella (camarero)
Una consulta está esperando un cuello de botella si el tiempo transcurrido es significativamente mayor que el tiempo de CPU. El tiempo transcurrido incluye el tiempo de ejecución de la consulta en la CPU (tiempo de CPU) y el tiempo que espera a que se libere un recurso (tiempo de espera). Por ejemplo, si el tiempo transcurrido es de 2000 ms y el tiempo de CPU es de 300 ms, el tiempo de espera es de 1700 ms (2000 - 300 = 1700). Para obtener más información, vea Tipos de esperas.
Ejemplos de consultas en espera:
| Tiempo transcurrido (ms) | Tiempo de CPU (ms) | Lecturas (lógicas) |
|---|---|---|
| 2000 | 300 | 28000 |
| 10080 | 700 | 80000 |
Consultas paralelas: ejecutor o camarero
Las consultas paralelas pueden usar más tiempo de CPU que la duración general. El objetivo del paralelismo es permitir que varios subprocesos ejecuten partes de una consulta simultáneamente. En un segundo de hora del reloj, una consulta puede usar ocho segundos de tiempo de CPU mediante la ejecución de ocho subprocesos paralelos. Por lo tanto, resulta difícil determinar una consulta enlazada a la CPU o en espera en función del tiempo transcurrido y la diferencia de tiempo de CPU. Sin embargo, como regla general, siga los principios enumerados en las dos secciones anteriores. El resumen es:
- Si el tiempo transcurrido es mucho mayor que el tiempo de CPU, considérelo un camarero.
- Si el tiempo de CPU es mucho mayor que el tiempo transcurrido, considéralo un ejecutor.
Ejemplos de consultas paralelas:
| Tiempo transcurrido (ms) | Tiempo de CPU (ms) | Lecturas (lógicas) |
|---|---|---|
| 1200 | 8100 | 850000 |
| 3080 | 12300 | 1500000 |
Paso 3: Comparar datos de ambos servidores, averiguar el escenario y solucionar el problema
Supongamos que hay dos máquinas denominadas Server1 y Server2. Y la consulta se ejecuta más lentamente en Server1 que en Server2. Compare las horas de ambos servidores y, a continuación, siga las acciones del escenario que mejor coincida con las suyas en las secciones siguientes.
Escenario 1: La consulta en Server1 usa más tiempo de CPU y las lecturas lógicas son mayores en Server1 que en Server2
Si el tiempo de CPU en Server1 es mucho mayor que en Server2 y el tiempo transcurrido coincide estrechamente con el tiempo de CPU en ambos servidores, no hay esperas ni cuellos de botella importantes. Es más probable que el aumento del tiempo de CPU en Server1 se deba a un aumento de las lecturas lógicas. Un cambio significativo en las lecturas lógicas suele indicar una diferencia en los planes de consulta. Por ejemplo:
| Servidor | Tiempo transcurrido (ms) | Tiempo de CPU (ms) | Lecturas (lógicas) |
|---|---|---|---|
| Server1 | 3100 | 3000 | 300000 |
| Server2 | 1100 | 1000 | 90200 |
Acción: Comprobación de los planes y entornos de ejecución
- Compare los planes de ejecución de la consulta en ambos servidores. Para ello, use uno de los dos métodos:
- Compare visualmente los planes de ejecución. Para obtener más información, vea Mostrar un plan de ejecución real.
- Guarde los planes de ejecución y compárelos con SQL Server Management Studio característica comparación de planes.
- Comparar entornos. Diferentes entornos pueden dar lugar a diferencias de planes de consulta o diferencias directas en el uso de CPU. Entre los entornos se incluyen las versiones del servidor, la configuración de la base de datos o del servidor, las marcas de seguimiento, el número de CPU o la velocidad del reloj y la máquina virtual frente a la máquina física. Consulte Diagnóstico de diferencias del plan de consulta para obtener más información.
Escenario 2: La consulta es un camarero en Server1, pero no en Server2
Si los tiempos de CPU de la consulta en ambos servidores son similares, pero el tiempo transcurrido en Server1 es mucho mayor que en Server2, la consulta en Server1 pasa mucho más tiempo esperando un cuello de botella. Por ejemplo:
| Servidor | Tiempo transcurrido (ms) | Tiempo de CPU (ms) | Lecturas (lógicas) |
|---|---|---|---|
| Server1 | 4500 | 1000 | 90200 |
| Server2 | 1100 | 1000 | 90200 |
- Tiempo de espera en Server1: 4500 - 1000 = 3500 ms
- Tiempo de espera en Server2: 1100 - 1000 = 100 ms
Acción: Comprobar los tipos de espera en Server1
Identifique y elimine el cuello de botella en Server1. Algunos ejemplos de esperas son el bloqueo (esperas de bloqueo), las esperas de bloqueo temporal, las esperas de E/S de disco, las esperas de red y las esperas de memoria. Para solucionar problemas comunes de cuellos de botella, vaya a Diagnosticar esperas o cuellos de botella.
Escenario 3: Las consultas en ambos servidores son camareros, pero los tipos de espera o los tiempos son diferentes
Por ejemplo:
| Servidor | Tiempo transcurrido (ms) | Tiempo de CPU (ms) | Lecturas (lógicas) |
|---|---|---|---|
| Server1 | 8000 | 1000 | 90200 |
| Server2 | 3000 | 1000 | 90200 |
- Tiempo de espera en Server1: 8000 - 1000 = 7000 ms
- Tiempo de espera en Server2: 3000 - 1000 = 2000 ms
En este caso, los tiempos de CPU son similares en ambos servidores, lo que indica que es probable que los planes de consulta sean los mismos. Las consultas se realizarían igualmente en ambos servidores si no esperaran a los cuellos de botella. Por lo tanto, las diferencias de duración proceden de las diferentes cantidades de tiempo de espera. Por ejemplo, la consulta espera en bloqueos en Server1 por 7000 ms mientras espera 2000 ms en E/S en Server2.
Acción: comprobar los tipos de espera en ambos servidores
Solucione cada espera de cuello de botella individualmente en cada servidor y acelere las ejecuciones en ambos servidores. La solución de este problema requiere mucho trabajo, ya que debe eliminar cuellos de botella en ambos servidores y hacer que el rendimiento sea comparable. Para solucionar problemas comunes de cuellos de botella, vaya a Diagnosticar esperas o cuellos de botella.
Escenario 4: La consulta en Server1 usa más tiempo de CPU que en Server2, pero las lecturas lógicas están cercanas
Por ejemplo:
| Servidor | Tiempo transcurrido (ms) | Tiempo de CPU (ms) | Lecturas (lógicas) |
|---|---|---|---|
| Server1 | 3000 | 3000 | 90200 |
| Server2 | 1000 | 1000 | 90200 |
Si los datos coinciden con las condiciones siguientes:
- El tiempo de CPU en Server1 es mucho mayor que en Server2.
- El tiempo transcurrido coincide estrechamente con el tiempo de CPU en cada servidor, lo que indica que no hay esperas.
- Las lecturas lógicas, normalmente el controlador más alto del tiempo de CPU, son similares en ambos servidores.
A continuación, el tiempo de CPU adicional procede de otras actividades enlazadas a la CPU. Este escenario es el más raro de todos los escenarios.
Causas: seguimiento, UDF e integración clr
Este problema puede deberse a lo siguiente:
- Seguimiento de XEvents/SQL Server, especialmente con el filtrado de columnas de texto (nombre de base de datos, nombre de inicio de sesión, texto de consulta, etc.). Si el seguimiento está habilitado en un servidor, pero no en el otro, podría ser el motivo de la diferencia.
- Funciones definidas por el usuario (UDF) u otro código T-SQL que realiza operaciones enlazadas a la CPU. Normalmente, esta sería la causa cuando otras condiciones son diferentes en Server1 y Server2, como el tamaño de datos, la velocidad del reloj de CPU o el plan de energía.
- SQL Server integración clr o procedimientos almacenados extendidos (XP) que pueden impulsar la CPU pero no realizar lecturas lógicas. Las diferencias en los archivos DLL pueden dar lugar a diferentes tiempos de CPU.
- Diferencia en SQL Server funcionalidad que está enlazada a la CPU (por ejemplo, código de manipulación de cadenas).
Acción: Comprobación de seguimientos y consultas
Compruebe los seguimientos en ambos servidores para lo siguiente:
- Si hay algún seguimiento habilitado en Server1, pero no en Server2.
- Si hay algún seguimiento habilitado, deshabilite el seguimiento y vuelva a ejecutar la consulta en Server1.
- Si la consulta se ejecuta más rápido esta vez, habilite el seguimiento de nuevo, pero quite los filtros de texto, si hay alguno.
Compruebe si la consulta usa UDF que realizan manipulaciones de cadenas o realizan un procesamiento exhaustivo en las columnas de datos de la
SELECTlista.Compruebe si la consulta contiene bucles, recursiones de función o anidamientos.
Diagnóstico de diferencias de entorno
Compruebe las siguientes preguntas y determine si la comparación entre los dos servidores es válida.
¿Las dos instancias de SQL Server son la misma versión o compilación?
Si no es así, podría haber algunas correcciones que causaron las diferencias. Ejecute la consulta siguiente para obtener información de versión en ambos servidores:
SELECT @@VERSION¿La cantidad de memoria física es similar en ambos servidores?
Si un servidor tiene 64 GB de memoria, mientras que el otro tiene 256 GB de memoria, sería una diferencia significativa. Con más memoria disponible para almacenar en caché las páginas de datos o índices y los planes de consulta, la consulta podría optimizarse de forma diferente en función de la disponibilidad de recursos de hardware.
¿Las configuraciones de hardware relacionadas con la CPU son similares en ambos servidores? Por ejemplo:
El número de CPU varía entre máquinas (24 CPU en una máquina frente a 96 CPU en la otra).
Planes de energía: equilibrados frente a alto rendimiento.
Máquina virtual (VM) frente a máquina física (sin sistema operativo).
Hyper-V frente a VMware: diferencia en la configuración.
Diferencia de velocidad del reloj (menor velocidad del reloj frente a mayor velocidad del reloj). Por ejemplo, 2 GHz frente a 3,5 GHz pueden marcar la diferencia. Para obtener la velocidad del reloj en un servidor, ejecute el siguiente comando de PowerShell:
Get-CimInstance Win32_Processor | Select-Object -Expand MaxClockSpeed
Use una de las dos maneras siguientes de probar la velocidad de CPU de los servidores. Si no producen resultados comparables, el problema está fuera de SQL Server. Podría ser una diferencia de plan de energía, menos CPU, problema de software de máquina virtual o diferencia de velocidad del reloj.
Ejecute el siguiente script de PowerShell en ambos servidores y compare las salidas.
$bf = [System.DateTime]::Now for ($i = 0; $i -le 20000000; $i++) {} $af = [System.DateTime]::Now Write-Host ($af - $bf).Milliseconds " milliseconds" Write-Host ($af - $bf).Seconds " Seconds"Ejecute el siguiente código de Transact-SQL en ambos servidores y compare las salidas.
SET NOCOUNT ON DECLARE @spins INT = 0 DECLARE @start_time DATETIME = GETDATE(), @time_millisecond INT WHILE (@spins < 20000000) BEGIN SET @spins = @spins +1 END SELECT @time_millisecond = DATEDIFF(millisecond, @start_time, getdate()) SELECT @spins Spins, @time_millisecond Time_ms, @spins / @time_millisecond Spins_Per_ms
Diagnóstico de esperas o cuellos de botella
Para optimizar una consulta que espera cuellos de botella, identifique cuánto tiempo es la espera y dónde está el cuello de botella (el tipo de espera). Una vez confirmado el tipo de espera , reduzca el tiempo de espera o elimine la espera por completo.
Para calcular el tiempo de espera aproximado, reste el tiempo de CPU (tiempo de trabajo) del tiempo transcurrido de una consulta. Normalmente, el tiempo de CPU es el tiempo de ejecución real y la parte restante de la duración de la consulta está esperando.
Ejemplos de cómo calcular la duración aproximada de la espera:
| Tiempo transcurrido (ms) | Tiempo de CPU (ms) | Tiempo de espera (ms) |
|---|---|---|
| 3200 | 3000 | 200 |
| 7080 | 1000 | 6080 |
Identificar el cuello de botella o la espera
Para identificar consultas históricas de larga espera (por ejemplo, >el 20 % del tiempo total transcurrido es tiempo de espera), ejecute la consulta siguiente. Esta consulta usa estadísticas de rendimiento para los planes de consulta almacenados en caché desde el inicio de SQL Server.
SELECT t.text, qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time, qs.total_worker_time / qs.execution_count AS avg_cpu_time, (qs.total_elapsed_time - qs.total_worker_time) / qs.execution_count AS avg_wait_time, qs.total_logical_reads / qs.execution_count AS avg_logical_reads, qs.total_logical_writes / qs.execution_count AS avg_writes, qs.total_elapsed_time AS cumulative_elapsed_time FROM sys.dm_exec_query_stats qs CROSS apply sys.Dm_exec_sql_text (sql_handle) t WHERE (qs.total_elapsed_time - qs.total_worker_time) / qs.total_elapsed_time > 0.2 ORDER BY qs.total_elapsed_time / qs.execution_count DESCPara identificar las consultas que se ejecutan actualmente con esperas superiores a 500 ms, ejecute la consulta siguiente:
SELECT r.session_id, r.wait_type, r.wait_time AS wait_time_ms FROM sys.dm_exec_requests r JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id WHERE wait_time > 500 AND is_user_process = 1Si puede recopilar un plan de consulta, compruebe waitstats de las propiedades del plan de ejecución en SSMS:
- Ejecute la consulta con Incluir plan de ejecución real activado.
- Haga clic con el botón derecho en el operador de la izquierda en la pestaña Plan de ejecución.
- Seleccione Propiedades y, a continuación, la propiedad WaitStats .
- Compruebe WaitTimeMs y WaitType.
Si está familiarizado con los escenarios PSSDiag/SQLdiag o SQL LogScout LightPerf/GeneralPerf, considere la posibilidad de usar cualquiera de ellos para recopilar estadísticas de rendimiento e identificar consultas en espera en la instancia de SQL Server. Puede importar los archivos de datos recopilados y analizar los datos de rendimiento con SQL Nexus.
Referencias para ayudar a eliminar o reducir las esperas
Las causas y las resoluciones de cada tipo de espera varían. No hay ningún método general para resolver todos los tipos de espera. Estos son los artículos para solucionar problemas comunes de tipo de espera:
- Descripción y resolución de problemas de bloqueo (LCK_M_*)
- Descripción y resolución de problemas de bloqueo de Azure SQL Base de datos
- Solución de problemas de rendimiento lento SQL Server causados por problemas de E/S (PAGEIOLATCH_*, WRITELOG, IO_COMPLETION, BACKUPIO)
- Resolución de la contención de inserción de última página PAGELATCH_EX en SQL Server
- La memoria concede explicaciones y soluciones (RESOURCE_SEMAPHORE)
- Solución de problemas de consultas lentas resultantes de ASYNC_NETWORK_IO tipo de espera
- Solución de problemas de tipo de espera de alta HADR_SYNC_COMMIT con grupos de disponibilidad de Always On
- Cómo funciona: CMEMTHREAD y depurarlos
- Hacer que las esperas de paralelismo sean accionables (CXPACKET y CXCONSUMER)
- ESPERA DE THREADPOOL
Para obtener descripciones de muchos tipos de espera y lo que indican, vea la tabla en Tipos de esperas.
Diagnóstico de diferencias en el plan de consulta
Estas son algunas causas comunes de las diferencias en los planes de consulta:
Diferencias de tamaño de datos o valores de datos
¿Se usa la misma base de datos en ambos servidores, con la misma copia de seguridad de base de datos? ¿Se han modificado los datos en un servidor en comparación con el otro? Las diferencias de datos pueden dar lugar a diferentes planes de consulta. Por ejemplo, combinar la tabla T1 (1000 filas) con la tabla T2 (2000 000 filas) es diferente de combinar la tabla T1 (100 filas) con la tabla T2 (2000 000 filas). El tipo y la velocidad de la
JOINoperación pueden ser significativamente diferentes.Diferencias de estadísticas
¿Se han actualizado las estadísticas en una base de datos y no en la otra? ¿Se han actualizado las estadísticas con una frecuencia de muestreo diferente (por ejemplo, un 30 % frente a un examen completo del 100 %)? Asegúrese de actualizar las estadísticas en ambos lados con la misma frecuencia de muestreo.
Diferencias de nivel de compatibilidad de bases de datos
Compruebe si los niveles de compatibilidad de las bases de datos son diferentes entre los dos servidores. Para obtener el nivel de compatibilidad de la base de datos, ejecute la consulta siguiente:
SELECT name, compatibility_level FROM sys.databases WHERE name = '<YourDatabase>'Diferencias de versión y compilación del servidor
¿Las versiones o compilaciones de SQL Server son diferentes entre los dos servidores? Por ejemplo, ¿un servidor SQL Server versión 2014 y el otro SQL Server versión 2016? Puede haber cambios en el producto que pueden dar lugar a cambios en la forma en que se selecciona un plan de consulta. Asegúrese de comparar la misma versión y compilación de SQL Server.
SELECT ServerProperty('ProductVersion')Diferencias de versión del Estimador de cardinalidad (CE)
Compruebe si el estimador de cardinalidad heredado está activado en el nivel de base de datos. Para obtener más información sobre CE, vea Estimación de cardinalidad (SQL Server).
SELECT name, value, is_value_default FROM sys.database_scoped_configurations WHERE name = 'LEGACY_CARDINALITY_ESTIMATION'Revisiones del optimizador habilitadas o deshabilitadas
Si las revisiones del optimizador de consultas están habilitadas en un servidor pero deshabilitadas en el otro, se pueden generar planes de consulta diferentes. Para obtener más información, vea SQL Server modelo de mantenimiento de la marca de seguimiento de revisiones 4199 del optimizador de consultas.
Para obtener el estado de las revisiones del optimizador de consultas, ejecute la consulta siguiente:
-- Check at server level for TF 4199 DBCC TRACESTATUS (-1) -- Check at database level USE <YourDatabase> SELECT name, value, is_value_default FROM sys.database_scoped_configurations WHERE name = 'QUERY_OPTIMIZER_HOTFIXES'Diferencias de marcas de seguimiento
Algunas marcas de seguimiento afectan a la selección del plan de consulta. Compruebe si hay marcas de seguimiento habilitadas en un servidor que no están habilitadas en el otro. Ejecute la consulta siguiente en ambos servidores y compare los resultados:
-- Check at server level for trace flags DBCC TRACESTATUS (-1)Diferencias de hardware (recuento de CPU, tamaño de memoria)
Para obtener la información de hardware, ejecute la consulta siguiente:
SELECT cpu_count, physical_memory_kb/1024/1024 PhysicalMemory_GB FROM sys.dm_os_sys_infoDiferencias de hardware según el optimizador de consultas
Compruebe el
OptimizerHardwareDependentPropertiesde un plan de consulta y compruebe si las diferencias de hardware se consideran significativas para los distintos planes.WITH xmlnamespaces(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT txt.text, t.OptHardw.value('@EstimatedAvailableMemoryGrant', 'INT') AS EstimatedAvailableMemoryGrant , t.OptHardw.value('@EstimatedPagesCached', 'INT') AS EstimatedPagesCached, t.OptHardw.value('@EstimatedAvailableDegreeOfParallelism', 'INT') AS EstimatedAvailDegreeOfParallelism, t.OptHardw.value('@MaxCompileMemory', 'INT') AS MaxCompileMemory FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp CROSS APPLY qp.query_plan.nodes('//OptimizerHardwareDependentProperties') AS t(OptHardw) CROSS APPLY sys.dm_exec_sql_text (CP.plan_handle) txt WHERE text Like '%<Part of Your Query>%'Tiempo de espera del optimizador
¿Hay un problema de tiempo de espera del optimizador ? El optimizador de consultas puede dejar de evaluar las opciones del plan si la consulta que se ejecuta es demasiado compleja. Cuando se detiene, elige el plan con el costo más bajo disponible en ese momento. Esto puede dar lugar a lo que parece una elección de plan arbitraria en un servidor frente a otro.
Opciones set
Algunas opciones SET afectan al plan, como SET ARITHABORT. Para obtener más información, vea Opciones de SET.
Diferencias de sugerencias de consulta
¿Una consulta usa sugerencias de consulta y la otra no? Compruebe manualmente el texto de la consulta para establecer la presencia de sugerencias de consulta.
Planes que distinguen parámetros (problema de examen de parámetros)
¿Está probando la consulta con exactamente los mismos valores de parámetro? Si no es así, puede empezar allí. ¿Se compiló el plan anteriormente en un servidor en función de un valor de parámetro diferente? Pruebe las dos consultas mediante la sugerencia de consulta RECOMPILE para asegurarse de que no se produzca ninguna reutilización del plan. Para obtener más información, vea Investigar y resolver problemas confidenciales de parámetros.
Opciones de base de datos diferentes o opciones de configuración con ámbito
¿Se usan las mismas opciones de base de datos o opciones de configuración con ámbito en ambos servidores? Algunas opciones de base de datos pueden influir en las opciones del plan. Por ejemplo, compatibilidad de bases de datos, CE heredada frente a CE predeterminada y examen de parámetros. Ejecute la siguiente consulta desde un servidor para comparar las opciones de base de datos usadas en los dos servidores:
-- On Server1 add a linked server to Server2 EXEC master.dbo.sp_addlinkedserver @server = N'Server2', @srvproduct=N'SQL Server' -- Run a join between the two servers to compare settings side by side SELECT s1.name AS srv1_config_name, s2.name AS srv2_config_name, s1.value_in_use AS srv1_value_in_use, s2.value_in_use AS srv2_value_in_use, Variance = CASE WHEN ISNULL(s1.value_in_use, '##') != ISNULL(s2.value_in_use,'##') THEN 'Different' ELSE '' END FROM sys.configurations s1 FULL OUTER JOIN [server2].master.sys.configurations s2 ON s1.name = s2.name SELECT s1.name AS srv1_config_name, s2.name AS srv2_config_name, s1.value srv1_value_in_use, s2.value srv2_value_in_use, s1.is_value_default, s2.is_value_default, Variance = CASE WHEN ISNULL(s1.value, '##') != ISNULL(s2.value, '##') THEN 'Different' ELSE '' END FROM sys.database_scoped_configurations s1 FULL OUTER JOIN [server2].master.sys.database_scoped_configurations s2 ON s1.name = s2.nameGuías de plan
¿Se usan guías de plan para las consultas en un servidor, pero no en el otro? Ejecute la consulta siguiente para establecer diferencias:
SELECT * FROM sys.plan_guides
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