This week, Brent and Richie discuss using full-text index, Elasticsearch, server restart issues, sp_blitzfirst, Always On availability groups, issues with users connecting to SQL Server, learning the basics of Python on SQL Server 2017, VMWare Hyper-V configuration issue, 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

Should I use SQL Server’s full text indexing?

Brent Ozar: Ron asks, “Recommendations for using full-text index. My database is about 50GB. I’m going to want to index about five tables with about a million rows each. 30% of the database is images, which doesn’t need to be indexed.” So I guess 70% times 50GB – about 35GB. Richie, if you wanted to full-text index 35GB worth of data, what would you do?

Richie Rump: Elasticsearch.

Brent Ozar: And that – how do you go about implementing that or where would you recommend that someone go read for more information about how?

Richie Rump: I don’t know, I guess Pluralsight would be as good a place as any to start. You know, because at least then you could get up and running really, really quickly, at least on easy scenarios and at least to get something out there and cranking. The one thing you do have to worry about is security; if you have any data that need to be secure, I’m not quite sure how you would necessarily handle that if it needs to be encrypted or something like that. I think they’ve got some stuff in there, but I haven’t taken a look at it since I started working here, oddly enough. So it’s at least three years or something like that, but yeah, Elasticsearch, there’s so much more you could do with Elasticsearch than you can with full-text search in SQL Server.

I mean, just the querying scenarios alone are just – they’re amazing, they’re phenomenal. You could do querying from URLs. I mean, it is, right now, the gold standard if you want to do any sort of things like that. So, take a look in Elasticsearch. I guess Solr is another one, but Elasticsearch is just a step ahead of Solr at any given time, so yeah, check that out.

Brent Ozar: You might be wondering why we don’t recommend SQL Server’s full-text indexing. It’s fine if you need to get started and you can literally only have one box. Like, they’re like, sorry, you can only have one server; that’s all you can ever have. But you can run Elastic on VMs, just as long as you can implement cheap VMs. It’s open source, you can run it on Linux, it’s free.

Richie Rump: Yeah, and you know, just from the what you can do from the query scenario, I mean, if you want to query – there’s like keywords you can’t use inside full-text search. And I mean, you start scratching your head, like, why can’t I do that? That doesn’t make any sense. Well, you can do it from a URL perspective in Elasticsearch and it’s all there and you can get pretty complicated, you know, searching scenarios, digging into certain things. It’s a big rabbit hole once you start getting down that, but man, it’s a fun rabbit hole when you start figuring stuff out.

Brent Ozar: If you’re going to start something in the year 2018, you know, it makes more sense to go that way. One of the bumps we hit at Stack Overflow was you couldn’t search for a term like .NET 3.5; that combination of things, just punctuation and all that.

Richie Rump: Yeah, and I believe you do need to move the data over to Elasticsearch server. It’s not like it can plug into SQL Server and everything is grand. So you will have to worry about getting data out and those types of things. But man, the power you get compared to what something you get in SQL Server, which they haven’t touched in how long, Brent? Was it 2005?

Brent Ozar: No, they did a redo of it in 2010 or – I think it was 2010. In 2008 R2 was when I think they did the redo, but…

Richie Rump: But even then, it just pales in comparison to what we can do these days with Elasticsearch and Solr.

Brent Ozar: This is also why you see people getting so excited about NoSQL databases. I was just talking to a user group last night about that. they said there was all this buzz about NoSQL databases and what is it that they can do that we can’t do. And they just make design choices. For example, with Elasticsearch, there’s not really the concept of security and auditing, it’s just anybody who is on the network could make queries against that server. And for a lot of stuff, that’s fine. You know, for a lot of kind of full-text stuff that you’re doing, products in a catalog et cetera, it’s totally okay and is so insanely blazing fast because that’s what it’s designed to do. With SQL Server’s full-text search, they kind of stapled it into the database engine.

Richie Rump: Yeah, and then you inverse that, right. And it’s like, if you want to start storing data in Elasticsearch, that doesn’t make any sense whatsoever, you know. So when you start looking at the specialized tools to do specialized things, like searching or document storage and things like that, that’s what you use NoSQL for and it’s in addition to what you need to do with SQL Server, not instead of.

Brent Ozar: That’s also a great way of saying it because I believe that if you could only pick one database, like if you’re only allowed to run just one data persistence layer, you’re only allowed to use one database, I think you could do a lot worse than SQL Server. SQL Server does a lot of things really well. But as soon as you start going, my needs are high enough that I want to add in a different layer or a different data persistence area, then that’s where these other solutions are phenomenal.

Richie Rump: And that’s where you really need to get to understand what my requirements are, what my needs are, what are the needs of the business in the future and those types of things, you know, and really pie in the sky. Well what do we really need to do and if I get this one thing done and it’s really successful, what’s the next step? And if they tell you, well we want to do more of that, then it’s more like, well, SQL Server can’t do more. I mean, we’ve hit the limit at version one. So at that point, would it make sense to go further out and what not? So these are the types of questions that you need to start asking your business and the people who know the functionality because you probably won’t know right off the bat. You could just say, oh yeah SQL Server will get that done, but maybe in their head, two years down the road, they’re thinking something else so you will need that room to grow.

Brent Ozar: Yeah, and I don’t think full-text is an area that Microsoft is really investing heavily in, in SQL Server. I think they’re adding lots of functionality to different areas; I don’t think full-text search is a big area of focus for them.

Richie Rump: You know, it’s one of those things, you’re not really growing, right. I mean, you kind of understand the scope of everything and Elasticsearch kind of handles all of that and they’re not really adding a ton of new features into Elasticsearch either. I mean, it kind of is what it is, so…

Brent Ozar: Yeah, does what it says on the label.

My SQL Server restarted. Where should I look?

Brent Ozar: Pablo says, “Ola amigos, the last four days, my virtual SQL Server got restarted due to an energy outage. No Windows logs about it but extended events shows me a resource… Mem-physical high. Can something related to memory take down the server? Where can I check?” You know, that’s really a tough one. So mem-physical high, that doesn’t sound like you’re running into a problem. That sounds like, from what I remember with extended events, that you’re totally okay, like there’s plenty of memory available. So I wouldn’t dig deeper into that one. That wouldn’t be the place that I would go. I would jump back to the Windows layer because usually, it’s not something at the SQL Server layer that causes the entire box to restart, usually it’s something else.

sp_BlitzFirst has no “more details” info….

Brent Ozar: Kevin says, “When I run sp_BlitzFirst and I click on the details link, it opens in a new window and no XML is there. Why is this?” It would depend on what was inside the details. I’m guessing it’s one of these click-to-see details. You could even just hover your mouse over it to see what it is or click on it in a different SSMS, like try someone else’s SSMS. But it might also be to do with the line that you’re on, like there’s no more details about that particular error.

How can I test connectivity between replicas?

Brent Ozar: Teshale says, “I was trying to add a node to my cluster for Always On high availability, but I keep getting an error that says the cluster node is not reachable. I can ping it both ways and also I can ping the clustered name.” Ooh, that’s good. What I bet it is, is it – so pings, when they go through, they’re on certain ports. Connecting to each other for clustering or for things like Always On Availability Groups are different ports

Though. So what I like to do is Telnet. So Telnet is – Richie and I are old enough to know Telnet. You,Teshale, are probably way not as old and grey as Richie and I…

Richie Rump: Before the web, Brent.

Brent Ozar: When I couldn’t access Gopher, I checked with Telnet…

Richie Rump: Finger, who needs that?

Brent Ozar: Oh, the finger command, and you would set a message so when someone fingered you, it would return a specific message and people would put jokes in there.

Richie Rump: Giving you the finger command.

Brent Ozar: Can’t understand why that didn’t catch on. So what you do is you Telnet into the ports that you’re trying to get access to. So for example, with Always On Availability Groups, it’s port number 5022 by default, you may have set a different one. So you would Telnet from one box to the other into port 5022 to see if you got a response back. Now, that’s pretty old school. I’m sure there are probably other ways to check to see if all the ports are reachable, but I would start there with 5022. And if you can’t, odds are there’s some kind of firewall going between those two SQL Servers. I guess I’d be a bad person if I didn’t also mention, I would make sure to check to see that you have exclusions set up on your firewall.

Brent Ozar: Teshale follows up and says that the firewall is off on both. That’s good. So at least it’s not coming from inside Windows. But what I worry about is that it’s somewhere in between the two boxes; network switches, stateful packet inspection, it could be any number of things that’s dropping connection in between the two of those. So Telnetting on port 5022, if you get a response or not, that will be the next indicator. telnet’s kind of weird to use. Like sometimes, the thing that comes back looks like it’s a blank screen when you successfully connect. So for example, to test it, test it locally first. Remote desktop into one of the boxes, Telnet into the port 5022. Make sure you get a response. You’ll see what it looks like. And then try another non-functional port, you’ll see how that looks.

SQL Server authentication stops working, needs a restart

Brent Ozar: R.P. says, “My users are connecting to SQL Server from an app and they’re providing SQL Server authentication. Sometimes they’re unable to connect and after we restart the SQL Server services, they’re able to connect. We’ve checked, but we didn’t find any issues.” You know what’s funny is I’m tempted to recycle the Telnet thing. What’s the exact error message that you get? Like, the exact error message from the client side, what’s the exact error message that they’re getting. And the second part of this, how long does it take to get the message? Does the message come back instantly, the error message, or does it take 30 seconds before it comes back?

Because if it’s 30 seconds, you may not be able to connect to the SQL Server. If it comes back instantly, it’s connecting, but there’s some kind of problem with the authentication. If it’s taking 30 seconds then the next level that I would try is, actually, what someone else earlier said – when Teschal said – is try to ping the SQL Server from that app server. Are you able to ping it? And if you’re not able to ping it, it means they can’t even connect to the SQL Server for some reason; can’t even connect to the Windows instance that it’s on.

Richie Rump: And, R.D. are you in good relationships with the network guys because maybe they’re in there going he-he, click, ha-ha, click, ha-ha, click…

Brent Ozar: Watch this…

Where should I go to learn Python on SQL 2017?

Brent Ozar: Let’s see, next, Heather says, “Do y’all know of a good resource for learning the basics of Python on SQL Server 2017?” Richie, if I put a gun to your head and said go learn Python, where would you go?

Richie Rump: Okay, so I think there’s a difference here, right. So Python, I’d go to Pluralsight because that’s kind of where I could learn the fastest. They distill it down and they say here are the things that are important. If I wanted to get deep knowledge, I’d buy a book. If I just wanted to get up and running, it’d be Pluralsight or some sort of other video training type thing.

I think your question was how do I learn Python on SQL 2017. I don’t know, it’s one of those things where it’s so new and it’s not in the bread and butter of the SQL community. So you would be – if you said R, there would be a little bit more resources because there’s more data-savvy people understanding R, but Python is a little different. I would say Google around. I don’t know of anybody giving any classes or anything. There may be something at PASS this year at the summit; maybe one or two on that. But I wouldn’t say, hey I’m going to go to the PASS Summit and learn Python on SQL 2017; you’ll get a brochure level of knowledge and that’s about it.

I would say, you may just have to get in and jump into the middle of it and just start cranking on things and reading blog posts from engineering groups and things like that and see how things are actually working out because that’s one of those things where it’s so new and people really haven’t gotten into it yet and there’s just not a lot of content out there. Just watch, somebody wrote a book and published it last week.

Brent Ozar: No, I bet you’re right. So there’s a couple of sessions on Python – they don’t focus just on Python, so there’s one, T-SQL, R and Python, there’s another one, Data Science Tips and Tricks. But what I liked about where Richie was going with that too, go talk to who the speakers are. And you don’t even have to wait for PASS. So you can go contact those speakers now. Dejan Sarka does so much SQL Server work with new features and I wouldn’t even be surprised if he’s in the midst of writing a book on this because he’s the kind of guy – I think he’s cranked out like 20 SQL Server books. The guy is a machine. He’s also really easily reachable on the web, does a lot of training classes, so I would just ping him and say, hey, what resources are out there?

Carlos Bossy, also a really nice guy. I don’t think he’s written a book on this, but I would totally ping on him as well just to go see if you can track him down and see what they know about how easy it is to get started. I’d also say, what pains are you trying to solve? Pinal points out learnpython.org. I’m going to put in that to send it to everyone, learnpython.org. And let me throw it up on the browser as well. There’s a lot of good stuff out there on just how to learn python in general. The trick is [crosstalk] – yeah, the SQL Server part.

Richie Rump: Yeah, and that’s the one that got me, like… Actually, when you said – when you said machine, it reminded me of that comedian Burt Kushner, I think. He’s got a really funny thing about the machine. I’ll forward it in chat. It’s a little blue, people, okay.

Brent Ozar: That’s our chat room. Our chat room is pretty blue.

I have users that start with a backslash

Brent Ozar: Julie says, “I’m running select star from sys.users, order by name, I’m seeing users that start with a backslash. It’s causing errors when I’m trying to add the user. I can’t run sp_user or drop user. Any ideas on how to fix?” No, you got me there, other than obviously escaping and [them] with square brackets, you know, just to make sure that you got that. But I’m sure – I know Julie is way beyond that. We see her all the time in here. So that’s the only thing that I can think of off the top of my head. I also think it’s an awesome repro to try and do. If you can create the user and then have problems dropping it, this is one of those questions that goes over awesomely well on Stack Overflow or on dba.stackexchange.com.

Richie Rump: Have you tried hitting the little pi icon in the bottom left of the screen, and then maybe that will unlock it.

Brent Ozar: The pi icon?

Richie Rump: I’m surprised you didn’t get that. That’s The Net with Sandra Bullock. That was like a whole thing.

Brent Ozar: Oh, I forgot all about that. That was so good. She had her little Volkswagen Cabriolet…

Richie Rump: Of course, you would know the car that Sandra Bullock drove in The Net.

Brent Ozar: Yes, I can’t remember the name of her co-star, the evil guy, but I can remember that she drove a VW Cabriolet.

Richie Rump: All I remember as a technologist, even a young one back then, I’m like, this is crap. This is nothing. This is stupid.

Brent Ozar: But you know, I remember – because one of the climax parts is she goes running into a computer show – I think there was a computer show – because she wanted to access the internet. I’m like, wow, there were computer shows back then. It was like car shows today, but they would have the latest models…

Richie Rump: And they were huge.

Brent Ozar: Yeah, and you would go – computer swap meets too. I would go to this school back in Memphis and we would go around – it was almost like you were going through LP records at a flea market. You were going through these different motherboards.

Richie Rump: Oh, this [mem slot] looks good. Let me pick that one up and…

Brent Ozar: Yeah, different CPU fans, you know, trying to figure out exactly what was the best one. Yeah, that was incredible. Bob Ward – uh-oh – Bob Ward says, “Oh never mind.”

Richie Rump: Computer Shopper, you know, magazines that look like phone books.

Brent Ozar: I would get so excited about that. Every time my computer shopper came out at the bookstore, I would go get it and I would fold over all the pages of the best-looking cases, the cases that I really wanted to buy. Those were good old days.

Richie Rump: And now, we just throw away machines.

I had CPU cores disabled and I had no idea

Brent Ozar: Daryl says, “Today’s blog post is fascinating…” Alright, let’s pull up today’s blog post, since, of course, on the recording, people won’t know exactly what Daryl’s talking about there. Today’s blog post was about a VMware configuration – VMware or Hyper-V configuration issue. He says, “So you’re saying I can put Standard Edition on a 24-core box and Standard Edition will only use four and if I had the issue, SQL ConstantCare would flag it?” Yes. Now it’s not that way in most hypervisors by default. Most of them will intelligently configure CPU, but a lot of times over the years, we’ve either inherited a setup from someone else or we just went and created a new VM, we didn’t really know what we were doing and we did the wrong CPU configuration.

SQL ConstantCare isn’t the only way to flag it. You can also flag it with sp_Blitz. Both of those works in terms of quickly telling you if you have that issue or not. And it’s funny because it’s not like a number one priority issue. It’s down in the teens, I think, teens or 20s. But it’s just that you have cores sitting around idle. And worst case, I have seen people with memory sitting around idle and not able to use it.

Richie Rump: Yep.

Brent Ozar: It’s so fun to now get all the data with ConstantCare and be able to trend this stuff and see, oh my god, we caught like 20 people with cores offline, you know, and have this immediate turnaround difference.

Richie Rump: Yeah, and everyone’s so, like, oh I never thought to look at that. and it’s like, well why would you, you know? It’s one of those things, like, you don’t even think about it. Well, why would they do that?

Brent Ozar: Yeah, or if they did it, why wouldn’t it be a big red flashing message that, yo, dog, I can’t use the rest of the cores. And I’ve talked to the rest of the – I don’t know why I always go to yo, dog.

Richie Rump: Yo, dog, I found some cores in your cores that are not being cored.

Brent Ozar: I heard you like cores. Turn your cores off. And in fairness to the Microsoft people I talk to when we go through this issue, they’re like, well look, we throw it in the startup event log. It’s in there as a line, we’re using this many cores, but no one ever looks. No one looks at their startup log. They never review it and there’s so much spam in there. There’s so much garbage inside there that you don’t read. Alright, well that’s all the questions that we’ve got this week. Thanks, everybody, for hanging out with us and we will see y’all next week at Office Hours. Adios.

Wanna attend our next Office Hours and ask questions live?