Voice of the DBA

Author: way0utwest

  • Break Problems Down

    Recently I was pouring over a few questions in the forums and noticed something that I would think is obvious, but apparently it isn’t so for other people. A poster was attempting to run a query across multiple databases and needed help structuring code to work in the context of each database. The poster was going back and forth a few times with others that were attempting to help them, struggling to get suggestions implemented into their code. That’s typical, as someone that needs an answer often doesn’t understand the solution and is learning. In this case, I found that the original poster was losing some of their troubleshooting skills in the process.

    Years ago I read a piece on Simple Talk from Kathi Kellenberger. She is now the current editor of the site, but at that time she was just a SQL Server MVP and expert that was looking to help others learn more. The article was called Solving Complex T-SQL Problems, Step by Step, and it stuck with me. There wasn’t any knowledge that I didn’t know, but it was an organized explanation of the Kathi’s steps that I found refreshing. This was a nice reminder to me to not move too fast, but ensure that I really understand what the problem is and work in stages.

    I’ve certainly been guilty at times of moving too fast and glossing over some details. That might be fine in some cases, but if I produce code that’s doing that, likely I (or someone else) is going to be rewriting code later. That’s not what we want to do, especially if this is a logical error. I might understand some less than optimal code from a performance perspective, but there shouldn’t be logical errors.

    In this case, the poster had incorporated some changes into code and kept running the entire script, getting bad results. They even started to pinpoint a few areas where data wasn’t correctly being returned, but couldn’t solve the issue. This was a somewhat complex script, and it could be easy to miss some mistakes. This was also a dynamic SQL script, which often means that we have to extrapolate from our code to understand what the engine will actually execute. There’s often a simple solution to help you break the problem down: use PRINT.

    I’ve done this for most of my career, in many languages. Even today, I sometimes use this in PoSh to ensure that I’m not missing something being done. Watch windows and other debugging tools are great, but they sometimes are more difficult to understand when there are long strings of data. Print often simplifies the process.

    No matter what tools, languages, problems, etc. you are working on, breaking things down is often the best way to tackle a complex task. You might be surprised how much clarity this can bring to a tough problem. Even if you need to ask for help, having a list of things you’ve tried, and some data on the results is very much appreciated by those you ask. That way they know what you’ve tried and what to recommend.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.9MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • In Memorium

    I’m getting older, and as I remind people when I talk about careers and balance, I likely have less life ahead of me than behind me. I think about that as I try to enjoy life and appreciate the good and bad things I experience. I’ve had a wonderful life, and I can’t complain about anything. I wish everyone could say the same thing. My apologies in advance for the tone of this editorial.

    This week I added a new category to the Database Weekly newsletter: Obituaries. I had Andy Warren suggest this earlier in the year, but I neglected to do it then, mostly because it’s a sad reminder of loss. This past week we experienced another loss in the #SQLFamily, with the passing of Naomi Williams on Oct 30. There is a memorial fund set up for her children. I only briefly knew Naomi as a fellow speaker and the organizer of SQL Saturday #550 – San Antonio, but I enjoyed her enthusiasm and joyful nature.

    This wasn’t our only loss this year. Tom Roush passed away on Jan 30, 2018 (his book of stories is available). Robert Davis passed on April 2, 2018 (memorial fund), and I found out this week that Aaron Lowe had passed on July 23, 2018. I’m sure there are others, and while I don’t want to learn we’ve lost others, I’d appreciate anyone letting me know so I can remember them fondly.

    Life goes on, and we all must deal with losses. Those increase as we age, and that’s becoming a regular occurrence as days pass by. Whether it’s someone I’ve shaken hands with or an individual famous in for some aspect of their lives, death is something that I see far too often.

    Many of these are sudden, unexpected, and unpreventable. However, a few aren’t, and I’d like to ask all of you to ensure you help others where you can. Suicide has taken a few people I have known, and if any of you are struggling, please seek help. If you know someone struggling, please refer them to a suicide hotline. If any of you ever need to talk, I’m here as are many of your friends and family. We have enough tragedy in life, so please don’t withdraw completely.

    This is a sad week for me, but I’ll toast to my current and passed friends at the Summit with fond memories, enjoying the moments we have and and will have in this life.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 5.1MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Setting Memory–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    I had a great time away, and upon my return, I found lots of emails and messages to review from work. One of these was a note that Kevin Hill had updated his article on misconfigured SQL Server instances. I’d worked with Kevin before I left and thought this was a great topic. As I reviewed his update, I started thinking about one thing: memory.

    I typically run 3-4 instances on a host. I usually do this to test different versions and their effect on Redgate products or to review questions from the SQL Server community. I don’t have unlimited memory, however, and need to be careful. At times I’ll set an older version of SQL Server to not start so that I don’t have too much memory pressure for my regular tasks.

    I’d like to think I do a good job of setting up SQL Servers, and I did a double check on one of my machines. Sure enough, I had:

    2018-11-02 14_49_19-SQLQuery3.sql - Plato_SQL2016.sandbox (PLATO_Steve (62))_ - Microsoft SQL Server

    This was my SQL 2016 instance, which is the main one. For the 2014 and 2017 instances, I’d reduced this to 4096 as I use those less frequently. However, for SQL Server 2019, I got this:

    2018-11-02 14_51_00-SQLQuery3.sql - Plato_SQL2019.master (PLATO_Steve (57))_ - Microsoft SQL Server

    The error is expected, since I set this up quickly after it was released (and before vacation) and hadn’t done anything. In this case, I need to enable advanced options.

    I do that like this:

    EXEC dbo.sp_configure 'show advanced options', 1
    
GO
    
RECONFIGURE WITH OVERRIDE

    That will turn on the option, so when I run the memory command it works.

    2018-11-02 14_52_55-SQLQuery3.sql - Plato_SQL2019.master (PLATO_Steve (57))_ - Microsoft SQL Server

    That’s not ideal, so let’s lower it to 4096. I can do that like this:

    EXEC sp_configure 'max server memory', 4096
    
GO
    
RECONFIGURE WITH OVERRIDE

    This will change the memory SQL Server uses. The doc pages describes this, and since I’ve done little on this instance, it hasn’t used much memory. My setting doesn’t do much, but it will prevent more pressure from activity in the future.

    SQLNewBlogger

    This was a quick post. Once I read the article and realized I ought to check things, I also realized this is a nice, short topic to write about and share with others. If you haven’t checked the settings on your dev machine, do so.

    And write about it.

  • The Short Summit

    This week is the 2018 PASS Summit, the largest conference devoted to SQL Server and the Microsoft Data Platform. This is the 20th Summit, and I’m sure there are a few people that have been to all of them. I think I’ve missed 3, though I was at the first one and I’ll be there later this week for a short trip.

    The annual Summit used to be an event that I looked forward to most of the year, a time when I’d see friends from all over the world that I only saw in person once a year. I might email, tweet, etc. with them many times, but the PASS Summit was one of the few times we’d be able to shake hands and really talk with each other.

    The world has changed a bit, with many more SQL events from SQL Bits, SQL Saturdays, Data Relay, and more that take place all over the world, and at every time of the year. If you want to talk data platform with colleagues, get inspired, learn something, or just share a beverage, you have many different opportunities each year. I get to more than my share, and I see many of my friends multiple times a year. I love that, but I also miss the excitement of there being just one event.

    That isn’t going to change and we’ll continue to have multiple events. I do think the PASS Summit is still the best place in the US to get excited about the data platform, talk with Microsoft developers, and share information with your peers. There are lots of social events, and if you get the chance to attend, it’s an action packed week. You should plan on being busy, being tired, and talking to lots of people. Attend parties, for the networking if for no other reason, and engage with others.

    Unfortunately I won’t do much of that this week. The timing this week is worse for me, personally, and I’m a little worn out from other events and travel this year. I’ll arrive Thursday and leave Friday, making this the shortest trip ever for me to a PASS Summit. I know I’ll be tired, but it’s always great to see friends and meet new people. Please, don’t hesitate to say hi, shake hands, or take a picture with me if you’re there.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.3MB) podcast or subscribe to the feed at iTunes and Libsyn.