Guide to Faking It at Database Administration
Guide to Faking It at
Your app stores data in SQL Server, and you don’t have a full time DBA.
Who cares? Here’s what you need to know – all based on our free Ozar’s Hierarchy of Database Needs email training course:
Backups and Restores
High Availability Planning Worksheet – helps you get the business on the same page by asking them how much data you’re allowed to lose, and how long you’re allowed to be down.
Backup Best Practices – among other things, explains why you need to back up to a file share, not local disk.
Automatically Restore Backups – don’t use the GUI! When disaster strikes, use this script to generate restore scripts from all the files in a directory.
Set Up Agent Alerts – how to set up failsafe operators and email alerts to get notified when SQL Server encounters corruption.
Opserver – Stack Overflow’s open source monitoring system.
Performance Tuning and Triage
When you need to find out why the SQL Server is slow, follow these three steps:
1. Check the server’s wait stats. SQL Server is constantly tracking what each query is waiting for, and that data is accessible via dynamic management view (DMV) queries. Here’s our two favorites:
- Use sp_BlitzFirst® to get real-time wait stats, or
- Use this DMV query to see cumulative wait stat data over time
2. Check the execution plan cache to find the culprit queries. Sure, we might have hardware problem with really slow storage, but what query is reading all that data? Run this query to get the top resource-intensive statements from your plan cache.
Before you run it, note the SET @SortOrder statements. They let you sort the plan cache. Here’s how to choose your sort order, and make sure to set all three occurrences of the SET @SortOrder statement:
- Choose SET @SortOrder = ‘CPU’ if your highest wait types are CXPACKET or SOS_SCHEDULER_YIELD
- Choose SET @SortOrder = ‘Reads’ if your highest wait type is PAGEIOLATCH_*
- Choose SET @SortOrder = ‘Duration’ if your highest wait type is LCK_*
Those sort order choices aren’t perfect – the DBAs in the crowd are going to instantly start debating different ways to interpret this data – but we’re just trying to get you started quickly with some analysis.
3. Check the top query plans to improve them. If they desperately need an index and there’s no other indexes on the tables involved, consider adding it, or at least consider using sp_BlitzIndex® to perform a health evaluation in that database. If they’ve got implicit conversions, consider right-sizing the parameter data types. We’ve got a ton of resources to help with query tuning in our training classes.
Need Help? We’re Here.
We specialize in quick relief for tough SQL Server performance and reliability pains.
In just a few days, our SQL Critical Care® diagnoses the root cause of your pain points and shows you the quickest way to get relief. Learn more.