Entrar
 
HomeBibliotecaWikiAprenderGaleriaDownloadsSuporte ComunidadeFóruns
Recursos para Profissionais de TI >
> 
> 
Forced parameterization

Pergunta Forced parameterization

  • quinta-feira, 7 de junho de 2012 16:28
     
     
    Entrar para Votar
    0

    Hello all,

    In one of our servers, we find high "plan count" values in the activity monitor for a number of ad-hoc queries. Do these high "plan count" values indicate that enabling forced parameterization for the database would help improve the performance as plans would be reused?

    Thanks.

     

Todas as Respostas

  • quinta-feira, 7 de junho de 2012 17:18
    Usuário que responde
     
     
    Entrar para Votar
    0

    Plan counts or usecounts?

    select db_name(dbid),sql,usecounts
    from master..syscacheobjects where objtype='Adhoc'
    order by usecounts asc

    Yes it could but you need to tested carefully. Take a look at http://msdn.microsoft.com/en-us/library/cc645587.aspx settiongs

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

     
  • quinta-feira, 7 de junho de 2012 17:25
     
     
    Entrar para Votar
    0

    Optimize for ad hoc workloads option is enabled.

    The "plan count" is shown in 2008 activity monitor with recent expensive queries.

     
  • quinta-feira, 7 de junho de 2012 17:28
    Usuário que responde
     
     
    Entrar para Votar
    0

    See Dan's blog

    http://weblogs.sqlteam.com/dang/archive/2009/06/27/Forced-Parameterization-A-Turbo-Button.aspx

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

     
  • quinta-feira, 7 de junho de 2012 18:11
     
     
    Entrar para Votar
    0

    Many thanks for the link, good article.

    One of our servers is experiencing performance problems, queries are running very slow with high io and cpu, high pageiolatch waits etc. We are at a point where we can't add more memory with the server.

    I found that the server currently has around 30000 ad hoc plans in the plan cache (optimize for ad hoc workloads option is enabled). Total ad hoc plans with use count = 1 is around 13000. We cannot do much to change the queries sent by third party applications. So I am checking the forced parameterization option, if it would help improve the performance. Any thoughts on whether we should enable the option?

    Thanks.