Voice of the DBA

Author: way0utwest

  • STRING_SPLIT Has Ordinals

    I noticed the docs for STRING_SPLIT now show that there is a third parameter. This brings us an ordinal for the positioning of the data. The format is now:

    STRING_SPLIT( string, separator [, enable_ordinal])

    This is a bit or int that determines if a positional value is added to the result set. Here’s a quick demo.

    No third parameter shows this:

    2021-11-16 13_32_11-SQLQuery2.sql - dkranchapps.database.windows.net.SimpleTalk (sjones (89))_ - Mic

    If I add a 1 for the third parameter, I see this:

    2021-11-16 13_32_37-SQLQuery2.sql - dkranchapps.database.windows.net.SimpleTalk (sjones (89))_ - Mic

    Right now this is only in Azure. I assume we’ll see this in SQL Server 2022, but we’ll have to wait and see.

  • The API Bottleneck

    While talking with a client recently about their performance challenges, I was relieved to find that the database wasn’t the problem. Instead, their API server was overloaded by the number of calls taking place in their application. While the database did provide the backing for the API calls, there was a fair amount of caching. However, as they’d moved to microservices, more and more of the interaction between modules was taking place as a network call to a single server, which became overloaded.

    Certainly, they could add more API servers, or break out different types of API servers, but each of these changes potentially has downsides. Data can become out of sync across two systems, which is why we often want a single database storing all data. Distributed systems are hard to architect and implement, and I suspect as more developers try to build them, we’ll find more issues like this. Not that developers are doing anything wrong, but they often don’t realize the challenges of building a distributed system when the workload scales.

    They’re relearning the knowledge that others have gained through their own experiments and failures.

    One of the things we try to do here at SQL Server Central is share our knowledge with others. We hope to help each of you learn from others, and hopefully not need to make the same mistakes in your applications.

    As I listened to the client talk, I was reminded of this post from Troy Hunt. In it he talks about the HaveIBeenPwned site, which he built a scaled to meet demands. The site doesn’t cost a lot, but there is a great quote about the API server he set up. He wrote: “I’ve tested it with 380k queries a minute! Infrastructure will scale out and magic will happen, so long as you’re not maxing it out for perpetuity, just go for it.”

    However, in your company, you might be scaling in perpetuity in an app, especially as a workload grows and more parts of your application need to make queries against an API. Good design, good coding, and adhering to good distributed system design, will help ensure you can handle the load. This is especially true in the cloud, where scaling up is just provisioning another resource, but there is a cost.

    You have to know what to do, so I urge anyone looking to build a distributed system to read about the experiences of others. Learn from their knowledge, rather than make your own mistakes and try to correct them. There is a lot of great information out there, but you need to be looking for resources that provide technical details, like Troy’s posts on how he built his site or Jeff Moden’s posts on writing high performing queries. That’s the type of knowledge that is invaluable if you want to be an efficient and effective developer.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • Daily Coping 17 Nov 2021

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

    Today’s tip is to do something playful outdoors – walk, run explore, or relax.

    Usually today would be a little hectic with work and coaching at night. However, no coaching tonight, so I’m going to take advantage of the time.

    It’s dark early with the time change, so I don’t want to be outside late, but it’s work from home, pandemic style. I’m going to knock off at 2pm and go for a hike outside, getting a little fresh air. It may be cold, but I’ll still enjoy November in Colorado. I’ll pick up a little extra work when I get home, and it’s dark.

  • Summit Reflections

    This is the second year that the annual PASS Data Community Summit has been virtual. Last year, as a pandemic gripped the world, I thought we might never see another one. I’m glad we have one this year, and I look forward to going back to a live, in-person Summit in 2022. That being said, this year’s event has been better than I expected in many ways, mostly because of spatial.chat.

    I can remember walking through the hallway of the Washington State Convention center and seeing Taiob Ali coming the other way. We stepped to the side of the hallway and had a chat among hundreds of people walking by. I thought about that in the Community Zone when he and I, with a couple others, were talking with each other in a group, away from others that were also in the room. We could see their avatars, but we were far enough away in the virtual space that our conversation didn’t upset others.

    At early Summits in Washington, there were couches in the Convention Center. Andy Warren and I often planted ourselves for hours on a couch, chatting with each other and those that came by. People would walk up and spend some time, others would drift away, but we had a conversation ebbing from work, life, Microsoft, family, hobbies, and more. We’d laugh and joke among ourselves, bonding and sharing, inspiring, and encouraging each other. I had that experience a few times in the Community Zone last week, as I stopped in various rooms, sometimes gathering in a crowd, sometimes (virtually) walking away to chat with just a friend or two.

    When I presented at the Summit, I often felt pressured, rushed, and excited by interacting with people. Getting questions and needing to go off script and explain something was a challenge and a joy. When the Summit opened this Wednesday, I had one of the first Q&A sessions. Most people hadn’t watched my recording, so I started to deliver the talk, but quickly realized I didn’t have time. I saw a few questions come in the chat, feeling pressure to get to them and quickly answer them. I had no idea how many I’d get, so I did my best to share my screen, show examples, talk through some of the concepts. It was scary and invigorating. It reminded me of the times I’d get a few questions, realize I wasn’t quite explaining things well enough, and then feeling pressure to deviate from what I’d rehearsed to help clarify the idea.

    Most years I’d have early mornings and late nights. I’ve be buzzing from my interactions and physically exhausted. As I logged off from holding open the Community Zone Wednesday night, I felt the same way. Getting going Thursday to host the SQL Saturday room for a long time and trying to watch other sessions was tough. Friday harder. Exactly like I’d feel many years at the physical event. I needed a break. I’ve enjoyed walks around the grounds of the Gaylord, or in the park behind the Washington State Convention Center; at home I go outside and see the horses. Rather than a quiet dinner with a friend (or even solo), I could go cook something and relax with my wife and son here. Both different shades of the same experiences.

    I am tired of virtual events. For me, they often lack the excitement and interest I get when I’m in a physical space. I wandered into different sessions, listened in on conversations of others, and enjoyed the interactions of being able to see and chat with others. I escaped at times to get away from the hectic environment, embraced it at others. Unlike my experience two weeks ago at Ignite, I felt like I was at an event.

    That being said, I’m looking forward to more live events in the next year. I want to engage with actual people and remember the wonderful emotions of being in the same room. I’ll miss those that don’t feel comfortable, I respect your choice, and I hope we can continue to find ways to touch each others’ lives. I’m also grateful that some of you are willing to come back to physical events and enjoy the #sqlfamily.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.