Post reply

Clearing Plan Cache

  • November 14, 2014 at 4:03 am

    #1758497

    thanks that's great. I can run that while performance is good and bad and then compare figures.

  • November 17, 2014 at 2:56 am

    #1758902

    Hi spaghettidba,

    I ran your clerk memory usage script and it provided me with a figure in Kb's during one of my slow performance periods.

    This equated to around 2GB.

    So my question is, what is the maximum upper limit for SQL Server cache memory to grow to?

    Is it the max memory that we set in SQL Server that it looks at? My max memory is set to 20GB.

    So should the SQL cache memory grow to 20GB in size before slowing things down? Is that how it works?

    I'm just trying to figure out why it went up to 2GB and then slowed down and not 20GB like I would have expected.

    Can we set the maximum limit for cache memory like we do for RAM memory in OS?

    If so how can we do this or check this?

  • November 17, 2014 at 3:20 am

    #1758905

    2 GB is not too bad. At least, not something able to bring your instance to its knees.

    It was worth investigating though.

    Did you capture the wait stats?

    -- Gianluca Sartori

  • November 17, 2014 at 3:25 am

    #1758907

    Sorry, I haven't answered your questions.

    Here you go:

    zedtec (11/17/2014)

    So my question is, what is the maximum upper limit for SQL Server cache memory to grow to?

    There is no limit. A smaller plan cache, means more memory for the buffer pool.

    Is it the max memory that we set in SQL Server that it looks at? My max memory is set to 20GB.

    No, that limit applies to all memory clerks.

    So should the SQL cache memory grow to 20GB in size before slowing things down? Is that how it works?

    I'm just trying to figure out why it went up to 2GB and then slowed down and not 20GB like I would have expected.

    That's what I was hoping to capture. This or another exotic clerk eating all the memory.

    Can we set the maximum limit for cache memory like we do for RAM memory in OS?

    No. Memory limits are global.

    -- Gianluca Sartori

  • November 17, 2014 at 3:53 am

    #1758917

    Thanks for that.

    Unfortunately I didn't collect the wait statistics. I will check next time and also see how full the cache gets before it causes slow performance in order to do a comparison.

  • November 17, 2014 at 4:03 am

    #1758920

    zedtec (11/17/2014)

    Thanks for that.

    Unfortunately I didn't collect the wait statistics. I will check next time and also see how full the cache gets before it causes slow performance in order to do a comparison.

    I suggest that you stop thinking about memory and focus on the bad parameter sniffing suggestion: it's much more likely.

    You could activate the Data Collector and monitor the instance for a while.

    You will be able to see the distribution of wait stats in time, query stats, multiple plans for the same statement and much more.

    -- Gianluca Sartori

  • November 17, 2014 at 4:08 am

    #1758924

    Ok will do, thanks.

  • November 17, 2014 at 5:43 am

    #1758946

    As asked in a previous post did you check for Single Use Plans causing Plan Cache Bloat? I have found that setting "Optimise for Ad-Hoc WorkLoads" makes a big difference usually. Its part of my standard build on all SQL Servers. Its a SQL server level configuration option.

    Check out this Link which will explain a little bit more about it.

    http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/

    Hope this helps

  • November 18, 2014 at 3:45 am

    #1759200

    That setting is set to false currently. I haven't tried that, so will enable it and see if it makes a difference thanks.

Viewing 9 posts - 16 through 23 (of 23 total)

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