March 28, 2017 at 6:17 am
Hi,
We just moved from SQL Server 2008 R2 32 bit to SQL Server 2014 64 Bit Ent ( no SP just RTM released in 2014 ).
Though the box has 14 virtual processors ( VMware - its a virtual machine CPU's ) 83 GB memory all sprocs are executing slower than expected. I ran sp_updatestats after moving to the new server . any other recompile objects sqls need to be run? I read that sql server 2014 had some cached exec plan issue?
Thanks
March 28, 2017 at 6:42 am
sqlguy80 - Tuesday, March 28, 2017 6:17 AMHi,We just moved from SQL Server 2008 R2 32 bit to SQL Server 2014 64 Bit Ent ( no SP just RTM released in 2014 ).
Though the box has 14 virtual processors ( VMware - its a virtual machine CPU's ) 83 GB memory all sprocs are executing slower than expected. I ran sp_updatestats after moving to the new server . any other recompile objects sqls need to be run? I read that sql server 2014 had some cached exec plan issue?
Thanks
Questions, what is the compatibility level for the databases? Why not any SPs? Some nice improvements have been pushed down to 2014 so that's worth looking at. There should be no need to recompile as the cached plans are not migrated during side-by-side migration but updating stats is necessary.
😎
March 28, 2017 at 6:45 am
There's no need to recompile. Procedures' plans are cached in memory only, and so cannot survive a server upgrade (they don't even survive a reboot).
2014 has a new cardinality estimator and, while most queries benefit from it, there will be some degrading. It's why performance tests are strongly recommended when upgrading to SQL 2014 or above. If you're running in 2014 compat mode, try switching back to 2008. If that fixes it, then the queries need to be rewritten/tweaked for the new CE.
Oh, and you should have the latest SP, there's no good reason to be running 2014 RTM
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 28, 2017 at 6:52 pm
Thank you for the replies.
It looks like after running slow for a day and users complaining, things became better and faster on day 2, Does it take a day or two to cache plans and save in memory and then use them?
Queries which took 17 seconds on day 1 , ran in 2 seconds on day 2. Memory was bumped up by the IT Guy.
The server has 96 GB ,memory allocated and SQL has been given 81 GB and rest 15 GB to the OS.
Currently the server is using ~88% Memory. If it gets to an alarming level,and we reduce the memory on SQL Server to say 70 GB to fix it , we will see reduction in memory utilization but I think SQL Server will again need to cache plans in the memory, so again performance will drop and become good in a day or two. Is that correct?
March 29, 2017 at 1:51 am
sqlguy80 - Tuesday, March 28, 2017 6:52 PMDoes it take a day or two to cache plans and save in memory and then use them?
No. Plans are cached the first time a query runs.
I think SQL Server will again need to cache plans in the memory, so again performance will drop and become good in a day or two. Is that correct?
No.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2017 at 12:19 pm
Thanks Gail for the quick responses.
So if we reduce the memory , say SQL is using 80 GB to 70 GB then what is the effect on stored procedures execution?
Thank you
March 29, 2017 at 1:06 pm
sqlguy80 - Tuesday, March 28, 2017 6:52 PM...The server has 96 GB ,memory allocated and SQL has been given 81 GB and rest 15 GB to the OS.
Currently the server is using ~88% Memory. If it gets to an alarming level,and we reduce the memory on SQL Server to say 70 GB to fix it , we will see reduction in memory utilization but I think SQL Server will again need to cache plans in the memory, so again performance will drop and become good in a day or two. Is that correct?
Typically using 88% of memory would be fine. Do you have anything else besides SQL Server running on this server? If not, you probably should just leave the Maximum Server Memory for the instance alone. If you don't know, you could watch the number of page faults (Memory pages/sec) in Performance Monitor to see if there's too much contention on physical RAM.
https://technet.microsoft.com/en-us/library/cc938631.aspx
Lowering the Maximum Server Memory could affect all SQL Server caches such as data cache and execution plans. As already mentioned by Gail, once the execution plan is in memory, and once the data is loaded from disk into data cache the first time each stored proc runs, then the second execution will benefit from already having the plan and data in memory.
March 29, 2017 at 2:51 pm
sqlguy80 - Wednesday, March 29, 2017 12:19 PMThanks Gail for the quick responses.So if we reduce the memory , say SQL is using 80 GB to 70 GB then what is the effect on stored procedures execution?
Thank you
Directly, nothing, but you'll likely get more data cache churn, so more load on the IO subsystem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply