Thursday, July 19, 2018

TSQL Tuesday # 104

This month's T-SQL Tuesday is being hosted by Bert Wagner (blog | twitter) - Code You Would Hate To Live Without. He wants you to share about the code that you would hate to live without, whether yours or someone else's.

I know there will be a lot of wonderful posts about this topic! I have 2 things that stand out as scripts I love.

  • I changed a stored procedure which used a cursor and ran for 45 mins, into a set-based query that runs in less than 5 mins. While I know I am stretching the topic just a little, it is something I think about often, very proudly. This was something that helped cement the idea that I was on the right path in my career. I knew I loved working with data and databases, but I remember the level of excitement I had when I got it to work. :)

 

  • The other script is something I put together which works with log shipping for multiple databases. I made a robust, automated process from start to finish. There are still improvements I would make to it, but I dont work for that company anymore, so I probably wont have a chance in production for a while. The vendor had multiple databases (approx 45) and that number would continue to grow as we added more businesses. Therefore, the solution had to be dynamic, nimble and easy to maintain. I am proud of the documentation (ohhh... I said a bad word), workflows and full process with error handling, I set up. It was also easy to troubleshoot and only run one or a few databases at a time if only some were lagging behind.

While neither script is a general purpose script (they are specific to the company I worked for at the time), they both make me feel confident in my abilities as a DBA. I know that I can keep at something to make it eventually bend to my will (muwhahha), and enjoy myself along the way.

Write comment (0 Comments)

Learning new stuff - quickly and accurately

I think one of the most helpful abilities to being an exceptional DBA is to be able to learn something new, fairly quickly and yet accurately.

I recently received a request to add an assembly to a SQL instance. I knew that functionality was there, it allows more flexibility within SQL, but I havent had any dealings with it yet. I was provided a script that had the hex value of the assembly and code to set the permissions equal to EXTERNAL_ACCESS.

Its going to sound weird, but I am glad that script threw an error because I have learned (and am still researching) much more about this. I learned that there are a lot of posts that provide code on how to set the trustworthy property of the database. Some of those posts point out that isnt the best method and explain why. Then, if you keep looking, there are a few posts which go into depth as to why.

In this case, I am having to work with the App Dev department to get the assembly signed. Since I like being able to feel confident in my responses to the developers, I need to understand more about what is going on than just the quick and easy answer. All this research has taken up part of my day today, and the testing will probably consume most of my tomorrow.

I point this out because earlier in my career, I would have stopped at the easy answer (which has the most posts). Now, though, I am looking to find those fewer posts which explain the why, in depth, so that I learn everything about what is going on, not just do something because its easy. I feel proud of my progression, and sometimes we need to recognize little wins like this. :)

This is just a quick post while SQL is installing. I am spinning up an instance of SQL in order to play around with installing an assembly, the different permission sets associated, and what is appropriate in the different environments (DEV and PROD) since the assembly already exists in one and not in the other. Back to the fun!

Write comment (0 Comments)

KILL spid (WITH STATUSONLY)

This article talks about how to stop or terminate a process that is running. Stopping a process shouldn't be the first step when troubleshooting as it can possibly make the situation worse.  However, it can sometimes be helpful in clearing blocking chains or other performance issues.  

In order to know what to stop, you will need to know what processes or queries are running.  There are a variety of ways to see all of the processes or queries running right now, including the following:

  • Microsoft built in tools - sp_who and sp_who2
  • Adam Mechanic's tool - Who is Active (Click here to download it)
  • Hand made query using the sys.dm_exec_requests and/or the sys.dm_exec_sessions tables

 

KILL

Once you know the session ID (spid) of the query or process you want to stop, you can do so by using the KILL command.  The kill command is pretty straight forward. The syntax is simply: KILL spid  (replace the text "spid" with the number of the process desired to be stopped)

A word of caution here, there are times when it is best not to kill a session. One example is if a process has been running for a while and using multiple threads, when it is killed, the rollback operation will only be single threaded. Therefore, the rollback could take much longer than letting the process finish on its own.  All of the same performance issues and/or blocking will still continue, so it actually could things worse, not better.

Some processes are not able to be stopped: per Microsoft, "System processes and processes running an extended stored procedure cannot be terminated."

 

UNIT OF WORK ID

The kill command can also be used to stop or terminate orphaned distributed transactions. The GUID value that is the Unit of Work ID (UOW ID) can be found in either the error log, the MS DTC monitor, or the sys.dm_tran_locks dmv.

The syntax is simply: KILL uowid

 

WITH STATUSONLY

While I knew about the kill command, I wasnt aware that you are able to check the status of the rollback process with the WITH STATUSONLY option until semi-recently.

The syntax is simple KILL spid WITH STATUSONLY - or - KILL uowid WITH STATUSONLY

For processes that are in the middle of being rolled back, this will show the status of the rollback. This option doesn't "do" anything, it just shows the progress. Keep in mind that as with other Microsoft provided durations, it isn't an exact number. This is more of a ball park figure that might be semi-right, but may also be a bit off from the actual time.

If you run this option on a spid that isnt part of a rollback, it wont hurt anything. You will just see an error returned indicating a rollback isnt in progress.

 

 

Its funny, but whenever I have to kill a process (which is thankfully rare), I cant help but do a bad-guy cackle in my head... lol!

 

Write comment (0 Comments)

My Goals for 2018

When Doug Purnel (blog) and I became accountability partners, we had to define our goals and share them in order to keep each other accountable.  I wrote out my goals, but hadnt shared them until now (its March).  One of my goals was to find a new job and when I submitted 1 application in January, I didnt expect it to be the job I accepted (and then move out of state and start my new job all within 1 month)!

Now that things have calmed down, I am publishing my goals to everyone.  I will update this main post with links to the other blog posts I create as I complete these goals.

Read more: My Goals for 2018

Write comment (0 Comments)

TSQL Tuesday #100

This month's T-SQL Tuesday is being hosted by Adam Machanic (blog | twitter) - Looking Forward 100 Months. He aims to stretch our thinking and calls us to think about what is to come.

I decided to have a little fun with this one (after all Adam bolded that part of the sentence didn't he?). When I read the invitation, and Adam mentioned some people might want to go slightly more science fiction than science, my brain exploded with excitement! Then, I re-read it and picked up on that it was supposed to be a post that might be given in 2026 (8 years or so from now) - I kind of popped my own bubble. I couldn't talk about invisible implants created to give us an edge, or everyone having a pocket assistant robot, etc. One idea, though, still seemed possible to me.

Part of our job in IT involves working with other people. Some of us work with other DBAs, others work with infrastructure folks, some end users, management, etc. Some of the people we talk to are technical, and some arent; some know what they are talking about, and yet others just think they do. One of the biggest things I find absolutely wonderful about our sql family is that the intention of most of us is honest and genuine, and that we are interested in helping others and doing our job well.

Many times, I will fall into a trap (that I lay myself I am sure) where my genuine intent and/or sincerity is not taken into account. Due to office politics, I may be viewed in a negative way, such as come across from a defensive posture, intimidating, or as a threat. My intentions in most cases are pure and I just want to be able to help solve the overall problem in the best way I know how. When some meetings are over, emails starting to fly around, I end up in a situation where someone is vying for more power, has a different idea, or doesn't agree, etc., and next thing you know I am seen as ___(fill in the blank)___ (the bad guy/ not a team player/ a bottleneck).

With the improvements of Siri/Galaxy/Alexa, and devices that can do fairly specifc things such as being able to tell why a baby is crying, my thought is, that at some point in time, I would love to see a device invented which can help read the intentions of others. Many people can read human "signs", such as being able to spot a lie. There are even professions which rely heavily on reading people, such as criminal profilers, or those who play poker much better than me. We are definitely putting enough science into the psychology of human behaviour and such to make it possible to understand the motivations behind actions.

The optimist in me sees this as a wonderful tool to help bring people together and solve the world's problems. I can see where people who are shy or who are interested in working with another group but are hesitant, would be able to trust each other more with such a device! Imagine some of the solutions that could be devised when people from different cultures or walks of life, could actually work together instead of constantly distrust each other.

My thoughts drifted back down from cloud 9 to the relevance in my job, and I could see teams of people working together to come up with incredible solutions to business processes. A device such as this, would help cut through office politics (SOME) and help identify people who are sincere in their passion/dedication to solving issues. While there would still be discussions which from the outside look like shouting matches, because multiple passionate people in the same conversation invokes "interesting" debates, the difference would be, when they walk out of the room, they understand that their ideas were being bounced around by others who are just as invested and genuine as they are.

Then, I started thinking about all the data that this device could produce in order to analyze. Imagine the dashboards and reports that could be built to show how someone grows as a person throughout the interactions they have. By interacting with people with more of a positive nature, we gravitate towards more creativity and productivity. Many psychologists "know" these things, but we would have more data to help prove it.  Of course, I would be remiss if I didn't point out that all of this data could be used to make robotics more human-like as well.  By having a deep understanding, and lots of data to back it up, we could end up with robots that are hard to distinguish from humans.  Although, to be honest, if you want any sci-fi movies and such, it is already on the table.  Therefore, by the time this device rolls out, it is already in place anyway. :)

Yes, the realist in me understands how every device can be learned to be "fooled" or tricked. I realize that a single device isnt going to turn some situations into rainbows and lollipops. I also realize that some people would use this as a way of targeting easier prey and such. In this blog post, though, I am wanting to focus on the positive potential (and forget the bad stuff since I am not in the high-tech engineering field and dont have to worry about creating it myself).

All in all, I know that there are ways of reading people now, and there are skills that I can learn to help out in many situations. I also know how much learning would go into that and want something to help in a much more immediate future. :) I realize I kind of went off the deep end and took the idea to "have a bit more fun" in a way that probably wasnt exactly what Adam intended, but thats the beauty of this - its my blog! lol :)

 

Write comment (0 Comments)

SQL Saturday Tampa #699 - 2018 (I'm Presenting)

I had the honor of presenting for the SQL Saturday here in Tampa this year.  I was really looking forward to meeting everyone!  I recently took a new position at a new company in Tampa, and moved literally days before the event.  With all the excitement of the move and being chosen as a speaker, it was a very exciting month!

 

I cant express how great it was - the staff was helpful and cheerful; the overall energy for the event was great.  I will be submitting to speak at the event next year as well!

 

 

 

Write comment (0 Comments)

How I Found an Accountability Partner!

In the Dec 2017 #tsql2sday blog chain, the author asked about learning goals (my response is here). I really loved reading all of the other responses! For me, coming up with goals isn't the hard part, but following through on all of them is. I start strong, and fizzle out...

When reading Doug Purnell's response (here is his post), he specifically mentions wanting to find others to help keep him accountable. I took the quiz he mentioned, and no surprise to me, I am also an Obliger. That gave me a great idea: I wrote Doug an email and asked if he wanted to work together to help each other! I will admit, I was a little nervous about writing a virtual stranger, but it helped that I had read his blog before and had seen his name while in the #sqlskinny twitter group a couple of years ago. When I emailed him, I started out with saying "I know we dont know each other, but...". :) I explained how I had heard his name, but then just asked straight out if he wanted to work with a stranger.

Luckily he said yes! We decided to meet every other week to help keep each other accountable for the goals we have set for the year. On top of that, we also decided to bring in someone once a quarter to help keep us even more accountable too. There are many people in the community who are open to "mentoring" others if they have the time. We chose 4 people, one for each quarter, so that each person is only having to commit to a 1 hour meeting, once. That will help us not only be accountable to each other, but also to someone else. So far, 3 of them have said yes, and I am anxiously awaiting the response from the last.

I wanted to write this blog post because early in my career when I wondered how to find a mentor, I thought there was a way they would find me, or maybe there was a way to sign up somewhere and it was arranged, etc. In reality, many times you just reach out to someone and ask for what you want. In addition to that, you can check out your existing work relationships. In looking back over the last 20 years, I realize I have had some mentors along the way, I just didnt "label" it with that term specifically. In the #tsql2sday blog chain for Nov 2017, the topic was who made a difference in your life/career. I listed 3 people and none of them were someone I have thought of with the term mentor, but it does actually fit.

I have been enjoying having Doug as an accountability partner. I almost didnt get a chance to write and post a blog recently (one of my goals is to blog twice a month), but was very excited to be able to email him when I finally got it done in time! I will make sure to post from time to time as things progress to give an update on how things are working out. Make sure to follow Doug on his blog too: sqlnikon.com

Write comment (0 Comments)

Archives

Powered by mod LCA