SQL Server – olfateo de parámetros

He leído muchos artículos sobre el rastreo de parámetros, pero no está claro si esto es bueno o malo. ¿Alguien puede explicar esto con un simple ejemplo?

¿Hay alguna forma de detectar automáticamente que se asignó un plan incorrecto a una statement específica?

Gracias por adelantado.

Está bien, pero a veces puede ser malo.

La detección de parámetros se basa en el optimizador de consultas que utiliza el valor del parámetro proporcionado para determinar el mejor plan de consulta posible. Una de las muchas opciones y una que es bastante fácil de entender es si se debe escanear toda la tabla para obtener los valores o si será más rápido utilizando las búsquedas de índices. Si el valor en su parámetro es altamente selectivo, el optimizador probablemente construirá un plan de consulta con búsquedas y si no lo es, la consulta escaneará su tabla.

El plan de consulta se almacena en caché y se reutiliza para consultas consecutivas que tienen valores diferentes. La parte mala de la detección de parámetros es cuando el plan en caché no es la mejor opción para uno de esos valores.

Data de muestra:

create table T ( ID int identity primary key, Value int not null, AnotherValue int null ); create index IX_T_Value on T(Value); insert into T(Value) values(1); insert into T(Value) select 2 from sys.all_objects; 

T es una tabla con un par de miles de filas con un índice no agrupado en Value. Hay una fila donde el valor es 1 y el rest tiene el valor 2 .

Consulta de muestra:

 select * from T where Value = @Value; 

Las opciones que tiene aquí el optimizador de consultas es realizar un Escaneo de índice agrupado y verificar la cláusula where en cada fila o usar una búsqueda de índice para encontrar las filas que coinciden y luego hacer una búsqueda clave para obtener los valores de las columnas solicitadas en la lista de columnas

Cuando el valor olido es 1 el plan de consulta se verá así:

enter image description here

Y cuando el valor sniffed es 2 se verá así:

enter image description here

La parte mala de la detección de parámetros en este caso ocurre cuando el plan de consulta se construye olfateando un 1 pero se ejecuta más adelante con el valor de 2 .

enter image description here

Puede ver que la búsqueda de teclas se ejecutó 2352 veces. Un escaneo sería claramente la mejor opción.

Para resumir, diría que la detección de parámetros es algo bueno que debe intentar hacer que suceda lo más posible mediante el uso de parámetros para sus consultas. A veces puede salir mal y, en esos casos, lo más probable es que se deba a datos asimétricos que están jugando con sus estadísticas.

Actualizar:

Aquí hay una consulta en contra de un par de dmv que puede usar para encontrar qué consultas son más caras en su sistema. Cambiar a orden por cláusula para utilizar diferentes criterios sobre lo que estás buscando. Creo que TotalDuration es un buen lugar para comenzar.

 set transaction isolation level read uncommitted; select top(10) PlanCreated = qs.creation_time, ObjectName = object_name(st.objectid), QueryPlan = cast(qp.query_plan as xml), QueryText = substring(st.text, 1 + (qs.statement_start_offset / 2), 1 + ((isnull(nullif(qs.statement_end_offset, -1), datalength(st.text)) - qs.statement_start_offset) / 2)), ExecutionCount = qs.execution_count, TotalRW = qs.total_logical_reads + qs.total_logical_writes, AvgRW = (qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count, TotalDurationMS = qs.total_elapsed_time / 1000, AvgDurationMS = qs.total_elapsed_time / qs.execution_count / 1000, TotalCPUMS = qs.total_worker_time / 1000, AvgCPUMS = qs.total_worker_time / qs.execution_count / 1000, TotalCLRMS = qs.total_clr_time / 1000, AvgCLRMS = qs.total_clr_time / qs.execution_count / 1000, TotalRows = qs.total_rows, AvgRows = qs.total_rows / qs.execution_count from sys.dm_exec_query_stats as qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as st cross apply sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) as qp --order by ExecutionCount desc --order by TotalRW desc order by TotalDurationMS desc --order by AvgDurationMS desc ; 

Sí, a veces es bueno o malo.

El optimizador de consultas de muchos tiempos elige el plan de consulta anterior para su ejecución porque almacena este plan en el caché para las consultas que se ejecutan con frecuencia. Ahora, ¿qué pasó cuando el plan de consulta anterior tiene un parámetro de exploración de tabla que es necesario cambiar para la exploración de índice después de boost los registros?

Encontré que en mi situación el optimizador de consultas utilizaba un plan de consulta anterior en lugar de crear un nuevo plan de consulta. El optimizador de consultas usaba un plan de consulta anterior del caché de consultas. He creado una publicación muy interesante sobre Parameter Sniffing. Visite esta url: http://www.dbrnd.com/2015/05/sql-server-parameter-sniffing/

Intereting Posts