Last week, I was reading a brand new article from a Microsoft employee about how you should directly query sys.dm_exec_requests in order to find out what’s running on your system.
I lost my mind.
There was a lot of yelling at the monitor.
In the year 2022, nobody should be reinventing the wheel. There are plenty of free wheels available for you to choose from. You’re literally wasting your time if you start from scratch with a boulder and chisel, and then try to turn it into a wheel.
The Microsoft data platform community is amazing, and has been that way for years. There are so many free resources to help you do your job faster, easier, and more accurately.
And sure, I’ve been around for quite a while, and I take for granted that everybody in the database business knows about all this cool free stuff. I’m not talking about the First Responder Kit, either – I’m talking about a stunning list of resources so large that it’s intimidating just to get started.
That’s where you come in.
What do you rely on every week?
In September, I want you to improve community knowledge about one free tool that you rely on every week in order to get your job done.
Your first reaction is gonna be that everybody already knows it, but trust me, they do not. Just by reading this blog post, you’re already ahead of many folks out there who don’t have the time to keep up with the industry. Imagine that you’re talking to a brand new hire at your organization who needs to get up to speed on how you’re able to do your job so effectively.
Pick one of these things to share:
- Introduce the tool to readers for the first time
- Tell a story about how it saved your bacon
- Share a non-default configuration option that you use, and why
- Write a review – explain what you like about a tool and what you wish was different
- Compare several free tools that do the same thing – explain the pros & cons of each one
- Put together a list of learning resources for a free tool – maybe you like the tool, but it isn’t easy to use, and you want to put together a set of links to show a new user where to begin
And there are any number of ways you can share it:
- Write a blog post (if you don’t have a blog, write on LinkedIn, SQLServerCentral, MSSQLTips)
- Record a short video
- Improve the tool’s documentation
You can schedule it anytime you want during September. When it goes live, leave a comment here with a link to it. I’ll post a roundup post, and I’ll set up social media re-sharing so that I can keep driving new folks to your work over time. I’ll be working on it too – most of my September blog posts will be focused on free community tools.
Let’s make sure that nobody in our industry has to reinvent the wheel again!
41 Comments. Leave new
While it doesn’t follow your rules, could I highlight my sp_helpexpandview & the older related blog posts, for those who struggle with dissecting nested views?
https://sqlbek.wordpress.com/tag/sp_helpexpandview/?order=ASC
Hell yeah, that’s totally within the rules! Just publish a new blog post with a recap of the resources for it.
Hi Brent – here’s my contribution. Thanks!
https://sqlbek.wordpress.com/2022/09/16/free-community-tools-sp_helpexpandview/
I like this post, this sounds fun. I’ve always wanted to participate in SQL Tuesdays, but their timelines are too short, and I don’t usually even hear about the new topic right away – whereas many of us follow Brent’s Blog like a hawk! 😀
A month is also more reasonable for folks who don’t already blog on a frequent basis (and have a harder time fitting it into existing schedules).
And this is a great topic!
Hahaha, thanks!
does this count? Its totally something EVERYONE has their own version of it but I use it multiple times per week. Ive even had other devs in past jobs reach out and say thank you for it. Its especially useful in larger environments or environments youre not used too yet. Its simple, and probably needs a complete rewrite, but its useful. If you agree I can type something up on linkedin for you 🙂
https://github.com/phesago/stpFindObjectBYString/blob/main/stpFindObjectByString
OK, like the post says, make sure to blog, present, or otherwise share about something during September. Cheers!
I’ve found this tool indispensable for migrations, code and performance testing (looking at you 2008R2 to 2017… what fun but I digress). It uses extended events backed with a SQLite database to do a more agnostic snapshot/replay. You can even build a cluster and perform a replay in real-time with minimal impact. MSFT has their own tool but it was much more cumbersome than I’d hoped.
https://github.com/spaghettidba/WorkloadTools
OK, like the post says, make sure to blog, present, or otherwise share about something during September. Cheers!
Not mine, but a great place to get a lot of pointers to tools and apps around SQL Server: https://github.com/NajiElKotob/Awesome-SQLServer
From my collection of sayings: Don’t reinvent the Wheel. Even not if you are *NOT* a professionall wheel engineer.
Regarding Tools:
* The first thing I install on a new PC is the Total Commander. I got a licence from my mother as birthday gift / wish maybe 25 years ago (~30 USD) and I still use / love it. It is MUCH more flexible / faster than the Windows Explorer when you have to handle files. And we all have to from time to time.
* EveryThing – a free tool which treats the NTFS table of your PC as database (instead slowly grabbeling through every single folder and subfolder) and finds files in real time while you are typing a search string (usually parts of the file name). And of course you could add network drives too (saved the day of my coworkers after someone moved an important folder inadvertently to a very wrong place deep in another folder structure, where it would be never be found again, because he / she was using the Explorer :-)).
Hint: set e.g. Win + question mark as easy to remember hotkey for a new search window in the options.
And of course Total Commander supports EveryThing too, so you can do the very fast searches there too.
Thanks for recommending Total Commander, I’m checking it out.
I just fainted when I saw that list you shared. Just came to with drool all over my keyboard, because I probably lay there for half an hour or so. Wow! What a list! Thanks for sharing!
Hi Brent,
I’ve been working on this in my spare time for a while, and finally decided to make a GitHub repository for it yesterday.
https://github.com/VladDBA/PSBlitz
Yes, the name is a combo of sp_Blitz and PowerShell 🙂
If you’re ok with it, I’ll demo it in a blog post on my less (than active blog) in September and add the blog post’s link here.
Thank you!
My contribution is here:
https://straightforwardsql.com/posts/my-toolbox-greenshot/
Hopefully, I’ll write some more before September ends (if not, wake me up).
Wait I just reread the thing – I was thinking the posts would need to be posted this month but September is next month 🙂
So we blog next month.
..right? RIGHT?!? 😀
Bingo!
Here’s a new article, published today, on LinkedIn.
The title is: SQLFacts, FREE SQL Server Toolkit
https://www.linkedin.com/pulse/sqlfacts-free-sql-server-toolkit-brian-walker
Great idea! I look forward to seeing what others share. My post can be found here: https://callihandata.com/2022/09/03/use-sp_ineachdb-instead-of-sp_msforeachdb/
I wrote my post here about:
– SQL Check by IDERA
– Database Health Monitor by Stedman Solutions
– Is It SQL by Scale SQL Consulting
https://www.jeeja.biz/2022/09/07/september-free-community-tools-awareness-month/
My contribution:
https://wisedataman.com/september-free-community-tools-awareness-month
Hope someone finds it useful! ??
Here’s my video of a script for creating SQL Server (or other) VMs in Azure! I use it to create cheap learning and demo environments.
https://m.youtube.com/watch?v=2feMuu6RhUQ
And here it is on GitHub: https://github.com/sdoubleday/terraform-an-azure-devbox/blob/main/README_PresentationSlideDeck.md
Brent, here’s my contribution–configuring custom query shortcuts:
https://www.michaelscalise.com/2022/09/10/sql-server-free-tools-awareness-month/
Hi Brent,
My contribution is a PowerShell script to quickly dump sp_Blitz, sp_BlitzIndex, sp_BlitzFirst, sp_BlitzCache, sp_BlitzWho, and sp_BlitzLock data into Excel and sqlplan/xdl files
https://vladdba.com/2022/09/15/psblitz-easily-outputs-sql-server-diagnostic-data/
If this needs my real name, it’s Vlad Drumea
https://github.com/misirajudeen/TSQLInventory/blob/main/SQLServerReport
Thought I would do my part towards this great initiative:
https://www.kevinrchant.com/2022/09/21/introducing-the-dbops-powershell-module/
A server health Check script that I use daily for LIVE troubleshooting –
> Script on github -> https://ajaydwivedi.com/query/health
> Blog post on this script -> https://ajaydwivedi.com/performance-tuning/sql-server-live-health-check/?swcfpc=1
> Metrics exposed – cpu trend, memory, ple, grant pending, blocking tree, top queries by cpu/mr, parallel workload
I wrote an open Source SQL Server Baselining Tool – SQLMonitor.
Github repo -> https://ajaydwivedi.com/github/sqlmonitor
Document -> Deployment instruction present on Github
Video Tutorials -> https://ajaydwivedi.com/youtube/sqlmonitor
Working on videos for SQLMonitor setup on various SQL deployments like SqlCluster (Active-Active, Active-Passive, N+ SqlClusters on N- nodes), Ag, multiple instances on same host etc.
Space-Capacity-Automation – https://github.com/imajaydwivedi/Space-Capacity-Automation#space-capacity-automation
Long back wrote this tool for managing DBA tasks like visualizing/adding/restricting files on new/old volumes with appropriate sizing. Very useful for large SQL Server environments using VMs with data/log files in Mount Volumes. Saved millions for my employer by reducing the DBA efforts.
Github repo -> https://github.com/imajaydwivedi/Space-Capacity-Automation
Document -> Deployment instructions present on Github
MetaDataSync – https://github.com/imajaydwivedi/MetaDataSync
Wrote this tool for ensuring that below objects are in sync b/w Primary replica and secondary replicas in Availability Groups. This detects even existing changed object like login password change on primary, job definition change on primary etc.
Objects synced -> Logins, standalone db users, user objects in system dbs, linked servers, jobs, logon triggers, sys config settings.
CAUTION:- Deployed and tested only for 5-6 set of Groups.
Hello Brent!
It was in March when, after sitting few days in a basement, our houses got bombed, me and my family decided to move out on a car, despite lack of information.
After we left Izyum, couple of days later bridges across river were destroyed, and our part of town got occupied.
If we stayed and didn’t go, big chance I wouldn’t be able to write comments anymore. On any website.
Your blog post motivated me to resume work on my “Database Administration Tools” repository on GitHub,
and update some of the stored procedures to +/- production quality (as I hope 🙂
Also created documentation (article) describing how to use “DBA tools”:
GitHub: https://github.com/aleksey-vitsko/Database-Administrator-Tools
Article: https://docs.google.com/document/d/1-icqTGFTR_4rUG9l7Vh6GgNDjbKYfy_f/edit
Note that I did not include all stored procedures from GitHub repository into article.
That is because don’t really want to share some SPs (like index defrag, integrity check) as there are already world-class solutions for this
Others, like ScriptLoginPermissions, ServerSpaceUsage, TempDBInfo, etc. – can be useful, so these were included
Initially thought to put article on MSSQLTips, but did not agree to their intellectual property and some other requirements.
On LinkedIn, people must be logged on to see the post. I was running out of time, so figured I’ll just put it on google doc for now, so anyone can access it
Check the article out, hope to get some feedback from DBAs.
Who knows, maybe one (or more) of these stored procedures will continue to be developed by the community, and/or become widely used in future ?
Regards,
Oh my goodness, what a story! I’m glad you got out okay.
One thing that’ll make a big difference: you’ll want to put a license on your Github project. Here’s how to do it: https://docs.github.com/en/communities/setting-up-your-project-for-healthy-contributions/adding-a-license-to-a-repository
That way, people will be able to use your code safely. Otherwise, you could add a copyright on there, and that gets a little tricky.
Thank you for the advice! Added License and Readme files
Quick overview of stored procedures from the GitHub link in above comment:
ScriptLoginPermissions – scripts server-level and database-level permissions, role memberships for specified login
ViewServerProperties – shows host OS, server machine, and SQL instance-level properties and configuration options, and more
MemoryManagerInfo – shows how SQL Server is using memory
BufferPoolSize – break down of the Buffer Pool
ServerSpaceUsage – shows how much storage space SQL Server instance consumes, and details
DatabaseSpaceUsage – help DBA quickly identify which tables are the largest in a specified database, and table/index/reserved/free space in the database
TempDBInfo – hows detailed information on current state of TempDB database
ViewSessionsConnections – use this to learn details about sessions connected to your system
VirtualFileStats – taken directly from Brent Ozar’s website and turned into stored procedure for convenience. Shows virtual IO stats
I have another one:
https://straightforwardsql.com/posts/my-toolbox-everything/
When I need to know pricing on an Azure VMs, I use Infracost. Here’s a video of me signing up and trying it out: https://m.youtube.com/watch?v=cDapePKJk38&t=5s
Ooo, that’s neato!
Thanks! I was quite pleased when I found that one, myself!
I’m going to shoehorn in one more after the end of September 🙂
This is my free script for automagically making a data entry webapp. I use it when I want to enlist end users in maintaining their own mapping, grouping, or filtering tables somewhere I can join to them easily, and I don’t want to think about the details beyond sketching out a csv file or creat table statement.
Video: https://m.youtube.com/watch?v=tVSCe1MYvZE
Github: https://github.com/sdoubleday/DataEntryApp
Thanks for putting this together, Brent! Terrific idea! Here’s mine – https://www.flxsql.com/2022/09/30/community-tools-month-2022/