Posted in T-SQL Tuesday, SQL Server

T-SQL Tuesday #131 – It’s like this…

It’s another T-SQL Tuesday, that monthly blog party. It’s kind of like a Halloween party but instead of costumes and candy, we write blog posts about a topic related to SQL Server.

Thanks to Rob Volk (b|t)for hosting this month’s T-SQL Tuesday. Rob has tasked us with using an analogy for explaining something in SQL Server.

I don’t know if I have a favorite analogy for what I do. But let’s see if this one works:

Pretend I have to run errands around town. I need to go to the grocery store, hardware store, and return something at the department store.

I’m also making a list as to what I need from each store. At the grocery store, I need to pick up a couple of the basics: eggs, a loaf of bread, half & half for my coffee and some cheddar cheese. At the hardware store, I need to pick up some nails to hang some pictures. In addition, I have to return a shirt that I bought online that is the wrong size.

The first thing I need to do is figure out which stores I need to go to and which order. So I open up Google map and determine which stores are closest to each other and what order it makes sense to go to each one.

Now that I know what I need to do, I run my errands and get everything done fairly efficiently. It worked great. As these are some fairly common errands, I now have my plan of attack for the next time I need to do these.

Over the next couple of months, I notice this works well for the most part. Sometimes my list changes slightly – I need to get mozzarella instead of cheddar or I am exchanging the shirt that didn’t fit for the size that does instead of just returning it, but it doesn’t seem to make a difference so the route I’ve come up with works well.

But other times, I notice things don’t quite work as smoothly. When I run my errands on the weekends, it seems like everyone else has the same idea so I’m having to wait in line a lot more. And why is checkout lane 4 always closed? Or if I need to cook for a holiday or special occasion, it takes me a lot longer to get everything at the grocery store since I need a lot more ingredients.

And then sometimes random things happen. Remember that time I went into the department store to return a dress and walked out with an Instant Pot? And then another time, there was car accident so it took me longer to get from the hardware store to the grocery store; traffic was at a standstill.

Some of these things I can figure out before I leave the house, like when my lists for each store are drastically different than usual, so I can decide on a new route before I go. Other times, I just don’t have that information or something else unexpected comes along and I’m stuck with the plan I have and can’t do anything about it because the problem has nothing to do with the route I’ve mapped out.

So have you figured out the analogy yet? It’s SQL Server execution plans.

Just as I’m planning out where I need to go, SQL Server figures out the good enough plan for getting the data it needs. Sometimes it works well when you have differences – like needed mozzarella instead of cheddar – but then it doesn’t when you suddenly need something completely different, like returning a shirt but buying a household appliance instead. If you have loads that require different sizes, like shopping for a holiday meals, SQL Server can figure out that it may want to use different indexes if they’re available so it may find a more efficient plan to use.

But you also have to remember that at times, queries aren’t slow because of a bad execution plan but other things like heavier server workloads at given times, like running errands on the weekend, or someone else’s query blocking you, like getting stuck in traffic because of a car accident.

These are all things you have to pay attention to when writing and troubleshooting a query using the execution plan.

Now if only my actual errand list could be made more efficient….

Posted in Docker, SQL Server

Installing Docker

When I hear about a new Broadway show, I usually like to wait until I see it before I listen to the soundtrack. Otherwise, I miss the context and I can’t always appreciate it.

When it comes to tech, containers seemed to fall under that category. I kept hearing and attending sessions about them, but it didn’t really start coming together until I finally set one up. Now that I’m starting to use it a little more, the container is starting to make more sense. I still have a lot of questions but now I’m in a better place to figure them out.

More importantly, you have to start somewhere. So let’s start by install Docker so we can create containers and go from there!

Continue reading “Installing Docker”
Posted in SQL Server, T-SQL

Working With Synonyms

Don’t you love it when you find out that the little quirk you thought existed didn’t? Here I was – thinking I had this great idea for an article about it. I wrote up my test cases and proved myself wrong. So I guess we’ll chalk this up to another edition of “Me and Jon Snow – we both know nothing.”

I can still use Game of Throne references even though the show’s over, right?

At some point, I got it in my head that while you could create synonyms successfully before the object, once the object was created the synonym still wouldn’t know about the object or underlying changes. I’m not sure where I got this idea, but it’s just not true. Well, not entirely true. And I guess I was very convincing in this argument because no one corrected me at the time.

So let’s takes a look at a couple of examples to see how this works:

Continue reading “Working With Synonyms”
Posted in Professional Development

In Search of a Mentor

Last year at SQL Saturday Boston, I led a WIT panel over lunch titled “Models and Mentors” where we talked about role models and mentoring. There was a good variety of people in the room from those who were just getting started to those who were well established in their careers. One of the questions I had for the room was who had a mentor. Some of those who would be considered more established raised their hands. People who said they mentored others also said that they had mentors themselves. In some ways, I was surprised by this but in other ways, it’s not that surprising at all. Mentors aren’t just for people who are getting started but for those who want someone who can help them as they navigate their career and beyond. I was also surprised to hear of people who had multiple mentors. Again, I probably shouldn’t have been because different people have different strengths and the areas where you need mentoring can be different.

Continue reading “In Search of a Mentor”

Posted in SQL Server, T-SQL Tuesday

T-SQL Tuesday #129 – My Time Capsule Contribution

It’s another T-SQL Tuesday! It’s our monthly blog party where a community member gives us all something to write about. Thanks to Tammy Clark (t|b) for hosting this month. (And if you’re interested in learning more, check out the T-SQL Tuesday website.)

Our directions from Tammy are:

Tell me what few items or bits of information you’d like me to put into the #SQLCommunity time capsule.

To me, time capsules are a snapshot of what’s going on at the time. So here’s a photo of what I’ll be adding:

My time capsule - my laptop surrounded by music, computer accessories, a guitar and mandolin, and two stuffed animals

Continue reading “T-SQL Tuesday #129 – My Time Capsule Contribution”

Posted in SQL Server, T-SQL Tuesday

T-SQL Tuesday #128: Learn From Others

TSQL2SDAY-150x150It’s another T-SQL Tuesday! I feel like it’s been a while since I’ve joined the party. Thanks to Kerry Tyler (b|t) for hosting this month. (If you want to know more about T-SQL Tuesdays, you can find out more info here .)

Kerry’s challenge for us this month is this:

“Tell me (us all, obviously) about something recently that broke or went wrong, and what it took to fix it. “

When I think about this topic, I start trying to wrack my brain for that amazing disaster story. My best story involves SQL Server 6.5 so I’m pretty sure that’s not going to help anyone. But I also realized that I’m not on the administrative side of databases. I work with programmers and help create those problems that people see in the field. (Hey, wait a minute! Wait, no, that probably is accurate… *sigh*)

When I break something, we usually call it a “bug”. Doesn’t it sound better that way? Sometimes the bugs are small where we didn’t know that the client had made a change to the database before we released the code so the script failed when they ran the installer. Sometimes they seem small but later cause big problems, like enabling a process to send messages to a Service Broker queue by default but not enabling the job to process those messages by default. That meant when the job was enabled after 5 million or so messages were waiting in the queue, it unsurprisingly used a lot of resources that suddenly caused poor performance in the main database, also on that instance. Sometimes the problems are quite obvious – writing a query for a small database that didn’t come close to having the statistics that a very large client database would have so the performance ended up being incredibly slow.

For me, a lot of the problem solving comes from knowing how to troubleshoot to find what is actually going on. For example, your monitoring tool is reporting blocking. Does it happen on a regular basis? Is the blocking always on the same table or action or is on something different? Is something else running on the instance at the same time? Do the end users notice the problem and where in the system do they see the problem? Essentially, it’s asking a lot of questions to make sure I understand what the issue is, looking for the repeatable patterns, trying to replicate the issues, then going through the work to try to find the solution – whatever that may look like.

Once I identified the problem, I can then work on the solution. In many cases, the solutions are easy – add the missing index, make sure explicit transactions are properly committed, rewrite a query. Sometimes the solutions take longer to implement – work with the programmer to move SQL generated from a 3rd party data layer into a explicitly defined SQL statement or a stored procedure, rework the processing of Service Broker messages into different sized batches and allow changes to the job so it only processes data during off hours, rework catch-all queries into stored procedures that use dynamic SQL based on the parameters that are based in. Some of these solutions take minutes and others takes weeks or months. Sometimes I wanted to rework the whole approach but couldn’t. The more I think about it, rewriting the query was the solution in a vast majority of the issues I’ve seen.

I’ve definitely learned how to be a better troubleshooter over the year because of all the different things I’ve seen and have had to fix. For example, I got burned really badly with a poor performing CTE so you better believe I’m going to test the CTE against different syntax to see which works better. I’ve also gotten to learn the different behaviors of different tools or really figured out how to make them work to my advantage. If it’s a simple rewrite to test better performance, I may stick with Management Studio while a major rewrite testing all of the small changes that can affect performance at every step of the way may mean I’ll use Plan Explorer instead. Because I’ve had experience with lots of different small things, I can build on them as I come up with different solutions to the problems I face.

For those who were hoping for a great disaster story or epic fail and solution from me, I’m sorry to disappoint you. Trust me when I say I do make plenty of mistakes and have had a lot of things not work the way they should have. But I guess what I’m trying to say it’s all the small failures and the little mistakes rather than the big disasters along the way that I learned from to keep improving and getting better so I can be better equipped to fix the new bugs tomorrow that I’m creating today.

Posted in Source Control, SQL Server

Setting Up Git

I somehow have been using some form of source control for my entire database career. And I’ve been using Git for the past several years at work. I decided to create a repository as another way to make my sessions and scripts accessible and as better way to organize them, rather than just uploading to the SQL Saturday or user group sites and keep them scattered across multiple folders on my computer.

You would think as someone who has used source control regularly, it would be fairly simple to do. When it comes to that someone being me, you would be wrong.

Continue reading “Setting Up Git”

Posted in Azure Data Studio, SQL Server

Presenting with Azure Data Studio

I had the honor of being part of the first Data Platform Discovery Day event, held over two days – one for US and one for Europe. This was a virtual event designed for those who are new to the Microsoft Data Platform. I was honored that they picked me to present my “Back to the Basics: T-SQL 101” session.

I decided this was the perfect opportunity to play around more with SQL Notebooks in Azure Data Studio (ADS). I didn’t give myself a lot of time to get ready – just a couple of days.

Continue reading “Presenting with Azure Data Studio”