Consultants: want a utility to gather SQL Server data?

Consultant Toolkit

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?” And imagine being able to just book a spot on my calendar to get that second opinion.

This app will make you more money.

This was the very first app I had Richie build when he started with us, and it’s been a core part of our business for years. It shaved hours off every consulting engagement we had and helped us do a better job every time. I can’t imagine consulting without it, and I’m excited to share it with you to see what you think.

To get it, sign up for a free trial of the Consultant Toolkit, and the download link will be on your receipt and in your My Account page. 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:

Windows authentication:

SQL Server authentication:

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.

“Wait, what’s with this AppName.exe stuff?”

The real app name is shown in the readme file, but I’m purposely not mentioning it here, dear reader, and I never will. (You’ll see it in screenshots here from time to time, but never typed out.)

See, if you’re a consultant, you’re going to be giving this app to your clients to run for diagnostic purposes. I don’t want your clients Googling the app’s name and finding me. This is your tool, the thing that’s gonna give you an edge over the amateurs who are still copy/pasting data out of SSMS.

Now, back to what the app does.

You get in-depth diagnostic data that makes you look like a SQL Server surgeon.

You can download a sample zip file from my lab. Here’s what the output folder looks like after a run:

The output includes a spreadsheet plus query plans

The top line is a log file with any error messages from the app.

The second line is a spreadsheet chock full of diagnostic data organized in tabs.

The remainder are the most resource-intensive query plans as reported by sp_BlitzCache. (The query metrics are in the spreadsheet.)

However, note that in the output, we’re NOT showing things like sp_Blitz’s URL column – because it links back to BrentOzar.com. When you hand this app to a client, trust me, they’re going to run it and look at the spreadsheet contents. They’re going to be amazed at the amount of great diagnostics you’re getting. However, I don’t want them seeing links back to me – again, this is your app. (Besides, as a trained SQL Server professional, you know the place to find more information about each of those warnings already anyway. You’re here, reading my stuff. You’ve already found me. Carry on.)

These queries support SQL Server 2008 and newer. Older database compatibility levels will throw errors as it runs, but you’ll still get a lot of useful data.

It’s by no means a monitoring app – if you’ve got $1,000 per server to spend, I’d highly recommend a full blown monitoring application. Those will get you cool features like email alerting, historical playback, and a database repository. This is a much more lightweight, one-off utility designed to solve the 3 specific use cases at the top of this post – especially in situations where clients just don’t have the budget or approval to put in monitoring tools.

This app is our new Consultant Toolkit.

We’re offering a free trial during our early access period, and the download link will be on your receipt and in your My Account page.

The toolkit includes documentation for how to best use it as part of your pre-sales process, building a health check, and giving instructions for your clients as to how to run it during an emergency if you’re not around.

Get it, play around with it, and let us know what changes & improvements you might need in order to use it during your daily consulting work by emailing us at Help@BrentOzar.com.

Menu