This week, Brent and Richie discuss sp_Blitz Backups, change history tracking, exams and certifications, using Azure for DR-only, which version of SQL server to use for a new DB, adding additional data files, load balancing, trace flag 2371, dealing with reporting services, and much 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.

If you prefer to listen to the audio:

Podcast: Play in new window | Download

Enjoy the Podcast?

Don’t miss an episode, subscribe via iTunes, Stitcher or RSS.
Leave us a review in iTunes

Office Hours Webcast – 2017-05-31

 

Brent Ozar: Wesley says, “Does Brent Ozar Unlimited sell Brent Ozar Unlimited coffee mugs? I need people to know who my DBAs are.” We do, but the thing is, they cost $4,000 and they come with a free training course. So you just have to come to one of our in-person classes and that’s when we give them away.

Richie Rump: Yeah, I tell people it cost me my soul, so, there you go…

Brent Ozar: But you got like half a dozen of them, so…

Richie Rump: I got four, I got four for my soul. And I’m good with that, right, because I got one for each of us here, you know, I don’t need anymore, there’s always one available, we’re good.

Brent Ozar: That’s my entire – I have an office bathroom and the entire bathroom is completely full of swag, you know, just stuff in boxes that we… Unbelievable.

Richie Rump: I have to share – my office bathroom is right that way, I’m looking at it right now. It hasn’t been updated since 1969, and I told the girls, I am not fixing this bathroom until you learn how to take care of the bathroom because they just throw stuff everywhere. They’re girls, you know, it’s already messed up, why not. I’m like, well you’re going to have to deal with it, not me, so hey, have fun.

Brent Ozar: Don says, “you should make the coffee cups available to those of us who purchased the online training.” The problem is taxes. As soon as we deal with taxes and shipping, things get really, really ugly.

Richie Rump: So here’s the thing, don’t call it a coffee cup, this is tea. This is Earl Grey, it’s not coffee.

Brent Ozar: Earl Grey hot, I would assume?

Richie Rump: Tea, Earl Grey hot, this is loose leaf, I think it’s Teavana that I’m drinking today.

Brent Ozar: Ooh very nice, yes.

Richie Rump: Yeah, I bought a big old bag of that sucker.

 

Brent Ozar: Let’s see here, we’ve got all kinds of fun questions. Clark says, “in SQL Server vNext, which is officially called 2017 now, have you guys used Python and can it be used for SSIS type things?” Well, Richie, I would ask you. So if you were going to do like data sciencey work, Python, anything, SSIS to get data in and out, would you run it inside of SQL Server? Or what would you do?

Richie Rump: I’d try it. I mean, if my data’s already there, I mean, it’s already stood up, why not, but typically we wouldn’t do that in SQL Server because we haven’t had the tools to do it. I guess, now that Python and R are in it, maybe we could do some more stuff. And frankly, I doubt that SSIS will have that. Think of SSIS as a completely different product. It’s under the SQL Server banner but it’s something completely different than the engine stuff. So I’m assuming you can’t, but hey, you got c# in there, so you know, have at it at that, buddy.

 

Brent Ozar: James says, “sp_BlitzBackups is awesome, I love it because I can show my boss the risks the business is taking.” That’s exactly why we wrote it. We have a project where the client’s building availability groups up in the cloud and we just want to be able to monitor how long it’s going to take to restore those databases. It’s tons of fun if you guys haven’t seen sp_BlitzBackups; Erik put a ton of work into that. And he has another brand new one coming out next week, sp_QueryStore, where he analyzes your SQL Server 2016’s throughput, like looks for your worst performing period of time in the last seven days and then finds the queries that were causing that workload problem, all by just running a stored procedure; it’s amazing.

 

Brent Ozar: Wes Palmer asks, “I’m trying to log DML actions and throw them into and audit table using triggers. Is there a better way to log those instead of using triggers?” Have you had to do any of that work, like history tracking work of whenever people make changes?

Richie Rump: Yes, and when I had to go and redo it I created history tables for it. So as we loaded the data, the schema automatically handled all that. So we had from and through dates pretty much through everything, so I didn’t have to sit there and throw rows out or anything like that. I guess, if you can’t redesign your whole entire application, I guess you can go ahead and use some of that new-fandangled stuff that they use for odd history stuff. I haven’t used it yet but…

Brent Ozar: [crosstalk] tables…

Richie Rump: Yeah, that’s an option now.

Brent Ozar: Damn, see I forgot all about that. Yeah, before temporal tables wither you had to change the app or else you used triggers, but temporal tables, that’s a great point; which are in ever version or every edition of SQL Server now. I mean everything’s included in standard edition since Service Pack 1. So that’s a really cool idea.

Richie Rump: Yeah, it’s funny me talking about SQL Server knowing that I haven’t really done much with it, you know, in the past year or so. And it’s like hey, let’s talk about Postgres, alright, let’s do that.

Brent Ozar: As you’ve worked with Postgres, what has been your most interesting difference between that and SQL Server?

Richie Rump: You know, I haven’t got into it too deeply yet, and it’s really the programming model of it, the PG/PLSQL is different, as well as, you know – I’m doing a lot of, hey this is SQL Server and then how we would move that data over into Postgres and then how we would transform the data types and do all that other stuff. There’s a lot going on in Postgres and kind of, there’s now – sometimes there’s not a one to one mapping, you’ve kind of got to figure it out, but there’s a lot going on from a programmatic side in Postgres that you just kind of have to rethink yourself. And from a SQL Server perspective, it was like, oh, okay no that’s good, I like that. I’d love to start doing some more perf testing on the Postgres side to really start seeing how it would work, but I’m not there yet, you know, we’re in our infancies of just getting, taking a really hard look at Postgres and Postgres in the cloud and seeing what perf is and all that fun stuff.

Brent Ozar: Getting rows in and taking rows out.

Richie Rump: Yeah, we’re just trying to get rows in, we’ll figure that out.

Brent Ozar: Expert level.

 

Brent Ozar: Let’s see here, John says, “we have high tempdb average write stalls of about 1.4 seconds. I recently changed tempdb from one file to multiple files, when it was one file it was lower.” Well really think of this as random versus sequential activity. The more files that you add, the more random your drive access can become. A couple of things I would think about is first, make sure you’re on a recent patch level for 2012 or 2014. There were improvements in there where SQL Server’s less eager to write to disk. Next thing I would look at is — get as much RAM as you can on the box, the storage, whether it’s 600 milliseconds or 1.4 seconds sucks, I mean that’s bad either way. So if you can keep it up in RAM you’re going to be able to avoid that. It doesn’t fix everything with tempdb latency, but it’s just going to help, and I say that – so like if you’re at 64GB of RAM, now’s the time to start looking higher. And then also local solid state, local solid state makes this problem much less of a big deal. With Intel’s PCI-express cards these days, 2TB is 1,000 bucks, so it’s much more cost-effective than it has been in the past.

 

Brent Ozar: Wesley Crocket says that, “the MCSA in SQL Server 2016 is an Azure marketing certificate.” I felt that way about Microsoft certs for a long time, since they discontinued the master. We have this discussion every now and then, we actually lost our Microsoft partner certification because not enough of us here have SQL Server certs, or any kind of certs, because they’re just not worth anything these days. I mean you nailed it when you said it’s a marketing certification for Azure, that’s really it’s hey, we brought out this shiny hammer, show us all the ways you would use this shiny hammer. I’m like, first off, that’s not a hammer, that’s a martini glass and you need to stop banging nails with that, it’s the wrong thing.

Richie Rump: Yeah, my favorite one is when I took the 2008 exam, SQL Server 2008 exam, it was like XML all the time, the answer was always XML. And it’s like what is going on here, because, in reality, XML is kind of crappy, what is this? But it’s all about hey, this is our new feature and we want you to know it.

Brent Ozar: It’s not related to what data people do, it’s related to what people want you to know. If I designed a certificate, which we’re never going to do because it’s a money-losing mess, but if we designed a certificate, it would be around things like can you backup, can you secure the database, can you make a query go fast, can you troubleshoot an outage when it happens. But none of that is what sells certifications or moves SQL Server as a product, so they just don’t put questions in like that.

Richie Rump: Yeah, speaking of stuff like that, that’s our – my favorite part of our classes is where we say, hey here’s a query, now what would you do to make it fast, and everyone kind of looks at each other like, he’s serious, right? Okay, we’ll figure it out. And so everyone has their answers and they, well this is what I came up with, what did you – and we had this great conversation about why this index would work, why this one’s better and all that, that’s a conversation you just don’t get very much.

Brent Ozar: And then – so I’m taking it to the next level for – I just sent out a survey to like 10,000 of our email subscribers to go, alright so for a query and index tuning class, how about a class where each person gets their own VM and it’s got a workload already running on it and I’m going to give you say one hour to figure out what the problem is and how to fix it, and then you gauge how many batch requests a second you’re getting afterwards. Then we’re going to get back together and I’m going to show you on my VM what I did in the span of that same amount of time. Then you’re going to get a fresh VM and you go do it again, see if you can mimic the same stuff that I did or change what you did again. So the cloud lends all these things so much more easily now because you can do them remotely via your own desktop.

 

Brent Ozar: Don says, “hey team, I have to build on my question from last week…” oh come on, man, like I remember that. “I have about 30 columns and 500 to 700 rows, I’ve turned on client stats and for 300 rows it’s about 600KB of data return. Does that support our developer’s claim that the SQL query is returning too much data?” 600KB – what I would do, especially if they have a web app, is use any tool like Chrome that will show how much their webpage render size is and I bet their webpage is going to be, say, 2MB or 3MB. So I’d be like, I’ll tell you what, if you think 600KB of data is a lot, how about you try to get your web page down to 600KB and tell me what happens.

Richie Rump: Yeah, and I would say it’s probably a lot more than that, your images are probably a lot more than 2K or 3K or MB, I bet you, again, how frequently are you running this thing? Are you running this five times a second, 100 times a second, are you running this once in a while, are you running it every time that the page loads? How frequently does the data change if there’s only 500 or 700 rows? It doesn’t sound like a lot. You probably should be caching this data somewhere and it probably should be – and if you’re using ASP.NET they have built in caching right there. Hey, even if you’re doing it every ten seconds, that’s all these calls that you’re not making that would save a ton on that 600KB going across for every call, however frequently you’re calling it. Don’t fall into the trap, cache your data, this is not a DBA thing, this is an app dev thing. So mister app developer who’s not watching right now, cache it, please. For the sanity of the rest of us, please just do it.

 

Brent Ozar: M.M. says, “my application is a data mart and reporting app, I’ve got several terabyte size databases, but we’re supposed to have DR but the budget was denied for physical servers. What do you think about Azure for DR only?” The problem is typically restore time. Cloud storage, its strength is not restore speeds. So if you got to our site and click Tools up on the top, in our first responder kit, we actually have a whitepaper on this for using the cloud just for disaster recovery where we did an exercise with Google. Google Compute Engine just sponsored us to do all kinds of cool tricks with log shipping and we give you the exact instructions on how to do it. You can take these same techniques and use them in Azure, it’s just that the PowerShell commands and whatnot that we give you are directly tied into GCE’s toolkit, You can do the same thing with Azure, the problem that you’re going to run into is, watch how long it takes those databases to restore the first time you do it and then communicate that to the business. Hey, is it cool that we’re down for, say, eight hours, ten hours or in the case of these multi-terabyte databases and Azure storage, sometimes that can be tens of hours.

Also check out on the blog, I’ve been doing this faux PAAS, like fake platform as a service, where we talk about building your own Azure SQLDB, one of the posts in there is about benchmarking your restore speed on Azure, so you can see, kind of, how fast that goes.

Richie Rump: Yeah, that’s my biggest problem with the cloud, your disk speeds, they’re not there…

Brent Ozar: They’re challenging.

Richie Rump: yeah, and you’ve got to code around that as a programmer.

Brent Ozar: As a database administrator, you may have to put every database on its own server so you can get the restore speed that you want.

Richie Rump: Yes, that’s a good – I’m going to write that down.

 

Brent Ozar: Tammy says, “our developers need a new database…” Well you’ve come to the right place, “We already had of instances of 2014, we could stand up a new 2016 instance, which instance would you choose or would you wait for 2017?” Man, I’d give them 2014 right now and just let them get started. Granted there’s stuff inside 2016 that’s available in standard edition, but I’d be like look, if you need a database, here you go, here’s one on 2014, or whatever you have production or development stuff in, and just code for whatever works in 2014. You’ll be fine in 2014 or 2016. If they run into performance or scalability problems, yeah, you could talk about a new version, but if you don’t need a new server, don’t stand up a new server, they’re so expensive.

Richie Rump: Yeah, and even in – I think the last big thing we got, from a developer perspective, was 2012, was all the T-SQL enhancements that we got inside there, the additional windowing functions and all that other fun stuff. So I say, go for it, go for 2014, it’s fine. If you want to mess with 2016 in a development environment, I’d say play with it, but understand what features are in there and would you need them, right. I mean that’s the question, do you need the features that are going to be in there? 2017 is interesting because you’ve got some Python-y stuff and you’ve got some R, so maybe that’s something that developers can play with, but I kind of wait for a release t, kind of, go with that. But maybe if you want to do it in a development environment, that’s cool, but I kind of would wait until at least Microsoft has a Go Live license or something.

Brent Ozar: which is a good point because often we’ve seen stuff where they’ve brought out a feature and they’ve either canned it shortly thereafter or it doesn’t scale or doesn’t perform the way you want it to. So maybe not code directly to 2017 yet. It does bring up another point, so if you’re going to stand one up in 2014, make sure to use the 2014 cardinality estimator, just so that they get the new cardinality estimator right from the get go, theycan plan their queries around that level of performance. there was something else I was going to talk about – oh, consider read committed snapshot isolation. This is – it’s the default, it’s called optimistic concurrency or MVCC, this is a switch that’s on by default in things like Oracle and Postgres. It’s not on by default on SQL Server, and it just makes a lot of your locking problems go away. If you search for RCSI on our site, read committed snapshot isolation, we’ve got a checklist on how you go about enabling them.

Richie Rump: Yeah, I think I even went to change my model database to turn that on [crosstalk].

 

Brent Ozar: Rex says, “for reporting server build out, what’s the indicator that you need to set up a build out with a load balancer versus a standalone? Is it better to overbuild or convert?” The big thing there is licensing. The more servers that you stand up, you have to pay for. So that’s the thing that I would use to guide my decisions. Ask the business, are you willing to pay for multiple SSRS boxes so that I can take one out, patch it and throw it back into the load balancing? Also, do you have a team who’s successfully used a load balancer already? Because that has a high startup cost if you don’t.

 

Brent Ozar: Robert says, “back to sp_BlitzBackup, can it be used for non-availability group environments?” Absolutely, you can use it for standalone servers, mirroring, replication, all kinds of stuff.

 

Brent Ozar: Mark says, “I’m running out of disk space where my current data file resides. What’s the best way to add another data file and how will it load balance?” I’ll tell you a secret, I’m kind of lazy, I use the GUI. I would right click on the database on SSMS, go into properties and just add a file from there, because for stuff like this that I never do, I’m never going to remember the T-SQL. I’m still amazed every day that I remember how to build an index and rebuild it from T-SQL without, you know, looking anything up. But for something odd and unusual like adding a data file, you can just add it through the GUI. If you’re adding it to the same file group, like the primary file group, it will not load balance. SQL Server uses proportional fill, so it will focus more attention on whatever file is the emptiest, so it will become a little bit of a hotspot. If you wanted to mitigate that, after you add the file you can do a round of index rebuilds to kind of even out the space between those. It’s not perfect, but if it’s just purely a space issue, I would just go and add the pother file for now and call it a day.

 

Brent Ozar: Let’s see here – this is a good time to speak a little bit about how you give advice. So when someone asks me a question, I try to read as much as I can into what their question is. I know, as sure as I say this out loud, there are going to be armchair architects who are like, well what you should do is add two precisely evenly sized files and do your index rebuilds until it reaches a point of perfect equilibrium. Ladies and gentlemen, this was somebody asking, how do you even add in extra files? So this is how do I get him across the finish line as quickly as possible? His drive space problem will be gone at that point, so…

 

Richie Rump: I have a question from a developer perspective. At what point would you, say I have a table and I want to throw that into a particular file group as opposed to putting everything in primary?

Brent Ozar: That’s a great question. It depends on whether or not the application can be up without that table. If the app can be up without that table, like history tables are a great example, or reporting tables. I can put that in a different filegroup so that then I can just restore primary in the event of an emergency, and the application is online as soon as primary is online. The only time I start micromanaging that is when I know the database is headed toward the 1TB mark, and when you get to a terabyte or above, restores are a non-trivial operation. So I may want to – it’s going to take me two hours to restore this thing, what are the core subset of table that I need for the app to come online, everything else can then go into a different file group.

 

Brent Ozar: Let’s see, Michael says, “we have a table with 500 million rows and we add 200,000 rows a day. We find that we have to update stats every day in order to sustain query performance. is that the right answer, or is there something else that we can do?” SQL Server’s notoriously vulnerable to something called the ascending key problem. Think about a data warehouse where every day you load last night’s sales and then your users immediately go query last night’s sales and it doesn’t have any good statistics about what happened yesterday. It is normal in data warehouses to have to update stats after things like data loads complete, even in high-volume OLTP environments. For a while at Stack Overflow, we were doing stats updates every single night. I’m fine with it if you maintenance window allows for it. I’ve even handed those commands to my data warehousing team and said, whenever you get done with your nightly builds, if you can, go update statistics, just so that their report queries will be as nice as possible the next morning.

 

Brent Ozar: M.M. Says, “is there a trick to getting sp_Blitz’s results to getting copy pasting properly into Excel? Some of the longer fields like [inaudible] scripts don’t work…

Richie Rump: That’s it, bye, thank you…

Brent Ozar: Next question… Yeah, so it comes down to Excel chokes when you paste XML into it. So some of our scripts like sp_BlitzCache have an export to Excel flag. Turn export to Excel equals one, and then it just omits all of the big fields that Excel usually chokes on when you copy paste stuff in.

 

Brent Ozar: Dan Clemens wants to add trace flag 2371. See, so here’s the deal, 2371 is only automatic updates to stats. It can trigger at unpredictable times. When I’m dealing with stuff like a data warehouse, at the end of my loads, I want the stats updates to happen exactly then. Even if they didn’t hit the thresholds, I want them to go ahead and trigger. I don’t want them to trigger in the middle of my loads or ten minutes into my loads, I want to go kick them off after the loads finish so that then I can predict the times. I’m not against 2371, if people want to use it, that’s totally okay, but when I’m managing a data warehouse, that’s just why I wouldn’t go that way. And that behavior is on by default now, starting in 2016, which is kind of cool.

 

Brent Ozar: Chuck asks, “in an availability group setting, how should I run SQL jobs on a primary after a failover and disable them on the new secondary?”  I’m a fan of having a separate server for agent jobs. So if I have something that’s got business logic, running reports, processing files, I like that being in an agent server or an SSIS server that my developers control. They can have full sysadmin rights on it, they can change the packages whenever they want, and they just point to the AG listener. So if this thing fails over in the middle of a job run, my developers know when they can retry that and when they can’t; like when there’s some kind of manual intervention they need to do. I, as a DBA, I don’t know that. I don’t know which jobs are easily restartable and which ones are not. So I tend to offload that stuff into its own server that my devs have control over.

For my jobs as a database administrator, things like backups, CHECKDB, updating stats. I know that those things are idempotent, so I know when I can safely retry and when I don’t need to retry them. So I just have them all on every replica, and at the start of every job, or at the start of processing each database, they check and see, should I process this or not? Like, am I the primary for this database? For example with backups, if you use the current maintenance plans code, Ola Hallengren’s backup scripts or Jen and Sean McCown’s Minion Backup, these things will all produce jobs that fire off and succeed all the time, they just only backup the databases that are appropriate to backup based on wherever things have failed over. Huge fan of that. So then I can use exactly the same code everywhere.

 

Brent Ozar: Doug says, “we are thinking of maybe setting up a SQL Server reporting services site outside of our DMZ or intranet to build reports for client consumption. Would that be a good idea?” I don’t do reporting services, but man, the phrase DMZ kind of makes me nervous.

Richie Rump: Yes, it does.

Brent Ozar: You work with clients that have to deliver, or folks who have to deliver reports to the outside world, and if so, how do they go about doing that?

Richie Rump: We never threw a server outside the DMZ, at least a database server; that never happened. Now I haven’t had to do reporting outside to the internet for about ten years now, it’s been a while. So we do a lot of our own reporting on our own, back in the early days we used [chris] reports on the web, which was a pain in the ass. We then moved to Reporting Server, and when it first came out, I mean this was – I think it was on 2000 [crosstalk] and we were super excited for that, and so we didn’t run reporting services blank. We used a web service to go and get the HTML and then we scrubbed the HTML to fit inside of our website. Now I think there’s a viewer controller thing for all that kind of stuff, if you want to do that.

In general, having data out there outside your DMZ, especially reporting data where it’s all nicely rolled up and all that, it just seems like a very bad idea, especially with all the security issues that we’ve had over the past… ever, and it just keeps on getting worse. So I mean Chipotle was just nailed and anyone who used their credit card in Chipotle for the past two months or something, their credit card – and I got hit. I got an email from my bank saying we’re going to send you a new credit card. Thanks Chipotle for your malware, I appreciate that.

Brent Ozar: The burritos are great though, you have to hand it to them.

Richie Rump: They’re not Mexican enough for me, ma, I’m sorry. I’ll go in there and I’ll eat it but – my kids love it, but I’m just… I need some [crosstalk] sauce in there somewhere. Just somewhere it needs to be on. But yes, I would think that’s a fireable offence, especially if that got compromised in any way, if you decided to put that outside the DMZ. So I wouldn’t recommend it, but it’s not my job, so…

Brent Ozar: Yeah, that’s my thought too, I would rather not do that, I would rather build the reports internally somewhere and then go deliver them externally. Alright, well thanks everybody for hanging out with us during this week’s Office Hours. I will see you guys in GroupBy in two days, on Friday we have the next GroupBy conference, and then see you at the next Office Hours. Adios everybody.

This week, Brent, Erik, and Richie discuss disabling telemetry in SQL Server 2016, proper place for log files, Availability Groups, measuring data returned from a single query, whether CHECKDB should be done on tempdb, limiting tempdb usage for each session, transferring large amounts of data from on-prem to the cloud, and Iron Chef spoilers.

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.

If you prefer to listen to the audio:

Podcast: Play in new window | Download

Enjoy the Podcast?

Don’t miss an episode, subscribe via iTunes, Stitcher or RSS.
Leave us a review in iTunes

Office Hours Webcast – 2017-05-24

 

 

Should I put tempdb’s log files on a separate drive?

Erik Darling: Alright, Mary has a question about where you put your files. Mary says she, “knows it’s a good practice to put the log files on separate drives from data files, but what about for tempdb? Where should that log file be placed for optimal performance?”

Brent Ozar: I don’t know if I’ve ever seen tempdb’s log speed be a problem, and if I did, I don’t know how I would find it.

Erik Darling: I guess my thing is that like, you know, logging in tempdb is pretty well optimized. There’s a lot of – like tempdb doesn’t care about, you know, taking snapshots the way it does for like, you know, regular log files. It just cares about being able to rollback, it doesn’t care about needing to roll forward because when SQL Server restarts, tempdb just starts fresh anyway. So again, I’m with Brent, I’ve never seen tempdb’s log be a problem, but usually, when there’s an underlying drive problem, it’s affecting all of the data files and the log files. So you know, I would just say if you have tempdb I/O issues, then your problem isn’t where the log file is, your problem is something bigger.

Brent Ozar: And have you – so when you built servers, did you put tempdb on a separate log – temp db’s log file in a separate drive or did you just put it with the data files?

Erik Darling: you know, with the San it just didn’t matter all that much. I guess, you know, from – we’ve more of an availability issue, so like if tempdb’s log or data file ever started growing massively, I wouldn’t want one or the other to be impacting the other groups. I wouldn’t want the log file to grow out of control and mess up the data files, and I wouldn’t want the data files to grow out of control and squish the log files. So I would separate it for that but I never separated it for performance. It was more of a cautionary thing. By the way, this is what fell that was incredibly distracting [crosstalk ] flamingo that my kid made me in her pre-K class, and it fell. What the hell is that? Because it was right near my head.

Richie Rump: Flamingos don’t fly…

Erik Darling: I know, they just fall. It just crashed.

Richie Rump: It was like the opening of Miami Vice, right.

Erik Darling: A lot like that.

Brent Ozar: I missed that, that was nicely done, that was nicely done, I have to hand it to you.

Erik Darling: Wind blowing through my hair and all that.

 

What should I use as a witness in my AG?

Erik Darling: Clark asks, “in SQL availability groups”, because there’s no other kind, “are there any benefits other than cost of running a file share as a third node versus just having three or more SQL nodes to have a quorum?”

Brent Ozar: Oh man, I just did a quorum module here at SQL Intersection. Normally I don’t like having any extra nodes in the cluster that aren’t involved in SQL Server, just, you know, more complexity if something goes wrong. I would just use a file share witness. Just use a file share witness, point it at that and then Windows, starting with WIN2012R2, Windows will automatically take away a vote from the file share as well, so you don’t have to worry about it going into an even number of voters. I’m simplifying a lot there, but just that I would rather use the file share.

 

Is a proc’s last execution date stored somewhere?

Erik Darling: Alright, well there we have it. Deborah asks… Let’s see, Deborah asks, “is the last execution date stored persistently some place? I know about looking at sys.dm_exec_procedure_stats and its limitations.”

Brent Ozar: Ooh, what do you want to use it for? What are you looking to find out? It is – I like where she’s going with proc stats, but you probably even remember the field names by heart, given how many times you’ve been in there.

Erik Darling: I would – I don’t know…

Richie Rump: Well what have you been playing with lately?

Erik Darling: Because I’m trying to think like persistently and I’m trying to think prior to query store, and I don’t have a good answer on prior to query store. Even query store flushes data out, so that’s not even 100%.

Brent Ozar: If you’re looking to audit whether or not something’s used or not, like the easiest way to do it is create a table of your own and then add a line to the end of the stored procedure that just either inserts a record into it or updates the existing record into it. But the instant that you know that it’s actively used, you need to get that line the hell out of the code.

Erik Darling: Makes sense to me.

 

Which Itzik Ben-Gan book should I read?

Erik Darling: Alright, Robert asks a funny question, and it’s funny because of the way we feel about exams in the first place. Robert asks, “do you have any feedback on the book Exam Ref 70-761, Querying Data with Transact-SQL first edition by Itzik Ben-Gan? Do you have a site you go to for feedback on technical titles?” I usually just read Amazon reviews.

Brent Ozar: Yes, yeah.

Erik Darling: As far as that book goes, that’s – I mean I don’t think it’s been updated and it’s a fairly old book at this point.

Brent Ozar: Really old, yeah.

Erik Darling: So Itzik – we’re going to have to move some stuff around here, don’t mind me…

Richie Rump: Don’t fall off your chair…

Erik Darling: Look pal, if I didn’t do squats, I’d be screwed. This book is much newer, I think it’s from, what, 2014?

Brent Ozar: I think so.

Erik Darling: Yeah, 2014, it’s much newer, it’s full of great stuff. It’s probably a lot of the same stuff but just updated for newer versions as well. As far as other books, jeez, I mean I usually just read Amazon reviews or, you know, see what other people are reading. [crosstalk]

Brent Ozar: Yeah, yeah, I was going to say that in – plus too, we’ve been around in the community long enough that we kind of go by author names, probably. Or you go – yeah, the author name that you hit in this one, he is awesome, Itzik Ben-Gan is a genius, and so I would just tend to gravitate towards the latest book that he’s written, that’s the one I would go for. But in terms of – you asked something else which is the exam prep; none of us in here are currently certified, I don’t think…

Richie Rump: Oh no, I’m sorry, my PMP certification just lapsed.

Brent Ozar: Yeah, certification dropouts, and if Tara was here she’d – the problem with cert exams is that they measure what the marketing team wants you to know, not what you do in your daily job. So if your goal is to pass an exam, yeah, get an exam prep book. But if you want to learn SQL Server bust because you’re interested in that topic, get Itzik’s latest book, and it will be phenomenal.

Erik Darling: Even as far as, you know, just whatever books goes, Itzik has two other great books, they were written for 2012 but they hold up pretty well today. There’s like this thing, I believe it’s T-SQL Fundamentals and High-Performance Windowing Functions, or window functions – I always forget if the “ing” goes on there or not. But high-performance window or windowing functions for SQL Server 2012, and they’re both fantastic reads, like you’ll learn a lot even if you already, you know, have a pretty good footing in T-SQL. If you want to get those two and then work your way up to that bible, it’s a pretty good few months you have ahead of you of reading stuff.

Richie Rump: Well that, and there’s always the documentation, I usually go straight to there and there’s a whole bunch of stuff that you could actually glean from just reading the docs; that’s me.

Erik Darling: I have much worse luck just reading technical documentation that you do Richie. When I read technical documentation, I look at – I look at the full syntax where they have, like, all the brackets and parentheses and N comma stuff and I just – I blank out and I just scroll right down to the examples and think oh, that makes sense…[crosstalk ]

Richie Rump: So lame.

 

Why are you wearing an AWS hoodie?

Richie Rump: I have a question for Brent. So you’re in a Microsoft conference-ish, you have Microsoft employees there, you’re walking around with an AWS hoodie on…

Brent Ozar: And it was worse than that, when I got dressed this morning, the shirt underneath said Google Cloud, and I went to – I walked to the door and I took the one look at the mirror before you go out and I’m like oh, oh no I don’t think I should push that quite that far. But I’m like, it’s a really comfortable shirt and it’s a really nice hoodie, but – and for some reason, Microsoft doesn’t seem to send me free clothes anymore, I don’t know why that is.

Richie Rump: I don’t know why that is either, let me just scratch my head a little bit, I don’t know. If you’d have worn that Google Cloud thing and all of a sudden people are like, oh there’s troll boy, trolling again.

Brent Ozar: Right, yes, yeah it’s almost like I feel bad because I didn’t have any other sweatshirt, I have like a kCura sweatshirt and it’s in my other suitcase, but… So yes, that made me remind me that when I go to conferences, I need a different set of hoodies.

Richie Rump: When I spoke at the local day of Azure and spoke on Azure functions and I was wearing that exact hoodie during the whole time.

Brent Ozar: Yeah, it’s really nice.

Erik Darling: This is what you get for picking sides and wearing tech gear. You always have to worry about what you’re wearing, it’s worse than like gang territory.

Brent Ozar: Yeah, I think the only way to win is like wear Atari or Nintendo gear.

Erik Darling: Plain black t-shirts.

Richie Rump: How could you be wearing Nintendo and not an Xbox One, how dear you Brent, how dare you.

Erik Darling: Already screwing up…

Brent Ozar: That would be a problem.

Richie Rump: And you have a Macbook, oh, look at that, you are so – oh look at that, and an iPad, so anti Microsoft…

Brent Ozar: Watch, iPhone, yeah, anti community, you name it.

Erik Darling: Brent’s going to get shot at one of these conferences.

Richie Rump: Shot bad looks, that’s what’s going to happen.

 

How do I calculate the size of query results?

Erik Darling: Alright, we should get back to questions, I guess, maybe. Don asks, “is there a way to calculate the total number of bytes returned from a SQL query in SQL Server 2014? Our developer is saying that my query is returning too much data and it is causing the web page to load slowly.” I would tell your developer to implement a paging strategy then.

Brent Ozar: yeah, and when you say paging strategy, talk more about what you mean.

Erik Darling: I mean when people want to look at a reporting query, typically they don’t need a million results. No human is going to sit there and consume probably more than 10,000 rows of data and be able to look at it and come up with anything meaningful from it. When I talk about a paging strategy, I mean return 50 rows at a time and if you need 50 more rows to figure something out then you click load more rows and get 50 more rows and then 50 more rows as you need things. You know, your developer, DBA, isn’t necessarily wrong that your query is returning too much data and I’d stop trying to measure it in bytes, I’d measure it in what you actually need and what you actually consume when you look at this…

Brent Ozar: Rows times columns.

Erik Darling: Yeah.

Brent Ozar: If you wanted to really calculate the size, the really crafty way to do this is take the exact query and write about the FROM, put into, and then put a table name. So like SELECT stuff into whatever the table name you want is, and then you can look at the size of the table afterwards, but just – I’d take the simple way, too, rows times columns. How many rows are you bringing back and how many columns are you bringing back?

Richie Rump: Right, and the other side of that is, how frequently are you calling that? So if you’re calling it every time the page loads, ask yourself, do I need to call this ever time the page loads? Can I cache this data somewhere so I don’t have to make that SQL call? Someone once told me, the cheapest SQL call you can make is the one you don’t make, I think it was Erik, he’s smarter than Brent.

Brent Ozar: Great post, Erik…

Richie Rump: But caching helps a lot because you’re no longer querying the database. Then you can actually say, yeah maybe our cache is huge now because we’re pulling back way too many rows as opposed to calling direct from SQL and now you’re doing all that stuff across the water.

 

Why did you rent a Toyota Prius?

Brent Ozar: Richie’s going to be jealous of my rental car this week, Toyota Prius.

Richie Rump: No…

Brent Ozar: Every now and then I switch and play for the other team.

Richie Rump: Yeah, just so you know, I actually valeted my car just so the valet knew I was driving a convertible Mustang. That’s…

Erik Darling: Can you please take my convertible Mustang?

Brent Ozar: Can you please park it out front, and I’m going to walk around it a few times.

Richie Rump: And, you know, when you come and get it, could you swing it around a couple times so people see it?

Erik Darling: Do some donuts.

Brent Ozar: And say, the Florida Mustang is here.

Richie Rump: Not even a GT people, I mean seriously.

Erik Darling: Sad, sad day.

 

Should I do CHECKDB on tempdb?

Erik Darling: Alright, Grahame says he read somewhere, it doesn’t say where, he does not cite his sources, but Grahame says he read somewhere that “CHECKDB should be done on tempdb, why?”

Brent Ozar: We used to have it in part of sp_Blitz too because I’m like, hey look, anytime you find any database errors anywhere, you probably want to know, and tempdb’s often on a different set of drives. These days a lot of people are doing tempdb on, you know, local storage and flash storage, stuff like that. It doesn’t do in-depth checking the way it would on a user database. The reason that we finally pulled it out of sp_Blitz was, if you use the default maintenance plans, SQL Server actually skips – the maintenance plans skip tempdb [crosstalk].

Erik Darling: Ola Hallengren’s system databases stored procedure, excuse me, also skips tempdb, and it’s actually kind of funny, if tempdb ever does go corrupt, I think – what was the way to fix it? You have to shut down SQL, delete the files and then start SQL backup, because even though tempdb reinitializes everything, if you don’t delete the files, it will just keep using the …

Brent Ozar: Using the same one again.

Erik Darling: But that doesn’t help if your storage is zapped because…

Brent Ozar: Right, if you have crappy SSDs, yeah, it will keep getting re-corrupted.

Erik Darling: So I don’t think I’d haul off and start running CHECKDB on tempdb, but you know…

Brent Ozar: If you get error corruption errors out of tempdb then that’s when you start…

Erik Darling: Because I don’t know if you remember, back at DBA Days what happens when tempdb is corrupted.

Brent Ozar: You should explain, that was phenomenal.

Erik Darling: So back at DBA Days, we broke tempdb and we ran a stored procedure that would overwrite some page data in tempdb and just kind of insert stuff in a loop and crap things up real bad, and when you do that, SQL Server stops and restarts. And if you happen to make that stored procedure an agent job that runs at startup, SQL Server will actually not start back up, just to kind of restart that box. So when tempdb does become corrupt, you know immediately because your SQL Server ceases to exist, it just goes – waves goodbye.

Brent Ozar: And if you want the code to do that, search on the site for Dell DBA Days tempdb and Erik’s code should do it to, kind of, auto-corrupt databases is out there; we had a lot of fun with that.

Erik Darling: Also the one that randomly changes database settings is out there as well, that was a lot of fun to write and run. We got to do that stuff again.

Brent Ozar: Some days we use our forces for good, but just not usually.

Erik Darling: Not when we have millions of hardware dollars from someone else. then we’re like oh, [crosstalk] setting off fire extinguishers in the server room.

Richie Rump: That was the barbecue pork, that’s…

Brent Ozar: Which probably caused us to make more bad decisions.

Richie Rump: There was a lot of bad decisions after the big beans. The big beans were probably the bad decisions and then it just cascaded all the way down there.

Erik Darling: We were just all meat-drunk and strange things happened.

 

Can I limit tempdb usage per session?

Erik Darling: Alright, Renee asks an interesting question. This is a question that I would actually like to answer it myself. “Is there a way to limit tempdb usage by default for each session, kind of like an available percentage that can be used? Resource governor doesn’t give such an option, or at least I didn’t find it.”

Brent Ozar: You know how you would do it? So the answer’s no, I mean you know, but how would you do it? My first thought was I would count the allocations. So if you run sp_WhoIsActive Adam Machanic’s excellent free tool sp_WhoIsActive, it will show you tempdb allocations by session. You could read his code to see where e gets it from. I don’t – think it’s in exec requests or something, and then you could kill sessions that are using more than a certain amount of space. Man, that sounds like a bad idea, that sounds so bad because especially, what if they’re doing inserts, updates and deletes and they just happen to allocate some space as that’s running? That seems horrifically bad, but I love it.

Erik Darling: Yeah because, I mean you know, especially if, you know, if they need a temp table or they do a hash join or anything stored stuff in tempdb and you just start killing sessions because that happens. It’s like the user isn’t even doing anything bad on purpose and you’re just sitting there murdering them, murdering their queries. Brent’s video paused in like the least flattering face.

Brent Ozar: Potato. Wi-fi potato. This week’s Office Hours is brought to you by Disney. Disney’s wi-fi signal at the Swann hotel is top quality pigeons ferrying matchbooks full of TCP IP packets back and forth.

Erik Darling: In other words, it is barely brought to you by Disney.

Richie Rump: Just so you know … and a dolphin, are not owned by Disney.

Brent Ozar: They are now, they are now. Disney bought them.

Richie Rump: Disney did buy them, I don’t know why they haven’t torn them down yet then.

Brent Ozar: I don’t know, especially when you see this wi-fi signal.

 

Can I kick people out of SQL Server for being idle?

Erik Darling: Deborah asks another question. “Is there a way to set a timeout from the server side? We have developers on management studio, or another tool, who never seem to log off, they just don’t go away.” This developer herpes will not leave your server, will not go away.

Brent Ozar: Well the bigger question is, is that a problem? Because it’s kind of okay if people log in and never log off [crosstalk]… I used to have a Blackberry Enterprise server and it would leave connections open but they would have locks on them and so I would just go through and kill them every night at midnight. If the client is Blackberry Enterprise server and there’s locks open, just kill it. But that’s also because I didn’t really care whether my executives got my email or not, because really, the less they know, the better off that they are.

Erik Darling: If they don’t get the email that the server’s down, the server isn’t down; no one’s waking you up at four in the morning now. I would actually have that as a [crosstalk]. If the server goes down, kill the best connections so no one can get in [crosstalk].

 

Should I use filestream to host files in the database?

Erik Darling: Alright, let’s see here, let me think of a way to paraphrase this one. Ah yes, so John asks, he recently joined a company that’s using file stream to serve up files in SQL Server. He read that this is not a good idea, but he’s not familiar with other options and how you would serve files via SQL Server.

Brent Ozar: Well you know, you had to do this a lot in your last job, you know. You were dealing with tons of files and databases, what did the vendor end up doing there?

Erik Darling: Well storing blobs in the database is a pretty terrible idea. No one wants you editing PDFs and stored procedures, it’s not good. So usually what people will do is they will just start having pointers, file system pointers, in tables and then the application will use that pointer to open up a file as appropriate. So if you have, you know, a viewer of some sort, let’s just stick with PDFs and say you have a PDF viewer that works by hitting the database and looking at stuff, it will just follow the path to the PDF file that’s stored in the database. It will do some lookup there and rather than using file stream to, you know, pull the file up, it will just go to the file system where that path points to and grab the PDF from there.

Brent Ozar: Now, Richie, you’re working on projects these days that do a lot of files and file handling and we need to keep around for Paste the Plan. So how are we doing it for Paste the Plan?

Richie Rump: That’s it, that’s exactly how, we create some sort of pointer, tells us where it is and then the application goes and does whatever it needs to get to it. Now, our first cut of Paste the Plan actually stored all the files inside the database, then we decided that was a horrible, horrible idea, but it was a proof of concept, so it kind of worked for that. And that was one of the first things we removed and just put in where – the pointer to where it is. This is the path to where the file is, and then you can go and get it yourself.

Erik Darling: You finally read our own blog posts about getting blobs out of the database…

Richie Rump: I mean it’s – see the thing is it’s super simple, right [crosstalk] in this case we’re using DiNoDb, which is no SQL, it’s just super simple to get something up and running and shove it into a database. I don’t even have to convert it to binary stream or anything like that, you just dump it and it’s there.

Erik Darling: Magical.

Richie Rump: Yeah, but when you’re talking about – especially the cloud, now you’re talking about I got to pay for storage and I got to pay for transfer data, I got to pay for all this other stuff – just storing that kind of stuff just doesn’t make sense, so we store that in S3, a system designed to store files, and we just kind of pump it out of there.

Erik Darling: Cloud storage ain’t the fastest thing in the world.

Richie Rump: We don’t need it to be, so…

Erik Darling: But someone else might.

Richie Rump: Someone else might, you got to do your own testing on some of that stuff, but we don’t need it to be the fastest thing in the world.

 

How do I upload 2TB of data to Azure?

Erik Darling: Akright, Ben asks, “what is the most efficient way to transfer 2TB of data from on-prem to Azure Cloud IAAS?” Infrastructure… [crosstalk]

Richie Rump: Ah man, I asked Cecil this question yesterday because we – away from the podcast, away from the keyboard, we wanted to take all of our raw audio files and our project files and we wanted to put them in the cloud so I don’t have this hard drive which is sitting right here, always there, in case it falls or whatever. I can’t figure out a really great way. We were just talking about it, we don’t have a really great answer. I think there’s some command line tools that kind of help out with some of that stuff. There’s some .NET libs that help out with some retrial logic and all that other good stuff. But I don’t have a good answer on how to upload large amounts of data into the cloud.

Erik Darling: 2TB, man that’s…

Richie Rump: Now, AWS has a great way to do it. They have their – I think it’s called a Snowball, where they actually send you…

Erik Darling: That’s so cool.

Richie Rump: Yeah, a box, you plug it into your network, you put all the files over there, you send the box back and then they plug it into the cloud. So that is their, kind of, solution to – their sneaker-net solution to that.

Erik Darling: I miss sneaker-nets, token rings, all that fun stuff…

Richie Rump: And Brent quit, Brent quit – oh there he goes.

Erik Darling: Yeah, Brent is giving up on this one, sticking a fork in it. But that’s okay because so are we. It’s close enough to 12:45 and, well, I don’t know [crosstalk]

Richie Rump: because I wanted to know the answer to that, I want to be able to upload files pretty regularly to the cloud, I’m going to start trying some tools and trying to figure that out. The problem is that it’s complete – my upload speeds are trash and it just slows everything down here locally, so I got to do everything overnight.

Erik Darling: I can only imagine. But I know that there’s some, like, Data Migration Assistant, I think it’s called. Azure Data Migration, but I don’t know much that – I’ve never used it, so I couldn’t tell you that it’s like the greatest thing in the world. Anyway, that brings us to close enough to 12:45, thank you for joining us, thank you for, well, joining Brent, sort of, partial Brent, kind of.

Richie Rump: Brent from the future.

Erik Darling: Brief Brent. We will see you next week, hopefully with less technical difficulties. Goodbye.

This week, Erik, Tara, and Richie discuss enforcing password expiration for SA logins, keeping things in sync between servers, development/prototyping missions of on-premise vs. cloud, ways of backing up databases, 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.

If you prefer to listen to the audio:

Podcast: Play in new window | Download

Enjoy the Podcast?

Don’t miss an episode, subscribe via iTunes, Stitcher or RSS.
Leave us a review in iTunes

Office Hours Webcast – 2017-05-17

 

 

Should I enforce password expiration on the SA account?

Erik Darling: Richard wants to know, “if there are any issues with enforcing password expiration for SA logins; like something may stop working when the password expires or auditors want it turned on global.”

Tara Kizer: I don’t like it for accounts, or as the application, service count, because you’re going to have to set a reminder to fix these before it expires because you don’t want, you know, for it to expire at three in the morning on a weekend, and you know, suddenly somebody’s got to work on it. So I don’t like it, and at least when I’ve had auditors at previous companies, you can push back on those auditors and say we’re just not going to do this one.

Erik Darling: Yeah, for me, you know, it’s less of an issue because I don’t like letting anyone use SA anyway, I let apps use it, I don’t like anyone losing it. So like [crosstalk]

Tara Kizer: I guess the was specifically the SA login and I jumped into just, you know, authentication of – SQL authentication.

Erik Darling: Yeah, like if it was domain accounts I would say go to hell because what’s that going to change, if it’s a domain account? But for SA, like, I mean, I’m surprised that the auditor remark was that they want a password enforced when they usually just – usually auditors are like, we want this disabled.

Tara Kizer: Exactly, I don’t even use the SA account so that’s fine if it’s going to expire. Usually, we either disable it or rename it, so it’s a different name at least, but yeah disable it.

Erik Darling: Have like a proxy SA account [crosstalk] has a weird name, I don’t know…

 

Is anything cool happening at Google IO?

Erik Darling: Wes – well that’s not a SQL question, “can we spend the next 45 minutes praying to the Google gods for awesome stuff to be announced at 10PST?” Sure, you can spend [crosstalk]…

Tara Kizer: What’s going on? Is there a conference or something? What’s happening in the next 45 minutes?

Erik Darling: I don’t know, I’m unaware. Alright, it’s going to be a quick Office Hours.

 

Should I change this global temp stored proc?

Erik Darling: Kirk, Kirk is asking the question that he asked yesterday on dba.stackexchange, Idera analysis is suggesting that he use tempdb and he makes some ANSI set commands and then he alter a global temporary stored procedure. Kirk, Tara and I both left comments on your Stack Exchange question asking for more information. Please [crosstalk] I can’t tell you if you should make that – because I’m pretty sure it’s dynamics, that’s what those pound, pound ZDP things usually are, and unless you tell us what the rest of that stored procedure does, I don’t know if I can tell you to change it. It’s a silly question.

Tara Kizer: Probably ignore Idera here unless you’re in control of the software that is upgrading these because you’re not going to be able to make the changes.

Richie Rump: It’s GP dynamics.

Erik Darling: It is, so I don’t really know what you’re going to get out of changing that, what’s it going to do?

Tara Kizer: Probably can’t even change it.

 

How do you keep things in sync between servers?

Erik Darling: Michael says, “I have an ETL process that runs on two servers independently, each row gets an ID, what ways have you guys used to keep these in sync between two servers?”

Tara Kizer: I haven’t ever needed to keep things in sync between two servers, besides reporting and things like that, but you’re talking about something separate, but you know. If it’s an ID, it’s an identity column I suspect you’re referring to, the, I mean, what people usually do is they reserve identity values on one server and then reserve another set of identity values on the other server. Like negative numbers for one server and positive numbers, and then to keep them in sync, you just, you know, replicate between the two, bi-directional replication between the two, and there’d be no collisions.

Erik Darling: Yeah, I’d be curious what the use case is to have IDs synced by UTL processes on two servers. That seems like something that should be resolved during the, whatever, L to main data source you’re going to end up with. It doesn’t [crosstalk]

Richie Rump: I’ve always worked on systems where we have a single point of truth, right, and then that gets federated to all the other systems. Now, we may have one system and it may be on multiple servers, right, but that bit of data’s truth is on that server, and then that gets sent around all over the place wherever it needs to go. So yes, having it – is that for speed, I guess, maybe? Maybe there’s a lot of data coming in and you’ve got it being processed in two separate systems, then you need to zipper them back up, and at that point, what’s your alternate key to align them back up again? That’s about as best as I could gather for you, man.

Erik Darling: Yeah, I don’t even know what – since it’s running, it’s ETL running in two separate places, I don’t even know, like, how you would resolve, like what the resolution would be if IDs were mismatched, like what would happen [crosstalk]…

Richie Rump: well typically, I mean, you’d go to the natural key, right. The natural key would tell me if it’s the same or not, but not all data has a really good natural key, and if you don’t, well how am I going to zipper that up in with using some sort of made up ID that’s out of the ether of SQL Server?

Erik Darling: what’s the worst data you’ve ever dealt with? Coming up with something like relations for or something, what’s the worst data you’ve ever had to deal with?

Richie Rump: I think – I had to deal with a bunch of – like large data, like 60TB stuff, but that wasn’t bad in itself. I think the worst data I had to deal with was, I was working really early in my career, so it had to be over 20 years ago, and it was weather data coming from the government. And, you know they – I was given this blue book, right, this thin blue book. It looked like an exam book, and it had all these formats in it, and we had to figure out which format that you’re dealing with. And it was a ton of just flat-file data, and it didn’t even have an end-of-line, you know, the whole deal, and it’s…

Erik Darling: Yeah I’ve dealt with healthcare data like that, that was awful. It’s like the healthcare encoding, I forget what it’s called but there’s like specific encoding that you have to use for stuff that’s miserable, and when I was in market research, would get all these, like, this time zone and zip code information that was just, like, a nightmare. Like FIPS code and DUNS code…

Richie Rump: The codes are a nightmare.

Erik Darling: Oh it’s awful.

Richie Rump: The codes are a big problem of why we have our health problems today.

Erik Darling: Yeah, like all the electronic records – I don’t want to touch you, you’re gross.

Richie Rump: You gross, I don’t want to touch you.

Erik Darling: It’s all gross. Let’s talk about something else, let’s stop talking about gross data.

 

Are you celebrity DBAs?

Erik Darling: Michael asks, “he would love to hear the thoughts of the Office Hours celebrity DBAs”…

Richie Rump: I’m not a DBA [crosstalk] I am not one.

Erik Darling: Yeah, we don’t have any DBAs or celebrities.

Tara Kizer: You’re now a celebrity, people in Poland saying hi to you and…

Erik Darling: Oh, that’s one guy from Microsoft, that’s not people in Poland.

Tara Kizer: You’re not celebrity level. Richie and I still have to work towards that.

Richie Rump: Yeah, yeah…

Erik Darling: [crosstalk] Just blog more and you’ll soon reach the very heights of SQL Server…

Richie Rump: What I blog about people don’t want to hear, it’s like I get like ten comments. Brent is does, like, some crazy, you know, hey what do you think about this, and all of a sudden there’s 100 comments coming in.

Tara Kizer: you’ve got to leave a question at the end, that’s – some of mine that have a lot of comments I’m, you know, asking people.

Richie Rump: I don’t want to ask people anything. What do you think about Entity Framework? I don’t – I know what you think, okay, I know, I don’t want to hear it.

Erik Darling: That’s like 50 angry responses, no one [crosstalk]

Richie Rump: Only? Wow, it’s a good day.

 

Should I develop apps in the cloud or on-premises?

Erik Darling: Alright, let’s see here – anyway, forgot what I was doing… Michael says, oh boy, let’s see, “implementing SQL Server via on-premise database platforms versus Azure or cloud-based database platforms, things like development prototyping issues, lifecycle costs and administrative DBA considerations. Jeez, that’s like seven books.

Richie Rump: At least.

Erik Darling: That’s a lot. Richie, what would you say about the development and prototyping issues of on-premise versus cloud, if anything? You can tell me to screw myself if you want to…

Richie Rump: Well, off camera, because I don’t want to say it. It’s different, right. So it’s significantly different. Not happy, happy, I’m trying to click not happy, but…

Erik Darling: Tell me more.

Richie Rump: It’s – so in the cloud, you’re essentially, if you’re dealing with a pure cloud application that you’re going to build in there and you’re talking about services talking to one another, and there’s a whole different philosophy that you’re going to have to go and develop your application. If you’re talking about taking an application and then setting up some VMs and having that run in the cloud, I don’t consider that a cloud application, I consider that an on-prem application that’s running in the cloud. So there’s a lot of things that we say, it’s in the cloud but it’s not really in the cloud type stuff.

I haven’t really messed with SQL Server database a whole lot. I’ve actually messed with Postgres in the cloud a lot more than doing SQL database. So I’m going to have to punt over to you guys about some more of those questions.

Tara Kizer: All I know is that the – our clients that are in the cloud are having severe performance issues, especially severe slow I/O. They’re not spending enough money on, you know, what they’ve selected for their server and are experiencing pains because of it. It’s not cheap to get really good performance out of the cloud.

Erik Darling: If performance is your number one consideration, you should – you need to either be prepared to stay physical or spend a lot of money on upsized cloud instances, especially for what Tara’s talking about. The stiff that is at the biggest premium up in someone else’s computer is storage and networking. So as soon as, you know, you need to write data to disk or you need to move data from your server somewhere else, you can run into some tremendously slow speeds from both of those, avenues I guess we’ll call them. Just because it’s, you know, the cloud disk if you want, you know, a good fast storage… You know, like when we were messing with Google compute, it’s not only tied into the type of disk but also the size of the disk. So like a bigger SSD means it goes faster, like if you spin up a 50GB SSD you get worse than, like, SATA speeds. So there’s a whole lot of stuff you have to consider. It’s a little bit easier if you have a current physical workload that you can compare it to and compare metrics to and know. That makes it a little bit easier, but if you’re starting from scratch, then you know, you’re just going to have to be prepared to size-up regularly.

Richie Rump: Yeah I think, you know, if you could find a small application and then bring it up to the cloud and then do some learnings on that, maybe if it’s an application that’s not even in production, or whatever, and so that you can say how will this application work in the cloud. It’s really hard for us to understand if the cloud is right for you without understanding what your needs are. Does it need to be fast, does it – how much traffic does it get, I mean what’s the performance? I mean, there’s a lot of stuff that goes on here, and since you run the app and you run the system, probably best to get into it and figure out, hey, will the cloud work for this app or will the cloud work for another app? Is it worth for us to start prototyping this stuff out? And then you can – there’s hard numbers, right, so how much is this costing on-prem versus how much would this be costing us in cloud? There’s a ton of stuff there that, you know, needs to be sifted through.

Erik Darling: Yes, you know, and another thing that comes up always is staffing complexity, you know, up in the cloud depending on how you choose to, you know, provision things. You could either have, you know, the cloud taking care of backups and, you know, high availability for you and all that stuff. But I don’t know if that meets your needs, because, you know, if you need to offload reporting or something else then you still need to come up with your own sort of solutions there.

Richie Rump: Yeah, it’s still in its infancy, I mean, there’s still a lot of growing pains in the cloud. It’s a lot easier if you’re Greenfield development. If I’m building something new in the cloud, things get a lot easier as opposed to I have something old and I’m bringing it up. We could code around a lot of different issues because we’re actively developing as opposed to I have this thing, this app, that was built to have a server right next door to it, right, talking to one another, and now I’m bringing it up into the cloud and everything’s going to crap. Well, those servers aren’t near one another, that’s just kind of the way it works out there. And they may not always talk to one another, and you know, if a switch goes down in your data center, everything goes crazy. If a switch goes down in the cloud, they yawn, you know, and they move on.

Erik Darling: Yes, cool.

 

Is there a better way to do backups than the backup command?

Erik Darling: Renee asks a question that I find particularly odd. I think Tara’s going to screech at this one too. “Do you know about a way to backup databases besides SQL Server built-in functions, stuff like copying data files and log files and pasting them?”

Tara Kizer: What are you trying to do?

Erik Darling: Stop, put the database down, stop, I’m begging you. That’s stop with an A and 15 H’s, “Stahhhhhhhhhhhhhhhp.”

Tara Kizer: Whatever backup software you’re intending to use, make sure it’s VSS aware so that it does a, it quiesces the database files and when it does the snapshots so that the data files and the transaction log files are at the same moment in time so that you’re not doing a dirty snapshot. Also SAN snapshots, but again, it needs to be quiesced.

Erik Darling: We’re not even there yet. Renee is asking about copying and pasting data and log files.

Tara Kizer: I know.

Erik Darling: We’re not even at the [crosstalk] part yet. So a long time ago I inherited a process that was an archival process. And the archival process would go through and it would find databases no longer in use on this awful clown car of a server that had upwards of 5000 databases on it. It would take them offline and then it would copy and paste the data and log file – it wouldn’t copy and paste, but it would use an SSIS-like package to move stuff over.

And there were times when the data transfer would be interrupted or go awry in some other way, and so one of the files would be incorrect, and so you wouldn’t be able to bring the database online, because say like a data file would have some issue inside it or a log file would have some issue inside it. And so I really can’t recommend enough to not do that, at all, ever. Like, aside from the permissions issues nightmare that you face trying to do that, you can also screw yourself up in a lot of different ways. Even, you know, if you wanted to do something like zip the files up and then move them, it’s still a heck of a problem. You’re really – take a backup or, you know, use backup software from a known vendor who does things reliably. Don’t copy and paste, please.

Richie Rump: Keyword, reliably.

Erik Darling: Yeah, because another problem with copy and paste is if you have large databases and you use copy and paste, that data has to buffer somewhere and it’s going to be on your server. So you’re going to have this horrible RAM nightmare when you copy and paste. [crosstalk] It happened to me once using PowerShell. I used a move item command to try to move a 5TB database from one drive to another. Do you know what happened? The server ground to a halt for about 45 minutes. Nothing else going on in there, that circuit was closed.

Tara Kizer: [crosstalk] Snapshot or something?

Erik Darling: We didn’t have that available to us, unfortunately.

Richie Rump: So you’re telling me it’s – you ran, you ran so far away?

 

What’s your second favorite Flock of Seagulls song?

Erik Darling: I did, I did, I ran all night and day. What’s your second favorite A Flock of Seagulls song?

Richie Rump: They had something other than one?

Erik Darling: Yeah.

Richie Rump: Really?

Erik Darling: I swear to god. Well Ah-Ha is a real tough one to come up with a second favorite for. A Flock of Seagulls is easy; an astute observer of new wave music like yourself, Richie, should have a very easy time with your second favorite A Flock of Seagulls song.

Richie Rump: I’ve been in alt-rock the last two years, and the Hamilton’s soundtrack, so – bet everyone knows that.

Erik Darling: You have a new wave playlist, you showed it to me and we argued about U2 being on it.

Richie Rump: [crosstalk] That’s the early U2…

Erik Darling: I don’t know about that. But no second favorite Flock of Seagulls – Tara, how about you?

Richie Rump: I can’t think of one.

Tara Kizer: I love that genre of music but I don’t have the bands memorized, so I’d have to look up what are their songs. I know the band name and I definitely know their music.

Erik Darling: Alright, so Space Age Love Song is an obvious second favorite, in case anyone needs to know, in case anyone is curious out there, that’s the second best A Flock of Seagulls song.

 

When I cancel a query, do I need to roll it back, too?

Erik Darling: Alright, Alex Ozar, wow, he is a [crosstalk] Ozar. He executed a long complex query with a BEGIN TRAN and a rollback in management studio and he clicked cancel – yes…

Tara Kizer: he’s asking whether or not he has to issue the ROLLBACKTRAN command or if the cancel would roll it back.

Erik Darling: The cancel should kill the transaction and roll everything back, as far as I know.

Tara Kizer: You’ll know if you try to close the window. Try to close the window and SSMS is smart enough to know whether or not you still have an open transaction.

Erik Darling: Yes, or you could run sp_WhoIsActive and see if you have any – or if you’re too lazy to download WhoIsActive you could run DBCC OPENTRAN. [crosstalk] Like the 90s, just like the 90s.

Richie Rump: Or you could just run ROLLBACKTRAN yourself?

Tara Kizer: Yeah, go ahead and run it. It will just error if you don’t have an open transaction.

Erik Darling: Alright, let’s see here. Ronnie is asking something about logins. Ronnie, that’s a better question for Stack Exchange because I don’t see an error message or anything in there, so you’re just kind of asking us to blindly troubleshoot your login issues, which is impossible, sir. Unless it’s an SPN issue, in which case, go look for SQL soldier SPN things and you’ll find good information.

 

Does SQL Server need the log files after a clean shutdown?

Erik Darling: Nick is asking, “when SQL Server stops cleanly, is everything in RAM flushed to data files such that the log files don’t contain anything important anymore? Just curious, I promise I’m not backing anything up with copy and paste.” Yeah, I believe part of the shutdown process is checkpoints, right?

Tara Kizer: Yeah, so any uncommitted transactions in the transaction log file are rolled back and anything that was completed get applied. So, but if your transaction log files still has the transactions in them, because that’s going to be dependent on your recover model and if it is full or bulk-logged, then it’s going to depend on your log backup frequency. It’s not necessarily about SQL Server stopping and restarting; that stuff still exists in there.

Erik Darling: Yes, so one kind of interesting thing you’ll see when you start up SQL Server is, if you go in the error log and you look at the very beginning, you may see a whole bunch of messages like, X amount of transactions rolled forward in this database, and stuff like that. So that’s how you know SQL is keeping track of what’s going on. That’s all I have to say about that; I got nothing else on that one. You guys?

Tara Kizer: No.

Richie Rump: No that’s – oddly enough that’s all the questions we have too.

Erik Darling: That is, alright, so unless you guys really want to hang out and babble, we can just call it a day here.

Tara Kizer: Sounds good.

Erik Darling: Alright, cool. Thanks everyone for showing up, we are leaving five minutes early because we have no more questions, but thanks for stopping by and we’ll catch you next week.

Tara Kizer: Alright, bye.

This week, Tara and Richie discuss increasing the size of tempdb, splitting database across several files, tracking DML changes to a table, upgrading different versions of service packs, recommendations for improving T-SQL skills, trace flags, replication, Always On Availability Groups, compatibility level of databases,  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.

If you prefer to listen to the audio:

Podcast: Play in new window | Download

Enjoy the Podcast?

Don’t miss an episode, subscribe via iTunes, Stitcher or RSS.
Leave us a review in iTunes

Office Hours Webcast – 2017-5-10

 

Do tempdb growth settings require a restart?

Richie Rump: So Wesley asks, “Here’s my really dumb question of the day, and a yes or no is fine; no explanation needed…” I say it is needed. “If I increase the size of my tempdb, do I need to restart the servers to pre-provision it?”

Tara Kizer: No, you’re good to go. I think the only time you have to restart SQL Server for a tempdb change is if you change its location. If you, like, move it away from, you know, the system databases because, you know, you accepted the default string installation. You can make the change, but it doesn’t take effect until after you restart SQL. But just increasing the size or even shrinking it, you know, you can do that live. It takes effect right away.

 

How do I spread data across multiple files?

Richie Rump: Okay, so Mark asks, “because of space requirements, I need to split a 1.4 terabyte database into three 5GB files, one file group. What’s the best method to equally spread the current db across three newly created files?

Tara Kizer: I’ve had to do that work. I mean, if it’s one file group, you know, you add the three files and then you rebuild indexes, you’re going to have to rebuild the indexes maybe even a few times. At least that’s what I had to do to do that. If you’re using multiple file groups, it different. There’s an article by Bob Pusateri, you might remember the article, Brent and Erik would know that. But there’s an article from him on how to move the objects around with no downtime. But one file group, I think you’re stuck with rebuilding the indexes so it spreads the data across those three files.

Richie Rump: I remember that article, I know which article you speak of.

 

What’s the best way to track DML changes?

Richie Rump: So next question, “I am currently tracking changes to an audit table using triggers; the problem is, I’m using triggers. Is there a better way to track DML changes to a table?

Tara Kizer: I always wonder, you know, are people trying to solve an auditing thing at their company, you know, it’s a policy, for whatever reason, it has to be tracked, or are people just curious? Because, I mean, you’re adding overhead to the transactions to do it. I mean, you know, a lot of people have used triggers to do it. I think change data capture also helps you here, or maybe it’s change tracking; I can’t remember which one.

Richie Rump: Change tracking.

Tara Kizer: Change tracking, yes. So I don’t like the idea of adding time to my transactions auditing.

Richie Rump: Yes, I concur there.

 

Can I manually commit someone else’s transaction?

Richie Rump: Alright, “so after finding a session with an open transaction that will likely never commit or rollback on its own and preventing version store cleanup, what steps would you take to determine the session can be killed, or is there a way to manually commit the transaction on behalf of the session?”

Tara Kizer: there’s no way to manually commit the transaction on behalf of the- session, that just – it’s not possible. If you restart SQL Server, it’s going to go into crash recovery because it has to rollback, and I can’t remember if you can determine how big the rollback will be. I don’t remember that, but you know, look at WhoIsActive, DMVs, sp_who2 to see what kind of writes it has done, that spin session ID has done to see if it’s just a small amount of writes. Your rollback should be pretty fast, even during crash recovery. You know, what is that session doing? I don’t necessarily agree with never – although I guess I have seen some sessions that just are stuck, you know. They’re not doing anything, I don’t think that they were waiting on anything… I don’t have a good answer for you.

Richie Rump: We have no good answers, only bad ideas.

Tara Kizer: I would suggest maybe posting that question on Stack Exchange or even Twitter using the #SQLhelp. SQL help hashtag, you know, it’s a little lengthy but you could shorten it to 140 characters or do two tweets like I do sometimes [crosstalk].

 

Can I update when I’m doing a join?

Richie Rump: So J.H. needs, “to update a row – rows in a standalone table based on multiple other tables join on after updates, inserts, deletes, triggers. Can you provide or point me to examples that I can use in tweaking?”

Tara Kizer: J.H. asking about triggers, because his join on after updates – well, at any rate, you know, you can do joins and updates and inserts and deletes just like you can with a SELECT. The syntax is basically the same thing. Write your SELECT query and then grab – starting at the FROM, all the way down and then paste that into your UPDATE, INSERT or DELETE. I mean, it’s the same command, it’s just the B NULL at the top. As far as examples, just Google SQL update join example, you’re going to see it; but it’s the same syntax.

 

Is there a third party app to check service packs?

Richie Rump: Yep… “Is there a third party tool that will tell me if SP3 will break my application before packing to SP3? Currently, we’re on SP2, SQL Server 2012.” Is there magic, is there magic somewhere here that will tell my application if it will break or not?

Tara Kizer; No, between SP2 and SP3, there’s nothing that’s going to break your application. It’s the major versions that there’s potential for, you know, syntax getting deprecated, you know, old ANSI join syntax, maybe later versions, I think, is gone. I’m not positive on this – deprecated for years. But it’s the major versions that you really need to run like the upgrade advisor, but between SP2 and SP3, no. your only concern is, you know, the inflation succeeding, making sure you have backups, so make sure you install it in a test environment. Always test your application in a test environment when you make a change like that, but, you know, there’s no third party tools to help you out with this.

Richie Rump: Do you have a patronous, maybe expect patronoum, maybe? That’s a Harry Potter reference, people.

Tara Kizer: Okay, I was going to say, I have no idea what you’re talking about.

Richie Rump: Wow, wow, and I read them as an adult, I mean really, seriously.

Tara Kizer: I’m just – I’m not a book reader, that’s all.

Richie Rump: Oh, I’ve got like four sitting right here that are just staring at me. It’s like, why are you working? Read me.

Tara Kizer: I’m in the middle of a book series, but you know, it takes me an eternity to finish stuff.

Richie Rump: Ooh, which one?

Tara Kizer: Divergent.

Richie Rump: Oh, you’re reading Divergent, okay. I haven’t cracked that one open yet. I’ve been hardcore nonfiction for a while now so…

Richie Rump: Okay, next question. “Where do the sub-tree costs come from in the execution plans?” I remember someone saying it was from the late 90s, and which was nothing wrong with that. There was a lot of good things happening in the late 90s, let me tell you people.

Tara Kizer: I started my career in the late 90s as a DBA. I know that Erik will say that it’s someone from Microsoft, I forget the person’s name, but someone’s desktop was used as like the baseline and the calculations were based upon that. But other people have disagreed with that; I’m not sure who’s right, but it’s just some number that Microsoft is coming up with. So when we see high costs, it just means that that operator is doing a lot of work, or, you know, maybe it’s more complex. Apparently it was all based on someone’s computer back in the 90s.

Richie Rump: yes, what that means to us is that it’s a random [crosstalk]. Right, I mean it’s like a foot the size of the king’s foot essentially?

Tara Kizer: Yes, exactly, right.

 

What’s new in SQL Server 2017?

Richie Rump: I’m going to ask this question and – just because I know the answer. “What’s new for 2018?”

Tara Kizer: Go for it…

Richie Rump: I don’t know, I haven’t been paying attention.

Tara Kizer: Well there’s nothing new, the product is SQL Server 2017, they’ve already announced that it’s 2017. So, I don’t know if there’s going to be a 2018 version; I actually doubt it. I think some of the community is a little upset that 2017’s been released so early after 2016, you know. There’s not much adoption for 2016 as it is, so 2017 will probably be even lower. But 2017, I think – I saw a blog or something like that about – it is being released this year because they’ve already named it 2017, but maybe it’s soon, I don’t know.

Richie Rump: Yes, so I’m just going to move all my production workloads over into Azure, into manage Postgres and just say, well sorry SQL Server, now that Postgres is here, everything’s going over there. So, that’s my story…

 

How should I learn more about SQL Server?

Richie Rump: Alright, let’s see what other questions – you guys have been talking a little bit, so let’s see what else we’ve got. “Do you have any recommendation of the best ways to improve your –SQL skills?”

Tara Kizer: I mean, it depends on what your level is at right now. I had a recent client that was doing some pretty complex stuff in their T-SQL and, I mean, really this stuff was beyond what I could help them with, you know. I helped them with everything else, but Brent had the suggestion to recommend that they attend Itzik Ben-Gan’s advanced T-SQL class. If you’re in the United States, he does come here. He lives somewhere else, but classes offered all the time; so if you’re looking to become, you know, really, really advanced, definitely is class, I think, is supposed to be like the best out there for advanced query tuning.

I mean, if you’re more of a beginner level – I mean, the way that I improved my skills for free is I just started, you know, looking at questions in forums and trying to answer them on my own in management studio and then as I learned more, I started, you know, answering questions. So I’m more of a learn on the job type person.

Richie Rump: Yes, and I’m more of a book-y type guy, so a couple of books, SQL For Smarties, I think is one of them, by Joe Celko. I remember reading that. Anything by Itzik Ben-Gan; we talked about him before, his class, but seriously, really in depth stuff on T-SQL. I do a lot of dumpster diving in docs, in the documentation. So it’s kind of how I learned all the new-y stuff in SQL Server 2012 when that first came out. And – because that is the last, essentially new feature dump that we have gotten in T-SQL over the past couple of years, with 2012, and it was just a ton of new stuff in there. While doing my dumpster diving, that’s when I learned that we have a lot of .NET stuff underneath the hood of T_SQL.

So when people say, oh I just turn off .NET, it’s like, no you don’t. It’s back there, it’s running, there’s nothing you could do to turn it off. Now you may be able to turn off the user executions of the .NET, you know, CLR inside of it, but it’s using >NET in the background and all that stuff. So it’s pretty interesting stuff when you start actually reading the documents and things like that, but that’s just my craziness when I just can’t fall asleep at night.

 

Should I upgrade to 2016 or 2017?

Richie Rump: Alright, next question, “My boss wanted me to start testing to upgrade our production servers to 2016 Enterprise. Is it worth pushing to 2017, will adaptive joins be added to 2016?”

Tara Kizer: I mean, if it were me, it would depend upon your timeline. So, you know, are you talking about this summer? Because I don’t know that I would be comfortable – you know, depending on when 2017 comes out, I don’t know that I would be comfortable upgrading to 2017, because that doesn’t give you a lot of testing time on it. You know, the product gets released, now you’ve got to complete the full, go through the full test cycle, you know. As far as the adaptive joins, very unlikely to go into 2016. From what I understand, they don’t back-port features. They’ll back-port, you know, bug fixes and minor stuff, but I think adaptive joins is a pretty big thing being added to 2017. It’s extremely unlikely to go to 2016 in my opinion, you know, who knows?

Richie Rump: I think I’m going to stand up, because I am standing up, and I’m going to say no, it will not be in 2016. That’s – I don’t think I can remember a feature that was back-ported to older versions. [crosstalk]

Tara Kizer: Yes, the only thing that I know of that was back—ported was troubleshooting data for large memory grants – the memory grant information is in 2012 SP3 and 2014 SP2, not available in the versions prior, in the builds prior to that. But that’s not a feature, that’s just more troubleshooting data available to us as performance tuners.

Richie Rump: It’s not a feature, it’s a bug. We’re pretty sure that’s a bug.

 

Can I run Standard Edition on a 32-core server?

Richie Rump: Alright, so Stuart has a 32 core server running SQL Server 2016 Standard Edition. I wonder if he stole Erik’s workstation? “I think the maximum cores allowed for this version/edition is 24. Can I keep my server at 32 cores and use the rest of the core capacity for non-SQL database engine activities such as SQL porting services or other non-SQL utilities?”

Tara Kizer: You know, I’m not a big licensing person, but I’m pretty sure you’re in violation of your license. You’d have to disable those eight cores. Whatever cores you have in the box have to be licensed, even if you’re not going to use them. There’s a way to do an affinity masking, so you can, you know, disable those other cores, but I’m not sure if that’s in violation of your Standard edition license. I would contact your licensing person to see if this is even allowed.

Richie Rump: Yes, so we’re not going to release your name or your location to anyone, just – it’s between you and me, buddy, you and me, Stuart. Alright – oh and I just released his name; there it goes. I’m sorry dude…You hear a knock on the door, yeah, that’s Microsoft coming.

 

Can tempdb slow queries down?

Richie Rump: Alright, so, “can a tempdb cause the database queries to be slow? I allocate a large tempdb data files for 4GB temp files instead of the auto-growth option. I wonder if I/O can cause the latch I/O wait with tempdb?”

Tara Kizer: I mean, can tempdb slow things down? For sure, you know; tempdb’s recommended, you know, to put that on the fastest disk possible and your four 10GB files are actually pretty small, in my opinion. That’s only 40GB. I’ve had tempdbs that were over 500GB total, so spread across eight files. So, in my opinion, you have not allocated large tempdb data files. But definitely, if tempdb is on slow storage, it can certainly cause your query’s overall system performance. So tempdb is critical for system performance to be as fast as possible. SSDs, you know, implement trace flags 1117 and 1118; those are recommended my Microsoft. They affect the other databases, but they do help out tempdb.

 

I’m having a problem querying LDAP…

Richie Rump: Okay, so Deb has two identical SQL 20… 2005, sorry Deb, my apologies, test and production. “One accesses LDAP perfectly, the other one does now. Any hints as to why or why not?” So what kind of troubleshooting steps would you take?

Tara Kizer: I don’t have any experience with querying LDAP from SQL Server. I know that it can be hard to configure. I’m not the person to ask. I would ask the question on Stack Exchange instead.

Richie Rump: Yes, I’m thinking some connectivity, I do some connectivity testing; making sure I could hit the server. And then maybe sure I could hit the service outside of SQL Server. Maybe it’s some sort of permissions deal that is going on from server to server. That’s kind of where I would go first, but I’ve been working in the cloud for almost a year now, and everything I go is security. So that’s just where I’m going now.

Tara Kizer: Yeah…

 

Can trace flag 4136 cause issues?

Richie Rump: alright, so Richard has a trace flag number, so here we go; trace flag bingo. “Can you tell me if trace flag 4136 for disabling parameter sniffing can cause issues?”

Tara Kizer: Heck yeah it can cause issues. I mean, I don’t have any experience with 4136, but if it does disable parameter sniffing, I don’t think that that’s what I would want to put in place in my production environment. Instead of that, you need to work on why you’re encountering bad parameter sniffing. Parameter sniffing is a good thing; if you disable it, every single query has to compile. You’re talking about needing large amounts of CPU for that to occur.

You know, if you have a really slow system, not much batch request per second, for instance, then maybe your server can handle it, but if you disable that, you’re going to see CPU issues because every query has to be compiled. So instead of that, your default needs to allow parameter sniffing to happen; it is a good thing, except when it’s not. So there is bad parameter sniffing. You need to work on what specific items are encountering bad parameter sniffing and put the workarounds in place. You know, sometimes all you have to do is add a covering index to fix the issue. I’ve had to put plan guides in place, index hints, you can use the optimize for. One of the less desirable approaches is optimize for unknown. But you need to find the specific queries that are having issues and resolve those, put workarounds in place for those. Otherwise, you’re talking about probably needing better hardware, if you’re going to disable parameter sniffing.

Richie Rump: yes, one of the problems I had a few years ago with a client that – they had this query that kept running. I mean they have it running at multiple times a minute, and their solution was option recompile. And it was okay for them because they had a pretty beefy system and the way their data patterns worked, they kind of wanted just for that one – a plan just for that one. They had plenty of memory, everything was good, we tested it for a few days and everything was great, but that’s not really the recommended way we, kind of – it’s not the first thing we went for, right, I mean…

Tara Kizer: Yeah…

Richie Rump: But that’s – in the end, that’s kind of what worked for them.

Tara Kizer: Yeah, I mean, you know, I’ve definitely used option recompile. And back in SQL Server 2005, we didn’t have that, so you did with recompile, the stored procedure [inaudible]. So I’ve definitely put that in place in production, even on queries that were run extremely frequently. You just have to be aware of that, you know, option recompile, you know – extra CPU time and if you’re going to put it on every single query it’s basically the same as disabling parameter sniffing. But it’s definitely – that’s one of the workarounds which – I want you to put that on, the specific queries that are having a problem. Let parameter sniffing be your friend until it’s not and then fix those specific queries.

Richie Rump: Alright, so we are out of questions. I don’t know if that’s a good or bad thing, but that’s a thing [crosstalk]

Tara Kizer: Are you not seeing more questions?

Richie Rump: I am not seeing any more questions.

Tara Kizer: Oh, okay, I’ll ask a couple…

Richie Rump: So now’s a perfect time to get some questions in. if not, we will…

Tara Kizer: There was one about trace flag 4199, I’m trying to find it in my list. I like that question.

 

How should I do replication for a reporting server?

Richie Rump: Oh here’s one, “I like to replicate a couple of databases from 2008 to 2014 for reporting from the 2014 SQL Server. Is that possible and which form of replication would you recommend, tips, tricks, gotchas?” Thanks.

Tara Kizer: yes, it’s definitely possible, but – you know, for reporting, yes I’ve definitely used transactional replication for reporting. You can also do snapshot replication. I don’t have any experience with snapshot. The business requirements for, on my reporting environments, were, you know, that the data needs to be near real time, and so that’s – transactional replication is the solution for that. If there can be a lot of latency then, you know, maybe snapshot replication, and you may even be able to do, you know, log shipping with the restore – put a delay in the restores on the secondary server. But replication is a really viable option for a reporting environment and you definitely can go between two versions.

Richie Rump: yes, I’ve used snapshot for reporting to reporting databases and it worked great. I’ve even used merge replication and then, you know, about once every six months we pulled our hair out, and that’s kind of the way that worked.

 

Would you recommend trace flag 4199 by default?

Tara Kizer: Yeah, I found that question about trace flag 4199. So it’s from Steve, “would you recommend turning on trace flag 4199 globally to enable optimizer fixes?” And for my experience, the answer is yes, except on 2016. I believe 2016 is the version that they have rolled in those fixes into the, you know, you don’t have to enable a trace flag anymore to do it. It might be 2014, I think it’s 2016. You’ll need to Google for that.

At one of my jobs, we deploy 4199, basically, on every new server because it proved to be beneficial. Microsoft was always about backwards compatibility, so I mean, they didn’t want to roll in these fixes by default; so that’s why it’s available in the trace flag.

Richie Rump: Yeah, almost to a fault, I would say. I mean backwards compatibility almost to a fault. You know, I don’t know if I want – you know, I know there are some companies out there that like, look, I have a 20-year-old VB, you know, 3 app, that needs to run. And it will run on Windows 10, but I don’t know if I’ll ever need to run a VB3 app ever again, or open the IDE, or anything else like that. So it’s kind of scary…

 

How can I let users query reporting tables during updates?

Richie Rump: So, we do have some more questions, which is great. “So what’s the best strategy to allow users to query reporting tables while they’re being updated? I.e. morning loads overlapping with users starting their day without blocking the morning loads.”

Tara Kizer: I like recommitted snapshot isolation level for this. I mean, you could also use snapshot isolation, but then you have to change your code to use snapshot isolation. So recommit snapshot isolation level was added in SQL Server 2005. In my opinion, SQL Server 2005 was the first product that could finally compete with Oracle, and that’s because of RCSI. So recommit snapshot isolation level is RCSI. So with RCSI, reads don’t block writes, writes don’t block reads. Now, if you’re increasing the isolation level in the query, you know, say do a UPDLOCK and an HOLDLOCK, that winds up at [inaudible – serializable] isolation level, you will have blocking at that point. So as long as you’re not adding those kinds of hints in your queries, you know – reads don’t block writes, writes don’t block reads.

But the thing to know about RCSI is it’s compatible only with read committed, which is the default of the database and is the default for your connection strings.

If you’re using NOLOCK hints or read uncommitted, then that is not compatible with RCSI. It can still live happy in that database, but you’re not getting the benefit of RCSI for those specific queries. So you need to switch your connections, make sure they’re recommitted and then start removing your NOLOCKs. In my opinion, RCSI is much safer to use than NOLOCK hints, because NOLOCK hints you’re not guaranteed to get good data. You can get completely bad data, whereas RCSI you’re at least getting good data, just might not be completely up to date.

Richie Rump: So back in the SQL Server 6.5, 7, 2000 days we used to copy tables in with a different table name, and then we would drop the reporting table – rename, I’m sorry, rename the current table that’s there, then quickly rename the new table that’s in there, and then drop the table that was originally there. So that was our poor man’s replication back in the day.

Tara Kizer: yeah…

Richie Rump: I’m showing my age now.

Tara Kizer: You’re as old as me.

Richie Rump: Scary… Not old as Brent.

Tara Kizer: We’re just barely under him though, I think.

Richie Rump: Yes, yeah by a year, but you know, I shove it in his face as often as possible, man. Wait until he reads the transcripts of this, it’ll be fun. (Brent says: this is totally going in your employee file.)

 

Should developers be allowed to view server state?

Richie Rump: Alright, “interested in your opinion of granting developers view server state or access to read error log?”

Tara Kizer: I don’t know, I don’t like – if someone needs elevated permissions then, you know… Developers need elevated permissions besides just selecting production, which is the only thing I allow in production, and even then I sometimes don’t give it out, then they need to be put on the on-call rotation with me because, you know, the more access you need, the more – you’re a DBA here. Is there an issue with the [inaudible] accessing the error log – what are they needing to see? What is going in the error log that developers are needing to see? If the application is writing messages to the error log, well I’m of the opinion that that should stop. That information should not be going to the SQL Server error log. Put it somewhere else, in the application.

Richie Rump: Yeah, I agree and when I walk into a place I’m like, I don’t want even access to production, I don’t even want read access.  Now eventually I’ll end up getting it and then they’ll probably – they try to talk me into writes as well, I’m like no, you’ve got someone else’s, no…

But I’m of the opinion that if you have a developer environment, then give them full access to everything, so then they can start testing new features and start playing around with certain things. And maybe they’ll even get better in their query writing and understanding SQL Server and all that stuff. Like that’s their area, but production, I’m a dev, I don’t want it; I don’t want access to it at all. And yet, I still have access to it here. What’s going on with that, man?

 

Can I use AGs without a listener?

Tara Kizer: Ron’s question, going back to the question about using replication for reporting, he asked, “why not use always on without a listener for reporting?” Just for clarification, always on isn’t a feature, it’s a marketing term. The feature you’re referring to is called availability groups. So always on is availability groups and it’s also failover cluster instances. So I think you’re referring to availability groups here and you’re having a readable secondary. That’s definitely a viable solution for reporting; that’s one I’ve deployed to production and supported. However, it’s not available in Standard Edition, which is what the person asking the question was using.

 

Does SSMS affect compatibility level settings?

Richie Rump: Alright, we’ll go one more. “What is the compatibility option used for SSMS? When you restore a db it has different levels depending on which version you have, you restore it for them.”

Tara Kizer: Yes, so I mean, Jamie’s referring to the compatibility level of the database, it has nothing to do with SSMS. So wherever it came from, whatever compatibility level it was using on the source server, that’s what it’s going to be restored to unless that lower compatibility level is not available in the version you’re restoring to. So if you restored from – if you were in 2008 with a 2005 compatibility level database and restore that up to 2016, it would not use a 2005 compatibility level because it’s not – 2005 wouldn’t be supported in 2016. So in that instance, it would raise you to the lowest compatibility level that that version supports.

So if you want to see the compatibility levels, just right click on the database, go to properties and I think it’s in the options page and just look for compatibility level. 90 is 2005, 100 is 2008 and 2008 R2, and then up from there. 2016 is what, 130 I think, something like that.

Richie Rump: Yep, that’s – I think that’s all we got. We’re almost at time.

 

Can I blame someone without access when the server breaks?

Richie Rump: Actually Ben had one last comment, “if you do not have access to it, you cannot be blamed when it breaks.” As a developer, that is wrong, that is absolutely wrong. You don’t have access to it, you still could be blamed when it breaks because someone had – end up writing that app…

Tara Kizer: Your code…

Richie Rump: And someone’s going to end up getting blamed, so… It’s a nice theory but it doesn’t really work in practice very much. Alright, so unless you’ve got anything more, Tara?

Tara Kizer: No.

Richie Rump: We’re good, alright, see everyone next week.