Author: Jay

T-SQL Tuesday, Late to the DevOps Party

This is what I get for slacking off on my blogging…


So, I totally missed this month’s T-SQL Tuesday. Which is a shame, because it’s an area I have a lot to talk about. Grant Fritchey hosted T-SQL Tuesday #91, which is all about DevOps. There were a lot of good posts. I liked Rob Farley’s comment: “DevOps is the idea of having a development story which improves operations.” Andy Yun had a nice take, closing with “This is what I believe DevOps is all about. The tools and processes being pioneered today help all of us build better, more stable software, which is better for all of us.”

My own personal experience with what I think of as DevOps took a big leap about a year and a half ago when I moved from a product group in my organization to a development-oriented team inside our operations group. That transition both confirmed and challenged an observation I’d made years ago.

My observation was that organizations are all on a track between two extremes. On the one extreme is the legendary “two guys in a garage” story. On the other is a Fortune 500 conglomerate, doing business in tightly-regulated industries. In that garage, Woz was free to design and innovate at will, and what he produced is now the stuff of legend. Apple, on the other hand, delivers a new product only after a monumental expense of time and resources. They still produce remarkable products – one cannot argue with the massive success of the iPhone and its successors. But how those products come to market is a far cry from that Homebrew Computer Club meeting in ’76.

The critical piece of this observation is that all organizations move from the former end of the extreme to the latter. They may move at different rates, even coming to a stop for any length of time, but they never go the other direction. Let me repeat that: They never go the other direction.

Let’s think about database backups as an example. Garage organizations may not do any backups at all. Then something gets lost, and perhaps a weekly full backup task is scheduled. The system grows and taking a simple full backup once a week no longer scales properly, so a better schedule is created, with more particulars about what is backed up, when, and how. This process keeps getting refined, until, one day, there is a standard procedure for performing backups, including off-site storage, regular testing of the restoration process, and all kinds of other aspects that operations people in Fortune 500 companies have to think about.

Many of us are quick to claim that the Garage is better. We love the folklore around how Apple (and other companies like it) got started. We love to reminisce about those times that we turned caffeine into code and all was right with the world. It’s just something about being a coder. But that’s not universally true. Would your organization be better off without a good backup restoration procedure?

The problem is that – good or bad – the processes pile on. Bad or obsolete procedures never get removed, just marginalized until they become an indistinguishable part of the Fortune 500’s ecosystem.

I help administer a database that does not need to be backed up. Yes, that’s correct. It does not need to be backed up. Ever. It’s a data warehouse, of sorts, that is populated by a convoluted ETL process originating from log files. Due to both size and the diminishing value of old data, we only keep data in this database for a short time. If we were to lose the database, we would recreate the structures, and then “re-dump” the log files into the ETL source folder, and let the process churn through them again. That method of recovery would only take marginally more time than restoring from backup. By the way, for this database, the backup process itself is a serious drain on system resources.

Getting our operations group to STOP backing up this database was an adventure, because of all of the procedures in place to ensure that every database was properly backed up. At one point, because it was causing a production issue, I disabled the backup job (thereby curing the immediate issue). For my quick thinking, I was rewarded with having my SQL Agent rights revoked.

Anyway… the moral of my story is that the situation gets more complex over time. Once again, it never gets simpler. At least, that was my theory.

Enter DevOps.

To me, this is precisely the point of DevOps: The ability to go backwards on this track.

Now, here’s the hard part. To do this – to move towards the Garage end of the track – takes people who understand what is important and what isn’t. You can’t trim excess process at whim. You have to know what should get trimmed and what shouldn’t. Because you’re removing processes that protect the organization and limit risk, you need smart and organized people who will do the right thing without a process directing them.

DevOps is all about the people. It takes good communication. In his T-SQL Tuesday post, John Morehouse emphasized it with his call to “Start talking.” David Alcock joked about Dev and Ops going to “couples therapy” (and did indeed make a good point). But it’s not just good communication. It’s good people. For DevOps to work, DevOps people must be smart about what they do and don’t do. And then, if they’re successful, they’ll do what I didn’t think was possible – move from the Fortune 500 end of that track back towards the Garage.

As long as they don’t get their SQL Agent rights revoked in the process.

What are you known for?

As I mentioned before, I’ll be touching on a few marketing topics over the course of the next several weeks. Taking a top-down approach, I’m starting with the question, “What are you known for?”

I hang around the IndyCar paddock whenever I can, which isn’t nearly as often as I’d like. At the St. Pete race one year, I remember listening to one of the team PR people talking about how to get into her line of work. I think it might have actually been a Pirelli World Challenge team rep, Kelly Brouillet. She said something like, “Do one thing really well. If I have a go-to person for one thing, that’s worth a lot more to me than someone who knows a little about a lot of stuff.”

She’s right. As I’ve progressed in my career, I’ve noted who the experts are. I tried to do something in PowerShell a little while back, and turned to Mike Fal when I got stuck. When I’m trying to figure out the right direction to go with a columnstore issue, I look up what Niko Neugebauer has to say on it. The reason I was listening to Kelly that day in St. Pete is because she’s a lot more savvy about marketing and PR in the racing world than I am.

Brent Ozar, one of those high on my experts list, had a session at the Summit last year, on the Professional Development track. He called it a “500-Level Guide to Career Internals” but it was really a wonderfully personal “How did I get here?” talk. One of the things he stressed was a consistency in message:

  • His Twitter account says: “I make Microsoft SQL Server faster and more reliable.”
  • His blog says: “I co-founded Brent Ozar Unlimited, a boutique consulting firm that helps you make SQL Server faster and more reliable.”
  • His 2016 PASS Summit bio says: “Brent Ozar loves to make SQL Server faster and more reliable.”

See a trend? Being known for making SQL Server faster and more reliable is Brent’s desired brand identity. BrentOzar.com is where I go when I have general SQL Server performance or reliability questions. It’s often the first place I go. Once I gain a little insight there, I might dig deeper elsewhere. Brent is very good about pointing people to experts in the field, who have a deeper but more narrow focus than he does. As for myself, you’ll find the phrase “fast and efficient SQL code” peppered throughout my online activity. I’m competent at quite a few things, but I’d like to think I really shine at that.

As the communications director for the IndyCar Ministry, it’s my role to get the message out that we are “dedicated to providing spiritual support and counseling to the IndyCar Series drivers, teams, and staff.” But what are we known for? That’s a tough question, actually, and one I’ve been wrestling with. “Being there when needed,” has come to mind. I’ve often joked that IndyCar is a traveling circus. The paddock is very close-knit, and rightfully so. Having chaplains who are inside that paddock, not just logistically, but emotionally as well – that means a lot. It makes the difference between whether or not someone will open up about a problem in their life. In any case, I’m still working on this one. In part, it’s actually what prompted this post.

In a similar capacity, I help Blue River Soccer “provide recreational, competitive, and educational soccer opportunities for the youth of Shelby County, Indiana.” Our tagline, which plays on what we’re known for, is “Ignite the dream. Launch the evolution. Respect the match.” We recognize that soccer is still in a transition period, at least in our area. So we set our sights accordingly. We try to give kids a passion for the game, give them the fundamentals, and set them up to move the needle forward – be better prepared to pass the game on to their own kids. Frankly, we’re not one of those associations with thousands of kids, paid coaches, and a big-time travel program. We’re known for being a place to which parents can feel comfortable bringing their kids, regardless of what they know about the game coming in.

It all comes back to that question. If you’re promoting something – yourself, your company, your product or services – you have to answer that first. So… what are you known for?

Marketing

I write software for a living, and my posts are generally about topics that I think are useful to other people who do the same. This is the first in a series that is a slight diversion from that. I say ‘slight’ because I think the core concepts of good marketing are useful to everyone in the business world, regardless of your role. Follow along, and I think you’ll soon agree with me.

I think it’s worth explaining where I’m coming from first. I am not a professional marketer. Like I said, I make my living writing code. But I do have some experience in this area, so let me tell you about it.

I started as a software developer at ExactTarget in 2008. ET was a marketing company in Indianapolis that was eventually acquired by Salesforce to become the bulk of the new Salesforce Marketing Cloud. I still work for Salesforce, now in more of a DevOps role in the Marketing Cloud.

Anyway, one of the benefits of being an employee at first ET and now the Marketing Cloud is the opportunity to sponsor a non-profit organization’s use of a free account on the platform. I took advantage of this years ago to help the IndyCar Ministry with their communication and funding efforts. The chaplains of the IndyCar Ministry serve the spiritual needs of the IndyCar paddock, as well as that of the Mazda Road to Indy ladder. IndyCar is a “traveling circus” of sorts, and having chaplains present to counsel and listen and minister to them is more vital than one might think. But doing so takes money – note the “traveling” part of that phrase – and that’s where I come in.

So I use the Marketing Cloud, as well as other tools, to help the ministry communicate with its supporters and spread the word about its efforts. I’ll be frank – I’m no marketer, at least not a paid one. I write software for a living. But I write software for a marketing company, and I’ve been helping the ministry for a long time now. I’ve been picking up some experience and expertise here and there over the years. Like most of life, this generally means the test comes first, followed by the lesson (expect an embarrassing post or two in the near future). But, as I often point out to the chaplains, I’m worth every penny they pay me!

Anyway, over the coming weeks, I’ll be sharing some of those things I’ve learned. Whether or not you realize it, chances are good that you’re actually a marketer too (and that itself is one of my upcoming posts). I hope I help you become a better one.

Godspeed!

SQL Saturday #597, Phoenix

I’m at another SQL Saturday again this weekend, this time in Phoenix, Arizona. I’m looking forward to meeting the Arizona SQL Server Users Group folks and being back in the Phoenix area for the first time in over a decade.

Since Microsoft was kind enough to open up compression (along with a host of other features) to all you non-Enterprise plebes with 2016 SP1, I figured I’d bring that session back out again. And after some recent adventures with partitioned tables (which I STILL want to write about in more depth here!), I’m also presenting a new session on querying against those.

Godspeed

Threading

A while back, I wrote an app that spawned a collection of threads to run some work in parallel, using the resources in the System.Threading namespace of the .NET Framework. Some time after that, I worked on another app that also had a threading component. This second app was reviewed by another developer from outside my immediate circle. He asked, “Why didn’t you use the System.Threading.Tasks” namespace? Uhh… because I didn’t know it existed?

That namespace was introduced in .NET Framework 4 – not exactly recent history – but I had somehow missed it for quite a long time. There are a few causes for that, but the one I’d like to focus on here is a trap that I think catches many developers at one time or another: We think we have it all figured out. While we are, to some degree, practical mathematicians – professionals who assemble algorithms to meet requirements – we are also creators. Our code is our art. And oftentimes, we don’t have the humility necessary to accept the possibility that our art isn’t beautiful. So we shy away from having the right people check our work.

This reminds me of an old saying: If you’re the smartest person in the room, then you’re in the wrong room.*

Now, this is not a commentary on my current team. I work with some really smart people, and I’m very grateful for that. But while my teammate may be one of the best PHP or Node.js coders I know, that doesn’t necessarily translate to an expertise with the .NET Framework. The true test is this – no matter how smart they are, if they’re not catching my mistakes, then I’m not being held accountable.

Lesson 1: Make sure someone’s catching your mistakes. If they’re not, then do you really think the reason is that you’re not making any?

So, back to the two apps… After the other developer’s feedback, I reworked the second one prior to release, and it passed its code reviews. The first app, meanwhile, developed some bad behavior in production. There was definitely a race condition of some sort, but I couldn’t seem to nail down where it was. I made a couple of adjustments to the code, but nothing seemed to bite. Of course, I couldn’t reproduce it in testing either.

Finally, I ripped out the threading code entirely and replaced it with nearly identical code based on System.Threading.Tasks. I was concerned about the risk of introducing more bugs, about the fact that I was still unable to reproduce the problem, and about how long it had been a problem, so I tried to remain as faithful to the original design as possible. And, yeah, honestly, I crossed my fingers.

Once this new version was released, the problem was gone.

Lesson 2: System.Threading.Tasks really is better than System.Threading.

I’ll never know what exactly fixed the problem. I could keep researching it, but the costs to me for that aren’t quite worth the benefits at this point. My takeaway was that the new stuff just simply works better. Whether that’s because it’s easier to use the right way (and harder to use the wrong way) or its internals are less buggy or some combination thereof, the end result is the same. I hope that’s old news to anyone reading this, but I wanted to share my experience just in case.

* I was unable to identify with certainty the source of this phrase. The leading candidate I found was 1962 Nobel Laureate James Watson.

T-SQL Tuesday, Microsoft Connect Edition

This is a bit of a deviation from my usual post. You can thank Brent Ozar for that. He decided to host Adam Machanic‘s T-SQL Tuesday this month. Since Brent Ozar Unlimited‘s newsletter is about the only one I read on a daily basis (and that even includes any racing-related ones I get!), I noticed it and thought I’d participate.

Brent’s topic is Microsoft Connect items. The one I chose is… well, let’s call it a pi-in-the-sky feature request. Ok, no more puns, I promise!

Ah, yes, SQL Server on Raspberry Pi… Once the Linux news came out, I think it’s safe to say it was only a matter of time before we saw a Connect suggestion like this. What I’d like to do is go through a little thought exercise on why it would indeed be a good idea, why Microsoft didn’t bake it in already, why it doesn’t work, and what needs to be done to get it there.

By the way, the Connect item also references R. I’m going to pretend for now that I didn’t see that. Maybe somebody like Ginger Grant will take a crack at it.

Once again, this isn’t my usual blog post. Typically, if I pose a question, I’ll give the answer. In this case, though, I don’t have all the answers. My hope here is simply to get a conversation going. So…

Is It A Good Idea?

This is basically a feature request. There are a lot of factors that go into the decision to add a feature to a product. One that is vital, but sometimes overlooked, is whether or not it fits within the overall theme of the product. It could be argued that small devices, in general, are not really a market for SQL Server. But I think Microsoft settled that argument when they announced Linux support. This is less about compact devices and more about running on one of the most popular Linux platforms.

Another factor is adoption. No one wants to add a feature that doesn’t get used. What would SQL Server on Pi even be used for? Truthfully, I really don’t know. But that’s the beauty of Raspberry Pi. The foundation’s initial goal was for use in education and developing countries, but it has found uses far beyond the original target markets. There are a lot of hobbyists out there, experimenting with Raspberry Pi. I’ll bet quite a few of them interact with SQL Server in their day job. So, rather than ask why Microsoft should add this feature, should we ask why not? I think this is a classic case of putting it out there and just waiting to see what happens.

Why Doesn’t Microsoft Support It Already?

I know only one answer to this question, and it drives the rest of this post. But first, let’s be clear – Microsoft put SQL Server on Linux to compete with Oracle. I don’t think anyone’s questioning that rationale. So, something like Raspberry Pi, while it may be a “nice to have,” is not on the v1 list of requirements.

But Raspberry Pi is just a platform for Linux, and SQL Server runs on Linux, so what’s the problem? Well, there may be others, but there is one absolute show-stopper. SQL Server’s minimum memory requirement on Linux is 3.25gb. The Raspberry Pi 3 Model B comes with 1gb. Which leads us to…

Why Doesn’t It Work?

Here we have an impasse. You can’t add memory to Pi. Well, you could, but it’s going to involve a soldering iron, hardware schematics, probably some assembly language skills… effectively, though, you can’t. You get what you get, and right now you get 1gb at most.

As for SQL Server, it’s not an empty requirement. It won’t install on less than 3.25gb. And once you install it, if you then lower the amount of memory, it won’t start. Ironically, I used an Oracle product, VirtualBox, to test this.

The reasons for this requirement are a bit cloudy to me. If you research memory use in SQL Server, you find two types of results. First, you have the question of just how much memory SQL Server can use effectively, on the high end. That’s not exactly our problem.

Second, though, you have the issue of how SQL Server uses memory. It has its own memory manager. When you configure it, you give it a constraint – how much memory to use – and it does its own thing inside this constraint. Incidentally, it’s surprising the number of Google search results are along the lines of “Why is SQL Server using all of my memory?” Well, because you allowed it to!

So, approaching SQL Server’s memory usage from the angle of “How can I get it to use as little as possible?” is a bit outside the norm. On that barebones Ubuntu VM of mine, I can see an idle SQL Server only consuming about 700mb. Yes, that’s while idle, and SQL Server will need room to do its job, but that’s a far cry from 3.25gb. Incidentally, it’s also consuming about 2.7gb of virtual memory.

You can see, to a degree, what SQL Server is doing with that memory. The most helpful DMV is probably sys.dm_os_memory_clerks. As the name implies, it shows how much memory is under control of all of the active mid-level memory allocators. If you add the pages_kb up, though, you don’t get the full memory footprint. Of course, the executing code needs space as well, and here’s where things get blurry. SQL Server stores its data in 8k pages, but it also uses those same pages to hold some system objects. So the question of what is used by the engine, by features, by cached data… it begs the usual PASS community response… it depends.

As a side note, what’s the least helpful DMV? That would be sys.dm_os_sys_memory. It doesn’t even work on Linux yet. That’s another Connect item.

What Needs To Be Done?

This is the question I really can’t answer. Could Microsoft give us the option to disable some features that don’t make sense on a compact system? How about full-text indexing? Would it really be necessary in this case? Ironically, even though you could disable it at one time, you no longer can. This leads me to believe there is the possibility that Microsoft has somehow made use of it elsewhere in the engine, which would kill it as a candidate. I wonder if that’s the case.

Another good candidate might be CLR. Once again, is this really necessary in a small system? And due to the inherent security vulnerabilities, this is a pretty isolated feature.

But wait! Before we go too far down that path, Microsoft has already figured it out! Check out the requirements for SQL Server 2016. Express Edition requires only 512mb!

I’m at a loss at this point. Does Microsoft have any plans to trim the Linux memory requirement? What did they do to allow Express to run so light? Can that strategy be applied? And, should they remove this barrier, what else is holding this up?

The most important question to me, though, is this: If you had a Raspberry Pi with SQL Server on it, what would you do with it? Comments are welcome!

SQL Saturdays

Sharpening my technical skills has been an ever-present goal since I started assembling computers for a guy named Dale around 1990. Over the years, some work better than others. There’s definitely nothing that can compare to hands-on experience, but that’s often a Catch-22. You have to have the knowledge to do the work to get the knowledge. So it falls to other means. At one time, I used Microsoft certification exams to put my learning on a schedule. Sign up for the exam, and I was suddenly under the gun to study for it. I’ve also read quite a few books over the years (see my last post for a few of my favorites). Conferences have helped – a couple of TechEd’s back in the day, and now I’m a regular at the PASS Summit.

But one of the most effective has been SQL Saturdays. I present at them semi-regularly, and I always make a point to be an attendee there as well, not just hide away in the presenter’s room until my turn comes around. If you’re in a line of work that would benefit from better knowledge about SQL Server and related technologies, and you haven’t already been to one, seek them out. I think it’s well worth the time and definitely worth the minimal expense.

Thanks to Brent Ozar, where I get a lot of tidbits like this, I have one more thing to share: A reflection on ten years of SQL Saturdays by one of the founders.

Godspeed.

A Christmas Wishlist

I finally had the chance to catch up on some reading last week, and it got me thinking about sharing this list. If you are in software development, then I would consider these four books to be required reading. I’m going to revisit this post in the future, because I’m sure there are more to add. But I wanted to start somewhere, and I’m confident about these four.

Fonts and Frustration

TL;DR – There are a couple of XML files at the end of this post. If you regularly present technical material using SSMS, download these.

I present technical sessions now and then – my local PASS group, SQL Saturdays, internal groups at my workplace, etc. I frequently find myself adjusting the fonts inside SQL Server Management Studio to make sure my material is readable on the big screen. I’ve also been in the audience plenty of times, watching with sympathy as one of my cohorts agonizingly navigates this problem.

Usually, it goes something like this. They first find the [100%] tucked away in the lower left corner of the text window, and blow that up to 150 or 200 percent. Then they run their query to find that the results are still at 100%. So then they eventually find the Options dialog under the Tools menu, find the Fonts and Colors branch of the tree, and then groan when they realize they have to figure out which three or four of the 30 different fonts they need to change. Sometimes, they’ll give up there and just go use ZoomIt (which any good technical presenter should have available anyway), but constantly bouncing around with ZoomIt will get old quickly over the course of an hour-long session.

But if they do manage to find the right fonts to change and take a good stab at what they ought to be, they get this wonderful message:

Font Frustration

Just the thing you want to see when you already have all your demo scripts loaded, right?

Oh, and don’t forget that – when the session is over – you now have to go through the same exercise to get SSMS back where you had it before the session.

So quite a while ago, I generated a couple of .reg files for myself, one called PresentationFonts.reg and one called NormalFonts.reg. You can imagine what these did when I applied them to the Windows Registry.

That worked great… until recently. The SQL Server Tools team has done some marvelous things with SSMS lately, and I’m very happy with the changes. But take a close look at one of those things they did:

Version Information

And where does this new shell keep its settings? Here’s a hint – it’s not in the registry. It’s actually in this file:


{LocalApplicationDataPath}\Microsoft\SQL Server Management Studio\13.0\ApplicationPrivateSettings

And this file is some bizarre hybrid of XML, JSON (with JSON inside of JSON, no less!), and I don’t know what else.

Fortunately, there is an option available. Under the Tools menu, there is “Import and Export Settings…”, which gives you a wizard for importing some or all settings from an XML file. So, with that in mind, here are my files that I use.

  • PresentationFonts.vssettings – This changes the font size to 16 for Text Editor, Execution Plan, Grid Results and Text Results.
  • NormalFonts.vssettings – This changes the font size to 9 for Text Editor, Execution Plan, Grid Results and Text Results.

NOTE: When you save these, save them with the .vssettings extension. Since I’m a cheapskate and use wordpress.com to host this blog, I’m prevented from using whatever extension I want. So they’ll show up as .doc files in your download dialog, but they really are just text XML. And the Import/Export wizard looks specifically for .vssettings files.

Obviously, you may not use the same settings I do, and you’ll have to customize them for your own uses. If you change the same four that I do, then all you have to do is fiddle with the sizes in the files. If you wish to change different fonts, you’ll want to export your settings with that wizard, change the font you want, export them again, and compare the files in order to figure out which GUID is which.

In any case, I strongly recommend having a pair of files like these parked right next to your demo scripts, where you’ll remember to run them as you prepare for your session.

I know this is a rather long post for a rather small tip, but I’m amazed at just how many of us fight this problem. If I had a dollar for every time I’ve seen a presenter struggle with font sizes, my Azure subscription would be paid for.

NULL Pain Revisited

In an earlier post, I covered an issue with special characters in Transact-SQL. I ended up creating a scalar function to handle it. Well, fast-forward a few months and now my query’s inability to go parallel is really starting to cause trouble. And, yes, scalar functions are absolutely poisonous to parallelism.

Anyway, I revisited the idea of stripping the null characters inline, rather than calling a function, and somewhere out there on the intarwebs I found this gem:


SELECT @var = REPLACE(@var COLLATE Latin1_General_BIN, NCHAR(0x00) COLLATE Latin1_General_BIN, NCHAR(9));

In my case, I’m replacing all instances with a tab character.

But I can’t find or remember where I got it! So, my sincere apologies to the real source, whoever you are. But even if I can’t claim ownership or identify the source, I still want to make sure this one’s out there for everyone else’s benefit.

Godspeed!