tsqllint

My PASS local group, IndyPASS, has its monthly meeting tonight. At my insistence, first-time presenter Nathan Boyd is showing off a SQL tool called tsqllint. Nathan, a coworker of mine at Salesforce, is the leading developer behind this GitHub project.

A lint (or linter), if you didn’t know, “analyzes source code to flag programming errors, bugs, stylistic errors, and suspicious constructs” (wikipedia). This one is designed specifically for T-SQL, is highly configurable, and includes a Visual Studio Code extension. What more could you want, right? If you want cleaner T-SQL code out of your developers, with less hassle on the part of your reviewers, it’s definitely worth your time.

If you’re in the area, keep in mind there’s a location change tonight. While IndyPASS usually meets at Virtusa, 1401 North Meridian (formerly Apparatus), this month’s meeting is at Moser Consulting in Castleton. As usual, doors open at 5:30pm, and we’ll turn it over to Nathan by about 6:15pm.

The 2017 Summit

As I adjust poorly to the three-hour time difference in Seattle this morning, I thought I’d share a few highlights of this week that are personal to me:

The Panel Sessions

I had a hand in bringing three panel sessions to the Summit this year, on Data Visualization, PowerShell, and Data Systems at Scale. I’m really looking forward to the discussions that emerge from these. If you plan on attending them, keep in mind that we’re using the #visualization, #powershellhelp, and #scale channels on sqlcommunity.slack.com as a way to take questions. Feel free to join the conversation there.

My Own Survey

There are plenty of ways to provide feedback to the people behind the Summit. But as I become more and more involved as a program committee manager, I want to give attendees a more direct, personal, and open-ended way to share your opinions. I will caution you, though – this is my own initiative and not part of PASS. I intend to use this feedback for the benefit of PASS and the Summit, but I make no promises about what I share and with whom. You’ll just have to trust me. And if you don’t? Hey, don’t fill out the survey! But if you want to help me help make the Summit better, here it is: Take My Survey

And thanks for your help!

My Own Session

It’s almost been an afterthought, but yeah, I’m presenting a session at the Summit this year. I didn’t quite plan on it, but I’m glad it’s happening. It’s targeted at developers, and – like nearly every session I present – it’s focused on making apps faster. So, if you’re interested in app performance, and you’re still around for the very last session of the conference, come see me talk about the .NET Framework’s SqlClient namespace in room 2AB!

PASS Local Groups

I’m speaking tomorrow evening at Michiana PASS. What’s my topic? Performance, of course! Actually, it’s about querying partitioned tables. Quickly. Because that’s how I roll.

But that’s not my point today.

Like any good technical community, the success of PASS doesn’t come from the top. Local Groups are the heart of PASS. If SQL Server is relevant to your job, become a part of your PASS Local Group. Or start one, if necessary. Share what you know, encourage your peers, and contribute to the community.

And if public speaking is something you wish you could do better, presenting in front of your Local Group is a safe way to dip your toe in those waters. I present a technical session about once a year to IndyPASS. In fact, I’ll be back in September. I’m also now starting to branch out to other Local Groups, like Michiana, to help them grow. As much as I enjoy taking part in SQLSaturdays, I think I might like this more.

Godspeed,

J

#PASS24HOP

In the past couple of years, I’ve had the opportunity to speak at a few SQLSaturdays, and presented to my local chapter, IndyPASS, a couple of times. I gained a new experience a few weeks ago, one that I had no idea would be so timely. I presented one of my sessions to YooperPASS, a new chapter in the upper peninsula of Michigan. This session was my first online session for PASS. I’ve presented online before at Salesforce, but that was to an internal audience. This was a new session, presented for the first time, to a bunch of strangers. Fortunately, the YooperPASS folks are very welcoming, and I think it went very well.

I mentioned this was timely, though, didn’t I? Well, that’s because I’ll be giving my second ever PASS virtual session on Thursday, the 20th. I’m a part of the 24 Hours of PASS: Summit Preview 2017! The same session I gave to YooperPASS, A Guided Tour of the SqlClient Namespace, will also be part of this webinar event. Because I’ll be presenting this session to a much wider audience now, I’d like to expand on my approach to this session.

First, I call it a “Guided Tour” because I’m cherry-picking those areas that I think deserve some special attention. A more comprehensive session would be impractical. So I had to cut things off somewhere. What made the cut are four topics that have valuable specific takeaways. A good technical session should give attendees something today they can put into practice tomorrow. I’d like to think I accomplished that with these topics.

Second, my choice of topics also gives me a chance to get across a broader message about how to scale ADO.NET code. Performance is always a focus of mine, and my experience has taught me that there is frequently a trade-off between application code and the database. One piece of code may perform better than another under a microscope, but its effect on overall system performance may actually be worse. In this session, I try to draw attention to this trade-off.

Finally, this is my attempt at an introductory session on an often-overlooked topic. Let’s face it, ADO.NET has been around a while. Because of that, I think the information available on it is inconsistent. StackOverflow didn’t exist until long after ADO.NET was introduced. Yes, there are some fine older resources on ADO.NET, but enterprises have changed a lot since many of them were written. Most current resources focus on Entity Framework, which is arguably just an abstraction layer over ADO.NET. So my session is intended to close the gap.

Anyway, I’m looking forward to being a part of this 24 Hours of PASS. If you tune into my hour, I hope it’s worth your time.

Godspeed,

J

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

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.

IndyPASS is going Back to School

Yes, I’ve been quiet on this blog forever. That’ll change soon. I’ve been working on a couple of topics that I think may be of use to people other than me.

But in the meantime, I’m presenting “Back to School: Query and Execution Plan Fundamentals” tomorrow night at the monthly IndyPASS meeting. Think of it like a SQL 101 for developers. Come join us for some free pizza and a little bit of learning.

Albuquerque Redux

In just a few weeks, I’ll be back in Albuquerque for their SQL Saturday. My session this time is Adding Lightness, and is a thorough examination of how compression works in SQL Server.

If you’re in the Albuquerque area and work with SQL Server on a regular basis, yet have never attended a SQL Saturday, well… GO! It’s excellent training, virtually free, and a great opportunity to network with your peers. Last year’s event in Albuquerque was outstanding, and I’m looking forward to going back.

Since I’m once again presenting just after lunch, I though I’d do something a little different this time. If you’re a developer or DBA who spends a lot of time addressing performance issues in Transact-SQL, feel free to join me for lunch prior to my session. We’ll make it an informal open discussion on queries and performance. Feel free to bring your war stories, current headaches, and burning questions.

Yay #SqlFamily

It’s a big #SqlFamily week for me. Tonight, IndyPASS is having my co-worker, Eddie Wuerch, present on SQL Server v.Next. It’s the first IndyPASS meeting I’ve been able to attend in a while, and I’m looking forward to it.

And then I’m leaving for Salt Lake City this weekend – my first trip to Utah! – where I’ll be presenting a pair of sessions for their SQL Saturday. Both are a result of the great feedback from the #SqlFamily on past sessions I’ve done. Adding Lightness expands on a segment of a previous session that was very well received, and Ad Hoc Rally takes a slightly more focused approach to material that I’ve also covered before.

So, if you’re in Indy, come join us tonight. The pizza’s free and the material should be excellent. Eddie is a great presenter, and I’m sure he has plenty of good info to share.

And if you’re in the SLC area, this Saturday is your chance to get some outstanding database training. In addition to the 24 scheduled PASS sessions, it’s also the Intermountain Big Data Conference, so there should be plenty to learn.

Godspeed!