Posted in tSQL, tSQL Tuesday

TSQL Tuesday #133 Wrapup

Thanks to everyone who participated!  I’m excited to go look up some interesting information, see some new presentations when they come out, test out some PowerShell splatting, and start putting demo and sample code into SQL Notebooks!

Rob FarleyThe Other Side of the Room: Rob has learned mostly non-technical lessons. He encourages people to put themselves in the presenter arena. When he gets useful feedback, he takes note and tries to give presenters he watches the same kind of feedback.

John McCormackPowerShell Splatting – What was wrong with backticks?: John had a lot of backticks in his PowerShell code to make it more readable and “keep” code on the monitor. An attendee asked why John didn’t use splatting. John was intrigued and learned how to create a single variable at the top, consisting of an array or hash table of the parameters used in the code. This makes the code easier to read both for attendees looking at a slide, and others using your code.

Jess Pomfret –  T-SQL Tuesday #133: Jess has learned Git and GitHub from contributing to dbatools (woot!) She now hosts presentation demos/slides there. She also learned how to run Docker containers on her laptop for presentation demos. Thirdly, she learned how to build pester tests to ensure her Docker containers are in the correct state for her demos.

Aaron BertrandWhat Else Have You Learned:   Aaron has learned that not all airlines get you where you need to be when you think you need to be there, and that rehearsing content is valuable for a better idea on the content timing. And, sometimes, there are technical difficulties. The two biggest take-home lessons are to be prepared, and do NOT ad-lib 🙂 Oh, and pay attention to what presenters you’re watching do when the going gets tough, so you can learn those lessons a little less painfully! Thanks, Aaron!

Deborah MelkinWhat Else Have I Learned From Presenting?:
Deb learned how to use SQL Notebooks for presentation by converting each script for her T-SQL 101 session into a Notebook. Now Deb is writing a presentation on a Starter’s Guide to SQL Notebooks, which I can’t wait to see because I too need to figure this stuff out! Deb is another converted user of Git and GitHub, and has learned some GitHub-based swear words along with a few lost files. Thanks for persevering and sharing with us!

Tracy BoggianoWhat Else Have I Learned From Presenting?:  Tracy learned that PowerPoint subtitles will get closed captioning! She also learned a whole bundle about Linux when presenting on a Telgraf/InfluxDB/Grafana stack she was involved with setting up at work.

Mikey BronowskiWhat Else Have You Learned From Presenting?:  Mikey had written a blog post about saving Outlook attachments with PowerShell, but wasn’t allowed to use PowerShell at the time in the office. So, he figured out how to save attachments from multiple emails that fit some criteria, using VBA. In Outlook. Cool!

Deepthi GoguriWhat Else Have I Learned From Presenting?:  Deepthi learned some great technical concepts including memory grant connections, redundant joins between tables and their relationship to foreign key constraints, and some information about the NOEXPAND hint. Hmm, I will be looking that up too!

Kevin ChantLearning New Things By Building Demos:   While building demos, Kevin has learned about SharePoint setup and configuration, how to record and edit a video in Camtasia, and Docker setup. Thanks, Kevin! I might not look up the SharePoint config though – I’ve managed to avoid it up to now so I’d like to keep that record going!

Todd KleinhansWhat Else Have You Learned From Presenting?:  Todd learned that sometimes the most amazing ideas aren’t feasible for one reason or another – mostly one of the three roots of any project: time, resources, quality. He’d like to build a VR version of a technical presentation, which sounds amazing, and I hope the near future gives you more of what you need to continue on this project!

Nate Johnson –  Learn By Presenting: Nate tried some Twitch streaming but didn’t have many views. However, he’s been involved in small group learning sessions and realized that he talks to quickly, he enjoys audience interactions, and the infamous, Never Type in Demos!

Justin Bird –  T-SQL Tuesday #133: Justin learned about the contract that SQLBits has with the speaker. The speaker puts in a lot of time and effort and gets… feedback. So, anyone attending a presentation, anywhere – please fill out feedback forms to help them get better! Great point, Justin!

Elizabeth NobleFilling In the Gaps: I love Elizabeth’s use of “sweet potatoes” as a swear word, which is completely unrelated to this topic. She learned how to create VMs with PowerShell, including domain controllers. But IMO the most important take-home message is that we don’t have to know all the answers, but be willing to learn!

Honorable Mentions:

Mike Walsh –  Presenting: What I’ve Learned: Mike got struck with a case of the 2020 Blursday syndrome, so that’s why this is in the honorable mention (aka a little late) section. He talks about learning about log chains and what doesn’t break them, from the audience. He also recorded a video about presenting and things he’s learned about himself and life through presenting 🙂

Joshua HandlerSurprise!! You learned something you didn’t expect!: Josh had some blog host “unvisible” issues.  Josh was presenting on dbatools and was talking about profiles. Afterword, one of the attendees who was also an MVP, let Josh know that there were many profiles you can use with PowerShell. He also got good feedback about his first time presenting!

Thanks again, and now I challenge all of you out there to think about what presentation you could write, and what ELSE you might learn while writing that presentation (or presenting it!)  Go forth and learn – but stay safe and stay healthy!

Posted in tSQL, tSQL Tuesday

T-SQL Tuesday #133: What (Else) Have You Learned from Presenting?

This month, I’d like those of you who have presented, or written a presentation, to share something technical THAT DID NOT RELATE to the topic of the presentation, that you’ve learned in writing or giving the presentation. This can include a work presentation, for those of you who haven’t spoken at an event! 

Why do I ask?  Well, if any of you have heard Allen White (b|t) speak on any topic, he will start his presentation speaking about how much PASS has contributed to his life and career. He will then talk about how every person has a story to share, and there will be a piece of that story that no one has ever heard before. We ALL have something to learn from each other. He will also tell you that you will learn more than you ever thought just by writing and presenting on ANY topic, as well as answering questions!  So, I’d like folks who have done this to share and encourage – especially the new folks who are on the fence about presenting. Feel free to go more in-depth with some of the technical details of your learning than I did!

I will share a couple of stories about my own experiences. My first presentation at our local PASS user group was a lightning talk about a bizarre performance hit we took from a foreign key with an ON DELETE CASCADE option. It was a rare scenario, so I figured most folks hadn’t run into this.

As I was researching foreign keys in general, I realized… hey!  Did you know there were other options for updating and deleting data tied to foreign key constraints? I don’t know that I’ve paid attention to that for the last… at least 10 years. I’ve never seen any of these options used in code I’ve maintained or identified as problematic.  You can specify default behaviors for both delete and update options – for example, if I update the value that the foreign key constraint points to, I can change all those foreign key values to be NULL. Or to update that foreign key value to the new value. Or… not do anything. There are ramifications for all of the choices, but I could definitely see how they might be useful in certain scenarios.

Also, people were interested enough in the topic to ask about indexing on some of the different fields, and asked me to expand this into a full presentation. I did so and presented at our next local SQL Saturday, including testing with the indexes that were recommended to me.

Learning summary:

  1. tables may show up in the execution plan even if they weren’t in the query AT ALL (look for constraints!)
  2. there are other options for coding foreign keys to handle updates/deletes to the primary key data that could be useful elsewhere
  3. Reinforcement: Indexes with low cardinality don’t always improve performance!

I also wrote a book on refactoring legacy T-SQL. I was talking about functions, and how SQL Server’s STATISTICS IO won’t show IO that a function performs. I remember thinking, “I CANNOT write a book in 2019 that talks about running a trace!”  So, I started using simple Extended Events to show the increased IO for functions that the native SQL Server STATISTICS IO sneakily avoids. I need to start making more use of Extended Events but this was a great .. kick in the pants to get going on those, as well as being able to code a very simple, more modern solution to share with readers on how to more accurately find IO stats of the SQL you’re running/tuning!

I could continue about buying a “lab” laptop, re-learning how to use Oracle Virtualbox, dealing with networking (not one of my strengths), using SQL Server core (sooo fast to install!), and all of those fun things. But, there will be future blog posts on those topics, and it’s time for others to share THEIR stories and experiences!  Thanks for reading!

 

Posted in tSQL, tSQL Tuesday

Learn from… me!

This image has an empty alt attribute; its file name is T-SQL-Tuesday-Logo-e1518373137845.jpg

Kerry Tyler (b|t) asked us in this month’s t-SQL Tuesday topic to talk about a lesson we’ve learned, in order to help others learn FROM our mistakes without also making them! I’ve wanted to talk about this for a while, as I’ve now seen this behavior from both sides and I know how detrimental it can be to everyone involved.

What is this I speak of? Our fraught relationship with third-party software vendors. I know, I know, we all love to hate them. (Even those of us that ARE them – we also have third-party software, we completely get your pain.) In a couple former jobs, it was pretty clear: “Hey, Sales just sold software so we need the software to do this in a time frame that’s way too short, and just asking for pain all the way around.” Developers grumble, QA grumbles, shortcuts get taken, functionality and performance are very far from meeting expectations, and everyone – customers, developers, sales, PMs, account reps…. EVERYONE is pointing fingers at each other.

It’s at this point that everyone loses sight that we ALL have the same goal. It’s easy to do – each party feels like they’ve been strung along (which is probably true). No one is happy with the end result. Flashback to when I was on the phone with a customer, who had rolled out this wonderful new functionality and performance was so bad that literally no one could use one of the screens in the software. This was my first interaction with these customers, as my immediate supervisor had moved on to greener pastures a week earlier. It was Friday night (of course), and I had plans with my family and was a bit frustrated.

I was looking at the SQL (which, btw, was generated by the application and not in a stored procedure.) First of all, any SQL changes would require an application patch. Whoops one. Second of all, wow, was this SQL bad. Cartesian join of large tables, so many returned columns that indexing was really out of the question, and a poor plan. As I was looking at the code, I remember saying, “I don’t think I can fix this in an hour. I don’t think I can fix this in SIX hours.”

So, at this point I learned that teleportation is possible, because there is NO other explanation for how the account manager got to the spot standing next to my desk from his office upstairs in the timeframe of 1 second. He frantically motioned me to hit the mute button (which I did), and then followed with, “We can’t say that to the customer!”

My response: “I’m not going to lie, that’s going to be really obvious really fast that this isn’t anything we can fix RIGHT NOW.”

What we didn’t hear: the customer hit THEIR mute button, turned around and said, “Finally, someone is going to tell us the truth!”

That SQL didn’t get resolved that night. What ended up happening is that enough people on the customer’s end went home, so the remaining people could actually run the query. The next week the SQL was pulled out of the application and I wrote a performance-tuned stored procedure. However, something much more magical happened – we started trusting each other.

My new boss (since the previous one had left the week before) was also new to the company. He stepped in to work with this customer (who had very legitimate reasons to be frustrated.) A few other people who had been involved in these interactions were encouraged to step out and let us handle the problems. I started working very closely with the customer, and my boss started being very transparent with them. They were incredible testers; we started working together as partners when we had a release that we felt was ready, and would get excellent feedback from them. With the significant increase in communication, we could really focus on what was most important for them and they would help us get it right, before they rolled it out to production.

What did I learn from this? Shut up and start listening. Try to understand where folks’ frustration is coming from. BE HONEST. Remind everyone that you are all sharing the same goal: the customer being successful. Really focus on what you can do on your end to reach that goal. Encourage people around you to focus on what they can do to help reach that goal. No matter what your scenario, don’t think of people as customers – think of them as PARTNERS. Treat them as partners. Encourage them to BE partners.

I still have a relationship with a couple people from that customer – they have become friends. Real friends. I know I can reach out to them if I need help – hardware questions, home lab setup questions (one of them is a hardware guru). I am pretty sure they would reach out to me if they had a tSQL question as well. Some of them were going to join me at PASS Summit this year (partially because we know this awesome restaurant in Houston, but also because .. we’re friends.) They have brought a lot of value to my life 🙂 So… please take what you can from my learning, I hope it brings you the good things it’s brought me!

Posted in tSQL, tSQL Tuesday

Life Hacks to Save Our Sanity

This image has an empty alt attribute; its file name is T-SQL-Tuesday-Logo-e1518373137845.jpg

Jess Pomfret (b|t) asked us in this month’s t-SQL Tuesday topic to talk about a life hack we’ve done that’s made things easier for us. This is perfect timing for me; I wanted to share a couple of stored procedures I wrote for the book (Refactoring Legacy T-SQL For Improved Performance). The CodeCascade procs can be found on a GitHub Repo called CodeCascade.

Actually, the idea for this code started back when I listened to Andy Yun (b|t) give a talk about nested views. I was dealing with a 2,000 line stored procedure that would be interesting to rewrite, but it really shouldn’t take me that long, right? I mean, 2,000 lines. Except that it called a function that was also 2,000 lines, which called a few procs, not to mention a few other things the original proc called, and then … well, yeah, you get the point. 20,000 lines of code later, I was like, WHAT did I get myself into?

So, since we all know how excellent we are *cough* at estimating time to perform tasks, I figured it would have been a good thing to have known about this cascading miasma of code before I dove in head-first. Andy’s presentation had code to show the cascade of a view – all of the views it called, and views they might call. I wanted to adapt this to work for stored procedures and functions as well. (Forewarned is forearmed, after all.)

So, I blithely knocked together some code to tell me exactly what objects (procs, views and functions) that my code called, using SQL Server’s dependency information. I went to run it on that exceptionally offensive proc. And guess what? It blew out after 200 recursions. So, I set the limit to 400 recursions, and… it blew that out as well. OH NOES! Well, I had also set a kind of breadcrumb trail field in this because I was particular about the sorting (I wanted each cascaded item to be under the parent that was calling it.) So, I had each object line check to see if that ID was already in the breadcrumb field, and if it was, add a message that it was part of a loop, and stop trying to recurse through that particular line.

Once that was polished up, I also did the “reverse” – what code CALLS my code, and what code calls THAT code? This is again based on dependencies, and checks for that eternal loop.

These procs have come in handy many times. I encourage you to use them and modify them if you’d like, but it’d be great if you left the intro flowerbox on that attributes the original code to me. If you do make changes that you think others would find helpful, please feel free to let me know and I’d be happy to add your changes and attribute them to you as well!

I hope these can help you as well 🙂

Posted in tSQL, tSQL Tuesday

Dealing with Impostor Syndrome

Jon Shaulis (b|t) asked us in this month’s t-SQL Tuesday topic about impostor syndrome. Do we have it? How have we worked past (or around) it? The first question is easy – I don’t know anyone who hasn’t dealt with impostor syndrome, unless that someone is new enough to whatever job or task they’re doing that they don’t have enough experience to know there’s no way you can ever know everything.

The second question can be more difficult. Also, if you lose self-confidence in one area of your life, it can destroy your self-confidence in all areas of your life without you even realizing it. Being self-aware, which is something we should all strive for, unfortunately makes you more prone to this in my opinion.

So, we all think of ourselves as “normal” because we grew up with us, and we know how we feel, and how our family or peers were or are, and what we look like in the mirror, and how much we curse when we accidentally jam our foot into the doorframe – all of those things are familiar and “normal”. We know what we do know and all of those things that we don’t know. When we see someone who knows more or is better at something than we are, it’s very easy to fall into the trap of thinking that person knows so much more “stuff” than we do.

But there’s something that it’s easy to forget. You know something that no one else in any given room does. You are better at something than each and every person. And the opposite is also true: each and every person in the room can do something better than you, or knows something you do not. Everyone has unique experiences and our brains all do “thought algebra” differently. Wait, whut?

So I can look at a and b, and to me, a + b obviously equals c. To someone else, a + b = m. Neither of us are necessarily wrong, everyone just collates information differently, and not everyone solves things in the same way. Nothing has more than just a single solution (well, except the meaning of life, but since no one has figured that out yet…)

So what does this have to do with impostor syndrome? Everything, actually. It’s very hard to do, and I am just as bad as everyone else with this, but we all need to realize how special and amazing each and every one of us are. (It’s still hard to not say “each and every one of you”, and to include me in the amazing people. See?) Maybe there are skills we still need to learn, and we probably will learn those skills. But as people, we are all unique individuals with the capacity to do amazing things. We also need to surround ourselves with friends who remind of this regularly. Discussing this with people we trust, who we know care about us and not about what they can get from us, will go a very long way to help us stay in the right frame of mind when dealing with impostor syndrome!

Thanks for listening 🙂

Posted in tSQL

Basic Use Cases of Lag and Lead

Lag and Lead are both window functions available in SQL Server 2012 and forward. Most of you have probably at least heard the term window functions, but what does that mean?  Well, first of all, it has nothing at all to do with the Windows operating system. For those of you that have done some studying up on performance tuning of queries, you’ll remember that the best way to make your query run faster is to limit the data set that you’re acting against as soon as possible in the query/procedure.

A window function will use just the dataset returned by the query, and act upon THAT while still within the query. It can do additional aggregations or perform other tasks on the initial select of the query, without requiring subqueries or other hits to the actual database table. Lag will find the value of n rows back, while lead can find you the value of n rows ahead.  Great, right?  But, what does that mean in practical terms?

Let’s set up some simple queries to illustrate.

/* please run the code setup from the attached document: setupCode.adhocsql
Then, on the same connection, run this code */

SELECT a.tripNumber
    , a.stopNumber
    , a.stopAction
    , a.actArriveDate
FROM #stops a 
WHERE a.tripNumber = 121279 
ORDER BY a.actArriveDate;

So, we can see in the results 4 stops on this leg. 

Maybe we need to find out what action is supposed to happen at the next stop. This is how we would have had to write this code in the past, which would have involved 5 table scans and 15 logical reads:

SELECT a. tripNumber
   , a.stopNumber
   , a.stopAction
   , a.actArriveDate 
   , (SELECT TOP 1 stopAction  
      FROM #stops b  
      WHERE a.tripNumber = b.tripNumber 
         AND b.actArriveDate > a.actArriveDate 
      ORDER BY b.actArriveDate) AS nextAction
FROM #stops a 
WHERE a.tripNumber = 121279 
ORDER BY a.actArriveDate;

Here are our results:

And, as we mentioned before, the statistics IO and time:

Now, though, we can use LEAD to find out what’s going on in the NEXT row.

SELECT a.tripNumber
    , a.stopNumber
    , a.stopAction
    , a.actArriveDate
    , LEAD(a.stopAction,1,'N/A') 
            OVER (PARTITION BY a.tripNumber 
                  ORDER BY a.actArriveDate) AS nextAction FROM #stops a 
WHERE a.tripNumber = 121279 
ORDER BY a.actArriveDate;

As we can see in the result set, the nextAction finds the stopAction from the next row, and we see our default N/A on the last row (because there’s no “next” row!)

Let’s break this down:

The red rectangle denotes the column that we’re choosing. The green rectangle is the offset – that is, how many rows ahead we’re looking for the value that we want. The blue rectangle denotes the default value if there is no “next” row.  The orange rectangle denotes a partition – that is, we’re going to start this over for every tripNumber. And, there must be an order by in the OVER clause for lag and lead because we need to know what will determine the “next” or “previous” row. This is denoted by the pink rectangle. Please note that the order by in the lag/lead does NOT have to be the same order by that the query uses.

This is our statistics IO and time for this query:

The Window Function version causes 1 scan of the table and 3 logical reads. Yay, less work!

LAG allows us to look back at previous rows. Say we want to see how much time elapses between stops….:

SELECT a.tripNumber
    , a.stopNumber
    , a.stopAction
    , a.actArriveDate
    , DATEDIFF(mi
         ,LAG(a.actArriveDate,1) 
                 OVER (PARTITION BY a.tripNumber 
                      ORDER BY a.actArriveDate)
         , a.actArriveDate) AS minElapsed
   , LEAD(a.stopAction,1,'N/A') 
                 OVER (PARTITION BY a.tripNumber 
                      ORDER BY a.actArriveDate) AS nextAction 
FROM #stops a 
WHERE a.tripNumber = 121279 
ORDER BY a.actArriveDate;

Here, we didn’t specify a default, and so we get NULL for the value of that field. And for this query, we also get 1 table scan and 3 logical reads!

What would this look like if we wanted to find data for a few different trip numbers?

SELECT a.tripNumber
 , a.stopNumber
 , a.stopAction
 , a.actArriveDate
 , DATEDIFF(mi
 ,LAG(a.actArriveDate,1) 
 OVER (PARTITION BY a.tripNumber 
 ORDER BY a.actArriveDate)
 , a.actArriveDate) AS minElapsed
   , LEAD(a.stopAction,1,'N/A') 
 OVER (PARTITION BY a.tripNumber 
 ORDER BY a.actArriveDate) AS nextAction 
FROM #stops a
WHERE a.tripNumber IN (121279,121230,121333) 
ORDER BY a.tripnumber, a.actArriveDate;

Our result set shows the lag/lead row calculations completed within each tripNumber:

Checking on the statistics IO, we still see 1 scan and 3 reads. Woo hoo!

These and other Window Functions can be used for some pretty advanced tSQL, but for now we’ll leave it with these simple usage cases.  Thanks for reading!