This week, Brent and Richie discuss the new Power BI Dashboard for DBAs, designing a new backup structure, creating SSRS reports, the difference between row and page compression, using EMC Avamar SQL agent for backing up databases, SQL Server and other books to check out, Veeam Backup for SQL Server, and how to best manage monitor over 20 SQL servers.
You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go.
Enjoy the Podcast?
Don’t miss an episode, subscribe via iTunes, Stitcher or RSS.
Leave us a review in iTunes
Office Hours – 11-22-17
What’s the new Power BI Dashboard for DBAs?
Brent Ozar: I guess we’ll go ahead and get into some of these questions here.
Richie Rump: Since we’re going to get started, I’m going to throw you a softball and say well, PowerBI dashboard, what the hell is that? Because I just saw that for the first time a few days ago, and tell me more, Mr. Ozar.
Brent Ozar: Right? So we have – and you know, it’s funny, I can actually do a demo of it because it happens to be on this VM. So on the blog, if you go to the blog and look for – up at the top – tools and then first responder kit, we unveiled this new PowerBI dashboard for sp_BlitzFirst where you can go in and analyze your SQL Server’s wait stats over time.
So I’m going to go in over here, I’m going to minimize some of these sidebars so you all can see it. So the first thing that we come in to the dashboard is you can zoom in and out to various time ranges, so you can see show me the wait stats that I’m having and my number of batch requests per second. So like, you can see here in the morning, I had a whole lot of wait times – if you hover your mouse over those you can see what kind of wait category they are. Had a whole lot of wait times but I wasn’t doing a whole lot of batch requests per second, and then all of a sudden round about eight o clock, my batch requests per second is able to go through the roof, and my SQL Server’s not waiting on stuff anymore. If I want to find out what was going on, I can click on various bars and I can see more detail about those, like here’s the kind of wait types that are involved with whatever bar I clicked on, here’s the warnings that were happening on the SQL Server at that time, here’s the queries that were running around about that same time, I can right-click on any one of those queries and get more details about it, see its performance over time. It’s really cool when I go to see you know, alright, what changed about this SQL Server in this time range. I can go through the sp_BlitzFirst results and see things like I had high CPU usage – I’m going to zoom over here – someone tuned a query, someone added indexes, so we can log into what kind of changes have been made on the server and see what happened over that time range. It’s all totally free, it’s all totally open source, you can go download it off of our site. It all works off of running sp_BlitzFirst to a table, so we get a lot of cool enhancements for that plan coming up.
Richie Rump: So I want to hijack this a little bit and do the podcast hosting. So what was the – why – because I haven’t even talked to you about this, so this is a good opportunity. So why a dashboard? What was the genius or the thought and the idea about hey, maybe we could do a dashboard behind all this?
Brent Ozar: I have had for maybe the last year or two, the ability to log a lot of this stuff to a table, and I’ve heard from people that they’ve built some kind of report out of it, you know, either their own SSRS report, their own just plain T-SQL on a view, but people have been kind of reinventing the wheel and they’ve been struggling with how do I just display it in an easy way. And when I saw Patrick LeBlanc show PowerBI, and there was another blogger who did PowerBI at GroupBy, I was like, you know what, this looks easy enough that I could probably bang out in a week and be reasonably happy with it. Now, you remember what – for me, as a consultant, when you take a week off, you’re writing down a whole bunch of labor. You know, you’re kind of basically giving that away, and I don’t do PowerBI consulting and I’m not about to start. Like, I’m not about to be the guy who does PowerBI, or builds reports for people. But I was just like, if I could give the community back one week of work and say, “Here you go, you guys can now see what your waits have been over time.” Hopefully, it just helps people to solve the easy problems quicker and then save the harder problems for us as consultants.
Richie Rump: So you say it took you a week, did it take you a week?
Brent Ozar: Yes, it took me about a week of solid work, and that was spread across a span of three weeks probably, like two days here, two days there. Because a lot of it is – you know how it is when any time you build a visualization, all of a sudden you find problems with the data, and you’re like, “I got to change the way we store that”, “I need this other field to store it too”. So then it changes things that you put into the database as well.
Richie Rump: Yes, so I mean, was that about SQL Saturday Houston time last year when…
Brent Ozar: That was when I started thinking about it. Like, how am I going to go build this thing, and then I never put pen to paper until – would have been September I think? Probably round about September when Erik and I’s precon for PASS sold out, and we were like, “Alright, we got to keep bringing more and more fun things in here. What else can we give the attendees?” But the original point of thought of it came when I talked to Patrick at SQL Saturday Houston.
Richie Rump: Yes, I saw that, and I’m not getting involved in that whatsoever. I’m backed away and I’m like, “Look, here’s lunch, and look, they’re talking and I’m walking away from that. I do not want to be involved in PowerBI” at that point, right? Because it was still fairly young in its development cycle.
Brent Ozar: It’s still – like the stuff that we have to do in PowerBI to get stuff to work, I’m like, “Are you kidding me?” Like, picking times – the way that we pick times in that PowerBI file looks like the most crappy, ghetto way of doing a time slider. Like, there should be a clock or something here. Nope, there’s nothing like that, and you go out and Google “How do you make a time slider in PowerBI”, well, we don’t really have a way to do that yet. So it’s – I’m sure that there’s going to be a lot of changes over the next six months as Microsoft adds more stuff into there.
Richie Rump: So last question, is there any more coming? Do you have any more ideas to add to this thing?
Brent Ozar: Yes, because now that we got the basics in there, sp_Blitz already logs to a table, and Blitz Index logs to a table. So now I can say, “Alright, show me the most recent health check for this server, show me its indexes all inside there, like, what’s a quick psychological profile of this thing’s indexes?” And then also on the left-hand side of it, we’ve got date and time sliders. The next step is I’m going to centralize this data across multiple SQL Servers, and you’re going to have a list of checkboxes for SQL Servers as well. So if you just want to pick one SQL Server to look at, you can do that out of your entire data state as Microsoft likes to call it.
Richie Rump: Yes, I love Blitz Index and if we could get that on the dashboard, that would be amazing. Oh wow.
Brent Ozar: The other weird part with it is, when I look at sp_BlitzIndex, I want to copy things out of it and PowerBI makes copy-pasting crappy. It’s really hard to copy paste the stuff you want out of PowerBI. Like, “Basically, you write down what you see on the screen, you type that into a text editor.” Come on now, really? That can’t be. “Well, you could also print it to pdf and then copy paste from the pdf.” What year is this?
Richie Rump: Alright, I’m done hijacking. I’m done.
How often should I do backups?
Brent Ozar: Well, let’s see, now we’ll get to questions here. What do we got here? Esther says, “Our company does full backups of every database.” Good. “Each databases schedule is determined by the frequency of its data updates.” See, I’m not a fan of that. I would rather just have all the databases backed up on the same schedule, who cares when the data changes? Generally, the company will say, “I need this thing restored to the most recent point in time” and to make your life easy, you don’t want to have to guess through, “Well, which restore should I do from which servers when?” I would really just do backups on the same exact time throughout. Esther says, “What would I suggest as a starting point to help us design our new backup structure? Any specific video, tutorial, blog post, et cetera.” Go to the site and search for the term RPO and RTO, recovery point objective and recovery time objective. And we’ve got several blog posts that talk you through how you should use those numbers with the business to determine the right backup schedule.
What tool do you prefer for building SSRS reports?
Brent Ozar: Chris asks, “Do you guys use a report builder or something else to make SSRS reports?” Richie, have you ever made an SSRS report?
Richie Rump: I was using SSRS when it was still in beta. I think when it was in first best was when I started using it. No, I don’t think report builder is something – it’s been such a long time since I’ve written a report, honestly. I mean, this was a while ago, so I remember writing RDL by hand, so does that count? Does it give me bonus points for that? I remember that, I don’t remember anything with report builder. So for reporting, it’s important that you get out the data that you want and you put it in source control. I mean, really, everything else, how it’s built and how it’s generated, you know, whatever works, but as long as it’s in source control and as long as it gets you the right data for the right people, you know, you do what you need to do to get that done.
Brent Ozar: I have no experience on that either. It’s been maybe ten years since I’ve used SSRS, so it’s tough there. That was actually my favorite job duty to let go of. I’m like, I never want to work with the business again to figure out how many decimal places they want for net profit percentage. I just don’t care.
Richie Rump: I went through so many early – you know, in the web, early 2000s report engines and they were all crap. I mean, every single one of them.
Brent Ozar: Crystal. Crystal Reports.
Richie Rump: Crystal was the best, and Crystal was a load of crap.
Brent Ozar: Joe says, “You don’t get TFS version control with report builder.” Good to know.
What are the differences between row & page compression?
Brent Ozar: Brent Unrah, good to see you sir, says, “Looking to start using database compression. What are the differences between row and page level compression? How do I go about which tables get compressed?” So I only start by saying what’s the problem that you need to solve? If the problem is high page I/O latch waits, meaning you’re waiting to read data from disk, first just try tuning indexes first, because that’s usually a much bigger bang for the buck for me than doing compression. Page involves a dictionary, row involves choosing the shortest data types possible, like the engine uses the shortest data types possible for all of your fields. In terms of which ones I ever pick though, I just – I’m like, “Okay, we’re going to use” – I want to say it’s page uses both – but books online would be the best gamble for that. There’s also a stored procedure built in that estimates how much you’re going to save from compression, and what it does is it just goes and compresses the object over in tempDB and sees what the savings is. So if you’re going to go that way, I’d consider column store more than I would row or page compression, because column store’s got some amazing stuff starting in 2016.
Brent Ozar: Mike says – Gary follows up with, “I’ve never seen row compression save space. In fact, it usually makes it larger.” I’ve seen it save space where people had really crappily designed data types that – and I wasn’t allowed to change the data types.
Richie Rump: You wound me, Mr. Ozar, you wound me.
Brent Ozar: I didn’t see it here, I saw it at the last – not even my last company, two companies ago.
Brent Ozar: Let’s see here, Brent follows up on the compression question. He says, “Perfect, thanks, the devs were hoping to save space on disk; their ETL keeps filling up the drives, they thought compression would save us from having to ask for more drive space.” No – oh, I’ve so many bad ideas there and I shouldn’t voice any of them out loud.
When should I add more cores to my VM?
Brent Ozar: Gary says, “Is there a threshold of CPU use other than 100%, where we should consider adding cores to a virtual machine?” Yes, so the first thing you look at is SOS_SCHEDULER_YIELD waits. If your top wait type is SOS_SCHEDULER_YIELD, then that means that SQL Server is waiting on CPU time. We’re specifically talking about measuring wait time here, not the number of waits, but wait time. If the majority of wait time isSOS_SCHEDULER_YIELD, then you can either tune queries, tune indexes, or add cores. I would much rather tune queries or tune indexes because those are “free” because your boss considers your time as worthless.
Richie Rump: You also get the biggest bang for your buck, right? So you get more data in there and all of a sudden these queries are going to start acting – it’s still going to be acting funky, and then, “Oh, why is this slow again?” “Oh, why is it always slow?” And then, “Let’s throw some more hardware at it” and you haven’t fixed the problem.
Brent Ozar: Well, and even throwing hardware at it, say you double the number of cores. Best case scenario, that makes the queries twice as fast. You add the right index, that can go ten, a hundred times faster. It makes such a much of a bigger deal.
What do you think of EMC Avamar for backups?
Brent Ozar: Kush says – good to see you Kush, haven’t seen you in a while. Kush says, “What are your thoughts and experiences on using EMC Avamar SQL agent in order to backup databases? We have over 100 SQL Server instances.” Look at Richie’s face. So I have a weird – I’ll play devil’s advocate. I’m the weird DBA who says, I would often like to hand over backups to someone else who knows what they’re doing and has the time to fix backups when they fail. The problem is the key is there. They have to know what they’re doing because most of the time when you hand backups off to another team, they don’t know what they’re doing, they don’t trust you with readable access to the reports to know that your backups are working, and then they take forever in order to do a restore because you may have to wait for them in order to get back before you can start your restore process.
Richie Rump: You got to put a ticket in first, Brent.
Brent Ozar: And the ticket’s only supported nine to five.
Richie Rump: And then you need your boss to expedite the ticket and then he needs to talk to their boss and then all of sudden two weeks later you’re like, “Look, the data’s there”.
Brent Ozar: And the only woman who knows Avamar is gone for two weeks. She’s on sabbatical, and the guy who was supposed to train as her replacement, he’s out for pizza.
Richie Rump: This kid got sick, he went home early.
Brent Ozar: It’s tough. And it’s not that I’ve heard anything bad whatsoever about Avamar or Backup Exec or Data Domain, or any of these – well, Data Domain I have heard bad things about. But in terms of like the technology being broken, it’s just that when you hand over the keys to someone else to do a task, just make sure you got some kind of enforcement to make them do that task quickly. But yes, I do kind of love having someone else do backups as long as they’re capable. Just the same reason I love Azure SQL DB doing the backups for me, and Amazon RDS, like Aurora doing the backups for me. It’s just one less thing that I have to worry about. Do I still have to worry that they’re doing their job? Sure, but you know, you have to worry when I’m doing my job. I’m not always good at my job.
Richie Rump: That reminds me, I need to check the backups on the Aurora.
Brent Ozar: No, it’s totally automatic.
Do I need to fully qualify my table names?
Brent Ozar: Joe asks, “Do one part names, like if I’m going to ask for my table by just table name instead of dbo.tablename, do those still cause compile locks? My devs are terribly lazy and they only use one part names for everything.” You know what you want to do, if you want to teach your devs and you want to scare the holy hell out of them, what you do is add another set of tables in a different schema but with the same name, and then you set their default schema to be that and just play stupid. “I don’t know who did that, could have been anybody on this server. Everybody’s sys.admin, you guys all have permissions. Wow, it’s a shame all your code broke.” Mentally Richie is making a note to punch me in the mouth.
Richie Rump: You are evil, because I’m one of those guys. Because I didn’t grow up in the Oracle landscape where you needed to, you know, fully qualify that – I don’t qualify now [crosstalk]. If the DBA sat me down and said, “Hey look, this could be a problem under these circumstances, I would like for us to start fully qualifying all these table names because of X, Y and Z.” Then yeah, okay, let’s put that as part of our procedure and let’s go through and do all of that. But if we’ve set all our defaults and all that stuff, and they shouldn’t have access to production, let’s just put it that way, then there really shouldn’t be any reason why we shouldn’t have used one. Now, his question, does this still cause compile locks?
Brent Ozar: Yeah, so I can’t remember about compile locks, but it can cause multiple entries in the plan cache, because each person can get a different default schema. So even if they have the same default schema, SQL Server may end up caching the different plans individually. So it causes, and this is where I’ve got to have an asterisk up here, it can cause plan cache bloat. It’s just that in the year 2017, I don’t usually see that as a really big problem when people have, say, 64GB of RAM or more. I see plan cache pollution as a problem when people have like 4GB or 8GB of RAM. But then, we just have the discussion that, “Meet Mister Desktop, Mister Desktop has 64GB of RAM in the year 2017, so should your SQL Server.”
Richie Rump: Yeah, and the other thing to add onto that is that if you’re in application and the application is running these queries, you’ll only have one login.
Brent Ozar: Yeah, that’s true.
Richie Rump: So if you’ve got users that are logging in running these queries, that’s one thing, but you’ve got an application, it’s running all of that under one login, you’re not going to get that plan bloat. So I don’t see a huge problem with it, you know. If someone thinks I’m wrong about that, please @ me on Twitter because I’d love to find out if I am. But I’ve had people come up to me an start yelling at me that, “Hey your demos did not have the schema and it’s wrong…” And all that stuff. And I’m like, “Are you new to SQL Server?” And he’s like, “Well I’m typically an Oracle guy.” And I’m like, “Well there’s the door, and you could go at it, and you can give me 20 bucks on the way out because you can afford it.”
Brent Ozar: Someone in here will say that they had a problem once that was solved by fully prefixing their tables, I’m sure that it’s true. I haven’t seen it as an episode in my career when I’ve said, “Oh thank god we fully prefixed our tables, or else we would have been dead.”
Richie Rump: Yeah, I’m almost 25 years deep into SQL Server, so…
Brent Ozar: And, even if it is a really big problem for some folks, I would just say guide them to doing it from here on out, but it’s not like going back and changing all your code’s going to give you any kind of performance benefit that you’re going to be noticing there. But you’re also talking to the same guy who produces a piece of code called sp_Blitz. And if you listen to the blogasphere, they’re like, “Oh my god, it’s infinitesimally slower because SQL Server looks in the master database.” I’m like, “Yeah, that’s where it usually lives, because it’s a stored procedure that’s run by the database administrators.” “Well you still shouldn’t do that, it’s bad practice…” Yes, so is wearing socks that don’t match your jeans and I do that as well.
Richie Rump: I mean, the last thing I want to throw out to that is if you are communicating with developers and developers are writing and querying code against your SQL Server, then you need to document that. and you need to have a specific documentation that says, “This is how we write queries and these are the rules that we have.” So that way, when somebody does something that you don’t like or that you don’t like, then you’ve got something. “This is our rules, our document, that says we’re supposed to do it this way; go do it that way.” As opposed to, “That’s not best practices and where are these best practices?” [crosstalk]
Brent Ozar: They’re in my head.
Richie Rump: Write it down.
Brent Ozar: So true story, we had a client a while back who was a real big believer in the best practices type thing, and they’d even written triggers that if you tried to do a SELECT* they would get rolled back. If you tried to do any kind of create stored procedure or a function that SELECT* anywhere in it, it would get rolled back. I was like, “Really?” If you put like three spaces between the SELECT and the *, it would go right on through. You’re just literally looking for the string. Put an enter in between, anything like that…
Richie Rump: Hey, parsing is hard, Mister Ozar; parsing is hard.
Brent Ozar: I get that.
What SQL Server books do you recommend?
Brent Ozar: Thomas says, “Hey Brent, have you guys come out with a blog post listing the best books to have on your bookshelf? I have all of them from an older post, but I haven’t noticed anything since 2016 came out.”
Richie Rump: I’m assuming technical books is what he’s talking about, right? Because I could go deep into other stuff…
Brent Ozar: I was going to say, what were the last three books that you bought?
Richie Rump: Agatha Christie’s Murder on the Orient Express. I read that in the jury room…
Brent Ozar: Recently? Oh wow, sure.
Richie Rump: Yeah, it was a good read, a short read. Patrick Rothfuss, The Name of the Wind. Phenomenal fantasy book, and it’s not what you think if you’re thinking Dungeons and Dragons type stuff, it’s not what you think; it’s great. They just came out with a 10th-anniversary edition of that. And then, I forget the guy’s name but it’s a book on smart baseball. It’s on, kind of, analytics and approaching baseball strategy using analytics, type stuff. Those are the last three that I’ve read.
Brent Ozar: And when you want to learn technical stuff, where do you go?
Richie Rump: Since I’m always learning technical stuff, is that what the question is? I usually hit the documentation first. And there’s a problem with documentation, especially when we’re talking about new libraries that keep changing, is that you could have version issues with the documentations because most open source projects don’t keep previous versions. And I only say this because I hit this yesterday, because I had a new version of in one project and then the old version in another project. So everything was working right with this one code. The code was exactly the same in both services, so it was working right here but it wasn’t working right over here, and it was the stupid version number on the library. Brent was laughing at me actually. It was, ha, ha, ha, ha on Slack. That was not cute.
If I really need to learn something like brand new huge type of stuff, like Python – I’ve never used Python, I want to learn Python – I most likely will go to some sort of video training, and that’s like Pluralsight. If it’s SQL Server-based, I’ll even use our own training. People use our trainings, Brent; it’s amazing.
Brent Ozar: I don’t know about that.
Richie Rump: And then there are some – if I need something that’s core knowledge, that’s not so new, I need to learn the newest version; like maybe some computer science stuff, maybe some algorithms and security type stuff, then I’ll hit a book. But those are usually my steps.
Brent Ozar: The problem is that books are so outdated. They’re so good for core concepts, algorithms, design patterns, you know, scaling, sharding, whatever. But in terms of like SQL Server 2016 and 2017, books are so far behind. It takes a year to write a book, you’ve got to get through the editing process, then you’ve got to ship it. By the time it comes out it’s already outdated. A classic example is Alan Hurt has been working on a SQL Server clustering and high availability book for like four years. And every time he gets close to the finish line, they ship another version of SQL Server and he goes to add that into the book. So I haven’t bought a SQL Server book in maybe three or four years.
There’s two that are actually on the road to me from Amazon that I’m kind of excited about that are around design patterns, but I haven’t seen anything that I would buy around 2016-2017. My most recent book was the WD~50 Cookbook. Not WD-40 but WD~50 Cookbook by Wylie Dufresne; utterly gorgeous, just beautiful.
Richie Rump: There’s a book that actually I was looking at, and you’ll probably see the receipt here pretty soon – I haven’t bought it yet but I’m going to buy it. It’s called The Imposter’s Handbook, a CS Primer for Self Taught Programmers.
Brent Ozar: That’s cool…
Richie Rump: So Rob Conery – maybe you’ve heard of that – he actually built a video training company and sold it to Pluralsight. Well, he went ahead and wrote this book based on computer science type stuff, and it’s designed for people who don’t have a CS degree. And there’s videos along with it and all that stuff. So it’s called The Imposter’s Handbook. That’s the type of stuff that I would get as a book because it’s all core knowledge; it’s stuff that’s not going to change.
Brent Ozar: Let’s see here, Gary says, “You wear socks that don’t match? That’s against our best practices; you need to fix it by the end of the day. Absolutely, I’ll fix that by taking off my pants.
Have you heard anything about Veeam backups?
Brent Ozar: Chris says, “Backups aren’t my job. The guy sitting across from me is implementing Veeam now. Have you heard anything about it for SQL Server? Yes, we’ve even done webcasts for Veeam, actually, years ago. I like Veeam a lot, I think it’s pretty cool. There have been versions with gotchas, but the stuff that’s out now is pretty solid. The big thing to be aware of, though, is make sure you understand how point in time recovery works and to what points in time you can actually recover. Some people will set up backups say every hour and that’s it, and they’ll accomplish it via VM Snapshots with Veeam. Just know that those are the only points in time to which you can recover.
Richie Rump: I have a Veeam bottle opener somewhere in this office. That’s all I know about Veeam.
Brent Ozar: If you’re going to know something about it, that’s not so bad.
Brent Ozar: Michael says, “Happy Thanksgiving. Thankful for all y’all do. Thankful for Brent, Richie, Erik, and Tara. Avamar and SQL Server run, for me, full local native backup and they keep these local, and then they let Avamar be there for restores that are older than one day.” Yeah, I’ve seen that use. I’ve also seen people like backup to a file share and then Avamar backs up the file share as well.
Bad idea stories
Brent Ozar: Brent says, “I think your bad ideas make for great office stories though…” Okay, so one of the bad ideas, for like the slow ETL process with no disk space was I happen to have a client where the servers had a whole lot of RAM but they didn’t have a whole lot of disk space. We’re talking like half a terabyte to 2Tb of RAM, but only half a terabyte worth of disk space. So we bought RAM drive software, and I’ll let you figure out the rest from there. [crosstalk] Because it was ETL. It was like, you know, we didn’t care of the thing goes away. I’m like, alright…
Richie Rump: So wait, so you have a problem with disk space, we’re talking it’s like half a terabyte and yet the new phones come close to that? I don’t understand. It just doesn’t…
Brent Ozar: Brent says, “Was it faster?” Ironically a lot of RAM drive software isn’t actually all that fast. They have like single threaded limitations and in most cases, I can get good San storage to actually go faster, or good solid state to go faster. You would think it would be blazing fast because it’s all RAM, but RAM drive software tends to be kind of crappy.
Brent Ozar: Rick says, “USB RAIDed RAM…” The problem is, unless it’s USBC or USB3, you can hit some bandwidth limitations, and it’s really sad that I know that.
Richie Rump: Yes, it really is.
Should I enable hot swap CPUs in VMware?
Brent Ozar: Gary asks, “Do you have any experience with the impact of using hot swap CPUs? I found out it was turned on and I had CXPACKET waits.” Yeah, there are some gotchas around hot swap that don’t let VMWare expose the numa information up to the guest. Now, unfortunately, that’s beyond what I can answer fast, but if you search for Frank Denneman, he has a VMWare book and it’s absolutely phenomenal. He even goes into configuration details for SQL Server with that exact issue. Totally free too, right now, it’s sponsored by Rubrik.
Richie Rump: You know, you just recommended a book for a guy who says , “I don’t really read books much.”
Brent Ozar: It’s funny, and when it came out – Frank poked me on Twitter and he’s like, “Hey, I got a new book out.” And I’m like, “Alright, well if it’s free, I’m going to go read it.” And all I did was hit Ctrl+F for SQL and it’s like a 400-page book. I’m like, “This is actually good information.” Now, did I read it? No.
Richie Rump: Alright, there we go.
How should I monitor 20 SQL Servers?
Brent Ozar: And the last one that we’ll take is Landon says, “Do you have any suggestions for how to best monitor over 20 SQL Servers? I’ve been researching a bunch of tools like Redgate SQl Monitor, Idera SQL Diagnostic Manager or Quest’s offerings, but I wanted to see what you’ve been most impressed with over the years.” The three tools that I see people having the most success with are Idera SQL Diagnostic Manager, Quest’s Spotlight and SentryOne’s Performance Advisor; they keep changing the name of it, I think it’s called SQL Sentry right now. So Idera’s, Quest’s and SentryOne’s – those are the ones I see people having the most success with. There are other tools, I’ve just seen a lot of people who aren’t as successful with them because a lot of them don’t give you a whole lot of guidance on what does a specific number mean.
For example, they just show you that you have a CXPACKET of 500 seconds in an hour span; what does it actually mean? Those three tools seem to do a better job of getting you towards root cause analysis. They all have about the same overhead, they all cost about the same, they all do about the same amount of stuff. The way that I would do it is go get price quotes from all three of those, then pitch them against each other and see which one will knock the price down the cheapest.
Brent Ozar: Well thanks everybody for hanging out with us this week, and we will see you next week on Office Hours. Adios everybody.
