Tips for SQL Server Performance and Resiliency Chris

Tips for SQL Server Performance and Resiliency Chris Shaw CShaw@xtivia. com @SQLShaw www. xtivia. com

Basis of Presentation This presentation is built from my observations while working with SQL Server. Included are the items that I see on a repeating basis that impact not only how SQL Server performs but the resiliency of SQL Server. • • • 19 years as a DBA 3 years as a Virtual DBA Currently monitoring close to 200 SQL Server Monitoring database servers since 1999 Over 300 Health Checks performed recently

ASSUMPTIONS • These tips are based on common occurrences I have witnessed, your mileage may very. • If you have a question feel free to stop me to ask. However, I may have to move past it depending on time. If I do, we can touch base after the session. • Email Me - It’s Ok - cshaw@xtivia. com • I ask you, help me make this presentation better, if you have suggestions please review the session. • If you liked the session please review the session. • Lets get started…

Tip 1 - BACKUPS • • • Many backup strategies are designed backwards • Start with the question, how much data can we lose • Then ask what do we need backed up (CLR, Certificates, anything outside the database) Recovery Modes • If full then backup the log or the transaction log will revolt and eat your storage Then determine how you are going to back up • 3 rd party tools (Lite. Speed, SQLServer Backup Pro, Veem) Secure your backup • Encrypt it • Store it off site Testing your backups (We shouldn’t need to talk about this…)

Tip 2 - SECURITY • • Everyone does not need to be an SA • Don’t determine permissions based on trust. A DBA’s job has nothing to do with trust Everyone does not need DBO Access • Permissions of least privilege (just enough to do what is required) BuiltinAdministrators Did I mention you should secure your backups Enforce Windows Policy Management Week Passwords (Check Them Passwords) • PWDCOMPARE() being removed after SQL Server 2014 Service Accounts • Stay away from Domain Admins Linked Servers • Connecting as SA

Tip 3 - MAINTENANCE • Fragmented Indexes • We could do a whole day on indexes alone, in short maintain them. • Ola Hallengren (https: //ola. hallengren. com/) • SQL Fool (http: //sqlfool. com/) • XTIVIA has one as well where we track the history of index fragmentation • Stale Statistics • Consistency Checks • Monitoring • Database Mail, Alerts, Operators (alerts script)

Tip 4 – HAVE A BASELINE • Baselines will provide you with a starting point to determine the performance of your server. • Determine what is normal for your server. • What should you baseline? When • During Business Hours • After Hours • In Season • At Rest What • Performance Monitor Counters (It’s your Friend) • Wait Stats • Configurations (SQL Server, OS, Hardware) • Job Executions

Tip 5 – MANAGE YOUR MEMORY • Max Memory Setting • Starving the OS • Min Memory Setting • VMWare/Hyper. V • Lock Pages in Memory • SQL Server Error Log • Overuse/Underuse • Cycle those Error Log • Retention

Tip 6 – CHANGE MANAGEMENT • No Change History • History not searchable • No Change Management • Review of Changes • Documented Changes • Communication • Changes not scripted/Made in the UI. • No Roll Back Scripts

Tip 7 – DISASTER RECOVERY PLANS • Don’t ignore the basics • RPO – Recovery Point Objectives • RTO – Recovery Time Objectives • Not paying attention to the details, 2 power plugs one circuit • Have a plan at all • Testing your DR • Over Engineering

Tip 8 - Temp. DB • Fast Storage • Located on different spindles then other database files • Recovery Model – Simple • Multiple Files • All the same size • One data file per CPU (no more than 8) • Initial Database Size • Manage File Growth Size • Less frequent larger growths

Tip 9 – SHRINKING DATABASES • Auto Shrink • Jobs created to shrink • Shrinking the database in the maintenance plan • Shrink is not always a bad thing • Shrinking without understanding the downstream impacts, is a bad thing • Reindex after shrinking

Tip 10 - STORAGE • Knowing how to tell if you have a performance issue • Monitoring Storage Space • Testing storage • SQLIO • Hammer. DB • File Locations • Isn't it all the same with SAN • Auto. Grow • Perform Volume Maintenance

Q&A - THANK YOU! QUESTIONS? Chris Shaw CShaw@xtivia. com @SQLShaw www. xtivia. com

Thank you to all of our Sponsors! § Diamond Sponsors § Platinum Sponsors § Gold Sponsors § Marquee Sponsors 15 | 9/19/2015 SQLSaturday #441 – Denver 2015

Thank you to all of our Sponsors! § Silver Sponsors § SWAG Sponsors 16 | 9/19/2514 SQLSaturday #441 – Denver 2015