November 19, 2012 at 12:50 pm
With little to no code changes, I went from a SQL 2008 server to a clustered SQL 2012 server, both Standard Edition. Old server had 78 GB, new servers have 128 GB per node. Other significant difference is we have turned on mirroring for redundancy and log shipping to another cluster for SSRS reports on the same subnet.
While it is only a 2 socket server, we went from a very slow RAID DP shelf shared with dozens of other servers, to a 24 spindle 15K RAID10 shelf shared by a total of 4 machines (2 clusters).
I have set the maximum server memory to 110GB, but am getting insufficient system memory and 'unable to reuse a session which had been reset for connection pooling' errors a couple of times a day.
I do see high CXPacket waits as well as ASYNC_NETWORK_IO waits. As a result, I have MAXDOP set to 4 (8 cores) and changed cost threshold to 30. The network IO waits are likely due to a linked server which holds staging data which is in another data center. I have also turned on 'optimize for ad hoc workloads'. --both of which were settings used on the old server as well.
Buffer cache is sitting at 8.97GB with 100% cache hit rate. Proc cache at 88.1 GB(!) with 19% hit rate!
Any suggestions / recommendations? While it will keep my memory free (and my cache hit rate low), I'd rather not run FREEPROCCACHE every night.
Thanks in advance.
November 19, 2012 at 2:03 pm
You need to figure out what is chewing up your plan cache. Looking for single use plans, etc. What did the plan cache look like on the old server?
November 20, 2012 at 6:52 am
It is an inherited system, and I've traditionally been an operational DBA more than a programmer, having come from an infrastructure background.
Unfortunately, I don't have any historical data. That system performed so poorly that I was regularly seeing disk latency in the 5+ second range. I know that we have now moved the bottleneck to the code and away from the hardware, so now it is just a matter of getting all that cleaned up.
Lots of UDFs that I believe are hurting me now.
December 27, 2012 at 1:43 pm
Update--
It would appear that SP1 and CU1 have fixes for this-- the issue is related to an inability for SQL to correctly deal with NUMA.
December 30, 2012 at 9:34 am
I'd also look into any hard memory limits you could be facing due to running Std edition - both Windows and SQL.
It's possible to max out at 64GB, and I see your new hardware has 128 per node.
Cheers,
JohnA
MCM: SQL2008
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply