Post reply

SQL Plan Cache - Database Autoclose option

  • September 25, 2012 at 6:00 am

    #264708

    Hi All

    I've been doing some research on the topic of Plan caching in relation to the auto_close database option.

    I have a 2005 instance with +- 80 databases, one of which has the auto_close option set to true. In the SQL error logs, I noticed that everytime the database "auto-closes", the plan cache is completely flushed for the entire instance.

    Is this normal?

    Also, does the same thing happen on 2008? I tested the scenario on a 2008 instance and it seems that when database "auto-closes", only plans for that specific database are flushed out.

    Has anyone got some insight into this?

    Thanks

  • September 25, 2012 at 6:34 am

    #1541819

    Yep. By design. Here's some description of it. Yet another reason to avoid auto_close.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • September 25, 2012 at 6:41 am

    #1541825

    Grant Fritchey (9/25/2012)

    Yep. By design. Here's some description of it. Yet another reason to avoid auto_close.

    Ok, so I'm definately going to change the option to false.

    Question is, if I make the change now, will it clear the cache again?

    I don't have a test 2005 system to test this process.

    Thanks

  • September 25, 2012 at 6:43 am

    #1541826

    SQLSACT (9/25/2012)

    Grant Fritchey (9/25/2012)

    Yep. By design. Here's some description of it. Yet another reason to avoid auto_close.

    Ok, so I'm definately going to change the option to false.

    Question is, if I make the change now, will it clear the cache again?

    I don't have a test 2005 system to test this process.

    Thanks

    I'm pretty sure, if you change it, you'll get one more cache flush. But I could be wrong.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • September 25, 2012 at 6:47 am

    #1541830

    Grant Fritchey (9/25/2012)

    SQLSACT (9/25/2012)

    Grant Fritchey (9/25/2012)

    Yep. By design. Here's some description of it. Yet another reason to avoid auto_close.

    Ok, so I'm definately going to change the option to false.

    Question is, if I make the change now, will it clear the cache again?

    I don't have a test 2005 system to test this process.

    Thanks

    I'm pretty sure, if you change it, you'll get one more cache flush. But I could be wrong.

    Ok, thanks

    I'll have to change it during a quiter period

    Thanks for your help

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

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