[Video] Office Hours 2018/8/29 (With Transcriptions)


This week, Brent, Erik, and Richie discuss Microsoft cumulative updates, AlwaysOn Encrypted, query tuning, poison waits, the DBA career, CXPACKET waits, THREADPOOL issues, reporting services, and more.

Here’s the video on YouTube:

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 Webcast – 2018-08-29


Brent Ozar: John asks a question that is a stumper. John says, “Microsoft released 2017 Cumulative Update 10 yesterday. I tried to look through the hotfixes included, but I don’t see any reference to the brand new security hotfix they just put out. How can we tell if CU10 includes the security hotfix or not?” None of us know. For those, if you’re only listening to the podcast, we’re all doing various interpretive dance here.

Erik Darling: Yeah, you have to install it to find out what’s in it.

Brent Ozar: Yeah, and then you would even have to know how to trigger whatever the GDR hotfix was, how to trigger whatever thing it’s doing in order to improve your security. That is, what we call, disappointing.

Erik Darling: Yeah, well I mean, Microsoft’s documentation is supposed to be open source, so you could maybe ask them to improve upon that.

Brent Ozar: Or, you could submit two pull requests. You could submit one pull request that says it has the security fix and one that says it doesn’t and see which one they accept.

Erik Darling: Yeah, play both sides of that coin.

Richie Rump: You don’t ever do that in my code base, Brent Ozar. You don’t ever get to do that.

Brent Ozar: Oh god, I check in some pretty crappy pull requests, I will say that. Richie found some of my terrible SQL the other day and he’s like, “Brent, this can’t possibly be right.”

Richie Rump: I’m not saying I spend all afternoon fixing that yesterday. I’m not saying that at all; none whatsoever. But the unit tests passed and that’s the important thing.

Erik Darling: Ladies and gentlemen, we have a man who has eaten a $1000 pizza by telling people to make their queries SARGable using upper in a where clause. That’s where we’re at.

Brent Ozar: You know what, it wasn’t – even worse, I’ll raise you more than that. I’ll raise you a $1000 pizza over that. It wasn’t in the where clause; it was in a join.

Richie Rump: It was in a join. It totally was in a join.

Brent Ozar: I was uppercasing two sides of a join. And, of course, Richie, god bless him, has to keep the poker face when he comes in and asks me, “Hey, Brent, can you tell me a little bit about what’s going on?”

Richie Rump: No, I’m pretty sure it was, “It’s Brent Ozar’s fault. Look at this line…” And then he goes, “We need this.” And I’m like, “Okay.” And I start working some derived table magic and make it look like Frankenstein’s query.

Brent Ozar: That’s not pleasant.

Richie Rump: But it went from like a minute 20 with this one particular set of data, and it went to two seconds and I’m like, okay, we’ll call this one done.”


Brent Ozar: Yeah, on a related basis, Nick also asks – he says, “It seems like the quality’s been going down on Cumulative Updates lately. We saw the ones recently where they pushed out a Cumulative Update and then rolled it back for the security fix, pushed out again another change and rolled it back. Do you have any comments on that?” My thought is, yeah it does seem like the monthly cadence for patches is a little bit more than SQL Server can handle right now, or a little bit more than Microsoft’s testing seems to be able to handle right now. I don’t blame them. It’s hard. There’s a huge surface area to cover, you know, but yeah. I have a much lower confidence level in Cumulative Updates than I used to have.

Richie Rump: And we’re a small shop, right. I mean, we just have a code base that we started a year and a half ago and I’ve got, you know, a whole slew of unit tests and it goes through. And it just this last week alone, I go and put a fix in and then something else breaks, you know. Everything passed, everything looks great, but something else that we didn’t consider came up in fix. So imagine something a code base, a freaking SQL Server that’s been around for 30 years and how you would test all the permutations of all the crazy stuff that we see out in the wild. That’s really, really hard, especially when you’re trying to pump these things out as quickly as possible.

Erik Darling: I mean, we do a monthly release of the First Responder Kit, but thankfully the change churn is a lot smaller. I’m not saying, like, everything always goes well 100% of the time. There’s obviously some craziness out there in the world that we can’t account for either, but I’d like to do some due diligence testing and at least make sure everything compiles without too much red text. That’s just always a good sign.

Richie Rump: Are you guys hearing all that noise in the background?

Erik Darling: No…

Richie Rump: Oh good, my audio is – my parents are getting a screened in pool and they’re installing it right now, so they’re drilling all over the place.

Brent Ozar: Wow, nice. Are they using that with your rent money? They’re taking your rent money and putting it towards it?

Richie Rump: No, my rent money is still going to my house to my mortgage I still have to pay even though we’re not living there.


Brent Ozar: Tammy asks, “What are some reasons not to use Always Encrypted?”

Richie Rump: I guess the question for me would be, what are the reasons you should use Always Encrypted?

Erik Darling: What do you need to encrypt? What are you going after? Do your queries need to search encrypted things? These are all questions that I would want to ask up front.

Brent Ozar: Do you use linked servers or replication with it too, because that can throw some monkey wrenches in your ability to replicate that data from one place to another and decrypt it on the other side.

Erik Darling: Like, do you have to restore stuff to dev or do refreshes and stuff, because then you have to deal with whatever certificates, moving those things around. Obviously, security makes things more complicated. I know from the fact that last week, I entered an RSA token roughly 3000 times. But yeah, obviously, security makes things far more complicated. You know, if you want reasons not to use it, because it makes your life more complicated. If you want reasons to use it, because it probably may not get you fired having some.

Brent Ozar: And I know a lot of shops that needed encryption and they already rolled I in the app layer before SQL 2016 came out or when it was still Enterprise Edition only. So if you already needed it…

Erik Darling: What’s that plug-in – NetLib – that does the TDE?

Brent Ozar: Yeah, NetLib Encryptionizer, I think it’s called.

Richie Rump: Yeah, and some of the projects I’ve worked on, they just encrypted the data that needed to be encrypted, as opposed to just kind of doing everything. And with TDE, of course, you don’t lose the cert – I mean, there’s a lot of things that you could really screw up on some of that stuff.


Brent Ozar: Lee says, “Thank you all for the First Responder Kit and the other information and scripts you share. I’m being given new servers that the vendors are abandoning to use, so all these things help me get a handle on the new-to-me servers and fix the weirdness of vendor installs.” You’re welcome; Erik busts his hump on that.

Erik Darling: Vendors are the worst…

Brent Ozar: Especially us.


Brent Ozar: Pablo asks, “What influences SQL Server to choose between a hash match and a nested loops join? Identical dev and prod servers are showing this difference and one query took five minutes on one side and two hours on the other side.”

Erik Darling: Boy, there are so many things that could be different between dev and prod. It’s a terrifying question. I would start looking at the plans themselves. Like, you know, aside from just looking at the join type, look at how many rows we’re expecting from one place to another. Beyond that, look at what indexes are getting used. So cardinality estimation is going to be one, indexes in use are going to be one. Obviously, there are some join strategies that work better with indexed data, where has joins can excel with unindexed data, because it’s going to hash all the values anyway. Memory is another big one; available memory. If SQL Server is, like, no, you know what, we’re just not going to get a good enough chunk of memory to do this has join. I am going to go with this nested loops join. So there’s all sorts of strange considerations. There’s a lot to look at for you, young man.

Brent Ozar: Damn, the memory one’s a good one. I forget about that.


Brent Ozar: Gordon says, “This isn’t really a replication question…” Gordon, you’re pushing the limits. Gordon says, “I’m looking at migrating an on-premises environment including merge replication up to Azure SQL DB. Would SQL Data Sync be a replacement for the replication?”

Richie Rump: Do they have that in AWS with Postgres; I’m confused?

Brent Ozar: Merge replication – my question would be, what are you doing merge replication for? If you’re doing it for high availability between two places then Azure SQL DB kind of replaces that. If you’re doing it to let people, like, change stuff on-premises and change stuff up in the cloud and keep it in sync between the two, I would not do that. I don’t have any good points I would – like, so many times, I see people doing merge replication and they’re doing it between two shoe boxes. Like, one box can’t keep up with the load, and I look and it’s a VM with four cores and a VM with three cores and they’re like, we had to scale across separate boxes; maybe scale up a little bit?


Brent Ozar: Steve asks – he says, “ConstantCare sent me a poison waits email. Can you talk about poison waits a little?”

Erik Darling: Which one?

Brent Ozar: I should go look at Steve’s data. Hold on, let’s see.

Erik Darling: You should. Let’s pause for a moment.

Richie Rump: Yeah, we have, what, three poison waits warnings in ConstantCare now?

Brent Ozar: We do, so let’s see. He has – the one he’s getting is – I don’t see poison waits in that one. Where on earth – which poison wait is he getting? Steve, tell us about the one you’re actually getting.

Erik Darling: Steve, you’ve confounded us.

Brent Ozar: Yeah, I’m going in to look – oh, resource semaphore. Okay, so we got one on resource semaphore.

Erik Darling: Ooh, you’ve got some memory issues. More specifically, when SQL Server goes to run queries, it has these queues of memory that it sticks them in. And when certain queues fill up, they’re prioritized and they will hold on to – and other queries have to wait for those queries to finish and give up their memory so they can run. When you run into that, you’ve got resource semaphore. So it’s a memory wait and it’s caused by a mix of queries asking for large memory grants and queries asking for any memory grants getting prioritized.

So usually, what you want to start doing is, if you’re on a version of SQL Server that supports it, maybe Brent can tell me from the magical data – if you’re on, like, 2016 or 17 or 2014 with a Service Pack or 2012 SP3 or something, you can start looking at memory grants. You can use sp_BlitzCache and you can sort by memory grant and you can start looking at what queries are using a lot of memory. But that kind of downside to this is, if you’re hitting resource semaphore waits, you are most likely going to be hitting some cleared plan cache issues. So, like, when SQL Server goes to use memory for a query, if you are running out of it, like instance-wise, you don’t have enough memory to cache your data and then you don’t have enough memory to also run these queries, that memory has to come from somewhere.

The stuff that gets cleared out when a query needs memory is the plan cache and the buffer pool. One query can ask for, by default, up to about 25% of max server memory. And queries as a whole can actually take just about 75% of your server memory running all at once. So you could be clearing out a significant amount of plan cache and buffer pool data to run these big queries.

So take a look. If you can, run BlitzCache. Figure out what’s big by memory grant, if you’re on a version that supports it, start looking at those. Otherwise, you’re stuck running some crappy DMV queries that just constantly look at what’s running in the memory grants there.

Brent Ozar: And you also don’t take biblical action based on what comes out of this sp_BitzCache just the first time you run it. Because remember, like you said, maybe your memory’s been cleared out or parts of your buffer plan cache have been cleared out. Run this a few times over the course of several days, just to get a rough idea of which plans are in there recently.

Erik Darling: To kind of get a feel for that, Brent, you don’t mind scrolling down a little bit in the second window, there will be a little bit of information about what’s in your plan cache. So it will tell you how many plans you have and what percentage of them were created over different chunks of time. So if it’s, like, a lot of plans getting created in the last four hours, then obviously you have less plan cache stability than you’d want to start making those big decisions based on.

Brent Ozar: Good point.


Brent Ozar: Next up is [Xuan], I believe it is, says, “How can I grant users without sysadmin roles the rights to modify agent jobs that are owned by SA?” So like, they want to play around with agent jobs but not give people SA.

Erik Darling: I don’t know that you can.

Brent Ozar: I want to say that isn’t there a role for – I’m in the wrong place, of course. I think there’s a server level role for agent stuff. I could have sworn there was.

Erik Darling: I don’t know, but if you’re running into the situation where you have so many people who need to mess with agent jobs, I would just want to give them an agent jobs server for themselves that they can just go and do whatever to their heart’s content that doesn’t make them SA or doesn’t promote them beyond the point that you would be comfortable with on the prod server.

Brent Ozar: I adore that so much.

Erik Darling: Can you do that with SQL Server Express, or do you actually need Standard?

Erik Darling: Well, Express doesn’t have agent.

Richie Rump: Oh, that’s right.

Brent Ozar: Diabolical.

Erik Darling: Stupid Express…


Brent Ozar: Samantha, who shares the name of one of my favorite Big Brother people this season – Samantha asks, “Have any of y’all ever reached a point in your career where you doubted if it was the right path for you? There are days where I feel like a rock star and there are days when I wonder how the hell I got here. Does anyone else feel that way? I should have been a geologist; rocks don’t change this fast.” When was the last time – yeah, I’m sure it’s happened to all of us. When was the last time it happened to you guys?

Erik Darling: I don’t – I think, once I kind of got into SQL Server, I knew that’s what I wanted to do. Whether it’s what I always want to do in my life is another story, but you know. I’m comfortable with it as a career path now. I like the rate of change, you know. It’s nice having new stuff to look forward to and learn about. To be honest, if SQL Server is going to continue the every two years thing that they were on, or even longer stretches earlier, I think I would get bored because you sit there and you stare at the same product for however long. Like, I’m sick of 2008 R2. I can’t wait for those to go away.

Richie Rump: Well, but to be fair, you don’t look at the whole product. You look at, especially, the engine, right? I mean, you’re not SSIS on top of that.

Erik Darling: I would be really bored if I was doing that.

Richie Rump: Oh no, absolutely, yeah.

Brent Ozar: Yeah, I hit it big time when I was on Richie’s track. When I was a developer in the late 90s, early 2000s, I was like, I am on the complete wrong track. I suck at learning languages. I love learning; I suck at learning languages. It’s like, syntaxes that are different between platforms, and the more that I sat on the developer track, I’m like, I am never going to get better at this. I don’t enjoy it, so I’m going to go somewhere where I still have to learn but the language doesn’t change. Like, SQL has been essentially the same for forever, so I totally felt that way and that’s when I switched over to databases fulltime.

Richie Rump: I think I was a little different because I started off as a developer. I kind of always knew I wanted to be a developer when I got to college. I was going to do electrical engineering, and then psychology, and I kind of fell into the computer thing and I’m like, writing software, that’s my jam. And then I kind of moved up in the company I was in. I was a senior and then they made me a manager and then I was doing project management stuff and doing all this other manager type stuff. I did that for about five years, and then I’m like, I’m not happy. I don’t really like what I’m doing. I don’t really like dealing with some of these people and having to listen to what they think the problem is. That’s not the problem, and all this other stuff.

And then when I went back to development, I was so thrilled. And I consider working with SQL Server and doing database development, development. So that’s still development in my head, but man, I strayed from my path and I was just so miserable. It was so awful.

Brent Ozar: I think it’s also – I was just talking to a client the other day about this – there’s never been a better time if you want leave database administration. There’s never been a better time to go down all kinds of different tracks. Same thing with development. I mean, the whole world is open. There’s all kinds of different data jobs that you can go pursue if you want to. I don’t want people to think that the DBA job is dead, that the developer job is dead, that database development is dead, anything like that.

These careers are hugely strong. But if you find out that it’s not for you, there’s never been a better time to go look at what else is happening in your organization and go, how could I spend more of my free time doing that the next fulltime position I get is only doing that piece?

And when you write your resume, I know a lot of folks who put one page of qualifications of all the things that they do that they’ve done in the past and three quarters of them, they hate doing. It just burns their soul to have to deal with that stuff. Don’t put it on your resume. Don’t put linked server troubleshooting on the front page of your resume if that’s something that you hate.

Richie Rump: So I’m taking VB3 off my resume right now.

Brent Ozar: Bad news, it’s now supported in serverless. We’re going to move exclusively to Visual Basic.

Richie Rump: In Access 2.0…

Brent Ozar: Let’s see, Samantha says, “My manager wants to make me a supervisor now. I haven’t even reached the pinnacle of my career yet.” Well I hope not. None of us have reached the pinnacle of our career yet. And says, “I love IT. I love DBA. I’ve hit a crossroad.” Okay, so that I really can’t speak to because I went down that path. I thought that there was a ceiling limit on database administration, and when my manager said, do you want to lead the team? I was like, sure, management? Why not? I hated management. I suck at management, as all of our employees can tell you. I am hurp a durp completely at management. It is a totally different skill set than technical and I have huge respect for people who are good at management; people who can manage other people, drive them toward a particular goal or whatever. And if you don’t like it, don’t. there are so many places that you can go technically without having to manage people.

Richie Rump: Yeah, I think I was a little different from your experience, Brent, where I actually did it. I was good at it. I delivered the largest project that the company had ever even approached. But what I didn’t like was having to deal with middle-management because there’s such a power struggle. Dealing with the CIO and CEOs, they’re phenomenal. They’re great people. They want things to get done. Dealing with the people that actually get the work done, the lower level workers, they’re great because they just want to get the work done.

And then you get these power struggles within middle-management and I moved into middle-management and it was awful. I mean, people were backstabbing me and they were backstabbing each other and they’re all jockeying to get upper-level stuff. And I’m like, I just want to get this project done. Why are you guys – this is terrible. This is awful. We’re supposed to be working as a team, right? I did a trust-fall thing and you caught me and we were supposed to be a team, and it’s not the way some organizations work.

So I guess, if you think that you’d be good at it and it’s interesting to you, then go for it. Try it out. Maybe you could do it on a trial basis. But if it’s something like maybe, maybe not, I kind of like what I’m doing now, then hang back.

Erik Darling: Until this day, they sabotage Richie’s audio.

Richie Rump: Again?

Brent Ozar: Yes.


Brent Ozar: Let’s see. Joseph says, “Can y’all give a sentence or two about how CXPACKET waits in SQL Server 2016 Service Pack 2 can be caused by bad query estimates?” Let me rephrase it, how can bad estimates cause CXPACKET waits?

Erik Darling: I don’t necessarily think that it’s a bad estimate thing. I think that it’s a bad distribution thing. So usually, when I see CXPACKET waits that are like bad, bad, it’s for one of a few reasons. Either the CPUs are just tanked and they can’t talk to each other fast enough, so CXPACKET ramps up. Because if you have threads are just sitting there saying I need to talk to you but I can’t talk to you yet, they’re getting scheduler yielded all over the place, they’re in balanced power mode or something and they’re just not running up at full speed. They’re running at like half or 75% speed, so they’re not talking to each other as quickly as they should be.

And then, finally, there is just when parallelism is skewed all over the place. And I wrote a blog post about it recently where you can see how that will trigger bad CXCONSUMER waits; a query that ran for two days with one thread getting bajillions of rows. So that’s really where I see it going. I don’t think a bad estimate – maybe if like, you know, SQL Server really overestimates the number of rows that are going to come out of something and decides to use a parallel plan instead of a serial plan then maybe. Because that would potentially lead to the kind of skews I’m talking about where, like, one value or one set of values ends up demolishing one thread and all the other threads are sitting around doing absolutely nothing, or close to nothing.

So that’s what I’d check. The only thing is, that stuff isn’t in cached plans or anything that I could tell you anything useful about or like how to go find it. It’s something that you really have to catch in action. You have to, like, narrow it down to which plans are generating that kind of rotten CXPACKET and then go troubleshoot them one by one.


Brent Ozar: Joe asks, “Similar to a previous question, why would we not want to use row or page compression; it seems like a no-brainer?”

Erik Darling: It’s like – heavily modified tables, I think, have issues with compression. I can’t remember the ins and outs of it because it’s been a long time since I’ve thought about row and page compression in depth. But I think, if it’s not a heap and you insert the data to it then compression is delayed or something, or compression doesn’t kick in until you rebuild or something weird. I don’t know, there’s something odd about it. [Rinchy Shay] wrote amazing blog posts about it ten years ago that…

Brent Ozar: Yeah, and every time somebody asks us that, we have to still go back to the books. There’s also something to do with if the data set already fits in memory and you just incur extra CPU on crunching it, that was another one.

Erik Darling: You know what I always hated about it is, like, when – I was just like, cool, data compression. We’ll use this stuff, we’ll compress stuff and then maybe we’ll do better with memory usage. But as soon as you read that stuff from disk, it’s uncompressed and you need the same amount of memory to manage the uncompressed data. I was like, I really wanted that to be a thing.

Brent Ozar: Plus these days, a lot of times when people are asking about compression – oh, there’s two other things; that it doesn’t compress off-row data, so if you have big strings. But these days, when people ask about compression, I’m usually like, haven’t you checked out column store? If you really want amazing compression, that seems to be a better way to do it with the kind of data that people expect to compress.

Erik Darling: Especially the kind of indexes that people are making when they want the data compression. Like, these big honking indexes with a billion includes and key columns everywhere and it’s never anything pretty. It’s never for a pretty reason that people are like, maybe we’ll compress a few things. It’s always some big awful ugly table that is full of giant, giant columns and you’re just like, no…


Brent Ozar: Next, Jeremy asks, “We’re running Always On Availability Groups and we have a File Share Witness. The policy default is if the resource fails, try to restart a max one time in 15 minutes. What do you recommend?” I don’t think we’ve ever told anyone to change that. I don’t think I’ve even ever looked at it. But the two people I would ask are either Edwin Sarmiento, who teaches Availability Groups classes for us; Edwin Sarmiento or Allan Hirt, who has a book coming out any day now about SQL Server Availability Groups and he may even cover it in there.


Brent Ozar: Steven says, “Any suggestions for tracking down the cause of this error; SQL Server failed to connect with error code… to spawn a thread to process a new login or connection?”

Erik Darling: Yeah, baby, you got THREADPOOL. You ran out of worker threads.

Brent Ozar: And where would you go troubleshooting that?

Erik Darling: Well, let’s see. That’s a tough one to catch, because it’s either happening and hitting F5 is really difficult, or it’s not happening and it doesn’t look like anything of the sort is going on. This is where a monitoring tool – actually no, because monitoring tools blank out for that too. Monitoring tools don‘t have threads.

So this is where the remote DAC comes in really handy, because I would pay attention to when you see this in the error log. I would pay attention to your wait stats. So if you see this THREADPOOL wait inching up, then you need to have the remote DAC turned on, unless you’re connecting locally. But hopefully, you’re not already peeing into a server with THREADPOOL issues and running Management Studio and doing other stuff. So I’m going to assume that you’re a good person and you’re running remote SSMS to get into the server.

Turn on the remote DAC, get in there, use sp_BlitzWho or sp_WhoIsActive. Hit F5 when things start to go south or if you notice things getting weird, and you’ll be able to catch all the THREADPOOL goodness in action. THREADPOOL is, like I said, running out of worker threads. Don’t try to adjust the max worker threads, sys.configuration_option, because what that does is basically – picture 30 kids screaming in a classroom. If you turn that up, you just add more screaming kids to the classroom. It might fix the THREADPOOL, but your CPU’s not just overwhelmed with screaming kids. So don’t do that.

It really comes down to, usually, figuring out if the number of CPUs you have is good for your workload, which if you’re hitting THREADPOOL it most likely isn’t. And then, of course, tuning queries and indexes to prevent the kind of awful blocking scenarios that go on, because typically, when we see folks hitting THREADPOOL issues, it’s a lot of parallel queries starting up, running, reserving threads and getting blocked. And those threads don’t get given back, right. Those threads are reserved for those queries until the queries are done with them. So you just have all these queries piling up, reserving threads, blocked, sitting there doing nothing. The server might look bored too because it’s not doing anything. But anyway, that’s where I’d look.

Brent Ozar: Screaming kids, I don’t know that we’re ever going to beat that analogy; a group of screaming kids, just adding more screaming kids to the room. That’s fantastic.

Erik Darling: Yeah, that’s all it is.


Brent Ozar: And the last question we’ll take for today, Darshan asks, “For reporting purposes, we created a table and we dump data into it so users can query their PowerBI reports with it. Now the table is huge, so what would be the ideal solution to move it?” The thing I would say is, define what huge is, because for the rest of your life, you’re always going to be working with the largest table you’ve ever worked with.

Generally, when they talk about a very large database, a VLDB, the general standard is a billion rows in one table or 1TB worth of data. So when you say huge, generally, you’re talking over a billion rows in it. He says, “Should I think about moving it to Azure SQL DW?” You could. There are all kinds of things that you can think of that point architecturally.

But when you come to the point where you’re thinking about a different database platform or different backend for reporting, then I would stop to think about bringing somebody in who’s done it before. And that’s not a sales pitch for us because we don’t do data warehouse architecture either. But you’re getting ready to make a decision that will haunt you for the next five years or more.

Bring in a specialist on the platform you’re thinking about working with just because if you only read Books Online, every one of them looks like it can handle huge datasets. You want to find somebody who’s done it and found the gotchas.

Richie Rump: And, you know, the add-on to that, I consulted with the company that brought in Teradata and they spent millions on this server; millions. And then things weren’t performant and it’s supposed to be fast, but because of the way they architected the data, it wasn’t. There you go.

Erik Darling: Yeah, if you’re on a version that supports it, I would want to look at column store for that very big table, just to see if it continues being very big, because it sounds like if you have that kind of table where folks are dumping lots of stuff in, that’s not going to be a long table; that’s going to be a wide table. There will be ass sorts of dumped badly named columns in there. So I would want to look at column store, either clustered or non-clustered, especially because you’re saying Azure SQL DW, those like pretty much your only options once you get up there. So if you want a preview of how things might look there then column store on-prem, if you’re available, would most likely be a good way to test that out.

Richie Rump: And for the record, Brent, ConstantCare now has over a billion rows in one table…

Erik Darling: What kind of compression are you using?

Richie Rump: What’s that? I don’t know, never heard of it.

Brent Ozar: And I can’t pull any of them down with PowerBI. It’s so funny, PowerBI, all the ads are like, just connect in and get all the data. That is not how it works. Alright, well thanks, everybody, for hanging out with us this week and we’ll see y’all next week on Office Hours.


  • This field is for validation purposes and should be left unchanged.

Previous Post
First Responder Kit Release: What Does A Fish Know About Friday?
Next Post
Building SQL ConstantCare®: You Should Probably Learn Power BI. I Did.

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.