Post reply

Procedure Cache Hit Ratio

  • October 22, 2016 at 11:09 am

    #331497

    Last night I just migrated one of my busiest databases from SQL 2008 to 2012. Max server memory allocated to SQL Server In 2008 was 50000 and now in 2012 is 51200. Also 2012 has better processing power. However what I am seeing from my monitoring tool that SQL is having issues looking for some query plans in cache. Could this be because so many processes are being run for the first time and possibly has to recompile? How do I make this go away?

  • October 22, 2016 at 1:38 pm

    #1908176

    MVP_enthusiast (10/22/2016)

    Last night I just migrated one of my busiest databases from SQL 2008 to 2012. Max server memory allocated to SQL Server In 2008 was 50000 and now in 2012 is 51200. Also 2012 has better processing power. However what I am seeing from my monitoring tool that SQL is having issues looking for some query plans in cache. Could this be because so many processes are being run for the first time and possibly has to recompile? How do I make this go away?

    First of all you are using the default settings and boundaries which aren't close to any "one size fits all", take it with a grain of salt. Secondly, are there any page buffer cache fluctuations or any other signs of pressure? I guess not and directly after a migration, you will see this kind of fluctuations, perfectly normal and to answer you question "How do I make this go away?" , be as the Doctor said, stay calm and wait.

    😎

    Quick question, is the 2012 instance fully patched?

  • October 22, 2016 at 4:50 pm

    #1908185

    Personally, I think that if you have a heavy (read that as "frequent") usage database and it takes more than 5-10 minutes for most everything to settle into cache, then there's a problem.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 22, 2016 at 6:35 pm

    #1908192

    Jeff Moden (10/22/2016)

    Personally, I think that if you have a heavy (read that as "frequent") usage database and it takes more than 5-10 minutes for most everything to settle into cache, then there's a problem.

    Well, not just reads. A bunch of loading also. SSIS jobs loading data from teradata, db2 and Oracle. Many SPs, some executing for the first time. I'd like to think that 5-10 mins is a very tiny interval before the verdict is called

  • October 22, 2016 at 6:39 pm

    #1908193

    Eirikur Eiriksson (10/22/2016)

    MVP_enthusiast (10/22/2016)

    Last night I just migrated one of my busiest databases from SQL 2008 to 2012. Max server memory allocated to SQL Server In 2008 was 50000 and now in 2012 is 51200. Also 2012 has better processing power. However what I am seeing from my monitoring tool that SQL is having issues looking for some query plans in cache. Could this be because so many processes are being run for the first time and possibly has to recompile? How do I make this go away?

    First of all you are using the default settings and boundaries which aren't close to any "one size fits all", take it with a grain of salt. Secondly, are there any page buffer cache fluctuations or any other signs of pressure? I guess not and directly after a migration, you will see this kind of fluctuations, perfectly normal and to answer you question "How do I make this go away?" , be as the Doctor said, stay calm and wait.

    😎

    Quick question, is the 2012 instance fully patched?

    ?? this gives me comfort. Yea it is fully patched. After about 5hrs things are looking better a bit. >96%

  • October 22, 2016 at 9:37 pm

    #1908198

    MVP_enthusiast (10/22/2016)

    Eirikur Eiriksson (10/22/2016)

    MVP_enthusiast (10/22/2016)

    Last night I just migrated one of my busiest databases from SQL 2008 to 2012. Max server memory allocated to SQL Server In 2008 was 50000 and now in 2012 is 51200. Also 2012 has better processing power. However what I am seeing from my monitoring tool that SQL is having issues looking for some query plans in cache. Could this be because so many processes are being run for the first time and possibly has to recompile? How do I make this go away?

    First of all you are using the default settings and boundaries which aren't close to any "one size fits all", take it with a grain of salt. Secondly, are there any page buffer cache fluctuations or any other signs of pressure? I guess not and directly after a migration, you will see this kind of fluctuations, perfectly normal and to answer you question "How do I make this go away?" , be as the Doctor said, stay calm and wait.

    😎

    Quick question, is the 2012 instance fully patched?

    ?? this gives me comfort. Yea it is fully patched. After about 5hrs things are looking better a bit. >96%

    Shouldn't give you much comfort at all. That's a long time for cache to ramp up. That's going to continue to be a problem whenever you need to bounce the service or boot the machine.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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