Episode 50: SQL Server Settings

As database administrators, we want to get as much performance as we can out of our SQL Servers.  Microsoft has provided a few knobs for us to tweak in the Operating System, the local security policy, and in the engine itself and in episode 50 of the SQL Data Partners Podcast, we review some of these SQL Server settings and how you might take advantage of some and avoid others.

My guest today is Justin Randall, the professional services manager at SQL Sentry.  As someone who is going into many environments he has seen some of these knobs be used wisely and some not so wisely.  Justin lives in Charlotte, North Carolina and is a frequent speaker in the SQL Server community.

This episode touches base on the power settings of the server, instant file initialization, max degree of memory, boost SQL Server priority, shrink database and a few others.

I am also happy to announce Steve Stedman is joining me as co-host of the podcast.  Steve was a guest in episode 13 and since that time we have kept up with each other and as I thought about where I wanted to go with the next 50 episodes, I think Steve can help me get there.  We are introducing a new segment on the show called the Tuning Review which is a short conversation between Steve and I centered around performance, disaster recovery, or questions/interactions we have with listeners of the show.

Show Notes

Justin on Twitter
Trace Flag 1118
Trace Flag 3226
SQL Sentry Performance Advisor
Glenn Berry Diagnostic Queries

About Justin Randall

SQL Server SettingsJustin Randall is the Professional Services lead with SQL Sentry.

Transcription: SQL Server Settings

Coming soon

Episode 49: Table Variables vs Temp Tables in SQL Server

When you code and you need to use a temporary object what do you use in SQL Server–temp tables or a table variable?  There is plenty of conventional wisdom out there and my guest Wayne Sheffield and I talk about the differences between these two objects in episode 49 of the SQL Data Partners Podcast. In this episode we cover not only these objects, but also global temp tables and the changes these objects have in the newer versions of SQL Server.  This episode includes a discussion on

  • Are table variables stored in memory?
  • Do table variables write to disk?
  • What can temp tables have that table variables can’t?

What has your experience with temp tables and table variables been?  Have you seen something different than what Wayne and I discussed?  I am interested to know if you have other examples of using global temp tables.  Leave us a comment below and join the conversation!


Show Notes

Wayne on Twitter
Wayne’s Website
Wayne’s Interview Questions

About Wayne Sheffield

Temp TablesWayne is a consultant with the SQL Solutions Group and is a certified master of SQL Server.  He is an Eagle scout and a leader of the Richmond SQL Server Users Group.  Wayne is a former SQL Cruiser and is the main reason I decided to go.  He and his wife built their dream home in Cumberland, Virginia.

Transcription: Temp Tables vs Table Variables

Coming soon!

Episode 48: Is the on-premises Data Warehouse Dead?

My guest today is Tim Mitchell, the owner or Tyleris Data Solutions and a Microsoft MVP.  I pose Tim a very simple question–Is the on-premises data warehouse dead?  The last several years have brought a myriad of new technologies for how to store and report on your data.  Microsoft’s first cloud offering was all about services in the cloud and they were pitching a ‘everything in the cloud’ approach.  When these technologies were marketed, some data professionals thought they would not longer have their familiar technologies.  Time has proven that this hasn’t quite worked out the way some thought.

Tim and I discuss how organization are starting the juggle the various technologies and how traditional data warehouse folks will continue to provide value to organizations.  We also discuss why organizations consider cloud solutions and scenarios where going to the cloud makes sense.

Episode Quote

“The traditional model is good when you know the questions you want to ask.”

I would love to hear about your experiences with the data warehouse.  Are you getting the training you need?  Have you tried any cloud services?  What is still the big challenges for data warehousing?  Let us know if the comments below!

This episode is sponsored by CozyRoc


Show Notes

Tyleris Data Solutions
Tim on Twitter
SQL Prompt
Bids Helper

About Tim Mitchell

Tim Mitchell is the principal and lead architect for Tyleris Data Solutions.  A veteran of more than 12 years in the data management space, Tim brings to the table his experience in consulting with clients in healthcare, finance, retail, manufacturing, and advertising.  Tim has been a Microsoft Data Platform MVP since 2010, is a prolific international speaker, an active technical blogger, and coauthor of SQL Server Integration Services 2012 Design Patterns.

Transcription: On Premise Data Warehouse

Carlos L. Chacon: Compañeros, welcome to the “SQL Data Partners podcast,” the podcast dedicated to SQL Server-related topics, which is designed to help you become more familiar with what’s out there, how you might use those features or ideas, and how you might apply them in your environments.

This is episode 48. Episode 48, can you believe it? I know it might not be a big thing for you, but episode 50 is right around the corner. I’m pretty excited about it.

I feel like that’s a milestone just for myself. It’s been fun putting together these podcasts, and I hope that you have enjoyed listening to them.

My guest today is Tim Mitchell, a data warehouse consultant with Tyleris Data Solutions, from the Dallas area. He’s a Microsoft MVP, a SQLSaturday speaker, and a Texas Ranger fan, which I hope you don’t hold against him. At the time of this recording, the Texas Rangers are on top of the AL West, so there is that.

We’re going to mix it up a bit, today. We’re going to start with a question. Is the on-premise data warehouse dead? I think we get to that answer a little too quickly. However, Tim does have some interesting thoughts on the subject and the different roles and responsibilities if you will between the cloud and on-premise data warehouse.

We also get into what’s new in the landscape of tools and technology, and what this means for IT veterans, as well as those looking to move over to the analytics side of the house. This episode of the SQL Data Partners podcast is brought to you by COZYROC, the leading provider of SSIS library components for custom tasks and scripts. COZYROC, go to the next level.

Compañeros, thanks again for joining us in the SQL trail of this episode. You can reach out to me with questions or comments. Did I ask Tim good questions? Is there something maybe I missed? Let me know on Twitter. I’m @CarlosLChacon, or you can shoot me an email at [email protected]. Let’s get to it. Welcome to the show.

Children: SQL Data Partners.[music]

Carlos: Tim, welcome to the program.

Tim Mitchell: Thank you, Carlos. Glad to be here.

Carlos: Yes, always nice to have a Texan.

Tim: That’s right.

Carlos: Are you originally from Texas?

Tim: I am originally from Texas, yes. I’ve fought to get rid of my accent, so to a lot of folks, I don’t sound like a Texan. But yes, I’ve been here for more years than I want to admit to.

Carlos: You know everything is bigger in Texas. Of course, that’s why we wanted to have you on the show today is because we’re talking a little bit about data warehousing, which I think you know a bit about.

Tim: I’ve dabbled, yes.

Carlos: As a consultant, as a speaker, as someone who’s traveled that block. The premise of our conversation today is the on-prem data warehouse dead?

Tim: The too long didn’t read is no. It’s a much longer explanation. The short answer is no. There’ve been a lot of things that have come up in the last several years about new technologies whether it’s big data, whether it’s doing things in cloud, big data in-the-cloud or mixture of those.Initially when those new things started spinning up, I think DBAs and especially BI practitioners were a little worried that it would take away our meal ticket because we’re used to the dimensional model. We’re used to MDX. We’re used to the traditional on-prem data warehouse.

I think the majority of our users are used to that as well. The executives want predictable answers. They want the same answer over and over again. They want to know that they can always get back to the same place without having to go through machine learning or whatever else you’re going through to get to the answers.

A lot of the concern that I saw, I think part of it was merited. But I think it was overblown in a lot of ways as well because the short answer is the data warehouse, just like every other technology, is a part of the tool set that we use.

I’m biased because I do, the vast majority of what I do is at least touching data warehousing if not pure data warehousing. But I’m seeing a lot more of these technologies not replacing data warehousing necessarily but supplementing what you’re already getting as part of the on-prem data warehouse.

Calros: Potentially solving other needs there. Are there questions that you might have?

Tim: You think about the small business. If I’m Mom and Pop, I have 5, 10, 15 employees, and I need some data analytic solution. I need to look at my sales. I need to look at my inventory. I need to look at my trends over time.Spending $25,000, $30,000, $50,000 on hardware and licensing simply isn’t an option to get up and running. Even after you spend that, you have to hire someone like me to come in unless you have an in-house expertise.

That’s a hard thing to do for a small business, or even a medium-sized business to come up to speed quickly. If you think about looking at the on-prem data warehouse versus the in-the-cloud data warehouse, if you can spin up a machine, if you can pay not per piece of hardware, not per core that you’re using, not per license.

But if you can pay per a logical unit of processing that you’re doing, if you can come up to speed in an afternoon on a dimensional model as opposed to taking weeks at best or months more likely and bringing up your hardware, getting everything installed, hiring someone to do the whole thing for you, you’re going to save a ton of time.

There are different needs for different organizations depending on the size, depending on how sensitive their data is. I want to talk about the sensitivity of data as well. But for a lot of organizations, doing the traditional on-prem data warehouse simply doesn’t meet their short-term needs. It’s killing a fly with a sledgehammer. Yes, it addresses the problem, but it’s really overkill for what they need to do.

Carlos: If you don’t have that regular reporting and you’re a small organization, then you’re maybe nimble, agile. That word gets thrown around a lot. The cloud provides that ability where you don’t have to make all that, I would say, other investment in process because your questions are changing. Your target continues to move.Again, it’s another niche or area that some organizations have. But if you want something like year over year reporting, then I think, yeah, your data warehouse is still…or a lot of people are going to go for that type of information.

Tim: Right. It’s not necessarily different audiences, but different answers that they’re looking for. If you’re standing at the mahogany desk delivering answers about what we did last year, and what our profit margins were, and things like that, that’s a pretty simple answer of this needs to be structured data where every time I ask the question, I’m going to get the same answer. It’s a repeatable process.There’s not any flux about that. I’ll just throw at it. If I’m using machine learning, for example, to deliver a shareholder reports and show profitability, at some point, some variable might come in that would throw off those numbers. That’s probably a bad thing for a public company.

But if you’re using a more traditional structured reporting for those types of deliveries, that’s where the data warehouse, whether on-prem or in-the-cloud, fits in well. If you’re delivering more answers that are they’re more influx, which of my customers are more likely to churn in the next three months? Where should I spend my advertising dollars based on prior performance?

Those are the kinds of things where those up and coming technologies that haven’t traditionally been part of the data warehouse really fit in well. Machine learning if you’re using big data analytics and things like that, those really fit in well. Again, not replacing what you’re doing with the data warehouse, but adding in functionality that previously was hard to get in the traditional brick and mortar on-prem data warehouse.

Carlos: This is something that we’ve addressed on this show is that business is looking more for IT to provide some of that value, to like, “Hey, you’re looking at all these data. You have it stored. Can you continue to provide answer to questions or even help us identify questions that we may not be currently asking?”

Tim: I think the traditional model helps you get to, it helps you answer the questions that you already know you want to ask. Some of those other things that are not really in the realm of data warehousing help you figure out what questions you need to ask in the first place.

Carlos: Right, to look at those trends. Another big consideration for the cloud versus the on-premises, environments is cost. Again, can things continue to change? You referenced the…at least from on-premises environment, I have, I want to say some cost, but I know I’m going to have a server. I have to pay for that and I have to have staff. How do people manage those costs?

Tim: I think it’s a harder thing to predict in-the-cloud because if I’m planning for on-prem, I know I’m going to need a server. I can speck that out. I know I’m going to need a number of licenses, and I can get that quote directly from Microsoft, whatever other software I need. There are some other cost — cooling, electricity and whatnot. But those are within some range of predictability. I can predict this from month to month.If someone, especially if a company is first coming into the cloud, it’s a harder thing to predict as far as what your costs are going to be versus what you need. Typically, I can only really speak from the Microsoft side. But Microsoft measures out their units and measure, and not in the number of machines you have, but how much horsepower you want.

That doesn’t necessarily translate to, “I can get this many IOPS, or this many network operations, or something.” It’s going to be a little bit of trial and error figuring out how much horsepower I need related to the load that I’m throwing through that.

The flipside of that is that specifically with respect to cost. I worked before I got into consulting. I worked for a hospital for about four years. We did nightly processing, but once a month, we put an enormous load on our data warehouse and reporting infrastructure.

Had we not had that monthly load, that every end of the month, if we hadn’t had that, I literally could’ve cut my hardware architecture in half, because I was really planning for the end of the month, because everything hit on the end of the month. All my reconciliations, all my checks and balances and things like that.

If you’re working in-the-cloud, even though it takes a little bit of trial and error to figure out how much throughput you need in-the-cloud, you can scale up at certain points and say before the end of the month, I know I’m going to need double what I normally have from a nightly processing. You can help control your costs and not buy for what’s the most I’m going to ever have to run through here without having to buy any new hardware.

If you’re doing that on-prem versus for the one-time-a-month or one-time-a-year that I really need a ton more horsepower than what I ordinarily get, I can spin it up, pay for the amount of time that I have spun up, and then spin it back down.

Carlos: From a cost perspective, the cloud providers are counting that, as the big carrot to come and entice people over into their environments. Did you think eventually those costs will justify maybe some of the unknowns of saying, “You know what, I am going to go ahead and just move all these into the cloud?”

Tim: I think we’re probably already there in some organizations. If you think about the situations that I was talking about, where once a month we’d regularly have to spin it up, there are a lot of organizations, that have those spikes like that, where, yes, they’re going to play around with and then figure out what their load needs to be for those times of the month.If you look at what you paying on-prem, hardware cost will continue to go up. Licensing cost will continue to go up, but just the economy of scale and putting that into the cloud to a certain point, costs are going to continue go down.

If you look at different vendors — Microsoft has been specifically on the data warehouse side. They’ve been a little bit in catch-up mode because I think Amazon had a bit of head-start on them. Microsoft with SQL Azure Data warehouse is doing some catch-up in terms of not just the feature set but pricing as well. I think they’re actually catching up to the point where maybe not every workload makes sense to put that into a data warehouse in-the-cloud.

I think there was a strong argument for a lot of those continuing to be on-prem like I mentioned earlier. But in terms of the cost benefits that will continue to be more enticing and I really think we’ve already hit the tipping point, where if am a small organization or I have a fairly smallest needs, it doesn’t make sense for me to go out and spend licensing hardware and everything, a 100 grand and then have to air-condition it and provide electricity and everything else.

I think we are at that point where for a lot of organizations, especially startups and small organizations it already makes sense for them to go to the cloud Even though there are a few more unknowns, it makes a lot of sense.

Carlos: You mentioned also Amazon product Redshift as your SQL Data Warehouse. In fact Matt Usher, the program manager for Azure SQL Data Warehouse was on the show a couple of weeks ago and we were chatting with him. We mentioned at the top of the program, this influx of new technology, big data and things like that, not only have thrown organizations into a little bit of tailspin but also the IT force.Some of the things that they’re going to have to learn, for example even for me, which I consider myself an engine guy, when I was talking with Matt I was expecting to be talking about cubes and whatnot in-the-cloud. What we were really talking about as you mentioned is really compute, which is a slightly different set up or mix of skill sets and we talked about some of these different questions that you can be answering.

My question is, as we see even PASS start to put in additional conference, we have the Analytics Conference now. I’m seeing more SQLSaturday sessions about moving from the ETL or the data side into the analytic side. How are people supposed to keep up with what’s out there? Where they should be going? Maybe what questions or what scenarios they think could be a good fit for them?

Tim: That’s a great question. It’s very easy to get lost in that forest. When I first started SQL Server, I started SQL Server on version 2000. Back then you could the SQL Server guy or girl. You could pretty much know the product and in the end know all the different high availability options and BI options and things like that.You could talk about the data platform in general but SQL Server specifically has become such a big platform all on its own, in additional to all of the other pieces that are hanging of the side of it. That I don’t think a person can reasonably be expected to make themselves an expert on all those pieces. Really, I think you have to familiarize yourself certainly at a high level.

People like us we talk to clients a lot, so we don’t necessarily have to have all the answers for these 18 things they might consider but we at least have to have a general understanding of — these are the types of problems that this tool solve, these are the types of situations that maybe don’t fit so well for that particular technology.

There are lots of learning resources out there. You mentioned things like SQLSaturday. If you look to SQLSaturday even perhaps a couple of years ago, you would see the non-SQL Server or cloud-based offerings. You wouldn’t see a whole lot of talk at SQLSaturdays in there, simply because it wasn’t something that folks were really going to en masse.

I think there was still a hard-line with Microsoft where they were all-in on the cloud and everything they were implying that everything should move over to the cloud. From what I’ve seen they’ve really backed off on that and they’ve pushed more of a hybrid solution, where we realized that not every workload is appropriate for the cloud.

We realized that some of your data is too sensitive for you to feel comfortable taking it from your own servers and putting it up in-the-cloud. I think the educational realm has changed to match that as well. So, rather than where you have to be all-in the cloud or you’re just old school.

Now it’s more of, everything is a tool and everything has its place and there is that on-prem data warehouse, there is the in-the-cloud data warehouse. There’s things like Hadoop that are coming up, there’s SQL Server Linux for crying out aloud, if you want to really get into some of the fringe things.

Carlos: [laughs] Sure.

Tim: There is lot of different pieces that are coming up to solidify Microsoft’s position that it’s no longer cloud only. It’s cloud plus on-prem. It will always be a hybrid solution. In response to that, it seems there have been a lot more initiatives on education SQLSaturday, the PASS summit, the BI summit starting to look a lot like that and — I forget what they’re calling it, but the…

Carlos: The Analytics Suite Conference.

Tim: Thank you. I had a brain far there. The Analytics Conference, it really matches where Microsoft is going, where everything is going to be connected — you’re going to have on-prem pieces, you’re going to in-the-cloud pieces. You can see a lot more push on the education side for that as well.

Carlos: Yes, interesting. I wonder when the job postings are going to catch up with that because it seems like if you are in the data space — at least the ones that are coming my way they still want you to be like, “Hey, we want you to know everything about performance, and all these things, high availability, you name it on the engine side, but oh yeah, by the way you have to support the data warehouse and do all this ETL type components.”I feel like, “Well, yes, I can we maybe talk to some of those.” It feels like still two different worlds to me.

Tim: Yeah, that’s a good point. One of the things when we first started talking, one of the things that data professionals were a little frightened about from what I could see was that the cloud and these other pieces would take away their jobs. If anything, I think it’s helped them. For those who are going to keep up their skills sets it’s really helped them to solidify the fact that they’re continue to make a living in the space.Their job is not going to look like it did two years, five years ago certainly not ten years ago. But the types of jobs that are available are going to be increasing because you need people with the specialized skill sets.

What I think you will see less of, is the do-everything, “Hey, if it’s SQL Server, I know it.” Yes, I think you can be conversational about most of the pieces within SQL Server, but there’s a lot of things that I’m conversational about that I’m not the guy you want developing that solution.

I can give you five bullet points on pros, five bullet points on cons but when it comes to writing code or cranking up a UI, cranking up the dev. environment, I’m not the person to do that. I think the best that we can do as professionals is remain conversational about those things. Know where they fit in, in terms of the big picture but not stray too far away from — “These are the areas that I want to specialize in.”

While there is room for generalist, I think that the room for someone who does the entire stack in every piece and I know Hadoop and I know yadda yadda yadda.

Carlos: That’s too much.

Tim: Exactly. Those professionals are very, very few, and far between, because you’re going to spend your whole life, I mean your whole working life and your personal life just keeping up with the technology.

Carlos: I think part of that is that you’re probably too far down in the weeds to come up and then provide that business value that the organizations are looking for. It’s one thing to know all of those but then to be able to speak to it in a healthcare or an insurance, or a technology framework is a little too much.

Tim: Exactly right. You have to know where they’re appropriate. Funny thing that you mentioned like healthcare or something like that, I’ve been seeing more lately, really specific –I’m looking for a healthcare DBA. I’m looking for this type of DBA.I think that’s probably a function of where we see things going because, if I’m running a hospital for example, I don’t want to hire a tech person. I don’t want to hire somebody who is — they’re a code junkie and they really, really know the technology front to back. Ideally, I want to have someone who knows where it fits in. I don’t want someone who’s so focused on, “This is the hammer that I have, so every problem looks like a nail.”

I would rather have someone who’s looking out for the business first and foremost, the technology as a supporting piece of that and not as the end goal for learning what I need to learn.

Carlos: Going back to that education. I think we’re still a little bit on the flipside there because a lot of our — at least again from the SQLSaturday site, it’s all technology-based. “Here’s a hammer, [laughs] go take this hammer and learn how to use it.”

Tim: Honestly, those are the easier things to learn because I can sit down with someone and I can teach them — this is how to write a SQL Query. This is how to build in SSIS Package. This is how to build a Cube. But to sit down with someone in that same timeframe and explain to them — this is why getting the data right on your medical coding procedures is so important.

Carlos: Exactly.

Tim: It’s a harder thing, it’s a longer tail to learn. It’s a more important lesson to learn. That’s more tribal knowledge and more grounded and just absolutely getting as much experience as you can rather than, “Hey I took a class and now I’ll learn how to do X.”

Carlos: I feel like our conversation did veer a little bit into the cloud. At the same time we’ve talked about some ways or the position is that the on-prem data warehouse is not yet dead. If someone were looking to get into that space, any specific skills or thoughts around, I guess, what I’d call the traditional approach?

Tim: I think if someone’s getting into — if they’re coming in from say a DBA role and they want to get into doing Data Warehousing, whether it’s on-prem or not on-prem, there are a few baseline things. I mentioned earlier learning the technology is a secondary concern but there are some baseline things that you have to know if you’re going to do those types of things.Above and beyond knowing — you can’t do Healthcare Data Warehousing until you know how to build a Dimensional Model. Some of those things like learning how to build a Dimensional Model doesn’t matter where you’re learning to build those things whether on-prem or in-the-cloud, there’s still going to be a critical piece of that. Learning how those technologies fit in together certainly is a big piece.

Learning how you secure those pieces whether it’s on-prem, in-the-cloud, whether it’s Hadoop, whether it’s SQL Server on Linux. Knowing how to give the person the information they need and only the information they have the rights to, is going to be critical. Really focusing on those baseline skills would be my recommendation. You can learn those things. You don’t necessarily have to be working in any particular technology to do that.

You can be — hopefully it’s not a dirty word in here — you could be an Oracle person who’s specialized in Oracle for a while.

Carlos: [laughs]

Tim: You can learn the Dimensional Model. If you’re doing Tabular, you can learn to do Tabular Manipulation, Tabular Queries and things like that. Just building on that knowledge even if you haven’t built data warehouses or worked in Hadoop or worked in other technologies before, you can learn some of those baseline things ahead of time.

Carlos: Oracle may not be a dirty word on the show, Tim but if we never invite you back you’ll know why. [laughs]

Tim: I’ll take the hint. I’ll take the hint. I did mention Linux, but I also said SQL Server.

Carlos: Yes. That was your saving grace there. [laughs]

Tim: All right. It’s Oracle and Windows just for the record.

Carlos: [laughs] That’s right. Very good. Last closing thoughts about on-premise data warehouse?

Tim: I’m really excited about the future of the data warehouse on-prem and in-the-cloud. Traditionally, I’ve been an on-prem data warehouse guy. Most of my work still remains around there. The funny thing is two years ago had you asked me if I was excited about some of the other technologies that are coming up, I would’ve really said, “No.”Most of where I tend to go, I try to base it on the questions that I’m getting from my clients, the things that I’m being asked to do and two years ago, maybe a little longer than two years ago but two-ish years ago, the types of questions that I was getting around some of these emerging technologies that weren’t on-premises data warehousing, were — not to be demeaning about the questions but they were fairly immature questions, really rooted around “Well, everybody seems to be doing this, should we be doing it as well?”

The questions that I’m getting nowadays are much more mature. There’s much more focus in those questions. I see some value in moving this to the cloud or I see some value in integrating machine learning into my environment.

What are the realities of doing that? When I start to get questions like that, I realize the market has matured and enough desire has come up organically and not because it was forced that there really is recognition of the value of those tools. Even though, again, with my experience of an on-prem data warehouse guy, I am excited about these other pieces.

I know that the things that I’ve done up to this point they’re not going to be wasted because maybe I do more in-the-clouds. Five, six, seven years from now maybe I’m doing more in-the-cloud. Maybe I’m doing a lot more as your data factory or whatever as your data factory turns out to be, as your machine learning and things like that.

But I know that my job and those of my peers, it’s not going to go away because data warehouse in its current form or in something close to it will continue to be a big part of business reporting because it has to be.

Carlos: I agree. I guess I’ll reference James Philip again, the VP of the Microsoft BI space. He mentioned how things were going to change there and with the advent of a lot of these different technologies, people are going to be integrating different things.You mentioned that you’ll begin to be the glue that helps a lot of those things stick together. I think the future is definitely bright if you’re willing to put in the time and continue to keep up on things or even specialize in a vertical.

Tim: Yeah, the glue, that’s a fantastic analogy because there are so many pieces that aren’t necessarily connected to one another but it’s going to be up to people like you and me to figure out which piece is appropriate where, but just as importantly figure out how they all tie together.

Carlos: Very good, so the future looks bright.

Tim: Yes absolutely.

Carlos: Compañeros, I want to take a moment tell you about COZYROC, the leading provider of SSIS library components with custom tasks and scripts. You’d handle zip, gzip, or tar files and your ETL. No problem.Do you have an interaction with an SFTP client or need United States Postal Service Address validation? Maybe Salesforce Integration, Dynamics CRM or heaven forbid even SharePoint integration, save yourself the headache of trying to get these components set up on your own and try the COZYROC tools. If you need to send emails or connect to Amazon Web Services, they have a task for you.

They even have tasks to help with Database Partitioning and File Stream Interactions. Their library of component makes you more productive by cutting your development time. COZYROC, SSIS plus has been successfully deployed around the world. It’s free to try and I know you’ll enjoy these great tools. So check it out at cozyroc.com. COZYROC, go to the next level. Tim, let’s do SQL Family.

Tim: Let’s do SQL Family.[music]

Carlos: Awesome. One of the things I always like to talk with people about their favorite tools. Tell me your favorite SQL Tool. It can be a free tool, a pay tool but what does it do for you and why do you like it?

Tim: Oh, gosh. There’s a lot that I use that just help me on a day-to-day basis. I would be remiss if I didn’t at least mention SQL Prompt. If you’re writing T-SQL Code without SQL Prompt, you’re spending way too much time doing it. It’s one of my favorite tools. I love like the Data Compare, I think, is part of that package as well.

Carlos: It is.

Tim: BIDS helper is a great low-end tool. It’s a free tool. If you don’t already have it, go get it. It’s free. My favorite tool absolutely, and it’s a free tool that you can use. It won’t cost you anything at all, is BIML. I’ve been using BIML for I guess about four years now. If you’re doing SQL Server Integration Services Packages and you’re not using BIML, you will be at some point.BIML is not appropriate for every project but if you can make patterns out of your ETL Processes, make that a reproducible process — I literally in one project, saved a client months and months of work simply by using BIML. They had a lot of packages. They were almost identical, 250 different processes that needed to have two different packages per process but they were virtually identical.

They had the same type of transformations, the same type of operations, a truncated reload and so on. Even learning BIML, I actually had to learn, that was when I was still in my learning phase of BIML. I saved them months of development time and who knows how many dollars, it saved them over time. It’s not a great fit for everything.

Again, like we talked about the Data Warehouse and those other tools. There’s a tool for every job but when you find a good use for BIML, it is a fantastic time saver.

Carlos: Yeah. Makes a lot of sense. For all of the little widgets that you don’t have to open and change one little thing. [laughs]

Tim: Exactly, if you get rapidly changing metadata, BIML is a fantastic way to solve that problem.

Carlos: If you could change one thing about SQL Server, what would it be?

Tim: If I could change one thing about SQL Server, it would be the Data Quality story.

Carlos: Yes. OK.

Tim: I got really excited when Data Quality Services came out in SQL Server 2012. I’ve not seen it evolve like I hoped it would. Still it’s a good tool for doing some low-end Data Quality exploration, but natively in SQL Server or SSIS or whatever tool you’re using therein, the Data Quality story is still largely manual. It’s still a lot of manual processes.There’s a lot of third party tools that Melissa Data comes to mind immediately that can help solve that problem. That doesn’t come without the cost. I would love to see some things built into SQL Server. Whether it’s SQL Server Data Quality services or some evolution of that, or some of the lower hanging fruit on data quality whether it’s an ETL or just day-to-day operations that, that job would be a little bit easier for me.

Carlos: A bit better. Very good. What’s the best piece of career advice you’ve ever received?

Tim: The best piece of career advice, I’ve gotten a lot. I think my favorite one is that “my career development is my responsibility and mine alone.” I think about the first long-term tech job I had. I worked for a school district for about five years. It was a great place to learn. Very low stress, definitely an eight to five kind of thing.During those years there, I really didn’t take charge of my career. I was doing some things and learning and blogging a little bit. But I was relying on my employer to send me to class, to send me to SQLSaturday, to send me to this and that. That really wasn’t the place of my employer.

If you find an employer that will help you look after your career development, it’s fantastic. It’s also very rare. I learned. I got some advice very early on. In fact, one of the guys that told me this, I would be glad to call him out — Andy Warren. I met with him the first SQLSaturday I’ve ever did — the SQLSaturday number three out in Jacksonville.

One of the very first things he said to me was that “You are responsible for your career development.” I’ve taken that lesson to heart. It’s the best advice that I’ve ever got.

Carlos: Our last question for you today, Tim. If you could have one superhero power, what would it be and why do you want it?

Tim: Carlos, that’s a dangerous question because I just saw Deadpool a couple of days ago.

Carlos: [laughs]

Tim: The imagination can run wild. But honestly I don’t really lose arms that often so being able to regenerate an arm, really isn’t a practical use day-to-day. I think if I could have one superpower, I would remember everything.

Carlos: Oh, there you go.

Tim: I can’t tell you how many times I’ve gone to a SQLSaturday and someone would come up and say hi to me. “Hey Tim, how you doing?” “Good to see you again.” I’m racking my brain thinking, “I’m such a terrible person for not remembering your name but for the life of me I can’t remember your name.”If I had a superpower I’d remember names, places. I’d remember that line or code I wrote eight years ago and what its purpose was. That would be a really useful superpower.

Carlos: Very good. Well Tim. Thanks so much for your time.

Tim: Carlos, my pleasure. Thank you for having me.

Carlos: For having you on the show.That’s going to do for us today compañeros. Thanks for tuning in. Now, if you enjoyed today’s episode, I invite you to leave a rating on iTunes.

[background music]

Carlos: Now I won’t beg you but I will grovel if I need to. So please get those ratings in and I would appreciate it. Any questions or comments you can reach out to me on Twitter @CarlosLChacon and I’ll see you on the SQL Trail.

Children: SQL Data Partners.

Episode 47: Where should I put my data?

As data size expands and the way we interact with data changes, in many cases we will need more than one way to store and access that data.  Numerous products have entered the data storage market to solve particular pain points and in this episode we discuss several of the data storage technologies currently available on the market.

The expansion of data and increased expectations the businesses has for analysis and modeling of data we may need more than one storage type to meet those expectations. As data professionals, it is incumbent upon us to understand how these tools work and put them to their best use–before somebody else puts them to sub-optimal use and we are stuck supporting them.  I am joined by Kevin Feasel, a previous guest on the show, who walks us through some of the technologies available and sorts out under what circumstances we want to consider using each one.

Episode Quote:
“We have this great expansion of data requirements and data storage mechanisms . . . but there are some major difficulties with a relational database.”

This episode is sponsored by

In this episode, Kevin gives us his definitions for big and small data and looking to get the right technology for our needs. Let us know if you agree in the comments below!

We touch upon the following technologies in the episode

Relational Database
Multidimensional Database
Hadoop Cluster
Columnstore Database
In-Memory Cache
Key-Value Database
Document Database

Kevin On Twitter
Episode 13 – The Apply Operator

About Kevin Feasel

Kevin is a database administrator for ChannelAdvisor and the leader of the PASS Chapter in the Raleigh NC area.  He also enjoys old German films.

Transcription: Data Storage

Coming soon

Episode 46: Unraveling Code

Have you ever looked at some tsql code and thought–How does even run? I know I have and it can be daunting to take a piece of code and review it for performance or to make a change to it.  I enlist the help of Jen McCown from the MidnightDBA team to discuss approaches to unraveling code.  We tackle the issues of nested views, documentation, formatting, Hungarian Notation, and visualizing how all the code pieces fit together.

Thinking of modeling your database after the system tables?  Well, Jen has a few thought for you there and share her pet peeves about where things went a bit awry with the internal tables.

At the end of the day, we just want our code to be understandable and usable for the next person who has to review it, because that next person just might be you.  🙂  This episode packs in a little humor with some nuggets of thought on how you might go about unraveling your next code snippet.

What is your biggest pain point when it comes to unraveling code?  Do missing semicolon’s just drive you up the wall?  Lets us know in the comments below.

Episode Quote

“Just look at the code . . Well, as nice as that sounds, sometimes just looking at the code is not good enough to understand what is going on.”

This episode is sponsored by Database Health Check

Show Notes

Jen on Twitter
Today’s Performance Tip: Views are for Convenience, Not Performance!
Bad habits to kick : avoiding the schema prefix

About Jen

Jen is 1/2 the team at MidnightDBA with her husband Sean and a frequent contributor to the community.  She is a proud mother.  When she is not writing documentation or unraveling code from her home in Texas, you might find her at the vendor booth for MinionWare software.


Transcription: Unraveling Code

Coming soon!