SQL Optimization
Hey all,
How many of you all optimize the server memory from the 2 PB max to something like say you have 24 GB's you give it a maximum of say 21500 leaving 3.5 GB's for the OS.
I'm finding more and more companies, as I've observed, either use the default 2 PB or as-much-as-possible or they have no idea they can change the memory allocation of SQL.
I'm curious also if there is any other 'performance' setting that can be applied in the 'server properties' on a SQL Management Suite that could be better than the default settings. I only know of the memory min/max setting.
Thanks in advance all.
How many of you all optimize the server memory from the 2 PB max to something like say you have 24 GB's you give it a maximum of say 21500 leaving 3.5 GB's for the OS.
I'm finding more and more companies, as I've observed, either use the default 2 PB or as-much-as-possible or they have no idea they can change the memory allocation of SQL.
I'm curious also if there is any other 'performance' setting that can be applied in the 'server properties' on a SQL Management Suite that could be better than the default settings. I only know of the memory min/max setting.
Thanks in advance all.
Comments
-
JBrown
Member Posts: 308
Optimizing MS SQL??, nah, it runs optimized out of the box, and takes care of all the memory, cpu and IOPS on the fly itself. j/k
You need to visit Brent Ozar Unlimited - SQL Server Consulting and Training by Brent Ozar Unlimited® and go over his white papers and howtos. they actually run various online classes regarding SQL optimization.
here is a good place to start, download the ebooks and scripts: First Aid - Free SQL Posters by Brent Ozar Unlimited® -
iBrokeIT
Member Posts: 1,318 ■■■■■■■■■□
Worth a watch from VMworld 2014:
Advanced SQL Server on vSphere Techniques and Best Practices
by Scott Salyer, VMware Inc.
http://vmware.mediasite.com/mediasite/Play/5d0558d2730c4807aa34eeb7f42bd10a1d?catalog=daea26ff-5689-4512-a046-87ead2afc5182019: GPEN | GCFE | GXPN | GICSP | CySA+
2020: GCIP | GCIA
2021: GRID | GDSA | Pentest+
2022: GMON | GDAT
2023: GREM | GSE | GCFA
WGU BS IT-NA | SANS Grad Cert: PT&EH | SANS Grad Cert: ICS Security | SANS Grad Cert: Cyber Defense Ops | SANS Grad Cert: Incident Response -
Deathmage
Banned Posts: 2,496
That hit the spot guys, thanks for the remarks.
Applied a number of those setting to the SQL and the puppy is performing way better now. Most of the common strategies on the VMware side where done during the intial stages of the cluster but some of the memory tweaking for the host and setting the reservation to the server maximum set inside of SQL Configuration Manager were handy to know. Just looking to bleed as much performance as possible out of SQL, namely giving the SQL and Application server's the VMXNET3 drives helps allot since they have an affinity rule to be on the same host so that's helping with the raw data processing on the vSwitch.
However NUMA seems like a interesting setting, but we don't have a need for it, atm, this baby runs fine on a single socket 6 core xeon.
Again thanks for the links and information!!!!