SQL Server Defaults SUCK Presentation by Kevin G

SQL Server Defaults SUCK!! Presentation by Kevin G. Boles The. SQLGuru@gmail. com

Who Am I? ? Kevin G. Boles SQL Server Consultant Extraordinaire “The Wolfe” for the Relational Engine Indicium Resources, Inc. AIM/Twitter: The. SQLGuru GTalk/GMail: The. SQLGuru@gmail. com MVP 2007 -2012, MCT, MCITP, yada-yada

What Do I Do? � World-class Better than average relational engine expert � ~45, 000 man-hours invested in SQL Server � Absolutely LOVE doing targeted performance analysis and tuning work, and designing and building high-scale, high-performance database applications � VERY good at making others better at interacting with SQL Server � Couldn’t SSAS my way out of a paper bag ◦ … with both ends open!

AGENDA � Awesome Platform!! � Brainstorming � The �Q Hit List &A

AN AWESOME PLATFORM! � You can default EVERYTHING on your SQL Server installation (and probably application installation), and the engine will just keep on working hard to respond as efficiently as possible. � But at some point if you want scale, high concurrency, SLA, etc, you simply must start doing some things right and stop doing things wrong to get where you need to be. � Fortunately there are LOTS of opportunites on both sides of that coin!

AGENDA � Awesome Platform!! � Brainstorming � The �Q Hit List &A

Brainstorming � For 2 minutes you call out any “suboptimal” defaults you can think of and I will write them as fast as I can. � Lets see how many of mine you get and how many you get that I left out of the session

AGENDA � Awesome Platform!! � Brainstorming � The �Q Guru’s Hit List &A

Disk Configuration � Sector Alignment ◦ Can give up 50% IOPS on pure 64 k random IO!! ◦ Done by default on newer OS’s for 4 GB+ partitions �What about the OEM/Recovery partition(s)? ? � 64 K NTFS cluster size

Server Power Configuration � Default is Balanced – NOT GOOD! � High Power is what you want � Should consider tweaking what items can be powered down, etc. Lots of important devices can be disabled/throttled in windows! � Don’t forget BIOS/EUFI. Many servers have power controls there too. � Can see double-digit performance gains!

Remote DAC Access � “Secret” ◦ ◦ back-door entrance to SQL Server Dedicated thread and (limited) resources Specific port Admin-only connections allowed Remote access NOT enabled by default �sp_configure 'remote admin connections', 1; ◦ LOTS of caveats, provisos, limitations, gotchas, etc ◦ Read BOL BEFORE you need to use it in anger!! � It’s like insurance. You have it hoping you DON’T need it. But if you DO need it you BEST have it available!!

Database Mail � Not enabled � If you don’t know something failed or errors are being thrown that can’t be good for you! � SQL Agent failsafe operator � Set up alerts for various severity (17+) and error numbers (no fixed defined list, but 823, 824, 825 at a minimum)

Backup Compression � Just use it � Edge case to not (i. e. severe CPU saturation) � Or use third-party tools, which offer additional benefits such as variable compression, encryption, management, GUI, etc.

THERE IS NO MAINTENANCE!!!! � Ola. Hallengren. com ◦ ◦ ◦ Free Fully documented GREAT developer support VERY flexible and powerful Covers all facets �Backup �Statistics �Defrag �Check. DB � NEVER use a maintenance plan (well, certainly not for index/stats MX)!!

Database File Size and Growth Increments � Data: 4 MB initial size for model, 1 MB growth!! � So your 1 TB data warehouse has ONE MILLION 1 MB file fragments dotted all over your spindles! � Devastatingly bad ◦ Rotating media thrashing due to severe OS file fragmentation ◦ Internal object fragmentation ◦ No free space in database

Database File Size and Growth Increments � Log: 1 MB initial size for model, 10% growth! � Devastatingly bad ◦ Rotating media thrashing due to severe OS file fragmentation ◦ Too many Virtual Log Files ◦ Can end up with HUGE tlog growth fragments that must be zeroed (no IFI on tlog) before released for use

Database File Size and Growth Increments � If you have severe fragmentation I strongly urge a defrag evolution � Auto-growth should be EXCEPTIONAL event � Size database/tlog for 12 -18 months out � Monitor free space � Set appropriate growth increments ◦ Kimberly Tripp posts on good tlog construction ◦ Beware zeroing data files if don’t enable IFI

Temp. DB Configuration � 1 file per CPU core is poppy-cock! � Start with maybe 1 file per 4 PHYSICAL cores, limited to 8 files, then monitor for tempdb object allocation waits AND file IO stalls ◦ Too many files on too few spindles can CAUSE POOR IO PERFORMANCE due to forced disk thrashing!! � Size equally and with identical, INTELLIGENTLY-SET growth factors ◦ Or just fill up drive(s) allocated for tempdb?

msdb Indexing � BAD structures for storing backup and restore activities and history � No pruning by default (you REALLY need to set this up)!! � Some third-party systems create a LOT of rows and can try to auto-clean them ◦ Massive table scans that can significantly affect server performance � http: //weblogs. sqlteam. com/geoffh/archive/2008/01/21/MSDBPerformance-Tuning. aspx

Cost Threshold for Parallelism � Default of 5 is universally too low on modern hardware and SQL Server � No hard-and-fast rule other than that � Lacking any other information, I typically start with 15 for OLTP and 40 for OLAP systems � There are ways you can “encourage” parallelism ◦ Adam Machanic ◦ http: //sqlblog. com/blogs/paul_white/archive/2011/12/ 23/forcing-a-parallel-query-execution-plan. aspx � Cogitate on CTFP set to zero …

Max Degree of Parallelism � Default of Zero is “almost” universally bad ◦ Means use all you want ◦ Can easily lead to poor performance as one or a few queries overwhelm the box � Firm rule is no greater than the number of PHYSICAL cores on each NUMA node ◦ Minimize remote memory access, which is quite slow compared to local NUMA memory access � Look for opportunities to directly specify MAXDOP for individual queries, especially on mixed-mode servers

Max Server Memory � Default is integer limit (i. e. unlimited) � SQL Server can get “too much”, causing poor performance on box due to paging � Various formulas floating around internet

Lock Pages in Memory � Semi-religious discussion � Be sure to read up on effects of using this via BOL and some blog posts (Jonathan Kehayias, Thomas Kejser, etc. ) � Can cause severe issues with server if done incorrectly � Enable via Local Group Policy Editor. See BOL

Optimize for Ad Hoc Workloads � Seeks to avoid single-use plan cache bloat, which is becoming increasingly prevalent due to rampant ORM use � On first compilation, hash is generated and that is stored in plan cache instead of entire plan (MUCH less space) � Each compilation checks for hash existing and if it does, places full plan in cache � Potential exists for issues due to lookups on larger number of plans

Optimize for Ad Hoc Workloads � Consider using job to flush plan cache if you have excessive cache bloat ◦ I have done this as frequently as every 15 minutes. You are trading CPU ticks for compilations to keep more data in the buffer, saving IO. This is often a good trade!! Note: can run into compilation locking issues though. � Use this article as a launch-point. Follow the various links. ◦ http: //sqlmag. com/blog/effect-optimize-adhocworkloads-right

Instant File Initialization � Data file creation by default will have every bit of every byte of the file zeroed out � MASSIVE write hit on disk(s)!! � IFI tells windows to “just create the file and give it back” ◦ NOTE: potential security risk because can read old data in file ◦ But if someone is reading your SQL Server data files with a hex editor, you are already pwned � Affects database restores too!

Instant File Initialization � Almost certainly want to enable it � Set via SE_MANAGE_VOLUME_NAME ◦ http: //technet. microsoft. com/en-us/library/ms 175935(v=sql. 105). aspx � Already enabled if you use Local System, but you don’t do that … RIGHT? !? � NOTE: Tlog files are ALWAYS zeroed, so beware large growth fragments there!

Trace Flags � DEFINITE ◦ 3226 – No backup success messages in error log � ALMOST DEFINITE ◦ 1118 – No mixed extents ◦ 835 – Lock Pages in Memory on Standard Ed. ◦ 2371 – Auto-update statistics mod counter scaling � http: //blogs. msdn. com/b/saponsqlserver/archive/2011/09/07/change s-to-automatic-update-statistics-in-sql-server-traceflag-2371. aspx � Or disable auto-stats update on large tables, in DW especially? � CONSIDER ◦ 4199 – LOTS of optimization improvements!

Miscellaneous � Anti-Virus Exclusions ◦ http: //support. microsoft. com/kb/309422 � Patch Everything!! ◦ DRIVERS AND FIRMWARE!! �Most common cause of database corruptions are one or other or both of these ◦ Windows ◦ SQL Server � sp_Who. Is. Active � Performance Dashboard

AGENDA � Awesome Platform!! � Brainstorming � The �Q Hit List &A

QUESTIONS? ? � Kevin G. Boles ◦ @The. SQLGuru ◦ The. SQLGuru@gmail. com � Don’t forget about the #sqlhelp hash tag � Don’t forget about free downloads on all past SQLSaturday. com Schedule Pages

References http: //kejser. org/default-configuration-of-sql-server-and-query-hints/ http: //www. brentozar. com/archive/2008/03/sql-server-2005 -setup-checklist -part-1 -before-the-install/ http: //www. brentozar. com/archive/2008/03/sql-server-2005 -setup-checklist -part-2 -after-the-install/