You’re a consultant, and you need to get diagnostic data from your clients’ SQL Servers.
You’re tired of asking them, “What version are you running?” and “How big are your databases?” You’re sick of running DMV queries one at a time, copy/pasting the results into files. You’ve played around with the built-in tools like PSSdiag, but they take so much overhead, and they’re missing so much important data.
The Consultant Toolkit is a simple app that helps you make more money, faster.
1. Download the app, and run it at your clients (or give it to your clients to run.) You can email it to them, or put it on your web site. There’s nothing in the app or spreadsheet to point back to us – it looks like you built the app yourself. (The name isn’t even Consultant Toolkit – we purposely don’t list the name here so your clients won’t Google it and come back to us.)
2. Point it at a SQL Server, and it exports diagnostic data to Excel. In a matter of minutes, you get an Excel spreadsheet with tabs for sp_Blitz, sp_BlitzIndex, sp_BlitzCache, and even saves the most resource-intensive query plans into separate .sqlplan files. Here’s a sample output file.
3. You analyze the data and look like a hero. You’re able to track down complex issues in minutes, build more comprehensive health check reports, and make more money with less manual labor.
You can even install the Consultant Toolkit at your client sites, set up a scheduled Windows task, and have it automatically upload the Excel spreadsheet & query plans to Amazon S3. You can regularly keep tabs on your clients’ servers without having to VPN all over the place.
I’ve used the Consultant Toolkit for years as part of my SQL Critical Care® process, and I can’t imagine doing my work without it. Give it a shot – I bet your clients will love the results.
This is a desktop video, so you may wanna full screen this to see the action in glorious 1080p detail:
When you’re a consultant, you need to get diagnostic data from your clients.
When you’re talking to a new sales prospect – do you waste a lot of time asking basic investigation questions like what version they’re on, how much data they have, what kinds of workloads they have? You know they’re not really giving you accurate answers because often they just flat out don’t know.
Instead, imagine just emailing them a diagnostic app that gathers data and packages it into an Excel spreadsheet they can send you. You can review their server before your sales call even starts, and you can give them real value right from your first interaction.
When you’re working on a client’s SQL Server – do you waste a lot of time running diagnostic queries, saving their outputs to a file, and then trying to shuffle that data around from place to place? Do you feel like you’re constantly having to update your diagnostic queries each time you find a new problem, and you don’t really wanna be in the writing-basic-scripts business anymore?
Instead, imagine the diagnostic app running deeper diagnostic queries, like gathering the customer’s top queries by reads, CPU, duration, executions, and more, saving those execution plans out to files that you can review later. You’ll spend less time hitting F5, and more time doing the kinds of analysis that your customers really pay you for.
When a client has an emergency and you’re not around – isn’t it frustrating that they ask you, “hey, SQL Server was slow yesterday around 6pm – do you know why?” You ask them questions, but…of course they didn’t gather any diagnostic data, or if they did, it’s a useless Profiler trace with no insight. You can’t give them answers, and everybody’s frustrated.
Instead, imagine the customer running this diagnostic app whenever they have problems, and emailing you the spreadsheet saying, “Hey, when you get time, can you analyze this and see why our SQL Server was slow at this moment?” The data would include running queries, wait stats, memory metrics, and more.
When you hit a wall and you need help – do you email your network and kinda fumble around, not sure how to pass the data around? Everybody seems to use slightly different ways of gathering and viewing data, and it’s really hard to interpret someone else’s data.
Imagine being able to pass a standard spreadsheet around to your network – after redacting client-sensitive stuff – and asking, “Hey, you’ve seen this metric setup before – do anything about these metrics look weird to you? What would you do next in my shoes?”
Download the app and extract it to your local drive, like c:\temp. You don’t have to run this on the SQL Server itself – you can run it from any desktop or laptop with at least .NET 4.5.2. Then, to gather diagnostic data on one of your servers, go to a command prompt and run:
If you use tricky characters in your server name, user name, or password, like quotation marks or spaces, you’ll need to surround it with double quotes. For example, if my password is @#!, I might want to use “@#!”.
It’ll take about 5-15 minutes to run a bunch of DMV queries, and when it’s done, check out the Output folder. There’s a zip file with your server’s data.
By default, it runs a relatively limited set of queries. When you’re working hands-on with a client’s server yourself, and you want even more, add –deepdive (that’s two dashes, not one) as a parameter, and it’ll run more. If you go into the Resources folder, you’ll see the queries it runs: things like sp_Blitz, sp_BlitzCache, sp_BlitzIndex, and other utility queries we’ve written over the years.
When it’s done, the output folder will have a zip file (plus a folder with the contents of the zip file.) Start with the spreadsheet – it’s chock full of diagnostic data organized in tabs.
Requires SQL Server 2008 or newer – if Microsoft doesn’t support it, neither do we. Also works in Amazon RDS and Azure SQL DB Managed Instances. It somewhat works in Azure SQL DB, but the problem is that Microsoft can (and does) restart and fail over Azure SQL DB databases all the time, so you don’t get much performance history up there.
Requires .NET 4.5.2 or higher – which came out way back in 2014.
Recommended: sysadmin permissions – because to query for things like CHECKDB success, you have to be SA. The app will run with lower permissions like VIEW SERVER STATE, and it’ll throw warnings when it’s unable to query those SA-only functions, but you’ll still get a ton of usable diagnostics out of it.
Recent changes:
Ongoing: monthly updates to the First Responder Kit scripts included in the app, like sp_Blitz.
2021-01: Added a Plans Duplicated tab to list the unparameterized queries that are flooding the plan cache the most.
2020-07: The .config file now has UploadToS3 and DeepDive default options that you can set to True, making it easier for your clients to send you lots of diagnostic data without typing in commands.
2020-02: The Databases tab now has all columns from sys.databases, making it easier to troubleshoot obscure or new features. The Deadlocks tab now only includes the victims (not all of the queries), so you have less deadlock files to sift through in the output.
2020-01: new tabs for Indexes Missing tab, Deadlocks.
2019-09: new –upload command line switch uploads the results to Amazon S3, plus new Plans CPU ByHash tab that helps expose unparameterized queries in the plan cache.
2019-08: bug fixes.
2019-07: bug fixes.
2019-04: more health checks.
2019-03: new “CPU Now” tab, gather more query plans faster.
