Here’s some of my favorite community scripts and how I like to run them. If you haven’t tried the optional parameters on your favorite scripts, there’s definitely some cool features to try.
sp_WhoIsActive
Written by Adam Machanic and hosted at whoisactive.com
This script is great for all kinds of performance troubleshooting. Here’s the parameter I add on: @get_plans = 1
This will slow the procedure down a bit, but it can be super useful in finding the execution plans of currently running queries. Here’s a quick before and after.
sp_WhoIsActive;
sp_WhoIsActive @get_plans = 1;
Awesome! Now we have the query plan. It won’t contain actual row count or actual executions, although I’d stay tuned for SQL Server 2019 and see what happens for live query executions.
sp_BlitzCache
It’s no secret that I’m a big fan of the First Responder Kit from Brent Ozar Unlimited, but this procedure in particular is amazing.
When you run sp_BlitzCache with no parameters, you get the top 10 plans from cache along with some information on your plan cache’s health. Add one of my two favorite parameters for more specific searching.
sp_BlitzCache @MinimumExecutionCount = 100;Minimum execution count will only analyze queries with your number or greater executions. That’s great for troubleshooting systems with high batch requests per second, because you can exclude expensive queries that only run once or twice a day. Pick the right number based on your system.
sp_BlitzCache @OnlyQueryHashes = ''I’m a big fan of using the query hash to identify and track problematic queries. Check out my post for why I like it so much. If you have one or more query hashes that you want to track, you can list them here in a comma separated list.
Let me know if you have other favorite procedures or parameters! Stay tuned.