none
Forced parameterization

    Pergunta

  • 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.

    quinta-feira, 7 de junho de 2012 16:28

Todas as Respostas

  • 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:18
  • 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:25
  • 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 17:28
  • 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.

    quinta-feira, 7 de junho de 2012 18:11