Getting Started with the Consultant Toolkit

so today I'm gonna be talking about getting started with the consultant toolkit I'm Brenna czar and I use this toolkit everyday usually several times a day because I do performance tuning work on big ugly nasty terrifying sequel servers so what I'm gonna talk about is who it's for who it's not for the kinds of stuff that it helps you with and show you how to use it and show you how to analyze the results so when you're a consultant some of the things you have to do is for example talk to a sales prospect someone will book a meeting with you cold and when you start talking to them you don't know anything about their server so there's a lot of the awkward dance like when you first start dating someone in high school so tell me what you like tell me what kind of consultant you need and it's really awkward and both of you are having a bad time because the client doesn't really know what to say they don't know anything about sequel server that's why they're hiring you so before I had this I would even have discussions or with people on the phone or in WebEx going well can you open up management studio and tell me what the version number is and the clients like where do I find that that burns a whole lot of time and it makes them feel stupid and it makes you feel kinda awkward you know sometimes you just want to elbow them out of the way and go take control the keyboard and go looking at their server going here here just let me take a look real quick and see what's going on then once you get past that hurdle once you sign a contract and you start working with them when you pop open that sequel server for the first time it's really intimidating cuz there's so many things you want to go look at you want to go poke around and see if have they got backups have they got corruption are they on a supported build or not how many people are cysts admins how long did the jobs take there's a million questions that you want to ask and you don't want to necessarily be connected to the sequel server live while you're asking those questions but in today's world you're sitting there Remote Desktop into something running queries poking around it's all a very manual process in very time-intensive and when you disconnect the phone you go back and you're like oh man I wish I would have run that diagnostic query that other one I didn't think that I needed it but now I actually kind of do and then later you would have a follow-up engagement say three months later six months later even a month later you go back in and you want to take a fresh look at the server it's like Groundhog Day you're starting again all over from scratch because the client can change anything at any time you know how the servers are everybody's a sysadmin everyone can change anything and they do they change stuff all over the place and you're right back where you started then one of my favorite questions was a client would emergency call in and say on a Monday and go we had a terrible performance emergency over the weekend the server was dead slow on Saturday everything was timing out so to fix it we rebooted it can you tell us why it was slow and it's frustrating as a consultant because all the juicy data is gone now and you almost want to lecture them and say no no please don't reboot just call me when these things happen and I'll be able to go in and take a look well I didn't want to pay your rates over the weekend that's fair also I was drunk I wish I had some kind of way where I could empower my clients to go gather that data for me and then send it to me so that I could review it over at my leisure the things that I hated about that work as a consultant was I got so sick and tired of the manual labor of copy pasting a query into a remote desktop window running the query copying the results pasting the results out into Excel and because that data shuffling back and forth was so bad I tended to run less queries than I really wanted because the process was just so terrible I would try to run the bare minimum of queries that I could in order to get out and get back to delivering value for the client man I hated writing my own DMV queries from scratch I remember when I first got started consulting it was just overwhelming I would look at thousand dollar a server monitoring tools that can tell me absolutely everything and then community scripts that we tell me like one or two things and I'm like isn't there something that somewhere in the middle and all of us over time we built up this little Windows Explorer folder you know I'll folder where you keep all of your scripts and they're kind of haphazardly a haphazardly organized and you kind of sort of know which ones to jump around in but you never really gather all of them and then sometimes you need to ask for a second opinion sometimes some of us work as solo independent consultants some of us work in teams of say two to five people and you want to ask for someone else's opinion but they're busy right now and when you go to ask for their opinion they're gonna ask for things like will can you show me the top five query plans did you get information about these indexes and then you're like ah the game of telephone is going to start again so that's where the consultant toolkit works so what this thing is is it's a zip file it's not even an app that you're gonna go and install somewhere everything is self-contained in a zip file you're gonna go download that run it and it'll run all kinds of diagnostic queries against your sequel server let's go see how it works so I've already downloaded it onto my own server here and I put it into this folder consultant toolkit there's nothing to install it's just a command-line application there's a readme which I know you'll never read life goes on so I'm gonna fire open a command prompt and I'm gonna change into the same directory that this thing is in I'm gonna go into a CD temp consultant toolkit and then the app that's the name of it right there now I needed to say here briefly you're never gonna see that name inside my documentation that's public you're never gonna see it in the blog posts you're never gonna see it on the sales pages for this you will see it in videos but it's not gonna be written down the reason why is I want this to look like it came from you I want this to look like it's yours the client is hiring you they're not hiring me so even if you go in and look at things like the readme because strangely your client will look at the readme but I know you won't there's instructions in here on how you go about running it with Windows authentication with sequel server authentication with Azure sequel DB all kinds of stuff but there's nothing in there about me that's on purpose that's because I want them to be your clients not my clients so now I've got a setup here if I want to I can point it at a different sequel server so for example I can say data source and then sequel 2017 B that's the name of my server here I can pass in Windows authentication sequel authentication all kinds of stuff I'm just gonna hit enter here and it goes off and starts running queries now it's running a bunch of diagnostic queries while it runs I'm gonna go over here into the output folder this is where it's doing its work for each collection that it's run it's dumping out into an Excel spreadsheet plus in a second you're gonna start seeing query plans plop into here because it captures the top 10 queries by which ones have been running the most CPU time running the most often duration reads writes memory grants temp DB spills all kinds of stuff and saving each one of those two separate files these are all sequel plan files that you can then go open up and management studio later in order to understand what the queries that we're running right now were what were the biggest ones over time I mean you're just looking at these things plop down into files you're like this would take me forever to go and gather which leads to another interesting question how long does this thing take to run by default we're aiming for around 5 minutes they're all low priority DMV queries not blocking anybody else but if you want even more information if you want to gather things like average CPU duration CP or which ones are average longest in duration like long-running report queries you can run it with another parameter called deep dive which will gather even more stuff so this thing's gonna run on for like five minutes let's go ahead and look at another sequel servers data that I've already got captured when this thing gets all the way done it's gonna create a zip file like this which will have all of those query plans and Excel spreadsheet type stuff inside of it I'm gonna go extract this what you would do with a client for example with a sales prospect is you would say here's this zip file with the consultant my consultant toolkit and whatever you want to name it is fine here's my app go run this and then in the output folder see it just finished go copy and paste the zip file into an email or however you prefer to communicate between clients get me that zip file and then I'll be able to go extract it and go see what was going on on the sequel server so let's go see how the output looks so here's the one that I ran this morning on another server up in the cloud let's go extract that fella here's my lab server up in the cloud and I got all kinds of stuff in here the place that I'm gonna start is in the Excel spreadsheet I'm gonna double click on the Excel spreadsheet and it's got wording in here on the first tab because what I've learned is a consultant over time is that clients want to open this and see what they're about to send you which is fair I want my clients to be well educated about the data that leaves their offices so I have instructions in here that says anything that you want to redact you're welcome to but you may want to just give me some idea of what you're worried acting so that I know for example if you delete all the information about your backups that I know that there's going to be a surprise when we go in there and run it in person you'll notice all these tabs across the bottom so the way that I read it is I first start with the uptime tab because so much of sequel server's Diagnostics are stored in memory before I look at anything else I just want to know how much health history I'm dealing with here the case of this server only got about a day's worth of uptime so I'm not gonna have really great Diagnostics about the in-depth history of the server I get what the machines name is whether or not I'm on a named instance what version I am whether or not it's clustered and whether or not availability groups are enabled there's much more details about all of these in other tabs like what availability groups are set up on the server whether I'm a primary or a secondary what databases are in this in the availability group how far behind replication is all kinds of other stuff how many cores I'm dealing with how much RAM what the data size is that's on the servers and then what the target and total memory is this right here is a great example for me because I can see that the VM has 61 gigs worth of RAM but target is only 25 gigs meaning sequel server only wants to use 25 gigs out of 61 this could be because they set max memory to low could be maybe somebody grew this sequel server over time maybe they have multiple instances of sequel server on there maybe there are other apps we'll get into all kinds of other stuff on the other tabs but this is just a really good place for me to start - tell me about the server and then on the next tab health this has the output of SP Blitz now in a lot of these tabs what I want to do is I want to tell you how to run these same queries in case you want to go gather this information yourself for example up here it says the query source is SP bullets with check server info turned on so that way you know where the kind of data comes from here it's the first responder kit and it tells me a lot about the people first tab uptime told me about the server the second time tells me about the people who are managing it are they doing backups are they doing backups to the same place where the databases live is their database corruption I want to know right away whether or not NIC Frye says I'm not sure if this is the right channel for the consultant toolkit yeah sure go ahead and ask questions in there so I want to know right away as quickly as possible is there gonna be a showstopper that's going to prevent me from having a successful engagement at this client no backups and no Check DB and database corruption are great examples of showstoppers memory dumps have occurred I have a poison wait there's been errors in the default trace the stuff really from priority one to priority fifty is a really good idea of just how dangerous this server is in now you'll notice that if you've run as people it's before as people it's usually has a URL column that lets you go back two Brando's are calm and see more information about each line it's not in here and it's not in here because remember these are your clients not my clients I want them to come to you for questions not come to me and of course you as a consultant you already know what these things mean you know what it means when their database corruption you know what it means when there's a full recovery model without log backups so this tab tells me about the people who are managing it I'm gonna scroll down a little further just to show you something that I always find interesting the list of sis admins the reason why I find a list of sis admins helpful it's because I know who's gonna be working with me on the engagement versus maybe some applications that might be working against me someone who has a everybody sysadmin and inside the box over on the next tab I get weight stats in the twenty five hour since this server started up how many hours of weights have I had and what weight types are they remember how I said the the first tab uptime tells me about the server the second tab health tells me about the people managing the server the third tab weight stats tells me how unhappy the users are with query performance those three tabs alone are really all I need for a sales call when the weights are this bad when the weights are multiples of clock time then I know that the server really is in trouble whereas if the server's absolutely bored then I have a different response when I'm dealing on the sales call for example if this was a sales call I was about to take on I'd say oh you know what you have a server that's brand spankin new sequel server 2017 you're doing a great job of managing that you're on a current patch version a VM with eight cores back on the the health tab it tells you that it's a VM 61 gigs of RAM you got a decently sized transactional database around 300 gigs you do have database corruption so we're gonna have to talk about where the storage is for this VM and I notice also that users are probably unhappy with performance you're noticing that this thing is spending a lot of time waiting on storage or maybe getting those 15 second i/o warning errors which we have a tab for that as well this changes the whole consulting dynamic when I'm having sales calls people are like oh my god you're a psychic you know about my server and you've never even seen it shut up and take my money and then the contracts start raining down you look like such a pro as opposed to your competition because your clients are talking to several other consultants when they're starting the sales call process your competition is like so tell me about your sequel server how big is it and then you come on to the sales call and you look radically different you look like an established pro nick says does Excel have to be installed on the workstation when you run the app no not at all you can run it from a single server management studio machine you can run it from you could run it from the sequel server itself I'd really rather not do that I'm just kind of hyvee itchy about running it on the sequel server itself but you can run it from any VM any workstation anywhere doesn't need Excel Steve says how close is this to what sequel constant care sends to you the format is wildly different but we get a lot of the same data Jim says no you wouldn't be able to open the file without Excel on the workstation yeah wherever you go to open it you're gonna want Excel let's see what other Kevin says is the author set on the excel file properties to your name oh dude please let's find out um I'm not even sure where that's at if you want shoot me a shoot me an email with that Kevin and I'll go dig in and make sure to cuz I don't know what's going on there somebody said it's just there I'm looking up oh wait hold on it's right there hold on file author Brando's are Oh wait Kevin I'll tell you what Kevin if you shoot me an email go to our site and go to our site and hit contact up at the top I will give you this the consulting cool toolkit free for a year for finding that bug I love people who find bugs now everybody's gonna be emailing going I'm Kevin yeah but only me and Kevin know what his last name is oh that's a secret between the two of us all right so that's the first three tabs in it how much would you pay for a tool that does all this but wait there's more there's a databases tab that tells you all of the databases on the server how large they are how big their VLF SAR all kinds of stuff there's a list of files for every set for every database I find this really useful because I want to know how slow their temp TB is for example here this server has been set up pretty well with 8 temp TB data files I can see the exact path that those data files and log files are all on and then I can see how fast or slow their write stalls are or their read stall files are or their read stalls are so that that way I can very quickly tell whether or not their storage is in good shape or not indexes so those of us who like running espy blitz index I can see all kinds of stuff like aggressive indexes which tables are dealing with locks and what kinds of lock duration I'm dealing with here I'm dealing with 4,500 minutes worth of locking on the post table just inside the one day that the sequel server has been up so I'm able to have a great discussion with the client about it looks like you really query the daylights out of this posts table and I've even been known to as we lead up to sales calls tell clients hey look I'm gonna give you this for free because it's desperately needed you need an index on this post stable on accepted answer ID to scroll across and you can see here's how many indexes already exist on the table here's how many rows are in that table the weather is tomato size is here's the create index statement so that I can start delivering value even before we sign a contract I can say look I'm gonna fix all kinds of things for you when we get together but just do me a favor create this index first and it'll get some of that noise out of the way cuz I want to be able to fix some harder problems when we get on the phone together again you look like an amazing genius when you're on the phone with the clients because they're like man this person shipping value even before that's shipping don't need to say that a little slower shipping value even before we get started and of course when you're dealing with recommending indexes to a client you want to know things like well what other indexes are on the post table well there are other tabs that give you complete inventory of the databases objects table sizes for each one of your servers now this does max out at ten thousand rows so if you have clients that have more than 10,000 rows worth of indexes that's just a heads up that you're probably gonna want to run as people it's index manually but what I love this for is that I can put this into a table in Excel and I can start filtering to say only show me the post table and nothing else just show me posts and then I can look at what indexes already exists on that post table before I go to recommend that they go add that index I can see what their definitions are I can see how often they get used all kinds of stuff if I continue to scroll across more and s people it's indexes results now that's the indexes tabs there's more information inside those I'll let you go explore those yourself then the plans tabs hold exactly what you think they hold the list of the topmost resource intensive queries on this server inside the plan cache now of course me explaining to you how to work through the plan cache is way beyond the scope of what I can do in this webcast but for those of you who are used to running SP blitz cache you can see that you're getting the top 10 queries by CPU and the top 10 queries by average CPU the ones that run very long in terms of CPU but hardly run that often I can see the query whether or not it's a stored procedure or a function or a statement inside those I can get the analysis on things that s peopleit's index file or best people it's cache found inside those how often they run their total CPU time duration logical reads all that good stuff and then when I want to see what that query is see you on how it has that ranking number right here top CPU number one number two we'll remember our buddy that little fluff folder over here here's where they are here are those top ten execution plans sorted by CPU as you go down there's CPU average as well duration all your query plans so that if you go well what wait what is that top query by CPU what's his execution plan look like you can double click it fire it open management studio sentry one plan Explorer or whatever your favorite tool is and then here you can go oh here's that post tables query that desperately needs that index you look like a rock star when you're having this discussion with your clients during the sales call I can show them their most resource intensive queries I can explain to them how we're going to be able to help them think about how dramatically different that sales conversation is with clients and of course that's just the sales conversation now when you sit down to work you don't even need access to the server in order to start building a plan now simply based on the contents of that spreadsheet you can start going right I know that I need to change based on that uptime tab I may need to look at the max memory config and raise that some by raising that some based on the other things that I see in the spreadsheet that's going to reduce how much time I spend waiting on storage it let me cache more data then I'm also gonna have to look at adding this desperately needed index on the post table which is also going to prevent table scans on the post table making things even faster we didn't have enough RAM to store data before I know that based on that here are the queries that it's going to impact these are the things that the end users are going to notice is going to be immediately faster and getting the satisfaction out of the end users that's your goal as a consultant it doesn't matter if Fix fragmentation doesn't matter if you fixed fill factor you have to be able to say fill in accounting and marry in sales are going to notice the difference when I make these changes and fill in accounting and Marian sales will say yes I've noticed that my dashboard is now faster every time I query on the post table whatever that consultant did they're a rockstar you should give them more money I don't know how often that really happens so there's all kinds of tabs here for slicing and dicing your plan cash in different ways by top reads by duration executions plans memory grants one of my personal favorites finding the ones that cause resource semaphore weights spills now you'll notice that some of these tabs are only available on newer versions of sequel server for example sorting by spills is only available in certain cumulative updates and service packs we have a line in the spreadsheet whenever something didn't get called let me find one that didn't oh this one was fast enough everything got called but when something didn't get called we explained to you in there why this one didn't get called like it doesn't have a new enough service pack another one of my favorites is show me the top 50 most recently compiled queries for this server can sometimes I'm dealing with unpromoted queries and I need to see exactly what under a mature iced-tea sequel is coming in and I can see that over here there's also plans right now as in what queries were running at the moment that this collector was running so you can go see remember how I said sometimes your clients are gonna go oh my god the server was so slow on Saturday night at 10:00 p.m. when you were at the tequila bar what was going on now they can run it for you you can empower them to run this on their servers send you the zip file and you can see you can see what queries were running you can go back over to the file and see here are the query plans for the queries that were running right now when this ran furthermore if I go even further out on the tabs you can even see what weight stats look like while this thing was running the collector took 348 seconds in order to execute during that here's how much time I spent waiting on each of these weight types plus there's a perf mod tab that will show you some perfmon counters that changed during that run so you get a sampling of what's happening during that ugly time for the client you can encourage them and empower them to run it as often as they want and then send you the results if you want to work out some kind of payment thing where they pay you you know 200 dollars or whatever it is every time they Anna let you analyze the spreadsheet for them you're absolutely welcome to do that there's other tabs to things like the error log out a sequel server so you can see if they're having those 15-second i/o warning errors you can say in this case for example I've got corruption in my Dynamics AX baseline database I can see when the corruption was a Kurt was detected when the database was restored I can see the maintenance plans that they've got set up the agent jobs that they've got set up how long they take it their longest when they're scheduled to run next so like if somebody doesn't have their check DB jobs enabled all kinds of stuff that you can gather out of here way more than I could even bother going through in a webcast so let's see here well we got a bunch of questions that have come in let's go knock through here so we have bill says I can see clients with a DBA just taking this and running it with themselves after you hand it over yes but there's a time bomb in the app the app time bombs out every 90 days it stops working and they have to come to you to get another one I want them to continue to foster that relationship with you it's good if they run it and find value in it because then they're gonna come back to you and this thing time bombs and go hey yeah I got a real itchin for that utility you were using and you can say great let's go through and talk about another engagement next let's see here da-da-da-dah an and says oh no I and that's not quite right about the author thing Mike says as al s6 this is an excellent tool but is a less experienced DBA how why decipher everything that was returned you hire a consultant that's why it's called the consultant toolkit and the way I get this question a lot from people who played around with it they're like oh my god this would be amazing as a full-time DBA yes but it's not written for you you can buy it if you want to buy it just know that it's usually start of your training journey that there's gonna be things that you have to go and learn and decide decipher for example what each of these things mean under forced serialization or compilation time outs Ann Ann says that's great having it ready saves a ton of time ben says do I need the SP blitz store procedures installed before I run the tool against the instance not at all everything's built inside the application and it doesn't leave s people it's stored procedures behind I didn't want to have it go overwrite your existing ones if you have customized versions for example Pedro says why not allow your customer to learn and use the tools you can if you want to use this as a training tool with your cos your customers you're absolutely empowered to it's priced per consultant so if you have 50 clients that's fine I'm only charging you now if you have 50 consultants at your company that's a little bit different Jeremy says is there a list of what doesn't run when doing view server state versus sysadmin yeah if you if you go out and Google for example what you get with view server state versus sysadmin you can find a lot of things that kind of break down permissions across those all right so coming back over to the slide deck so back into here Oh Charlie says does the queries running now tab include a column for the duration for each request not just the wait time yes back over on the plans now it happens to be these first two it shows you when the request first started and what its elapsed time is so far tells you all kinds of things like the memory grants and much more so back over on the slide deck so I mentioned that when I analyze the spreadsheet results out of here the uptime tab tells me about the hardware the health tab tells me about the admins and the weights tab tells me unhappy how I'm happy the users are the next thing that you'll probably want to know is how do I personally analyze the results I've been using this tool for years now and I'm so used to just ripping through it at high speed when you're a consultant you may want to second opinion at some point so after you've bought it on the documentation page there's a how to analyze the results where I talk through how I analyze the results if you want me to analyze your results what you can do on that page is you can book time on my calendar with a credit card just immediately pick an available window on my calendar and then you'll get a goto meeting invite immediately and at that time when we go and get together I just look at your desktop and we walk through that together you don't have to send me your clients data I don't do anything in writing this is your clients data that we'll talk through and I'll give you my own opinion the way that I would leverage that as a consultant is when you're up against a wall and you have a server that you just don't understand or you're not sure that you're gonna recommend the right thing for the client don't tell me what you think just get together and we'll open the file together and watch how I go through it and see if I come to the same conclusion that you do that for me is what's really valuable as a second opinion type approach I did mention that the application name is not out in public and it's okay if you write it and talk about it I'm just not gonna write it and about it and talk about it because I don't want people ever finding me I want people to go to you this is part of your arsenal this whole thing is about saving you time making you look good and helping you deal with more clients in less time so to go get it go over to Brent Ozark comm slash go slash toolkit where you can sign up go register for it download it get started playing around with it run it in your own lab to gain confidence with it and I've even got when you buy it through Bono's are calm I've got a whole page dedicated to how I use it with clients like the email templates that I use when someone signs up for a sales call here's the email that I auto respond back to them with to get them to go run it on their system and send me the results back you'd be delighted as a consultant how many people are willing to do that because they want help they want you to have as much data as they can before you start that engagement Mike says at what point in this sales pitch do you drop the pricing bomb go ahead and click on the URL you can see how its price there Doug says does the EXCI have to reside on the server for it to run can my client run it from their workstation and pointed at the sequel server instance yes you can run it from your workstation and you can even if you have like a VPN connection into their server you can run it from your desktop pointing at their sequel server it does not have to be on the sequel server it doesn't install anything on the sequel server nothing like that so let's see Bill asks can you rename the app without breaking dependencies not yet not at this time steve says why am I not seeing all those other questions cuz they're in GoToWebinar Q&A instead of the slack room all right well there we go that's everything that I wanted to throw show through to you Charlie says this is really just a great thing approach in pricing thanks for it and the generosity for us to help us help others yeah this was the big goal when when I pivoted our company and I said okay we're not gonna do as much consulting ourselves anymore but we have all these tools that we've built up how can I help y'all do your jobs even better and so we're like let's let's do it you know off we go so go play around with it let me know what you think and hope y'all enjoy it have a great week everybody and see you around

Channel: Brent Ozar Unlimited

Views: 5,784

Rating: 4.9316239 out of 5

Keywords: sqlserver, brentozarunlimited, brent ozar, mssql

Id: EGFzzelsq70

Length: 34min 58sec (2098 seconds)

Published: Fri Feb 01 2019