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…
If you prefer to listen to the audio:
Enjoy the Podcast?
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.