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.)
Manage your columnstore indexes: Columnstore Indexes Script Library – written by the industry expert on columnstore indexes, @NikoNeugebauer, who’s written over a hundred CS blog posts too.
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.
Where to find more
I’m only scratching the surface! Check out sqlserver-kit.org, an incredible directory maintained by Konstantin Taranov in Github. He’s got everything from trace flags to downloads to lists of blogs.
14 Comments. Leave new
We use SSMSBoost for a lot of neat functions (Formatting, shortcuts/templates, warnings for updates and deletes with no where clause, etc,). Great tool and the community version is no different from the paid version last time I downloaded it. http://www.ssmsboost.com
SSMSBoost was my favorite must first installed addin for SSMS. But after truncated many good functions from free version (https://www.ssmsboost.com/VersionCompare) and intrusive reminders to renew your free license our team stopped used it.
Our current setup for SSMS:
1. Last General Availability release SSMS 17.9.1 – https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017#ssms-1791-is-the-current-general-availability-ga-version-of-ssms
2. ApexSQL Search addin – free quick search and with killer feature safe rename. https://www.apexsql.com/sql-tools-search.aspx
3. ApexSQL Refactor addin – for formatting code in SSMS. https://www.apexsql.com/sql-tools-refactor.aspx
4. SentryOne Plan Explorer addin – for quick analyze plans in this awesome tool https://www.sentryone.com/plan-explorer
Complete list of all SSMS addins you can find here – https://github.com/ktaranov/sqlserver-kit/blob/master/SSMS/SSMS_Addins.md
If you want to become a SSMS ninja highly recommended free ebook
http://ssmsbook.sqldownunder.com or my short version of SSMS tips https://github.com/ktaranov/sqlserver-kit/blob/master/SSMS/SSMS_Tips.md
Speaking of StackExchange, let’s not forget the life-changing magic that is Opserver: https://github.com/opserver/Opserver
Also recommended another awesome projects – https://github.com/ktaranov/sqlserver-kit#open-source
How about http://databasehealth.com ?
Awesome article and list of links, Brent. Thanks for your contribution to SQL Server community, you like Tom Kyte in Oracle world, but more more funny and independent from Microsoft.
Congrats with your epic goal complete – https://ozar.me/2018/12/epic-life-quest-task-sold-500k-in-our-black-friday-sale/ ! And I think 10^6 dollars for Black Friday in 2020 year is possible for you and your awesome team.
Konstantin- thanks sir! And I can’t believe I wasn’t following you on Twitter before yesterday. You’re doing great work.
I use ApexSQL tools (freebie ones) – I like ’em.
OK, I managed to open up a bottle of Margarita at 3 o’clock today, but no one calls me as a consultant? What am I doing wrong Brent? Have a nice Friday, Jens.
P.S.: I know these Tools for a long time, but your Sessions are so entertaining, I do not want to miss any of yours.
My script in PowerShell to using format-sql.com – https://gist.github.com/MateuszNad/45ad0f1fc70ea6a70785018c2658ac70 Maybe someone will want to use it.
[…] Free Downloads for Powerful SQL Server Management Brent Ozar gathered the most popular scripts & tools in one place. […]
[…] Brent has a very nice list of Free Downloads for Powerful SQL Server Management. […]
Apex just took away all their free tools. What a crock.
Apex give nothing for free any more, moving to any FOSS I will find