Post reply

SQL Server exec plans for ad hoc queries filling plan cache

  • November 1, 2011 at 4:06 am

    #259422

    We have a database which is updated every hour. Changes are made by an application which uses DELETE/INSERT for each row that is updated or inserted. SQL Server is 2008 R2 Standard Edition.

    The query execution plans for the ad hoc queries are pushing out other (more important, larger) execution plans which is affecting reporting on that database and eating into the data cache.

    After looking at the plan cache we have found that (as expected) SQL is generating a new plan for each query.

    My question is, would creating a plan guide stop SQL generating a new plan for each update?

    I have seen in the cache that for an update to a float column for example, SQL parameterizes it to a numeric of varying length each time, which means every parameterized query is different. If I create a plan guide with the parameterized version of the query, specifying the correct data types for each column, will this force SQL to use this plan?

    I am aware that using stored procedures will solve this, but for other reasons that isn't an option.

    Thanks.

  • November 1, 2011 at 4:42 am

    #1401494

    Any reason against forced parameterisation?

    Here's a quick overlook into your options => http://msdn.microsoft.com/en-us/library/ms191275.aspx

  • November 1, 2011 at 5:01 am

    #1401511

    I would suggest you enable optimise for ad-hoc workloads. Plan guides are not an answer to this kind of problem, unless they're parameterisation ones.

    Edit: hang on... nHibernate by any chance?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply