Basic Settings for SQL Server, part 2

2015-05-28

Part 2: settings inside SQL Server

When the server is all good and installed, it's time to go through and set up a bunch of server settings. First of all - dedicated admin connections. This is used as a "get out of jail for free"-card for a DBA if all the schedulers are cranky and won't accept connections for one reason or another. Using the DAC, it might be possible to sneak in through the "back door" and solve whatever problem that is plaguing the system without having to go through the hassle of a reboot/service restart. Turn. It. On. Period. https://msdn.microsoft.com/en-us/library/ms190468.aspx https://msdn.microsoft.com/en-us/library/ms189595.aspx

Optimize for adhoc was introduced way, way back to minimize the impact of adhoc plans in the plan cache. Simply put, it works bu not storing a complete plan in the cache until it is reused at least once. Instead a "stub" is stored, and less memory is consumed by plans that are never reused. Having read the same recommendation from several sources, I always turn this on - BUT - keep an eye out for surprises in the plan cache. Kimberly L. Tripp lays it all out here: http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/ and her post links to a gazillion other posts.

Memory, then. The default setting for MIN_SERVER_MEMORY is 0 and MAX_SERVER_MEMORY is 2147483647 MB. Yes. The technical term for that amount is "a lot". It's a really bad idea to leave this setting as is, as SQL Server is a bit like my late lawnmower - it will eat EVERYTHING in sight. This comes with some consequences for wildlife, geography and neighbours, or in this case - other consumers of memory. There are several ways to come up with a number for MAX_SERVER_MEMORY, but a simple rule of thumb is 90% of total memory or 4GB, whichever is greater. Suddenly it wasn't so great to give the server 6GB of RAM, riiight? NOTE: the previous number is ONLY applicable if you have ONLY one instance on the server and NO other consumers (Fulltext daemon, reporting/analysis/integration services, etc.) http://www.brentozar.com/archive/2012/11/how-to-set-sql-server-max-memory-for-vmware/ Somewhat older but still very much sound: https://sqlserverperformance.wordpress.com/2009/10/29/suggested-max-memory-settings-for-sql-server-20052008/

TEMPDB has been a hot topic for contention for many years. I've read everything between 2-8 files depending on number of cores, and then tune upwards if you run into problems. I make it simple: on a SAN with lots of spindles or SSDs: 8 files and be done with. Too many files can actually hurt performance if you have too few spindles (but then again - everything hurts if with too few spindles). Paul Randal lays out a lot of information here: http://www.sqlskills.com/blogs/paul/correctly-adding-data-files-tempdb/

Lock pages in memory - perhaps the closest thing to a religious argument the community has ever had. I fall into the "on the hedge" category of DBAs. I personally don't use it most of the time, but it's nice to know that it is there should I need it. Jonathan Kehayias has a great blog post with both sides of the argument and lots of interesting information: https://www.simple-talk.com/sql/database-administration/great-sql-server-debates-lock-pages-in-memory/

Backup compression is another no-brainer. Or is it? The prevailing opinion is to turn it on as the servers out there usually has a lot more CPU than I/O resources. That is undoubtedly the case, but keep in mind the environment outside the database. If you're running a compressing backup solution like TSM, NetBackup or similar, the compression and subsequent deduplication of that system will suffer greatly. In essence: make sure you only compress your backups at one place, and if at all possible do it at the SQL Server. Paul Randal again has a great post on the subject: http://www.sqlskills.com/blogs/paul/sql-server-2008-backup-compression-cpu-cost/

Trace flags are usually something to be wary of, but there are a few that are a downright good idea to use. 3226 for instance, suppresses the backup success messages in the SQL Server log. There's enough information in there already, so a little less stuff to wade through is only a good thing. http://msdn.microsoft.com/en-us/library/ms188396.aspx 1118 tells the server to avoid mixed extents and give each new object its own 64KB of allocated data. 1117 tells the server to grow each file in a filegroup equally. This can come in handy to avoid hotspots as a result of uneven file growth. Both these flags are documented in in http://support.microsoft.com/kb/2154845/en-us 2371 modifies the behavior of statistics auto update - very useful on large tables. http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx 2562 and 2549 pertains to improvements for DBCC CHECKDB. https://support.microsoft.com/en-us/kb/2634571