PARAMETERIZATION FORCED (sugerencias)

[cancha]

Buen día comunidad tengo las siguientes inquietudes:

recientemente estuve investigando como se usaba la cache de una db en producción con la siguiente sentencia saco todos los queries adhoc y me di cuenta que son
la mayoría en comparación con los tipos procedures

select * from sys.syscacheobjects where objtype = 'Adhoc' me retorna un poco mas de 38000 registros
select * from sys.syscacheobjects where objtype = 'Proc' me retora un poco mas de 1000 registros

en las consultas adhoc hay varios que se repiten muchas veces es aqui donde se me ocurrio lo de PARAMETERIZATION FORCED para forzar que las consultas no se compilen en cada ejecución y usen un plan de ejecución

la otra duda que tengo es que el procedure cache hit ratio no lo tengo al 100% constantemente el promedio se mantiene en 85% activar PARAMETERIZATION FORCED ayudara con esto o algo tiene que ver?

con ese cambio se mejorara considerablemente el performance de la DB? la db en ocasiones llega a tener 25 usuarios concurrentes trabajando de un total de 65 que usan, si entran los 25 usuarios o N usuarios trabajando concurrentemente esas consultas ya no tendrían que consumir recursos en su compilación y usarían los planes creados por la PARAMETERIZATION FORCED correcto?

ademas que cuando hay mas concurrencia suele producirse bloqueos quizás sean las compilaciones de los queries en masa?

activando PARAMETERIZATION FORCED pueda que las consultas devuelvan información incorrecta?

tengo entendido que al activar PARAMETERIZATION FORCED este genera bloqueos mientra se va activando realizar esos cambios en producción mientras se trabaja no seria recomendable? o podría realizar el cambio con la db en uso ?

Saludos

[matanga]

Debes tener presente que no hay reglas generales para optimizar una base de datos, cada caso tiene problemas y soluciones diferentes, y por lo tanto no se puede afirmar que Forced Parameterization sea siempre una mejora de rendimiento.

Si quieres subir el 85% de Proc Cache Hit Ratio, las alternativas son:

1. Incrementar el valor de Max Server Memory, suponiendo que hay memoria disponible en el servidor y que la licencia y/o arquitectura de SQL Server y/o Windows lo permitan, puedes aumentar la cantidad de memoria total asignada a la instancia, lo que aumenta la probabilidad de un hit en Proc Cache. Esta es una solución fácil de implementar y es segura, pero imprecisa ya que no puedes configurar el porcentaje de Data Cache y Proc Cache.

2. Opción Forced Parameterization, esto convierte los valores literales a parámetros en select, insert, update y delete, lo que aumenta la probabilidad de tener consultas equivalentes y como consecuencia la probabilidad de un hit en Proc Cache. Esta solución también es fácil de implementar, pero no tan segura, ya que hay veces que el optimizador crea mejores planes de ejecución en función de los valores literales, por ejemplo en tablas particionadas o columnas calculadas, y por otro lado, no siempre funciona, hay excepciones como subconsultas dentro de un IF o INSERT.

3. Modificar el código, suponiendo que tienes acceso al código de la aplicación, puedes identificar las consultas de uso más frecuente, y en caso de que la mayoría tengan la misma sintaxis pero diferentes valores literales, cambiar el código para ejecutarlas con el procedimiento sp_executesql (hace lo mismo que Forced Parameterization), y en caso de que tengan diferente sintaxis, cambiar el código para agregar el hint KEEPFIXED PLAN. Esta solución es más difícil de implementar, pero la más segura, ya que se implementa de manera selectiva sobre cada consulta.

De todos modos, antes de evaluar estos cambios, asegúrate de que:

1. Hay un problema real de rendimiento y el origen es la Proc Cache

2. Vale la pena optimizar un ratio de 85% cuando no dista mucho del ratio ideal 90% o 95%.

3. 85% es un ratio que se mantiene constante, y no un ratio temporal que solo se produce en un momento dado.

ademas que cuando hay mas concurrencia suele producirse bloqueos quizás sean las compilaciones de los queries en masa?

No, los bloqueos no se relacionan con un hit o miss de una cache.

activando PARAMETERIZATION FORCED pueda que las consultas devuelvan información incorrecta?

No, generar o reutilizar un plan de ejecución no se relaciona con el resultado de la consulta, excepto por algún bug.

tengo entendido que al activar PARAMETERIZATION FORCED este genera bloqueos mientra se va activando realizar esos cambios en producción mientras se trabaja no seria recomendable? o podría realizar el cambio con la db en uso?

No, activar esta opción no genera bloqueos, pero si invalida los datos de Proc Cache y el optimizador tiene que volver a generar los planes de ejecución en la medida que se van ejecutando las consultas lo que puede producir problemas temporales de rendimiento, y aunque sea una opción que se pueda activar on-line, se recomienda hacer estos cambios en horarios de poco trabajo.

Saludos