October 22, 2016 at 11:09 am
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
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
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
October 22, 2016 at 6:35 pm
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
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
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply