Poll to see how many people set Max Server Memory

Question

Poll to see how many people set Max Server Memory

Peter Samson

SSC Veteran

Points: 216
More actions
August 6, 2010 at 9:43 am

#227516

On a packaged/COTS app forum, I was surprised to get significant, negative feedback when I suggested setting Max Server Memory. So I'm curious how many people here set it...
Peter
http://seattleworks.com including my blog The SQL Janitor

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 1

Peter Samson (8/6/2010)
On a packaged/COTS app forum, I was surprised to get significant, negative feedback when I suggested setting Max Server Memory. So I'm curious how many people here set it...

I'll trade you my opinion for the successful link to that. 😉

--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)

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

There is no "Don't know/ Don't understand/ Don't care" option, this is not a scientific poll 😀

Seriously now, for critical production databases I'll set it accordingly.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Peter Samson SSC Veteran Points: 216 More actions · Answer 3

Jeff, you should be able to link to it here

http://community.sageaccpac.com/forums/t/1193.aspx

Peter

http://seattleworks.com including my blog The SQL Janitor

David Webb-CDS SSCoach Points: 17398 More actions · Answer 4

We don't normally set it as a standard practice, but we watch the paging and set it if needed.

And then again, I might be wrong ...
David Webb

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 5

Peter Samson (8/6/2010)
Jeff, you should be able to link to it here
http://community.sageaccpac.com/forums/t/1193.aspx

I'll have to agree with the others. I've not ever had to change this setting for performance. Following your good link further to its logical end, I suspect that rebooting the server was better for the customer than changing that particular setting.

--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)

HowardW One Orange Chip Points: 29109 More actions · Answer 6

If it's a dedicated box, only running the core SQL services, then I'd agree it probably handles the amount of memory to allocate to SQL Server fairly well.

However, if you use SSIS, SSRS or other components that require a significant amount of memory intermittently, it's worth limiting the amount SQL Server can have allocated.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

if using BP locked pages then you should configure both min and max memory for each instance

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

I don't typically set it unless there is good reason. We typically run dedicated servers for SQL.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events