[Video] Office Hours 2016/09/28 (With Transcriptions)

This week, Brent, Richie, Tara, and Erik, discuss non-clustered indexes, dynamic SQL, replication, setups for servers dedicated to reporting services and warehouse databases, cross-cluster migrations, employment opportunities in the current market, and much, 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.

Enjoy the Podcast?

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

Office Hours Webcast – 2016-09-28

 

Can I create a nonclustered index on a partitioned table?

Erik Darling: It is 12:15 so I’m fixing to start asking some questions. Whoever wants to answer them can answer them. The first one we have up is from someone named Lori. Says, “Can I create a nonclustered index on a partitioned table? Does anyone have an opinion on this?” Tara looks like she’s concentrating, so I’ll go ahead.

Tara Kizer: I’m trying to figure out the slides still.

Erik Darling: Yes, you can. You can absolutely create many nonclustered indexes on partitioned tables, but you’re going to want to make sure that you align them to the partitioning scheme or else you will lose all of the magic management stuff that comes out of partitioning your table. You won’t be able to swap in or swap out or do any of the other cool stuff. So when you create your nonclustered indexes, make sure that you don’t create them on primary. Make sure you create them on whatever the partition schema is called.

Richie Rump: I actually worked on a system where we didn’t intentionally align them with the partitioning.

Erik Darling: What? You’re crazy.

Richie Rump: Yeah, because partition swapping wasn’t something that we really needed.

Erik Darling: Oh, there you go.

Richie Rump: We just needed the data partitioned so that we could query it better.

Erik Darling: Why didn’t you just create a partition view then?

Richie Rump: I didn’t—I came in and they already had all that stuff. Some of these tables were like five, six billion rows.

Erik Darling: That’s it?

Richie Rump: They just said for size perspective, getting on different drives and stuff, they did the partitioning.

Erik Darling: All right. I will consider that one answered. I don’t want to right click on anything there.

 

Which Summit R session are you most excited about?

Erik Darling: Angie asks, “Which R session are you most excited about seeing at Summit?” The third one.

Richie Rump: The Star Trek one over at the EMP. That’s probably the one that I’ll be heading off to.

Erik Darling: Whichever one is the quietest when I have a hangover in the morning, whichever one has the comfiest seats.

Richie Rump: Since when did the PASS Summit turn into the R Summit? That’s what I want to know.

Erik Darling: As soon as PASS starting being PASS and stopped being the Professional [garbles words], words that I somehow can’t pronounce at the tender age of 950.

Erik Darling: “May be in left field…” I don’t know. Who plays left field for the Cubs?

Richie Rump: Depends on the day, usually Jorge Soler.

Erik Darling: Yeah, who else is on that?

Richie Rump: Kris Bryant plays some left field. Chris Coghlan played last night. Szczur was in there as well.

Erik Darling: Who’s good? Who’s your favorite left fielder?

Richie Rump: Uh. Moisés Alou from the early 2000s.

Erik Darling: Oh. That’s a weird one. Where did that come from? What did he do?

Richie Rump: He almost caught the Bartman ball.

Erik Darling: Oh, okay. Little biased.

 

Can PowerShell integrate with Azure SQL DB?

Erik Darling: All right, let’s see. Oh yeah, “May be in left field, can PowerShell integrate with SQL Azure databases?” I don’t know because I have never touched Azure but I see a lot of stuff about Azure and CLI and whatever. So I assume that there’s some integration. How deep it is and how effective it will be depends on your ability to stare at dollar signs and periods for long periods of time.

Brent Ozar: I concur.

Erik Darling: Yes. Brent is here. He just flew in and boy are his arms…

Brent Ozar: Pale.

Erik Darling: Boy are his arms…

Brent Ozar: All right. What did I miss?

Erik Darling: We’re having a really high energy Office Hours. There are three or four questions. There was one about partitioning.

Brent Ozar: Uh oh. Have we started asking? Shall I be MC or is someone playing MC today?

Erik Darling: I was asking but I don’t care if you want to ask.

Brent Ozar: No, you go ahead.

Erik Darling: Okay, all right. Fine. You get to ask some questions.

Brent Ozar: I get to answer, yes.

 

How do I solve SPN issues?

Erik Darling: Here’s one just for you. I’m going to tee this one up for you. “Having an issue with SPN SSPI across domains. It works in each domain but not across the two-way trust.”

Brent Ozar: Man, I was literally just on site at a client in Omaha yesterday and they were running into this. What we’ll do is I’ll post a blogpost because I never remember how to do this either. If you search for “set SPN” and then “SQL University Advanced Training Week,” then “set SPN.” There’s a blogpost by Robert Davis. Another set of search terms you could use is “SQL Soldier.” Robert Davis is an MCM who has step-by-step instructions on how you go about fixing that, which is good because I never, ever, ever remember it. I mean, ever.

Erik Darling: I ran into that back in the bad old days. It was always like an adventure for me to fix, like the DOS commands. I’m like, “Oh yeah, I’ve got to do it for the domain and then for the server without the domain,” and just the whole thing was…[sighs].

Tara Kizer: Then depending on the version sometimes it was the port, sometimes it was the instance name.

Erik Darling: Yeah.

Brent Ozar: Yeah. Don’t feel bad whoever is asking, don’t feel bad if you don’t remember because no one ever remembers this. It’s a hard pain in the rear.

Erik Darling: Yeah.

 

How should we configure our reporting and data warehouse servers?

Erik Darling: We have one from a fella named Jason who said, “When you worked with our organization you suggested our production transaction db start up with the settings of MAXDOP 8 and cost threshold 50 to use 8 tempdb files and then modify accordingly. Do you recommend that same setup for servers dedicated to only reporting services and what about data warehouse databases? We are on SQL Server 2014.”

Brent Ozar: I’ll take the data warehouse one just because I’m like, “If any query is going to be a data warehouse-y kind of query, it’s going to be large anyway. It’s going to blow past the cost threshold of 50. You may still have some tiny queries on there that are like inserting, updating, and deleting a small number of rows but they’re not likely going to crossover that 50 threshold. So I still like the 50 threshold. Same thing with the number of tempdb data files. Servers dedicated SSRS, that’s kind of tricky. Do you mean they’re just running the engine? Or are they running reporting queries for SSRS? Or if they’re just like servicing as the SSRS database where they’re holding like the report server db and report server tempdb? In which case, those queries are going to be kind of small and you wouldn’t want them to go parallel either. I still would stick with the same numbers. How about you guys?

Tara Kizer: Just because how the question was posed, it makes me think that they do have the reporting services service and the databases, the report server and report server tempdb databases on the same server. If that’s the case, the best practice is to separate them. Our recommendation is for the SQL Server instance, it could be that report server and report server tempdb are on that instance. That’s where our recommendations are [inaudible 00:06:39] for the SQL Server instance. Once you start getting into all these other services, those should be on another box.

Brent Ozar: Like it.

Erik Darling: Groovy.

 

Why do I only see 4,000 characters of my dynamic SQL?

Erik Darling: We have a question from a fellow human about Dynamic SQL. It says, “I set my string to nvarchar max but I am only seeing 4,000 characters. Is there a way around this?”

Brent Ozar: I should make you answer this because you’re like Mr. Dynamic SQL.

Erik Darling: Fine. Yes, there are ways around it. The trouble is that Management Studio can only print so many characters. So your string is probably longer than that. What you’re going to need to do is do some substrings of your string. You need to do from 0 to 4,000 and 4,000 to 8,000 and from blah, blah, blah because you are using nvarchar max you can only print the 4,000 because it’s Unicode. If you were using varchar max, you could print 8,000 but that might screw you up when you try to do sp_executesql. Because you’re doing Dynamic SQL, I expect you’re using sp_executesql and not just exec because that would be naughty. That’s how I usually get around it. You’re going to have to print out substrings. What I usually do is I take the length of the string, like I get the actually length of it. Then I just divide that into chunks of 4,000 and I get the substring of each 4,000 chunk and print that out. That usually works.

Brent Ozar: I like it.

Erik Darling: Another trick that sometimes works is if you select the thing for XML path. SQL can sometimes print out more XML than…

Brent Ozar: Oh.

Erik Darling: Yeah.
Brent Ozar: Wow. That’s kind of cool.

Erik Darling: Yeah. It doesn’t always work because sometimes you have some weird characters in Dynamic SQL.
Brent Ozar: Yeah, sure.

Erik Darling: Yeah, they don’t really fancy printing in the XML. But if you have like a pretty generic string, you can do the for XML path trick and select your thing as that and it will print out magically a whole mess of characters.

Brent Ozar: I love it.

Erik Darling: I don’t, but it sometimes works. One thing that I found out, this is sort of on topic for this so I’m going to say it here. If you’re using Dynamic SQL and you are using nvarchar max and you are concatenating chunks together. So it’s like you have a dynamic block and then like something else and then a dynamic block, it’s really important that you preface each of those blocks with a capital N or else they get converted down to nvarchar 4,000 from nvarchar max. So it will actually sort of implicitly truncate your code. I found that out the hard way. I was working on some stuff in BlitzFirst. So be very careful with that as well.

Brent Ozar: It’s also not really good in terms of performance, like if you’re continuously adding on small pieces of the string together in SQL Server, SQL Server is not particularly quick at that. The longer the string gets, the worse it gets.

Erik Darling: We’re living proof.

Brent Ozar: Yeah. We build sp_BlitzCache as a huge amount of Dynamic SQL, then search and replace in the Dynamic SQL, and it’s genuinely hard to work on. So we feel your pain.

 

Why is our replication distribution job frozen?

Erik Darling: We have a question from Paul—not a fellow human—which planet are you from, Paul? “My team just moved our production SQL Server VM from one machine to another. During the move we also applied server updates. Everything tested as successful. Today, I found the job replication monitoring refresher for distribution running for over 24 hours. It is hanging. It is on the sp_repl.” Tara, take it away.

Tara Kizer: I’ve actually never seen that occur. I assume that you tried to stop it and maybe you couldn’t get it to stop. Did you try killing the SPID? I’ve never encountered that issue and I’ve used replication for a few years.

Erik Darling: All right. So we are undecided on that one, Paul. Sorry about that.

Brent Ozar: We would probably give the crappy answer, like restart the SQL Server instance. See what happens again. If it happens twice in a row, then open a support ticket with Microsoft.

Tara Kizer: You could also try restarting just the agent since that’s not going to affect the databases at least. Just make sure that if you do that you check what’s running. If you have like a rebuild index job, you don’t want that to stop because that will cause a large transaction to roll back possibly.

 

New PasteThePlan Laptop Skins

Erik Darling: That’s so cool. What you got there?

Brent Ozar: Those of you who are only on the audio will not be able to see this, those of you on YouTube will be able to see. I have laptop skins and iPad skins for the icons that we have from Paste the Plan. I was wondering how they were going to turn out. They are just completely and utterly gorgeous. I will send you guys links on how to order these. You “guys” meaning the team. I guess we could send them to the public too.

Erik Darling: We could make magnets out of them.

Brent Ozar: We could. Oh my god. Dude, that’s genius. Then you could like put them on your fridge and build your execution plan. Erik, that is utterly genius.

Richie Rump: We should put our team member faces throughout those as well.

Brent Ozar: I love it. We’re totally doing that.

Erik Darling: I got hired because I’m an idea man.

 

When will the training classes go on sale?

Erik Darling: Let’s see. We have another fellow human. “I thought I saw a sale on the SQL Perf Tuning four-day class last week but it’s gone this week. It’s probably a secret but when is the next big sale?”

Brent Ozar: It’s really neat how sales work. They expire. Then if they don’t buy in time and they go, “Boo, they’re gone.” Our next big sale, and really the only big sale through the rest of the year, will be the Black Friday sale. On Black Friday—and I always forget what the day is—it’s in November. It’s the first Friday after Thanksgiving. We will be running huge 50 percent off sales on the training classes. In the past, we’ve run one dollar sales, like you’ve been able to get one seat in a class for one dollar. We may still do those, it will be like a midnight doorbuster type thing. But through the rest of the day, we’ll have a limited number of seats in the classes for 50 percent off. So instead of being $3,995, they’ll be like $2,000.

Erik Darling: Word.

 

Will there be replication problems when we migrate clusters?

Erik Darling: We have a fellow human who asks if we have any experience with cross cluster migrations. “Is it is okay to go from SQL 2012 to 2014 or 2016 and would there be any problems with replication?”

Tara Kizer: That’s a very complicated question there. Is the person asking that they have two clusters and they’re going to be migrating the databases between the two clusters? Is that what that is asking?

Brent Ozar: I think that’s what they’re doing.

Tara Kizer: It doesn’t really matter that there is clustering involved. The migration is really still the same as far as SQL Server goes. Would there be any problems with replication? Did you test it? I don’t have any issues with replication when I’ve moved from one version to the next. You just have to reset it up and get everything in place.

Brent Ozar: That’s a great point. Say that you’re moving from 2012 to 2016 or 2014 to 2016, when you’re thinking through that, because Tara you’ve done this a bunch of times, when you’re thinking through a move from one server to another, one cluster to another, whether there’s replication involved or not, how do you go about building another environment and testing out your plan?

Erik Darling: Question mark.

Brent Ozar: That was you, Tara.

Tara Kizer: Oh, I thought it was a rhetorical question.

[Laughter]

Tara Kizer: It’s the same process for any project, application release. I don’t know necessarily build out a plan. I just go through the same setup that I do with any new instance. If you’re setting up a new cluster, a new SQL instance, I have the same set of steps that I go through really. And maybe on the cluster I have a couple extra things that I need to check, but as far as an upgrade, I do the same process every single time. It doesn’t matter if there’s clustering. It doesn’t matter if there’s replication involved. And replication, I might need to research to see if the distributor database is supported on whatever version that I’m on and is it cluster aware? Can it go into a failover cluster instance? Can it be part of an availability group, that type of a thing. As far as my steps, it’s the same steps as always really. I do backup and restore and I prep the database ahead of time using a recovery. Full backup maybe a day before the migration. Differential, no recovery, maybe a few hours beforehand. Then I start applying logs, that way when I’m ready for the production, the actual maintenance window, I just have to apply maybe one or two transaction logs and I’m basically done at that point so downtime can be just a couple minutes even.

Erik Darling: One thing that I noticed with—I don’t know if this helps you, I don’t know if this is applicable for you but one thing that I saw that’s kind of cool with Windows Server 2016 is that they have the concept of a rolling upgrade for the whole cluster. The cluster will exist on the older versions until you have all of the cluster nodes on the same Windows version. Then you can use a PowerShell do-dad to upgrade them all at once. I don’t know if that makes things easier or if it doesn’t apply to you, but I did see it out there and it is something to think about if you’re out there in the world and you need to upgrade a cluster.

Brent Ozar: Yeah, you should think about building out the new cluster on whatever new version of SQL Server it is. Fail it over back and forth, test your maintenance jobs, test how long they take. Do load testing against your storage. Really kick the tires on it really hard. Go through our setup guide just to make sure that you’ve gone through all the steps to set up a SQL Server correctly. If you go to the homepage of brentozar.com, scroll all the way to the bottom. There’s an email box. You can just put in your email and get our First Responder Kit. It has the exact same setup checklist that we use when we’re building new SQL Server instances, which I have to go update because it has a couple things in there that I’m like, “I’m not especially proud of that.” It has like partition alignment. Partition alignment is now [inaudible 00:15:38]. Yeah, I can take that stuff out.

Erik Darling: Yeah. Cool, all right.

 

When I add a new index, the query goes faster even though it doesn’t use the index?

Erik Darling: We have a question from another fellow human. “A new index I created helps a specific query drop from 15 minutes down to less than one second.” Yay. “However, the new index does not appear in the execution plan. I even tried dropping and recreating the index and the performance differences are still there. Why is this?”

Brent Ozar: I bet you had a bad execution plan at the time, at the time that you were getting a really slow query, it was taking 15 minutes. When you added a new index it just caused SQL Server to go build a new execution plan with whatever parameters you happened to pass in. Once it has that execution plan, great, life goes right on, but you may be a victim of something called parameter sniffing. In a perfect world, I wish you would have caught the bad plan initially and saved it. If you catch the bad plan initially and save it, if you right click on the select statement or insert, update, delete, whatever it is, in the plan right click on it and click “properties.” Over in the properties window you can see what parameters it was built for. That was probably what caused the slow execution plan. But if you don’t have the plan anymore, it’s kind of tough to figure this out but that also means that it may strike again at some point. Whenever the query is slow again, make sure to save that execution plan. Then you’ll be able to compare the two and see if it’s for example an issue of parameter sniffing.

Erik Darling: One other possibility, and something that I’ve seen, is that if the index you created was unique, SQL can sometimes use that additional information about a column to come up with a better plan for something. It can say, “I’m not going to use this index but there is a unique index so I know that this column is unique. So I do things far differently than I would if I have a column that I’m not sure is unique or not.”

Brent Ozar: A fellow human follows up with, “There are no parameters. It’s just an ordinary query.” SQL Server may be parameterizing the query for you. SQL Server will auto parameterize a query and turn your literals out into variables too.

 

How is the SQL Server employment market?

Brent Ozar: There’s a question in there about how do you guys feel about employment opportunities in the current market. Did you guys tackle that before I came in?

Erik Darling: No, that came up when we were asking other stuff.

Brent Ozar: So, “How do you guys feel about employment opportunities in the current market? SQL Server seems less in demand than AWS.” It’s kind of like anything else with supply and demand, right? If there’s no supply for it, it seems like, “Oh my gosh, this job is amazing,” just like with analytics right now. “Oh my god, we really need someone with ten years of experience in analytics.” That’s because there’s only four people in the world with that level of experience. AWS, it’s the same thing. Everybody who is going in AWS wants to find the five people in the world with ten years of AWS experience. There just aren’t that many so there’s a temporary gold rush. It’s just like when we went to go start to build applications. We wanted someone with ten years of good experience building Enterprise apps. We couldn’t find them so we had to settle for Richie instead.

[Laughter]

Sometimes you’re just kind of stuck. But the SQL Server jobs are still phenomenal. It’s still a really good market. I was just at this other client and they’ve been hiring DBAs for a while, can’t get good, experienced people where they’re at.

Tara Kizer: I think it depends on where you’re at. I know in San Diego SQL Server jobs are booming right now. The number of recruiters contacting me, it’s getting annoying right now. There are just a lot of SQL Server DBA jobs in San Diego right now. Unfortunately, the amount of DBAs in San Diego is not very many as compared to the number of jobs. Not just DBAs, SQL Server developer jobs, there’s a lot of BI. There’s just a ton of SQL Server jobs right now in San Diego, just not enough people to fill those positions.

Erik Darling: Today was born the Tara Kizer Employment Agency.

Brent Ozar: All the recruiters listening to this podcast are like, “Quick. Get her!”

Erik Darling: My two cents on that is I hope I don’t have to find out anytime soon. That’s all I got to say about that.

Richie Rump: Yep. I’m in that boat.

Tara Kizer: It’s not like I’m telling recruiters to contact me, just to make it clear. I am happy where I’m at.

Brent Ozar: Sure, Tara. Tara is out there on Monster, Dice, LinkedIn. “Please rescue me. I’m tired of being in this house.”

 

Should I back up multiple SQL Servers to one network share?

Erik Darling: We have a question from Alexandra—last name I’m not going to try to pronounce because I don’t want to appear insensitive. “Sorry, not a DBA here. Is it good practice to do backups from many different production servers on one network share? If so, should we use network service for service accounts on production servers and add permissions for network service on the share?”

Brent Ozar: That’s such a good question.

Tara Kizer: I don’t like using the network service for the services in the first place. I like a domain account that does not have local admin on the box. Use a domain account and then grant that account the access to the network share. You could do the network service and I think that when you add the permissions to the network share you just have to add the computer account which is the server name $ I believe. So you can do it that way. I prefer a domain account.

Brent Ozar: Agreed. Is there performance bottleneck with doing backups from multiple SQL Servers to a single network share at the same time? Sure it can be whatever the throughput is on that network share on the other end. Sometimes I see people backup to one net app. Like they’ll have one net app file share that has all kinds of capacity and performance and they’re able to do a whole lot of backups without really getting overwhelmed. Then also I’ll see people backing up to like a toaster NAS that has two hard drives in it and it simply goes to USB thumb drive speeds when you try to do multiple writes simultaneously. What you can do is you can map a drive to whatever that network share is and use the tool CrystalDiskMark. If you search for CrystalDiskMark on our blog, we’ve got several articles on how to use it. You can run one test from one server then run several tests at the same time from several different servers. You’ll see what the throughput looks like. Maybe it’s a network bottleneck. Maybe it’s a disk bottleneck underneath. Then your performance tuning adventure starts over on whatever is managing the backup share. But is it something that we generally recommend? Totally. I always want to have people backup off the box. If your backup requires the SQL Server’s Windows operating system to be up, it’s not really a backup. Sometimes I’ll see people who will backup locally and then copy the files off elsewhere. I often see those same people get kind of incompetent with their shell scripts and they don’t actually copy the files off that often. So just backup straight up to a UNC path and call it a day.

Erik Darling: That’s a wrap.

 

Should I run SQL Server as local service or a domain account?

Erik Darling: A fellow human says, “Speaking of domain accounts, thoughts on running SQL as the built in service account versus a domain account and why?”

Brent Ozar: Why? I’m with Tara, I’m a huge fan of using domain accounts because often I want to grant permissions to the service account to go do something, like go access this file share. Or—it’s always go access this file share.

Erik Darling: xp_cmdshell.

Brent Ozar: xp_cmdshell, yes. So it’s just so much easier with a domain account as opposed to using a local system.

Erik Darling: Some stuff still works. You can grant the built-in accounts instant file initialization the same way you grant a domain account that but the question for me is always like where else does this server have to get to? What else does it have to do? I’d rather have a domain account that I can be in control of and all the other stuff. Not a domain admin, not a local admin, doesn’t have too many permissions where it shouldn’t. I want to be able to control that sort of stuff and the domain accounts make that a bit easier.

Brent Ozar: There’s some kind of deal too with Kerberos authentication and an Always On Availability Group, all the SQL Servers in the same availability group have to use the same domain service account if you want Kerberos authentication to work. At this point, I might as well be a meat puppet that’s being voiced by someone else because that’s literally—I know to parrot that sentence and that’s all I know. I’d go hit Books Online if I want to know more.

Erik Darling: Part of the domain account thing is that they have to have special delegation privileges and they have to be able to set and reset SPNs and stuff like that to make those switch overs easy. Make sure you don’t run into the SPN SSPI stuff that sometimes people run into. I like that.

Brent Ozar: Yeah.

Erik Darling: Anyone else like that?

Brent Ozar: I like that a lot. You’re not alone. We love you. I’m just over here playing with my Paste the Plan icon sheets. That’s all. Beautiful icon sheets.

Erik Darling: I’m totally listening to you. I’m not distracted at all.

 

Should we split TempDB data files across multiple volumes?

Erik Darling: Real question from Angie, whoever that is.

Tara Kizer: Who is she?

Erik Darling: I don’t know. Some chick who just keeps showing up. I don’t know. All right. “Any reason you should split tempdb data files? We’ve got eight but split across two drives on a hosted VM so I don’t know the actual drive layout.” What y’all think?

Brent Ozar: In edge cases for certain storage controllers, certain storage vendors will actually tell you to do this. Dell Compellent, when we were back at Dell DBA Days, Compellent told us something interesting. That you want to create one volume for every controller that the Compellent had. So if the Compellent had two controllers, you would create two volumes and put half your files on one and half on the other in order to maximize your throughput. Generally, on a VM you don’t usually hit bottlenecks that hard but I wouldn’t have any problem whatsoever if someone chose to do that. That would be completely fine.

Erik Darling: She says, “The writes are horrible,” so I’m thinking that’s not the case.

Brent Ozar: Yeah, and you can test it usually with like CrystalDiskMark. Just test it with plain old CrystalDiskMark and usually it’s that the underlying storage is crappy so it’s not really helping you anything to stripe it across two drives.

Erik Darling: Are the reads also horrible or is it just the writes, that’d be my next question. Because I did write, did horribly write also a blogpost about caching. So I would check also the kind of caching that you have for the SAN that those drives are sitting on because certain kinds of caching can have write impact and especially for things like tempdb where you’re writing away. The cache, if it’s like write through I think, I forget. That’s why I wrote it down so I wouldn’t have to remember it. So certain kinds of SAN caching can bunk things.

Brent Ozar: The other thing I would throw out there too is there’s a service pack—if you search for tempdb less eager writes on our site, there’s a service pack and cumulative update for 2012 and I think it’s just built in the box in 2014 where it writes less frequently to disk. You’ll get a bonus. Holy cow. She says it’s 5,000 milliseconds for writes. Whoa. There’s a little guy with a stone tablet on the other end of that network wire scribbling things done. Five seconds. That’s pretty bad.

Erik Darling: Like one of those court transcriptionists in there.

Brent Ozar: That’s pretty heinous.

Erik Darling: I would also look at what I’m doing that’s hitting tempdb to do that.

Brent Ozar: Yeah.

Erik Darling: What’s going in there and doing that write. It’s weird.

Brent Ozar: Well thanks everybody for hanging out with us this week. It was an enlightening half hour as always. We will see you guys next week on Office Hours. Adios.

Erik Darling: Adios.

Tara Kizer: Bye.

Previous Post
Divide By Zero Hero
Next Post
Applications are Open For Our 2017 Scholarship Program

6 Comments. Leave new

  • It’s absolutely valid to have non-aligned nonclustered Indexes. One of the typical cases is supporting of unique key constraints.
    http://dba.stackexchange.com/questions/32996/unique-non-clustered-column-in-partitioned-table

    • Andrej – valid, yes, it’s supported. However, read that answer carefully: Remus notes that you’ll have performance problems and partition switching won’t work.

      • I’m aware of limitations of non-aligned Indexes but you don’t have much choice if you want engine supported data integrity in form of unique constraints
        …………………
        Who said trigger!? Go and wash your mouth! 😉

  • Another enjoyable Office Hours … I preferred it without the slides 🙂

  • First time I’ve watched an Office Hours. I really liked it. 🙂 In particular the question about Max DOP, Data Warehouse, TempDb, SSRS etc all in one question. I don’t understand the finer points of cost thresholds, so that’s given me something to look up and learn about. 🙂 PS: I love your work, and am extremely grateful for the help you provide for “the greater good”. Very cool. 🙂

Menu
{"cart_token":"","hash":"","cart_data":""}