• Replies 2 replies
  • Subscribers 336 subscribers
  • Views 19 views
  • Users 0 members are here
Related

fees by performance report freezing up

Jeff Stahel
$organization

Hello all,

We have a recurring issue with our Fees by Performance report freezing up. The only fix I have found (outside of restarting the SQL service or the SQL Server) is to run dbcc freeproccache to clear the procedure cache. Once that has been run, the report works fine. This issue has been a minor annoyance for a few years now, but since we have updated to 12.03 the report freezes very frequently.

Does anybody else have this issue? Any clues as to how to fix this? I'm not crazy about clearing the procedure cache every day, but that is what's been needed lately.

Thanks!

Jeff

  • $organization

    I have had it do this for a different issue, GiftAid at the moment but also had it for Direct Debit processing and use the following script to ensure I only remove the cache for related queries.

    DECLARE @PlanHandle VARBINARY(255);
    DECLARE @TopQueries TABLE (plan_handle VARBINARY(255));

    INSERT INTO @TopQueries
    SELECT  plan_handle
    FROM sys.dm_exec_cached_plans
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
    WHERE st.text LIKE N'%giftaid%'

    DECLARE TopQueryCursor CURSOR FOR SELECT plan_handle FROM @TopQueries

    OPEN TopQueryCursor
    FETCH NEXT FROM TopQueryCursor INTO @PlanHandle;

    WHILE @@FETCH_STATUS = 0
    BEGIN

       DBCC FREEPROCCACHE(@PlanHandle);
       FETCH NEXT FROM TopQueryCursor INTO @PlanHandle;

    END

    CLOSE TopQueryCursor
    DEALLOCATE TopQueryCursor

     

    At least that way you may only remove the plans related to that report rather than the whole cache.

    Mark



    [edited by: Mark Ridley at 8:53 AM (GMT -6) on 29 Oct 2014] bolding the bit of sql that needs to be changed
  • $organization in reply to Mark Ridley

    Thanks Mark, I hadn't thought of that. Great solution to the problem!

    I appreciate the help.

    Jeff