You’re responsible for managing and troubleshooting SQL Server.
Good news: you’re not alone. The SQL Server community has banded together and built a bunch of cool stuff to help you do your job easier. In a one-hour session today for the PASS DBA Fundamentals online user group (free to join, of course), I showed some off.
Tools I demoed live during the webcast
How to run a load tests: SQLQueryStress – an open source app originally written by Adam Machanic, and now maintained by Erik Ejlskov Jensen on Github. In the webcast, I’ll be using a set of free random workload scripts which run against the…
How to run demos: Stack Overflow database – the popular Q&A site StackOverflow.com gives away your questions, answers, comments, etc in an open format, and I import it into a SQL Server database you can use for learning T-SQL and indexing. I publish small (10GB), medium (50GB), and large (312GB) versions.
How to see what queries are running: sp_WhoIsActive – during the webcast, I’ll want to see what queries are causing me problems right now, and I won’t be firing up Activity Monitor. That thing is a hot mess. Instead, I’ll run @AdamMachanic‘s amazing tool, sp_WhoIsActive, and I’ll show you my favorite parameters.
How to see what queries have run recently: First Responder Kit – after the load test passes, let’s say someone asks me, “Hey, what queries were causing a problem earlier this morning?” I’ll show you how the open source sp_BlitzCache gives you the answers.
How to analyze their query plans: SentryOne Plan Explorer – when you’re struggling to understand why a query is slow, does it ever feel like SQL Server is fighting you back? Almost like it’s purposely trying to hide known anti-patterns and problems in the query plans? Plan Explorer surfaces them for you.
How to protect your databases: Ola Hallengren’s maintenance scripts – the built-in maintenance plans are a good first step at getting backups, corruption, and index maintenance. Over time, though, you’re going to graduate to needing something more powerful, and then it’s time to say hola to Ola.
And another dozen cool freebies
How to learn with free books – Redgate Books – you can download the PDF versions of their books absolutely free, including material on execution plans, source control, troubleshooting, and more. The site is just a little confusing – there are more books than you see at first. Click on the tabs under “Our books by topic” and you can see more books on SQL DBA topics, .NET, SysAdmin, etc.
How to manage more SQL Servers in less time: DBAtools.io – a collection of PowerShell cmdlets and a vibrant community of contributors. If you’re managing 50 or more SQL Servers by yourself, you need to drop what you’re doing and leverage these tools.
How to check for corruption at scale: Minion CheckDB – as your database volume grows, it can be hard to run CHECKDB frequently enough. This free tool helps you break the work into chunks and offload corruption checking easier. If your databases are 1TB or larger, you should check this out.
Monitor your servers for free: Quest Spotlight Basic – if your boss won’t spring for monitoring software, this is a free way to get a lot of useful analysis.
Watch tons of past conference videos: PASS.org, SQLbits.com, GroupBy.org – these conferences record all their sessions and distribute older ones for free. (You’ll need to create an account to watch the PASS ones, but it’s free, and worth it.)
Stay up to date with newsletters: SQLServerCentral and MSSQLTips – these come out daily, so it’s a lot of email, but just don’t feel guilty about deleting ’em when you don’t have the time to keep up. When you’ve got a few minutes, though, skim the headlines on these and you’ll almost always find something of interest inside. If you want to drink from the firehose, you can follow the same blogs I read (also available as an OPML file.)
Learn SQL Server’s DMVs with queries: Glenn Berry’s scripts – these queries help you understand what’s happening inside your SQL Server, but more importantly, they show you many system tables & metrics. The amount of diagnostic goodies we have inside the engine is just astounding.
Format your T-SQL online: format-sql.com – a shortcut to Redgate’s in-browser code formatter. Not as powerful as their paid SQL Prompt, but when you’re in a pinch and you need a quick format, this is perfect.