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.

NULL Pain

I had an issue today that resulted in a tweet to #sqlhelp:

#sqlhelp See Remarks section: http://ow.ly/Za6HX So how do you strip out 0x000? Suggestions?

As it turns out, when you have a character string in SQL Server that contains character 0x000, it really doesn’t know what to do with it most of the time, especially when you’re dealing with Unicode strings.

I did track down http://sqlsolace.blogspot.com/2014/07/function-dbostripunwantedcharacters.html, but I generally try to avoid calling UDF’s in my queries.

I ended up settling on this, which is based on the fact that REPLACE() works when the data is ASCII

CASE WHEN LEN(REPLACE(CAST([foo] AS VARCHAR(4000)), CHAR(0), '')) = LEN([foo]) THEN [foo] ELSE [dbo].[strip_char0]([foo], NCHAR(9)) END

My strip_char0 function was mostly like the one from sqlsolace, so I won’t repeat it here. But I did add in a replacement character option (in this example, a tab). And I’m only calling the UDF when I need to, which makes the performance geek in me happy.

My thanks to @SQLSoldier for being responsive on the #sqlhelp hashtag.

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.

It Does Not Depend

A common refrain in the SQL Server community is the phrase, “It depends.” And, truthfully, that’s the correct answer to many questions. There are so many variables in any given situation that what works great 99 times will not work on the 100th, due to some slight difference.

But here’s one piece of advice that – even if it’s not perfectly 100% – has a high enough success rate that I would say “It does not depend.”


EXECUTE sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
EXECUTE sp_configure 'Optimize for Ad hoc Workloads', 1;
RECONFIGURE;
GO

I have yet to come across a situation in which this shouldn’t be enabled. And I’ve heard others say the same.

By the way, in case you have the same question as an attendee at my SQL Saturday session today in Salt Lake City, no, it will not wipe the plan cache when you turn it on.

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!

SELECT TOP 1 * FROM PASS.Summit2015;

I’ve been uncharacteristically quiet during this particular Summit. I picked up a cold on the way out there, which made the week a bit different than I was expecting. Still, I always take away quite a lot each time I go, both big and small, and this year was no exception. I’d like to share one of the small things here.

At some point, we’re all running ad hoc queries in a production or otherwise significant environment. We’re doing root cause analysis, looking for bad data, trying to figure out why something is slow, whatever. Our ad hoc queries get rather large sometimes, as we’re pulling in information from all sorts of places. Often, we have several stored away in a toolbox for these occasions, our “go-to” scripts when we get that inevitable call in the middle of the night.

Here’s one small change to those queries that will make one small difference. Admittedly, it’s not much, but it could add up, especially if you’re in a large shop with a lot of people who are doing the same thing. If we all changed our ad hoc to include this, we might actually have an impact. So, what’s the change? Throw in “OPTION (RECOMPILE)” at the end of all your ad hoc statements.

What does this buy us? Well, basically, it keeps those one-off ad hoc queries from bloating the plan cache. Does it really matter? Not much, in the grand scheme of things, except that I know some of the queries in my toolbox are quite lengthy. Why have SQL Server cache the plans for those?

By the way, you can also enable the Optimize for Ad Hoc Workloads server configuration option. This will save you nearly as much, at least until you hit F5 the second time.

Anyway, I might share some of the other stuff I picked up from this year’s Summit. Just not my cold, I hope. But the one thing I always share is my advice that you attend. If you’re a data-centric developer like me, definitely go. It’s excellent content by a thriving community in a beautiful city. I’m already looking forward to next year.

Getting Better

When I (infrequently) post something here, it’s generally tech-related, and often specific to SQL Server. This one’s a little different. Throughout the last year and a half, I’ve improved my physical fitness dramatically, losing weight and exercising a lot. I get a lot of compliments (and they’re appreciated), but I also get asked frequently how I did it. This is my answer.

Before I get into the specifics, please keep in mind that everyone is different. We all have our motivations and fears, physical distinctions, our own histories. We are all at different stages of our lives. What works for one person is not a recipe for another. But, nonetheless, I’m sharing my own story here so that – if you’re wanting to head down the same path – you have one more perspective to consider.

It started around February of 2014, and in a very unassuming way. I was at work, attending a lunch meeting. Pizza was provided. Good pizza. And, for whatever reason, I just decided that day that I was going to skip it. This wasn’t the first time I’d turned away food in an attempt to improve my health. But it was the first in a pattern. Starting with lunch that day, I watched what I ate. I avoided foods with a lot of fat. I started counting calories. I ate smaller portions. I avoided soft drinks entirely.

There were a few effects from this. First, I was hungry all the time. Not starving, but always hungry, a mild yet unpleasant and ever-present kind of hungry. In the past, this is what would do me in. I would cave in and go back to old eating habits. But this time – again, I’m not sure why it stuck – I kept at it. I got used to being hungry. That sounds hard, and it was. And I’m not saying I was starving myself, not by a long shot. But I was always – ALWAYS – just a bit hungry. And yes, I said ‘was’ – more on that later. One thing that helped was actually counting calories. I would do the math each day, keeping a running tally. Being a competitive and numbers-based sort of person, keeping ‘score’ like this kept me focused.

Another effect is that my arthritis, which I’ve fought with since college, nearly disappeared. I eventually tied this as much to the soft drinks as the weight loss. Even now, if I have more than one a day, my arthritis tends to creep back in. It’s worth mentioning that I also take a couple of glucosamine/chondroitin pills each day. My experience with those has been that I need to stick with them. If I forget for a couple of days, then the arthritis creeps back. And ‘creeps’ is the right word, because it’s as if there is a build-up of of their effects in my body. Skip a day, and I’m not bad. Skip a few days, and the ‘reserve’ drops. Start taking them again and it takes a couple of days before I gain the benefits again. But even with the g/c pills, soft drinks have been the biggest factor. For what it’s worth, I can’t stand fake sugars, so I’ve never been into diet drinks. Caffeine was optional (though usually present), but it always had to be sugar or hfcs in my drinks. So, lots of Coke and Cherry Coke and Dr. Pepper, hfcs and all.

Anyway, this went on for about six months – simply changing my diet, day in and day out. And in that time, I dropped from about 195 to about 165. It was a steady drop, one that I could (and did) watch daily on the scale. I received a lot of compliments, both from people who saw me day-in and day-out at work and from people who saw me more infrequently. The latter were quite often very impressed. Again, I appreciated the compliments, and that certainly helped me stay on track.

In June of 2014, the Vice President in charge of our department at work (to whom I will forever be grateful), elected to sponsor a membership at a nearby Crossfit gym for anyone who wished to join. We already had the opportunity to get reimbursed for fitness classes (and still do), but this was a team-building attempt, a two-day-per-week membership that included a class at noon on Fridays for all those in our department who wanted to work out together.

I was originally not going to go, but I had one or two coworkers talk me into it. I’m not one to make this claim lightly, but that day changed my life. I quickly learned a couple of things about Crossfit that suited me perfectly. For one, the workouts are always varied. Today’s workout involved a pull-up/ring combination, three distinct bar movements, and some jumprope. That is very important for someone like me, who has a lousy attention span and needs to be mentally stimulated. Even the plan for the workout – things like AMRAP, EMOM, etc (go ahead, Google them) – will force you to think differently about the exact same movement, depending on the requirements of the workout. This is one facet of Crossfit that has helped me immensely.

Another aspect to it is that movements often use your body weight as resistance, or use free weights. Aside from the rowers (or ergs or whatever the darn things are called), there are no machines. The effect is that you are conditioning your body, as a whole, all the time, not just working on this or that specific muscle group. After more than a year of Crossfit, my balance has improved. My overall fitness has improved. I really appreciate that.

A final thing about the benefit of Crossfit in particular did not occur to me until much later. It’s the community that has formed there. I’m a developer. Not only that, I’m a data-tier developer. It goes without saying that my social skills are not my greatest asset. But attending the same classes, week in and week out, for over a year, and I’m on a first-name basis with most of my classmates, as well as most of the instructors. I suppose that’s to be expected, but it’s just one part of the friendliness there. The instructors do their best to make it a welcoming environment. Crossfitters encourage each other. Yes, many of us are extremely competitive, but we’re competing against ourselves as much as anything. We love to see each other do one more rep, lift a little more weight, get that next PR. There’s an implicit understanding that we each have our limits and we’re there to push those limits, not hurt ourselves trying to beat the person next to us. It’s a great environment, and though I almost never see any of these people outside of class, I’m very comfortable around them in class.

Anyway, that started in June, with our group’s Friday noon class. I took advantage of the +1 and also attended the 5:45am class on Tuesdays, starting the very next week. Yes, 5:45am. I’ve always been a bit of a morning person, but this was a time that I knew I could do without interruption. After all, the only thing you ever miss at 5:45am is sleep. That first Tuesday was rough. I had an issue with low blood sugar and didn’t have the benefit of a bunch of co-workers sharing my class who were also as out of shape as me. But I went back that Friday, and the following Tuesday, and so on.

At some point, I added a third day to my program, attending the 5:45am class on Thursdays as well. There was probably at least a month between thinking about it and actually doing it, as I was still taking a couple of days to fully recover from each workout. And yes, that’s part of it. Even now, after more than a year of Crossfit, I’m often sore from Tuesday morning’s workout into Wednesday evening. Part of it is the intensity of the workout. Part is my age – I’m now 40, after all. And part of it is just the fact that I didn’t start getting into shape until last year.

That brings me to another point about this process. It’s been quite a learning experience for someone who has such as limited history of exercise. Other than a couple of seasons of little league baseball, I never played organized sports as a kid. I was never on a team in school. I played pick-up games of this and that, and played a fair amount of racquetball in college, but that’s it. When I walked in the door at Crossfit for the first time, I hadn’t touched a barbell since at least college, and hadn’t run a mile without stopping in probably the same time. So, yes, a lot of this journey has been dealing with soreness. But it’s also been about learning what my body is actually capable of. I’ve learned what body parts give out and when. I’ve learned about muscle fatigue and joint pain and oxygen issues. I feel that I now understand my own body a whole lot more than I did. I know when I can push myself, and when to back off.

Like I said, I hadn’t run a mile since college (at least). That changed sometime late last year, during a Crossfit workout. That struck me – how I was able to do something so simple for the first time in nearly two decades. So, later that week, I went on a run. I probably ran about two miles or so. Shortly after that, I extended it by half a mile. And then another half a mile. By the time winter had really set in, I was up around 8 or 9 miles, I think.

Because I was fascinated by the process of improvement, and the “another half a mile” mentality, I decided I was going to try to run the 500 Festival Mini-Marathon in May. I went out on a snowy day to make sure I could at least hit the 5-mile mark. If I could pull that off, and then increase by half a mile each week, I would be able to go the distance in the Mini. I went the distance, and signed up for the Mini sometime in the next couple of days.

Training for the Mini was another adventure in learning what my body could and could not do. I quickly learned that each additional half-mile was always the hard part. If I went 8 miles one week, then 7.5-8.0 was twice as hard as 7.0-7.5. Then, the following week, 7.5-8.0 was relatively easy and it was 8.0-8.5 that was difficult. However far I ran, that last half-mile was always the killer. But I kept with it. Runkeeper, the app I use on my phone, helped. Being able to run out in the country, farther and farther from my neighborhood, also helped.

I think my time in the Mini was about 2:07. Respectable, as I’ve been told, especially for a 40yo first-timer. I don’t have any intentions of running any more races (though the occasional quirky 5k does appeal to me), but I will likely go back for the Mini next year. The race is quite an event – people lining the streets, massive attendance, bands playing along nearly every block, taking a lap around IMS. So I’ll likely do it again because I enjoyed the event.

Anyway, as I’ve been working out and running, a curious thing happened. For the longest time, I was still getting compliments about my weight loss. Only I wasn’t losing any more weight. I’m pretty steady in the 160-165 range now, and have been there since last June. But what I’ve found is that I’m redistributing my weight. I’m getting more muscle mass. So it looks like I’m losing weight. I have to say that’s more rewarding than when I was actually losing weight.

Remember when I said I *was* hungry. Well, as my fitness improved, my metabolism went up. Anyone with a basic understanding of human health and biology would recognize that, but to actually live it is different. Granted, I haven’t completely returned to my old diet, but I’m a lot more relaxed about it than I used to be. And I can get away with that. My portions are down, I try to balance the bad with the good, and I still avoid soft drinks as much as possible, but it’s not like it was during that initial time.

What all this means to me, day to day, is that I get a lot more out of life. I play soccer with my kids (and run a club at their school). I get involved in active stuff without worrying how bad I’m going to feel the next day. I’m not nearly as limited by arthritis as I used to be. When I go for a check-up, I get “Well, I hate to break it to you, but you’re perfectly healthy!” And, frankly, when I look in the mirror, I see improvement – more muscles, not a steadily-growing belly.

The scary part is that I’m now a prisoner to my own success. Like I said, I started late in life. I’m not getting any younger. When I miss a couple of Crossfit classes, or can’t go for a run for a while, I can feel it. So, I’m mentally conditioning myself to the fact that I’m going to be doing this the rest of my life. That’s a hard pill to swallow. But the alternative – the poor health, lethargy, etc – that was its own prison. It’s just a matter of accepting that I’ve got the 5am alarm set a couple of days a week for the rest of my life.

Anyway, that’s my story. I hope it helps someone. Godspeed!

SELECT INTO

I just came across a stored procedure that does something like this:


DECLARE @stmt NVARCHAR(MAX);
CREATE TABLE #foo (col1 INT, col2 INT, col3 INT);
SELECT @stmt = N'SELECT col1, col2, col3 INTO #foo FROM bar;';
EXEC sp_executesql @stmt;

The incorrect assumption that the developer made was that the first #foo (the one created with CREATE TABLE) was the same as the second #foo (the one referenced in the INTO clause). But that’s not the case. To prove it, try this slight variation:


DECLARE @stmt NVARCHAR(MAX);
CREATE TABLE #foo (name NVARCHAR(2));
SELECT @stmt = N'SELECT TOP 5 name INTO #foo FROM sys.objects; SELECT * FROM #foo;';
EXEC (@stmt);

If the @stmt was really using the first #foo, then what would you see? The first two characters from name from 5 rows from sys.objects, right? No, actually you’d see Msg 8152, “String or binary data would be truncated.”. But that’s beside the point. The point is that it isn’t using that first #foo. It’s creating a second one. And it’s getting away with that because it’s running within EXEC. Take out the use of @stmt and SQL will complain to you when you try the “SELECT … INTO #foo” that #foo already exists.

Also note that the original used sp_executesql, but that my second query used only EXEC. That was to prove that it was EXEC alone, not sp_executesql, that was the differentiator.

SQL Saturday #402 – Indianapolis

I’m once again presenting a session at a SQL Saturday, this time in my own backyard. SQL Saturday #402 is here in Indianapolis this Saturday. If you’re in the Indy area and spend any significant amount of time with SQL Server on a regular basis, I really encourage you to go. You can learn a lot in a short amount of time, make some good contacts in the industry, learn about great products that can make your job easier – and, if you’re luckier than me, perhaps even win a prize or two. They also have Friday “pre-cons” with room still available, if you want to take it a step further.

My session, Playing in Traffic, is the same one I presented in Columbus. I’m still including the same topics, but I’ve taken the feedback and retooled it somewhat. If you do attend, I can tell you that all of the presenters appreciate your feedback, especially your criticism. We’re all trying to improve, and what you share now can make a difference later.

Also, while I’m not fortunate enough to be presenting in Seattle, I’m definitely attending the Summit this year. If you’re wondering whether or not to attend, just ask anyone who’s been. It’s definitely a valuable experience for SQL Server professionals.

@@ROWCOUNT Strikes Again

Just when I thought I’d been thwarted the last time by @@ROWCOUNT, along comes this gem:

begin try
select top 10 * from sys.objects;
end try
begin catch
select top 20 * from sys.objects;
end catch;
select @@rowcount;

Yup, it returns 0. It seems that even a try/catch block will stomp on the results. Maybe I’ll learn one of these days.