Developing a data culture when talking is harder

Developing a data culture is no easy task. Having a culture where people look to the data to understand what’s going on is incredibly useful no matter whether it’s to address big-picture questions or the minutiae of individual situations. And typically organisations have a lot more data than they expect, so it’s quite realistic to assume that there is probably data that could answer a lot of questions, if only they have a way to get the answers.

I see a number of steps involved in establishing a data culture. Some involve technical hurdles and many are more directly cultural. It’s one thing to be able to demonstrate the quality of some data, but it can be quite another to have people understand and appreciate said quality. Being able to act on data first means knowing you have it.

As consultants (here at LobsterPot Solutions) who help our customers on the journey of developing data culture, we like to work from their sites, and make ourselves available for answering questions about the data. Ultimately, we want our customers to be able to get the answers themselves. Tools like Power BI can help this become a reality. But before they reach that point, it’s good to have them ask those questions of us. Not only does it help clue us up as to the things that we need to include in data models, but it helps demonstrate that they’re thinking about data. That their data culture coming along.

This willingness to ask questions has to filter through a large portion of any organisation trying to establish a data culture. It’s great to have senior management understand the significance of data, but if the people under them aren’t also making that same shift, then there’s trouble coming. What happens if the middle-manager reports up to senior management using an incorrect version of data that the senior manager can see. Having a ‘single point of truth’ for data is very important.

While they’re still on the journey to being able to get the answers themselves, the single point of truth for data might often be the team of people who are developing the analytics system – often the team which my staff or I are helping. We’re very used to having our days consist of the technical and design work that we’re doing towards a self-service reporting environment, as well as workshops and training we’re doing to develop the culture, and also a long list of practical questions about the data. The same questions that will get answered via the models we’re creating, but which are more time-critical and get answered in other ways – queries against a model or cube or warehouse, or maybe even against the operational system (taking care to avoid any kind of impact). These questions are important. Turning these people away prevents the development of a data culture, because their curiosity is extinguished. Their appetite for the answers is dampened, not whetted. I try to encourage everyone to have access to data, even if it’s through me.

Except that I’m not always there. And in a time when an increasing number of people are needing to work from home, maybe there’s nobody physically in the office who is working on the data.

Many organisations have a culture of asking each other questions via email, Teams, Slack, or whatever. But one of the biggest impacts I have is by being physically present, so that people ask who I am, find out that I’m not scary, and start to talk about the data. A lot of the culture around asking data-related questions is knowing how to ask those questions, so being physically present helps.

Suddenly this month, it feels like everyone is working from home.

Australia has reacted quite late compared to many countries, but is now banning large non-essential gatherings, and it wouldn’t surprise me if the restrictions only becomes more severe as time goes on until a solution is found. Organisations across the world are putting out their own advice, and telling people to work from home if they can.

Having teams spread out makes all kinds of communication harder, including those questions about data that I consider so important. If I’m not physically there, are people still wondering the same questions but not getting the answers? At what point do they simply stop wondering?

For me, I think this comes down to prompting the conversation about data. Find ways to tell stories about their data, and start communicating it – emails, SSRS subscriptions, whatever works, to get information out there to pique interest. With a standing invite to ask more questions. This can be put down to educating people about the opportunity with data, or to validate assumptions (this one is particularly useful because if you’re asking someone for help with it, then they’re more likely to look deeper and start to think about the potential), or to push the data through a “Quick Insights” tool to come up with ‘Did you know…?’-style trivia. The more the data can be discussed and be interesting, the more a culture around understanding data thrive.

Pretty soon, conversation should be able to flow more easily even if you’re not there, but communication channels need to be established. Self-service is great, but ideally you can make some sort of a forum for people to share their nuggets of insight. It’s the kind of thing that someone can share with a colleague in a “Hey – check this out” context. It demonstrates excitement about what they’re discovering. It demonstrates that they understand the business that they’re in, what the organisation does, and hopefully where there’s an opportunity for improvement. When people can’t walk up to each other in the same way, an effort might be needed to make this happen – Slack conversations, Team channels, whatever works to develop and maintain social interaction. Then find ways to incorporate data nuggets into the mix.

A data culture doesn’t have to be hard to instigate. It might require some work on the data itself, including data cleansing, modelling, and tools to let people get at the insight. But the key is to let interest in the data thrive, with enthusiasm firing, and to make sure there are ways to let that continue. Find ways to encourage conversation for the sake of your team, and for the sake of the data culture, find ways to encourage conversation about the data as well.

@rob_farley

New laptop – installing less

I was a Surface Pro user for quite a while. I had a Surface Pro 2 for a few years, and had a Surface Pro 4 for about four years, until last week when the screen decided it was going to start jittering. I wasn’t happy. Still, I had a good run with it, and arguably a new laptop was well overdue.

This time I’ve gone with an actual laptop – an HP one. I know Surface Pros are still an option, but I figured this would be okay. It’s nice to have a slightly better keyboard than the ones that are on the Surface, and my Surface Pens work just fine too. My physio tells me I should try to use a mouse when I’m working from a desk (and that I should try to use a second monitor when I can), and my preferred mouse is still the Microsoft Presenter Mouse 8000 which I’ve had since maybe 2008. I often just use the touchpad instead of a mouse, so this is different – and the touchpad on my new machine is fine, as was the one on the Surface Pro 4.

But the big thing with this new laptop is that I’ve made a conscious decision about what to install on it. And particularly, what things to NOT install.

For example, I’ve only installed SQL inside docker, not directly on Windows. I’m running the Linux version just because I can.

But today I feel like I’ve compromised.

Today I’ve installed SSMS, instead of persisting with just Azure Data Studio. It only took a week for me to cave, and the reason is Query Store.

In ADS I can configure Query Store no problem, either using T-SQL or the handy Database Administration Tool Extensions.

ADSExtension

ADSExtension2

…but the thing I don’t see in ADS yet is a way of viewing the reports.

What I want to be able to do is connect to a customer’s database in Azure, and pull up the Query Store reports. I know Azure has some great information about what’s consuming resources via the portal, but I also just like using Query Store for some of this. I’ve got used to telling our clients that Query Store should be collecting data, and I like being able to jump on and have a look on those times when it seems performance has dipped. It’s great to see that the plans that have changed, see what’s consuming the resources, and all that.

And while I know that I could analyse the Query Store without SSMS using the queries found in the documentation, and even tweak them myself… I have to admit that I like using the SSMS interface. When someone puts together an ADS extension for it, I’ll try to avoid using that as a reason to open SSMS, but for now, Query Store is the reason why I’ve installed it.

If you’re not convinced about how useful Query Store is, I recommend you explore some of the other blog posts that will be appearing today, as Tracy Boggiano (@TracyBoggiano) is hosting a T-SQL Tuesday all about Query Store. I’m sure you’ll find that it’s quite remarkable and worth using regularly.

And try running SQL on docker, and try Azure Data Studio. Don’t wait until you replace your hardware to do this…

@rob_farley

Dear MVPs

The MVP site says that the “Microsoft MVP Award recognizes exceptional community leadership”. Now I don’t have much of an idea about what gets classified as ‘exceptional’, and ‘leadership’ is a whole nother matter again, and while I wince a little at the ‘zee’ in ‘recognizes’, I do feel comfortable with what we mean by ‘community’. So I’m going to write about that for a bit.

To me, the community that we MVPs are involved in caters for anyone who is interested in similar technologies to us. We’re generally involved in a number of communities – online, local in-person, the people gathered at an event that we’re at, as well as non-technical communities such as the people who live near us. Quite possibly we don’t even know many of the people in our communities but that doesn’t stop them being a community. The people that find blog posts of mine are part of a community that I continue to reach, even if that blog post is more than ten years old. The people that sit on the bus with you in the morning form a kind of community even though you may never have even spoken to them.

Community suggests we have something in common with these people – a common interest or common culture or common behaviour – and the differences start to matter somewhat less. Hopefully it doesn’t matter to the people in my user group that I’m 6’4” with glasses and a beard, because that doesn’t describe any of them. It shouldn’t matter that none of them are part of the community of parents from my daughter’s school. We celebrate what we have in common, not our differences.

In fact, it’s the differences within a community that make it stronger. If everyone in my user group were 6’4” with glasses and a beard, it would probably mean that a new attendee with 20/20 vision might feel out of place. As much as I’d say “No, please, you’re more than welcome to stay”, they would probably count the number of eyes in the room and feel like they weren’t contributing enough. If you don’t feel like you have anything in common with anyone, it’s harder to establish community, and we have a great starting place in technology – but we have to understand that the differences matter.

Diversity matters.

I’m not going to pretend I have the faintest clue about discrimination. I’m a tall, straight, white, English-speaking male. The few occasions when I’ve been different to those around me don’t even begin to compare to people who face unjust discrimination on a daily basis. I think back to being the only dad doing the school pickup, not knowing whether I was welcome in that little community of mums. Some seemed overly friendly, some made me feel like I was being defined purely by my gender, and I didn’t always appreciate the kind of attention that some of them seemed like they were interested in giving me. What I wanted was to feel accepted (normal) and to focus on the commonality of the group. I didn’t want to feel different. But this group was hardly a significant part of my life. It wasn’t a daily battle.

When I worked as a “checkout chick” at Ringwood Coles nearly thirty years ago, I was the only male checkout person there. On the late shifts, my colleagues would gather at the service desk and chat about boys and clothes and whatever else, and I definitely wasn’t made to feel welcome. They had a community. I was not part of it. But this glimpse doesn’t help me understand what it must be like long-term, in the same way that someone who’s had a slipped disc doesn’t understand exhausting chronic back pain. This wasn’t a lifelong career I was fighting my way through. I really have no stories.

I have no idea about the people that come to my blog. I figure that they’re a pretty diverse crowd. The only barrier to entry I can think of would be that it’s in English. I don’t think my words alienate any of the readers. There’s certainly no aggressive atmosphere when someone finds an article about SQL query tuning or about developing a culture around data, no matter who the reader is.

And yet when it’s a crowd of people, there are those that are put off by what’s in the room.

When a group is almost entirely female, the men might feel out of place unless there is an effort to make them feel like they’re not out of place. Like they’re normal. The same. In our male-dominated technical communities, it’s the women who will feel out of place. We know this, and hopefully we’re even used to spotting it and making an effort to be inclusive of women.

But perhaps we’re not as used to people who look noticeably different. The person who belongs to a very different culture, perhaps very obviously because of the hijab they’re wearing, or because of the colour of their skin. Perhaps it’s someone whose personality is just a bit too ‘out-there’. Perhaps it’s a transgender person, identifying as a different gender to the last time they visited the group.

The way that we make these people feel welcome is to respect who they are. To remember that our community is stronger because of the differences, but that we have similarities with these people.

The SQL Server community recently got it wrong at some events. Jennifer Jones wrote about how unwelcome she was feeling at some events and that she had decided to leave the community. Because of the way that people refused to accept her, she has opted out. None of us want this. The voices making her feel welcome needed to be stronger than those that made her feel unwelcome.

We needed to do better. And still do.

As community ‘leaders’, we need to be champions of diversity. We need to stand up against harassment. We need to make sure that people understand we will not tolerate harassment at our events. We need to make sure that we help anyone who feels like the victim of harassment. Never devalue what they’re going through. Understand that they’re going through stuff that many of us have never experienced.

I don’t care what kind of harassment it is. It could be a woman finding that she’s drawn the attention of an amorous man. It could be that someone is being insulted because of a speech impediment. We need it to stop.

We need to educate our communities. We need to make everyone feel truly welcome. That’s what exceptional leadership is.

@rob_farley

Shortcuts – good and bad

This month, Jess Pomfret (@jpomfret) challenges us to write about shortcuts, and I’m going to write about something I used to do back in the late 1980s in French class when I still lived in England.

One of the worst things about French was verb conjugation. If you said “We do something” as opposed to “They do something” or “You do something”, then it’s not enough to just use the word “Nous” for “we”, you need to conjugate the verb (typically to –ons, like “Nous mangeons” for “We eat”). It was annoying to have to think about, and at that stage in my life (feeling a little unenthusiastic about an upcoming move to Australia, and big on shortcuts) I was happy to learn about an alternative.

The alternative was to use “On” instead of “Nous”. A quick internet search shows https://www.thoughtco.com/the-many-meanings-of-the-french-subject-pronoun-on-3572148 and that it’s valid to say “Olivier et moi, on est contents” (Olivier and me, we are happy), where “on” is swapped in for “nous”, and (the important bit) conjugates the same as the singular third person. It’s not “nous sommes contents” (we are happy), it’s “on est contents” (we are happy). I was definitely happy when I worked this out, and (almost as a bonus) my teachers would be a little frustrated. It was part of my mission to try to get an ‘A’ but with unsatisfactory effort – a trait I hope my kids didn’t inherit and which I promise I grew out of. I managed to avoid heaps of conjugation effort, even if I may have done myself a disservice in the process.

The other day I was watching a sci-fi show (maybe Picard) and I got to wondering whether gender-related pronouns would still be a thing, or whether English will evolve them out over the next few hundred years. I remember in C. S. Lewis’ books about Narnia, when asked what kind of creature she is, Lucy replies that she’s a girl. I see that the first chapter of the Bible says “male and female he created them”, and I consider this to imply that God created *them*, and the fact that Adam was male and Eve was female didn’t suggest that they weren’t fully equal. Eve was no less of a person than Adam – Adam was simply male, while Eve was female. I would almost rather Lucy had identified herself as a human, but maybe she was describing herself in comparison to her siblings. One of the girls as opposed to one of the boys.

I say all this because ‘on’ seems very much like a gender-neutral pronoun. It’s even number-neutral and person-neutral. It seems ideal. When we don’t know someone’s gender, we refer to them as ‘they/them’, and only adopt a gender-specific pronoun once we know someone’s gender. If we could stick to my French lesson’s ‘on’ that could work nicely, but even then I feel like using an ambiguous pronoun (another way which ‘on’ is used) is tricky.

Not everyone wants to be referred to by a gender-neutral pronoun. Narnia’s Lucy was proud to be a girl, and plenty of other people are also proud to be their particular gender. Others prefer to be known as ‘they/them’, and I hope they’re proud to identify that way too, but few want to be assumed to be the gender that they’re not, or identified as ‘gender-unknown’ once they’ve been identified. If I refer to a male person as a non-gender pronoun, am I suggesting that they’re not masculine?

To me it must be that person’s choice. At least until society drops gender-specific pronouns completely.

And pronouns should never be used as a weapon.

If I *refuse* to use the correct pronoun for someone, I’m insulting them. No question here. It’s antagonistic. It’s harassment. It’s hate-speech. It’s suggesting that they’re not exhibiting the gender that they’re trying to, and that’s insulting. Sticks and stones hurt far less than names, and this is just as bad.

If it’s done accidentally, then it should be corrected and the person who made the mistake should try harder. I’ve done this. I apologised, and still managed to do it again. But I wasn’t trying to hurt them. They know this, and I’ve talked to them about it. It takes practice, but it’s about the sentiment.

What’s not acceptable is what Jennifer Jones wrote about here. Deliberately referring to a woman as a guy, refusing to be corrected, and making someone feel like there is no one is in their corner is all bad behaviour. No one should have to put up with that. Jenn says she’s not planning to attend any more SQL Saturday events, and I feel like we’ve failed her as a community.

So despite the fact that I used a French-pronoun shortcut to make life easier for myself as a kid, I now think that this is a shortcut to be avoided. I’ll use a gender-neutral pronoun if I don’t know someone’s gender, and will probably continue to make assumptions, but if I’m corrected that someone prefers a different (including gender-neutral) one, I will try to do better.

We should all try to do better.

@rob_farley

Imposter Syndrome

It’s a funny thing, Imposter Syndrome. To say you have Imposter Syndrome means pointing out successes, which only exacerbates it, right?

I think the point is not that sufferers deny the success, but that they don’t feel they deserve any accolades. I suspect they thought they’d feel different by the time they’d achieved things, and because they don’t feel like they have their ducks in a row, because they don’t feel like they’ve figured out how to do life, they feel like a fake. They thought they would have eradicated that feeling of doubt about just about everything, and they haven’t.

I know from my own life that achieving things doesn’t mean I have a ‘clue’ about life. I know that I’m still trying to find my way, and that I look at other people and assume they have more of that clue about life.

When I get to speak at some event, I still compare myself to the other speakers and feel like they know what they’re doing more than I do.

When someone refers to me as an expert, I still feel very aware of the vast amount that I don’t know. Every day brings new challenges to show me I don’t have all the answers.

If someone thanks me for some help I’ve given them, I worry that it might not have been quite as good as it should’ve been.

Imposter Syndrome is a very real thing. It’s not about self-deprecation, or having a lack of self-worth, or denial of who we ‘really’ are. It’s simply that on the inside, we feel the same as we did before we’d achieved anything. Recipients of the MVP award consistently say that it’s “humbling”. We look at other recipients and see their strength, and assume that they have life figured out. We remember feeling like life would be easier afterwards, and then discovered that things were still hard. So we felt like we didn’t belong. Imposter Syndrome set in. And every time we get renewed we continue to feel the same, and that we’re not as good as all the other recipients. It’s humbling.

Imposter Syndrome is so commonplace that it’s the topic of this month’s T-SQL Tuesday (hosted by Jon Shaulis – thanks Jon!). Lots of people will be writing about various aspects of it. For me, as a consultant, I have to have enough confidence to lead my customers and my employees, and be sure that I can help them. Imposter Syndrome conflicts with what I have to do in my career. I don’t feel like I’m any more special than the next person, but I need to be able to walk into a customer’s office as an expert.

And so I’ve practised minimising the impact of Imposter Syndrome for a long time, and have learned what I think works for me.

The secret is that I’m not there for me. I’m there for you.

Life is about serving. I’m a consultant so I can help my customers do better.

I know there are ways I can help people. It might be as simple as listening to them talk things out. It might be offering an opinion if they want it. It might involve tuning their databases, or looking at the quality of their data, or demonstrating its business value, but however I’m able to help, if I focus on the needs of the customer and less on myself, then Imposter Syndrome has less power.

Someone else might be able to do a better job (and if I wasn’t actively fighting against it, this kind of thinking could really hold me back here), but I can still help a bit. I shouldn’t stop myself from helping just because someone else might do better. No matter who it is – be it an audience member at a conference, a customer, or whoever, I’ll try to think about the connection that I want to make with them, how I want to make them feel different, how I can help them do better. I’ll distract myself away from me.

I’m not thinking about me. I’m thinking about them.

Shifting the focus means that I don’t have to think about whether I’m an imposter or not. I can just get on with dealing with them.

I still get uncomfortable and feel humbled when someone refers to me as an expert or gives me an award. Because that’s when the focus has been put on me for a moment, and I to want be focused on someone else.

I don’t want Imposter Syndrome to stop me from helping someone else with what they need.

I don’t want it to stop you either.

@rob_farley

Positives from 2019

As I reflect on 2019 from a tech-community perspective, I see a few things of special note. Some I didn’t necessarily see coming, but certainly things that made me smile.

A few years ago now, Denny Cherry started the Speaker Idol events at the PASS Summit. It wasn’t a new concept – Richard Campbell asked me to be part of a Speaker Idol event at TechEd North America 2009 (I wasn’t eligible though, as I’d already spoken at a few TechEd Australia events, and suggested they ask a different Adelaidean, who won the whole thing!), and it had already been going for a while before that. So I knew that this was a great pathway into speaking, and I happily encouraged people towards it and coached anyone who asked. Last year that included my friend and former employee Heidi Hasting, who reached the final, and 2019 has seen her presenting career really flourish, and she has spoken at numerous SQL Saturday events, Difinity, and even the PASS Summit. I’m stoked! And at PASS’ Speaker Idol this year, my friend Deborah Melkin won the whole thing! I had the pleasure of spending time with Deb at the PASS Summit a few years ago, and we have talked a lot about presenting since then. It’s brilliant to see her step up. There’s a whole generation coming through, and it definitely feels like a gift to me to see it happening.

Of course one of the biggest names to come through in the data community in recent years is Hamish Watson. We’ve been good friends for a while now, and I love this guy to bits. Seeing his journey has been an honour, as his heart is reflected by the work he does for those around him. He has no selfish ambition. He’s entirely about seeing other people become stronger, and this is demonstrated in the work he has done in the Muslim community after the Christchurch attacks earlier in the year, and in his mission to MakeStuffGo. I was one of several people who nominated him for the PASSion award, and it was very cool to see him win it. His getting that award was definitely a highlight for me.

On a personal note, my journey has been a little more interesting this year, with a few notable differences. I had delivered my first keynote at the first Difinity conference, back in 2017, and done another keynote in 2018 in Perth. In 2019 I managed to get a spot doing a paid keynote, at the SMBiT national conference, which I thoroughly enjoyed. I’d like to do more keynote speaking – there’s something about inspirational speaking that’s just amazing, and if I can get paid gigs to do that, it’s obviously so much easier. I also got to speak at SQLDay in Poland and at Ignite US (my first American TechEd/Ignite), which both gave me the chance to see old friends and get to know people from the community that I now consider friends. So this felt like a gift too.

There are other things I could talk about. I could talk about my health journey this year and the positives I can take from it. I could talk about the LobsterPot Solutions story and how it’s developed this year and how Kelly is shining in her leadership role while still providing technical leadership to customers. I could talk about my amazing kids and what they’ve achieved…

I just love seeing people do better. Even though I’m not always the best person to help them, it’s been good to see people develop this past year, and not just the ones I’ve listed here, but others too. It makes me look forward to what 2020 might hold, and what we can all achieve.

(Thanks to Mala for hosting this month’s T-SQL Tuesday)

What were you thinking, Rob?

The writing challenge this month is from fellow MCM Wayne Sheffield (@DBAWayne), and is remarkably special, as it marks ten years since T-SQL Tuesday started. He asks us to talk about a time when we’ve wondered what someone was thinking when we’ve read their code.

…except that I’m not a fan of writing about other people’s code, so I’m going to write about some times that I’ve read my own code and had that same thought. In other words, I’m going to write about my documentation style.

You see, whether it’s me reading back some code that I’ve written or whether it’s someone else reading some code that I’ve written, the main thing that I need to communicate is what I was thinking. Whereas a lot of the documentation I see is more about what I’m doing – which should hopefully be clear to anyone who can understand code.

I see comments like this:

And I want to see a comment like:

I get that it’s more text, but I need to know the WHY, not the WHAT. It means I’m less likely to describe what the query actually does, but more likely to describe why I’ve chosen a more controversial construct.

This definitely applies if I’m grouping by a column that isn’t in the SELECT clause, providing an inverted predicate, doing a double OUTER APPLY (SELECT TOP (1)…), or even providing a table or join hint. I might hope I know what I’m doing, but I also want to make sure that someone else (maybe even future me) has the faintest clue too.

Thanks for hosting, Wayne.

@rob_farley

T-SQL Tuesday 119 – Changing your mind

First let me say that Alex Yates’ blog has an excellent title / sentiment: “Working with devs – databases should not be bottlenecks”. It’s close to my heart for a number of reasons, and not least because I used to be very much a developer and now I help solve database bottlenecks. I was in primary school when I learned about conditions and loops and modules. I dabbled at home with BASIC, got into the Unix space at university, and spent a number of years writing code in various versions of VB and then .Net languages. Throughout this time, I’ve had my mind changed a number of times, so Alex’s invitation for T-SQL Tuesday this month is good.

He wants us to write about a time when our mind has changed on something, and although I can think of plenty of times in my life when I’ve learned more about a situation and found that my earlier opinion was lacking somewhat, I’m going to focus on a pretty significant set of changes. Oh, and it’s worth pointing out that these days I hope I’ve learned that my opinions are shaped by my own limited understanding, and that there’s therefore every chance that I’m wrong about all kinds of stuff. On top of that, other people have their own opinions, shaped by their own limited understanding. So whenever I find myself disagreeing with someone, I try to remember that each of us is probably forming the most sensible opinion based on what we know. Most things aren’t worth fighting over.

The situation I’m going to describe is about databases – funnily enough.

Like Alex, I often work with developers to help them with the database they’re using. Developers typically have skills that I no longer try to maintain, and I have skills with data. And it’s good that we have different skill sets, because I don’t want to have to be a .Net (or Java or web) developer any more.

There was a time when I wanted to know everything about every type of coding. At university I enjoyed the subject Programming Paradigms, in which we had to learn about different approaches to coding, including functional programming and logic programming. I remember an assignment that had to be done in Prolog, solving puzzles where each letter represented a number and coming up with heuristics so that it didn’t just use brute force. It was eye-opening to realise that by adopting a different approach, you could achieve much better results.

Wind the clock forward to my early consulting days, and I was discovering that programming in the real world involved languages that were evolving fast. Whether it was VB3 through to VB6 and then the .Net framework, or PL/SQL with Oracle Web Services, programming was adopting generics and model-based architectures, and I was wanting to keep up, as well as moving up in management.

And then I changed my mind. About all kinds of things.

Firstly, I realised that I wasn’t going to be able to keep up with everything. But I could keep up with a subset. I changed my mind and decided not to try to keep up with the .Net world.

Secondly, I realised that I didn’t have to move up in management to grow my career. Leadership doesn’t mean management, and I was more interesting in leading. I changed my mind about pursuing roles in management, and focused on serving, increasing my influence and establishing my profile.

Thirdly, I realised that data was what mattered. Applications could come and go, but the database had to be strong. I changed my mind about doing application development and consciously moved towards the database. I’d also been strong with data – but I changed my mind about where my technical focus could be. I think my first community presentation wasn’t about data at all. I’m pretty sure it was about asynchronous calls from web browsers – AJAX stuff. I only started presenting about data later, after I’d had the change of mind.

These changes were the things that led me to involvement in the SQL community, and to setting up a company that lets me hire other people who are passionate about providing consulting services in data. Before then, I’d been moving through a career progression that was essentially fine, but might not have left me doing things I enjoy.

I’m not saying that everyone should jump into data. While I consider it to be very significant, there are plenty of things that are more significant – if you can cure diseases then please go and do that. But don’t be afraid of changing your mind about things. Understand that the path that you’re on may not be in the direction you want. Make choices. Understand others. And change your mind when you realise you need to.

@rob_farley

The SQL feature I’m still waiting for

This month Kevin Chant (@kevchant) challenges us to write about our fantasy SQL feature. And so I’m going to reiterate something that I had listed as a Connect item many years ago. It got quite a lot of upvotes, but was never picked up.

That feature was about making more predicates SARGable, by recognising when helper predicates could be leveraged to help performance. I wrote about it at http://blogs.lobsterpot.com.au/2010/01/22/sargable-functions-in-sql-server/, and the Connect item can be seen on the ‘wayback machine’ here.

The idea of this is to address the problem that people have when they write a predicate involving two date columns like “WHERE DATEDIFF(day, s.SomeDate, o.OtherDate) > 3”. This means that the number of days between the values in those two date columns must be more than 3, but could also be written as “WHERE s.SomeDate < DATEADD(day, -3, o.OtherDate)” or “WHERE o.OtherDate > DATEADD(day, 3, s.SomeDate)”. I appreciate that if you’re considering columns that also involve a time component then these aren’t exactly equivalent (in the first I’d need to convert o.OtherDate to a date type first, in the second I’d need to convert s.SomeDate to a date type, use 4 instead of 3, and make it >=), but I’m thinking about this from an index usage scenario.

If we have an index on s.SomeDate and we know o.OtherDate before the join is performed, then we might get a nice Index Seek operation to quickly find the rows in table ‘s’ that have SomeDate earlier than 3 days prior to o.OtherDate, but only if we’re using the first of those alternatives that I mentioned. If we know s.SomeDate and have an index on o.OtherDate, we might get a seek if we using the second alternative.

One of the query-tuning tricks I do is to introduce extra predicates to help indexes be used better. If I see a predicate that isn’t SARGable, I can often introduce helper predicates myself by adding them to the query. If I had all three of these predicates in my query, I haven’t changed the logic at all (again, assuming I understand the data types), but might have given the SQL Query Optimizer enough to do a better job of running this query.

And these helper predicates I add don’t have to be exact – they might just reduce the range. For example, even if I just filtered s.SomeDate < o.OtherDate, it might help significantly. If I can’t figure out an exact-enough predicate, well that’s not a problem, because I still have the original predicate. So long as I’m not filtering out more than the original one, then my logic is okay.

I just think that Microsoft could build some of this into the product.

@rob_farley

A quick tuning win with Memory-Optimized Tables

I hate writing ‘optimised’ with a ‘z’, but as a feature, I’m okay with writing about Memory-Optimized Tables as a way of optimising your SQL environment.

Let me start by saying that if you really want to get the most out of this feature, you will dive deep into questions like durability and natively-compiled stored procedures, which can really make your database fly if the conditions are right. Arguably, any process you’re doing (such as ETL) where the data doesn’t have to survive a system restart should be considered for Memory-Optimized Tables with durability set to SCHEMA_ONLY (I say ‘considered’ because the answer isn’t always obvious – at the moment inserting into memory-optimised tables won’t run in parallel, and this could be a show-stopper for you).

But today I’m going to mention one of the quick-wins available: Table Variables that use User-defined Table Types

User-defined table types are table definitions that you’ve created for the purpose of table variables that you’re going to pass between stored procedures. You can use them for any table variables, but typically I find user-defined table types are only used if the table variable is being used as a parameter somewhere. Still, this is remarkably often these days, for lists of things. Typically small lists, where operations are quick, and the need for parallelism is less.

For example, a list of Products:

, which is then used like:

The key thing here is that these tables already don’t survive a restart. They’re only scoped to the session, because they’re variables. In fact, a common misconception about table variables is that they’re automatically in memory only, but that’s simply not true.

…so why not change your type definition and make it that way? It’s really easy…

First, if you’re on-prem you need a filegroup and file that supports Memory-Optimized tables.

This creates a folder (not a file; despite what the command looks like, it’s actually a folder) for it to use. It’s just a thing that’s needed. You don’t need to do this on Azure SQL DB.

Then you simply have to change your definition. You’ll need to add an index, and use the WITH option.

Frustratingly you’ll need to script any objects that use your type, because you can’t drop the type until you’ve dropped any objects that refer to it – but this change should be going through source control and proper DevOps practices and testing anyway, in case it turns out that performance doesn’t improve.

Make sure you’re patched to CU3 of SQL 2016 SP1 to allow the table variable to be scanned in parallel to reduce the risk things slow down at all (chances are you’re already seeing serial inserts on your table variables, but you don’t need them when reading back), and CU7 to avoid some other annoying errors.

But it’s really as simple as that. For systems where data is frequently being pushed between procedures using TVPs, this is a low-risk change that can often have a noticeable impact.

@rob_farley

(Thanks to Steve Jones for hosting this month!)