Transcript ShawTipsx - SQL Saturday

Tips for SQL Server
Performance and
Resiliency
Chris Shaw
[email protected]
@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 - [email protected]
• 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.
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
• 3rd party tools (LiteSpeed, 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)
• Builtin\Administrators
• 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/HyperV
• 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 - TempDB
• 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
• HammerDB
• File Locations
• Isn't it all the same with SAN
• AutoGrow
• Perform Volume Maintenance
Q&A - THANK YOU!
QUESTIONS?
Chris Shaw
[email protected]
@SQLShaw
The user groups meets every 3rd
Wednesday from 6pm-8pm @ New
Horizons Training Center on 2727 NW
Loop 410 #103, San Antonio, TX 78230
More info? [email protected]
Gold Sponsor
Silver Sponsor
Bronze Sponsor
Swag Sponsor
15 |
SQL Saturday Host
Blog Sponsor
The user groups meets every 3rd
Wednesday from 6pm-8pm @ New
Horizons Training Center on 2727 NW
Loop 410 #103, San Antonio, TX 78230
More info? [email protected]
San Antonio SQL User Group
Personal Sponsor
Thomas LeBlanc aka
The Smiling DBA
Naomi Williams aka
Naomi The SQL
DBA