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.

Find more posts tagged with

Comments

JBrown

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

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-87ead2afc518

Deathmage

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