Puzzling times

It’s T-SQL Tuesday again today! I’m feeling a little distracted because I’m in Poland for SQLDay, suffering from jet lag, and with my mind turning to the presentations I have to give this afternoon and tomorrow. Yesterday I gave a full workshop on SQL Internals and Performance without turning on my computer even once, but I can’t let the day go past without writing a post.

The topic for the month is hosted by Matt McGiffen (@mattmcgiffen) and is on puzzles. It takes me back to the second ever T-SQL Tuesday, almost a decade ago in January 2010. Back then I wrote about an interesting puzzle I had come up with to explain how GROUP BY and HAVING work (the answer is here – and as my blog has moved in recent years as has Adam’s post, I can’t guarantee any of the links there).

This month I was reminded of something from years back (2007!), when Itzik Ben-Gan challenged people to solve a palindrome puzzle – how to find palindromes made up of words in a list. Even that post has moved – originally being at SQLMag, and now being at ITProToday. I took on the challenge, and was one of the solutions listed by Itzik in his follow-up post. I’ve never tried to create code to solve sudoku puzzles or other things, although when I was at university I did a subject that included using Prolog and needed to solve number-letter substitution puzzles, which I enjoyed in a strangely geeky way.

The reason why I mention the palindrome puzzle is that the approach I took was very similar to the approach that I’d take if I were trying to do it by hand – looking for words that start with the letters that I’m missing from the end. I’ve learned (and taught many times) over the years that if I can try to persuade the SQL engine to run a query the same way that I would in real life on paper, then when the computer does it, I’ve got a strong chance of it performing well.

These days the puzzles that I spend time on are the ones that my clients need me to solve, although I’m often tempted to pick up puzzle books and solve some things. We should never fail to find puzzles that exercise our minds – for what else will keep us sharp?

@rob_farley

SQLDay in Poland

Today I’m sitting in the historic city of Wrocław in Poland. I’m not sure I ever would have thought to visit, except that over a year ago I was asked to one of the special guest speakers at SQL Day – one of the largest SQL Server-centric events in the world.

The city is a charming place, and despite the rain that’s falling today and my jet lag from travelling for over 27 hours, this place has fascinated me, from the architectural mix of plain v elegant and old v new to its friendly people. I knew of Silesia from a board game, but didn’t know of Wrocław, and certainly didn’t know how to say it (which is roughly “Vrotz-waff”). I’ve walked around, enjoyed some coffee in a Czekoladziarnia, looked around the museum in the Old Town Hall, and will enjoy seeing the attendees at the event which is in the Centennial Hall – which I’m told is the second most significant building here (I even saw photos of it in the museum) and is on the UNESCO World Heritage List. The image below is from VisitWroclaw.eu.

hala-stulecia-jpg

Along with the PASS Summit (USA), SQLBits (UK), and the Data Platform Summit (India), I would consider this to complete the other Grand Slam of SQL events around the world. I’ve done precon seminars at the other three, and tomorrow rounds it off. It’s a great honour to be invited to be one of the special guests – when I look at the list of others they’ve had over the years I know I’m in excellent company.

I’m probably the only speaker here who won’t be using a computer. I’ve discovered over the past couple of years that if I can explain concepts without using one, it exercises a different part of the brain and people understand in a deeper way. And so I’m going to spend a day talking about SQL Internals and Performance, using a flip chart, and using audience members to help demonstrate concepts. Hopefully they’ll enjoy it and be able to approach their data in a different way. I have two other presentations to give later in the week which will also be different to the rest – one will hopefully involve audience discussion, and the other is a session that I’ve done as a keynote before, about story-telling and seeing yourself in movies. I hope to be thought-provoking and entertaining. If I can inspire the data community in any way, then I’ll be happy.

@rob_farley

My personal databases

This month’s T-SQL Tuesday sees Todd Kleinhans (@toddkleinhans) ask about what we use databases for – outside work. Because obviously as database professionals, we ought to be putting what we preach into practice in our personal lives. We should be leveraging our expertise to make sure that the data with which we live, not just the data with which our clients live, is taking advantage of everything we have to offer.

Obviously.

Except that I’m a consultant. Sure I’m a consultant specialising in the Microsoft Data Platform & Analytics space, but my main priority is looking after the best interest of our customers. Ultimately, that’s what they pay us for – it’s just convenient when it overlaps with our expertise in the Microsoft ecosystem.

Certainly I don’t like to tell my clients to pay for things they shouldn’t be paying for, and that includes my time in building a solution when there’s an off-the-shelf solution.

At home, there are all kinds of data that I’m interested in. I’m interested in my financial transactions, in the car usage, and more. And that’s before I even consider the things that affect the company, which obviously I need to track.

But I have applications for those things. My bank keep track of the financial transactions, and I use Xero for the business. We use Harvest for timesheets, and I track my car usage with an app on my phone. And most of these things provide the analytics that I’m after too. I can look for changes in my spending, or in how project time is tracking against budget, and generally manage my data WITHOUT having to spin up my own databases and creating my own applications. I do this type of work for our customers, but I also know that the best use of my time is not creating applications, but in tuning and troubleshooting, analytics and insight.

I treat my family and my company (and myself) like my customers, and don’t reinvent the wheel for no reason. If there’s a product out there that already does what I need it to do, I’m not going to roll my own. It’s just not in their best interest.

@rob_farley

T-SQL Tuesday – the confidence ‘cookie jar’

This month, Shane O’Neill (@SOZDBA) challenges us to write about the “cookie jar” that we reach into to re-energise, to give us back confidence when we’re feeling empty of it. He says we all have times of success that we look back upon to help convince ourselves that we can get through our current difficulties. Past achievements that signal that we can still achieve today.

Hi, my name is Rob Farley, and I’m not that person.

For me, this isn’t how life works. I don’t look back at times that I’ve been successful and therefore assume that things will be okay going forward. I’ve had enough experience to be confident that my past successes are no guarantee of anything in the future. I remember a time in my life when job interviews seemed to go well – I felt quite hireable – and yet not so long afterwards I couldn’t seem to get through an interview at all. If I went in on strength from past experiences, that wasn’t enough for a while, and my experiences then changed and became a negative influence on my thought-patterns.

From a technical perspective, I’ve had to learn that the ways that things were done in the past are no indication of how they will happen in the future, and again I find myself looking at past experiences and realising they have no bearing. Just because I knew something about a particularly technology some years ago doesn’t mean that I’ll have the faintest clue next time that area comes up, because I could just as likely find that my past patterns demonstrate that I’m now simply archaic.

Luckily, this isn’t how I work either.

I don’t look back at achievements and base my opinion of the future on that. Nor do I look at other people’s achievements and base my opinion of their future on that. I’d rather look at myself, or at the qualities of other people, to make those judgement calls.

If I put my confident hat on, I know that I’m good. I’m not overly comfortable saying that. I’m naturally self-deprecating, and concerned about the self-worth of other people, and find that I have to figuratively put on a different hat to make those kinds of statements. It feels like it’s bordering on arrogance, except that arrogance implies that I’m devaluing those around me. Really it’s just self-assurance. Knowing that I will be able to beat whatever the thing is that I’m going through. And I don’t feel always feel it, I just have to decide that I’m going to get up in the morning anyway.

I know I’m good in that I can solve whatever technical thing I’m given. I’ve always had that ability. Sometimes solving something might need me to ask others for help, but I’m okay with that. Sometimes solving something may be the decision to not do various things, maybe finding a workaround instead. But I’m not afraid of technical things.

I’m not afraid of new experiences. I did stand-up comedy a few years ago, and it was good. I don’t mean that I was funny – I mean that the experience was generally positive. Public speaking is nerve-racking, but if the alternative is to not do it, then I’d be worse off.

I am afraid of hurting people, and will choose to avoid those things that I think will tread on people, or subject people to unnecessary risk. In that regard, maybe I’m not much of a risk-taker. I can step out over an edge, but will hold back if the person with me doesn’t want to go for that journey.

I have no problem looking at the past and seeing things that I’ve achieved, but I don’t value those things as much as the achievements of other people. I’m immensely proud of the people I’ve been able to help along their journey, but I don’t look back at particular presentations or blog posts or projects with a sense of pride – that’s reserved for the things that make other people smile.

For my own “cookie jar”, I stand on who I am, on who God says I am, and figure that I can get through somehow. Not because I have done in the past, but because I will in the future.

@rob_farley

Why we do what we do

This month, Andy Leonard (@andyleonard) asks why do we do what do. He talks about the fact that money is rarely the only reason people become entrepreneurs, and challenges us to to explain what makes us get up in the morning.

Money is definitely not the driving force in my life. I do feel a burden of providing for my family, and don’t feel like money has ever really been a thing going spare, but I also see money as a tool to bless others rather than as something to hold onto.

For me, I created LobsterPot Solutions to help people. It’s a vehicle so I can serve people in whatever ways that I can – typically through consulting about their data, be it strategy, analytics, intelligence, data integrity, whatever. I feel like people could always do what they do better if only they had better data, if they didn’t have to worry about it, or if they could ask questions of it… all stuff that I can help with. And I have employees because it increases the reach I can have. It means we can help more people.

Of course, the company does have to be financially viable. And the employees need to be looked after in whatever ways they need. This takes money. But all of it is to provide a service to people, to help enable those people through data. Our customers do good things, whether they’re in government agencies, or health care, or charities, or mining, or whatever it is – and they’re trying to do the best they can do. So if we can help them make that best a little better, then we’re doing something right.

That’s my why.

@rob_farley

Automation and monitoring – can’t have one without the other

Automation is totally necessary. Otherwise, you’ll be going slightly mad over time, and you’re stuck in your job. I’m not even sure you can afford to take holidays if the stuff you do isn’t automated. Maybe you can train someone else to do it while you’re away, and over those two weeks they’ll wonder why you haven’t automated and then they’ll automate it for you. At that point you’re really in trouble. If someone’s going to automate what you do, that someone should be you.

One reason why people seem reluctant to automate things is about wanting to be sure that the task was done correctly each time. It’s like they don’t trust that something ran successfully unless it’s been eyeballed by someone.

So you automate that too! Otherwise you’ve just changed your job from doing the thing to checking the thing.

And to check that your checking is still working right… well you get the picture. At some point you need to have something eyeballed, but you can roll more and more checks into the one spot, and you can eyeball a report that says “Yup, all 1523 things worked today, and 0 of them failed”. Then go and have a larger impact in your world. You’ve already done those 1523 checks today.

Garry Bargsley asks us what we’re proud that we’ve automated and what our go-to tech for automation is.

Well, these days that tech is PowerShell.

PowerShell because you can do so much with it.

PowerShell because you can schedule it in a variety of ways, including SQL Agent and WIndows Task Scheduler.

PowerShell because it’s so easy to handle errors, using trap or try/catch, which helps with that monitoring.

In fact, it’s been PowerShell for a lot of years now.

It doesn’t matter whether I’m kicking off process for ETL, or running some executable, or some T-SQL, or what. PowerShell just handles it. I can use it to loop through datasets, or work in batches. I can manipulate files and use Azure just as easily as anything else, and it’s all straightforward, and easy enough to teach people about.

Sure, I remember my Unix days, and the power that scripting gave you, but I feel like that was so hard to read, so very obfuscated, compared to the PowerShell. If you’re new to PowerShell and it seems complicated – trust me it’s way simpler than handling everything as text. Go ahead and automate things you do!

But again, you need to monitor it all,. You need to be pushing Success and Failure statuses back to some central repository and report on them. It doesn’t matter how you’re automating your stuff, you need to report on it. Otherwise, someone’s going to ask why it’s not working, and you don’t want to be in the dark.

Oh, and my favourite thing that I’ve automated was probably a bunch of Azure ML stuff, because it was so simple and delivered really meaningful stuff to my customer.

@rob_farley

People I influence

This month’s writing task is to describe a time when we influenced someone. Thanks to Jason Brimhall (@sqlrnnr) for hosting. I should’ve got this post completed before now – it’s already nearly an hour past the end of T-SQL Tuesday in the GMT time zone – first time I’ve missed the deadline, but I think grace will hold out until Tuesday is over everywhere in the world.

I’d like to think I influence a lot of people – I’m a community leader (hosted the local SQL UG since 2005) and long-time MVP – but I’m also not comfortable thinking about whether I have influence or not. I can think of people that I hope I influence, but I also wouldn’t want to suggest anyone, just in case I’m wrong!

But there is definitely one group of people that I think I influence, and that is my employees. Over the ten years I’ve had the company there is quite a list of people that I’ve employed. There have been as many as seven in the company at one time, and while we do have turnover, there aren’t too many more than seven that have been with me at some point.

Now, sure I pay these people a salary, so they have to adhere to the company culture to a large degree, but I hire people to increase the reach that I can have, and serve more customers. To that end, it’s important that I influence my employees to be the best they can be in that service, making sure that the company is both viable and effective, leaving a positive impact on the data stories of our customers.

Every one of my current employees, and the vast majority of my former employees, are conference presenters and involved actively in the community. For a while now, I’ve thought that I would rather employ MVPs than be one myself (although I hope this doesn’t actually cause me to lose my status), and I have often looked for opportunity to get my team in front of people. At SQLSaturday Perth a few months ago, all four of us presented (by far the most). At Difinity in 2017, no company – not event Microsoft – had more presenters than us. Recently, my former employee Heidi Hasting did amazingly well at Speaker Idol at the PASS Summit, although presenting is something she’s only got into since leaving the company.

Needless to say, I’m tremendously proud of my employees. They’re all brilliant.

Hopefully I’ve been an influence in their brilliance, although I suspect they were there already.

@rob_farley

Non-SQL technologies

This month, Mala Mahadevan (@sqlmal) challenges us to write about technology to learn that isn’t SQL. Interesting topic – I feel like there are quite a number of technologies that I feel I know outside the SQL space, but I had to give some thought to what I should encourage people to learn.

PowerShell was an obvious one – arguably, any command-line scripting. The number of times that people need to do things with files, or have some automated process, or need to do some old-fashioned grepping, but don’t feel confident enough to spin up a PowerShell window… it beggars belief. It’s incredibly useful and anyone technical should be across it. General data folk probably don’t need to jump right into creating their own cmdlets, but they should definitely know about ripping into files, looping, all that.

To help get you started in that, I wrote something a bit over a year ago at https://sqlperformance.com/2017/09/sql-performance/need-to-know-powershell 

The other technology I can’t go past is Excel.

Excel because it’s the data environment that regular business people know, and you should know it at least as well as them.

They’ll want you to give them copies of data in Excel (tip: ask them what they will do with that data once you’ve given it to them!), they will create charts with Excel, they will give you data in Excel (another tip: learn about MDS and let them use Excel to maintain data stored within MDS), and they will implement their business logic there. Essentially, it’s where they do Data Analytics. Make sure you can read their Excel expressions / formulae, and the VBA code they write, and understand pivot tables, and so on. It will become a useful tool for you as well, because you can generate Excel spreadsheets through SSRS now (so long as you understand what SSRS will do around merged cells if you haven’t taken care).

And Excel can lead you onto teaching them about Power Pivot, Power BI, and Analysis Services. Your Excel experts can become expert data modellers, and you can be raising the next generation of data scientists.

So those are my two suggestions for non-SQL technologies – PowerShell and Excel. They’re not new, but they are set to stay significant for some time. If you don’t have them, you’re missing some serious foundations.

@rob_farley

Where projects often go wrong

I don’t write about our customers in a negative way. I just don’t. I don’t ever want a customer (or potential customer) to see what I’ve written about someone else and to worry about what I might be saying about them. So I don’t tweet about the times when something’s driving me crazy, or about code that I’m seeing – I don’t even tweet about the times that I’ve tuned a process from 15 minutes down to a second or two in case someone considers that I’ve just broadcast that they had a process that was taking 15 minutes.

But I do write for T-SQL Tuesday every month. Without fail. And this month, the topic is failed projects. Thanks to Jeff Mlakar (@jmlakar) for hosting.

And so I want to write about something I see which commonly makes projects fail, and that is:

A misaligned expectations and feedback loop

There’s a blog post I often show to customers that dates back to 2006. It’s Kathy Sierra’s post called “Don’t Make The Demo Look Too Done“, and it brings out a lot of good points.

The gist of it is that customer expectations largely depend on how finished the demo looks, because they don’t see what’s happening behind the scenes. They’re measuring the completeness of the project based on the tip of the iceberg, and not really listening to any commentary about how much work there is to do beneath the surface. I was thinking of putting a picture here of an iceberg, but I think you can imagine. You know what’s going on out-of-sight of the customer, and how the user interface is just a small part. It’s just that the customer might not, and will imagine, and either get excited about how much progress they think has been made or concerned about how little progress seems to have happened.

Kathy also brings out the fact that the depth of feedback will tend to be based around how complete the thing appears to be. If I asked for feedback on this post, you might say “You know, you really should’ve put a good picture of an iceberg in that last paragraph”. Or you’ll point out the fact that I have a mispelled word in this sentence. You probably won’t tell me whether you think Kathy’s post is a good way of making my argument, or whether I should continue to write blog posts every month. You’ll probably assume that the window for that is gone. (Of course, having said this, there are four types of comments that will almost certainly appear below – no prizes for the bleeding obvious.)

As a consultant who tends to get involved in customers’ businesses, not just their data (because honestly – there’s a reason why it’s called “Business Intelligence”), I do sometimes find myself saying “Now, please tell me if the opportunity for this level of feedback has passed…”, and then asking questions that probably belonged much earlier in the project. I’ll then see people taking deep breaths and wondering whether they should’ve either not invited me to that meeting or perhaps got me into meetings back when the opportunity for that level of feedback hadn’t passed.

Project-wise, I see that developers, whether database developers or user interface developers or whatever, tend to work based on their understanding of how things work, and customers won’t ask certain questions until they see how things actually look. Because before then, they don’t really get a sense that it’s being worked on. And then suddenly things look like they’re done, and they feel that the window for deeper feedback has passed them by. As much as I love the rich visuals available with Power BI, the speed with which data can be presented (and the same occurs even in SSRS) can often cause people to give the wrong level of feedback early on, and only later look at fundamental questions like whether the metrics that are being shown are being measured in the right way for the business.

All of this can lead to projects which are poorly estimated.

Customers will feel like things are going well if they’re seeing things that look pretty, and developers will become unstuck when they realise that they didn’t have key pieces of information early on because they hadn’t asked for feedback at the right times.

Sadly, there’s no easy solution, except to manage both sides better. Customer expectations need to be managed to make sure that they have a good picture of where things are at, and developers need to be managed to make sure that stories are being presented in ways that invite the right level of feedback. Methodologies can help, including rapid prototyping (although this should also be taken with salt because prototypes can often form the top of an iceberg and mislead – everyone should understand that prototypes are designed to be learned from and thrown away), but ultimately it’s about perspective and empathy. By understanding the difficulties faced by each side, a technical manager ought to be able to manage the expectations of both customers and developers, and advocate for the other in getting the right level of feedback needed at each point along the way.

It doesn’t surprise me that our T-SQL Tuesday host Jeff sees continuing behaviour around poor project estimation. I see it too. I’m guilty of misjudging the quality of feedback that I’ll need.

And I frequently show Kathy’s blog post to the customers and developers that I interact with, in the hope that we can all improve and get better estimations going forward.

@rob_farley

Triggers – good or bad?

Long time readers might remember my posts from years ago about implementing Table-Valued Parameters in SQL 2005, or about TVPs in SSIS, and think that I have some interesting views about triggers. I’m not sure whether Steve Jones (@way0utwest) knew this when he decided on this month’s T-SQL Tuesday topic, but either way, it’s been a long time and triggers are worth talking about.

They’re worth talking about because people misunderstand them and misuse them. And therefore they are hated.

Triggers are code that is executed when something in the database happens, such as an insert, delete, or update for DML triggers, or created, dropped, and so on for DDL triggers. The event ‘triggers’ the action, hence the name (but don’t get me started about the fact that the ‘trigger’ is not the thing that does the triggering; it’s the action).

And therein lies the problem. Someone does something which seems to be innocent, but then finds that some code has started executing, and the innocence of their tiny operation is lost. And who knows what awful things are done!? And all within the same transaction as your innocent little update…

But the problem isn’t what they do, it’s that you didn’t know it was there. Just like asbestos.

People complain about nested triggers, about triggers that are overly complex, about triggers which do too many things… but stored procedures have all these faults too. It’s just that you knew that the stored procedure was there, and you didn’t know that the trigger was there.

I wish that triggers were more visible inside the tools (Management Studio / Operations Studio / Visual Studio), right alongside stored procedures and procedural functions (those ones that use BEGIN & END and are bad, rather than inline functions). They’re code and should be treated as such.

Once you start to see triggers as code, and particularly if you use INSTEAD OF triggers rather than AFTER triggers, you can start to see them as being not dissimilar to stored procedures that have access to handy internal tables. In those two posts I linked to at the start, I show ways you can use triggers to effectively have table-valued parameters without user-defined table types. I think this is really handy and somewhat elegant, except that the code isn’t obvious. Of course you can document them, and find ways to let your DBAs know they’re there… but still they will be considered semi-visible, and be nefarious by default.

Like many features of technology, they will be used by well-meaning developers, and often the impact will be negative rather than positive. You may not be able to change the fact that they’re used without refactoring major parts of the architecture (again, like asbestos), but there are some things you can do:

1. Be pragmatic. Accept that you will come across systems that use triggers. Don’t overreact or ask that developers be fired – remember that you’ve seen worse.

2. Document them. Make sure that you don’t forget they’re there, and that those that come after you can discover them easily.

3. Learn to appreciate the power of them. I’m not saying you should start implementing them, unless you’re pushing data around using SSIS and are wishing you had TVPs, but you could look at them as being like stored procedures and appreciate that they could be an elegant solution.

4. Tune them. You tune the stored procedures in your system, and you can tune the triggers too. Every bad thing you come across is an opportunity to demonstrate your strength as a DBA.

Thanks again to Steve for hosting this month’s T-SQL Tuesday. For more on triggers, go and check out the other posts this month. I’ll leave it up to you to decide whether triggers are good or bad – I think it’ll depend on how they’ve been done.

@rob_farley