SQL performance issues after increasing memory from 32 to 64 gig

Question

Post reply

SQL performance issues after increasing memory from 32 to 64 gig

Viewing 6 posts - 16 through 20 (of 20 total)

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

jwa082276 Say Hey Kid Points: 676 More actions · Answer 1

Can you explain how to add the recompile hint for this procedure?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

jwa082276 (3/15/2010)
Can you explain how to add the recompile hint for this procedure?

Isn't this all dynamic sql anyway? You do not need recompile on that stuff. Each unique string gets compiled anyway.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Paul White SSC Guru Points: 150467 More actions · Answer 3

GTR (3/15/2010)
Believe Max and Min is not necessary if you have SQL 64 bit, unless you want spend more time on this.

No this is quite wrong I'm afraid. You are confusing AWE with the configuration settings min and max server memory.

AWE is used by 64 bit, internally - it's just that the AWE enabled setting has no meaning for 64-bit SQL Server. See http://blogs.msdn.com/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx for the details.

Min and max server memory specify the limits on Buffer Pool size, regardless of architecture.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 4

jwa082276 (3/15/2010)
Can you explain how to add the recompile hint for this procedure?

Erland covers the technique in depth in his article. The posted procedure does use dynamic SQL, it is true, but that is not the same procedure that produced the posted execution plans. The latter is the one the might benefit - who knows without seeing it.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Richard M. SSCertifiable Points: 7401 More actions · Answer 5

jwa082276, did you ever find out why the query was running slower with more memory?

We are having some weird issues that are intermittent but kind of similar after we upgraded from 64 to 128GB of Memory, but we had also a service pack for the application installed that updated some DB schema's and we are not finding anything conclusive.....

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

jwa082276 Say Hey Kid Points: 676 More actions · Answer 6

We ended up rewriting the queries involved....one of the stored procs had group by in the orderby clause..this was the culprit....after the rewrite...we had no issues.

Hope this helps.