SQL Server Archives - John McCormack DBA

Falling back in love with Data Community events

14th March 2022 By John McCormack Leave a Comment

Data Community Events

Last week, I had hoped to go to SQLBits conference in London but a variety of factors meant I could really only attend the Saturday morning sessions (and virtually at that). I’ve really missed Data Community Events like SQLBits and others.

I wanted to go because I’ve been slightly disengaged from the data community for a year or so, probably due a combination of factors such as lockdowns, zoom fatigue and a few speaking knock backs in the last year which dented my confidence a bit. However I wanted to at least attend SQLBits in part, in the hope it would inspire me and kick start some new blogging and possibly presenting opportunities. I’ve had some great times attending and presenting at data community events in the past such as at DataGrillen, DataScotland, and SQLBits and I knew it would be worth the effort to get back into the swing of things.

Just attend some sessions and take it from there

Attending sessions where you know little or nothing of the subject matter can be extremely rewarding.

I’m glad I did. The sessions I attended were all extremely enlightening and I enjoyed following along. One thing that immediately came back to me is that attending sessions where you know little or nothing of the subject matter can be extremely rewarding. These sessions serve to keep you informed of the overall technology trends and who is doing what. For example, I won’t have much opportunity to use Azure Arc in the near future but it’s been around long enough that I can’t ignore it completely. Attending Ben Weissman’s 20 minute taster session was just enough and it gave me some ideas about how it could be used in co-ordination with our on premises environment. I also really enjoyed learning about the developments to SQL Managed Instance since I last used them over a year ago. Some of the improvements released during the previous 12 months could actually make it a far more viable product for my company.

Keep it going

Thursday night (17th March 2022) sees the the latest meeting of the Glasgow Data User Group. I will make a point of attending, even although the speaker is discussing ETL in the cloud which is not a big area of professional interest for me, I know I will learn something and I hope it will entertaining as well as informative. Plus it will be good to see some old faces, albeit we are still remote.

12 blog posts

I committed to 12 blog posts this year, this one can serve as #1 and it gets me started. Hopefully by attending many other events, I can find the inspiration needed to get back to creating my own content and keeping up with developments in the data community. As well as keeping up with old friends.

T-SQL Tuesday #143 Wrap Up

19th October 2021 By John McCormack 2 Comments

What an honour it was to host T-SQL Tuesday this month and I received some really great submissions. This wrap up post aims to give a quick insight into each of them in the hope that more members of the SQL Family can find some time to click on them and learn more. I counted 22 posts including my own which was a great response. If you missed the original invite, you can find the link below.

T-SQL Tuesday #143 – Short code examples

I learned so much by hosting this and made sure I gave due care to reading every post. It was also a lot of fun and allowed me to interact with people in the community that I haven’t met before. If you haven’t hosted T-SQL Tuesday before, please contact Steve Jones as we are always looking for new hosts.

Wrap Up

Rob Farley – Short and to the point like I asked for, Rob details a quick way to find objects. And he was happy to clear up for the reader that I didn’t mean GOTO as in the old BASIC syntax you could run on your commodore 64. (For me it was an Amstrad CPC464)
http://blogs.lobsterpot.com.au/2021/10/12/go-to-scripts/

Koen Verbeek – Koen shows us numbers tables, tally tables and a dates table. These are really useful constructs for allowing your queries to go “set based”. Essential reading for anyone who cares about performance. https://sqlkover.com/t-sql-tuesday-143-short-code-examples/

Aaron Bertrand – Aaron shows us how he “bulletproofs” his answers for dba.stackexchange and Stack Overflow. db<>fiddle was new to me. I love some of Aaron’s demo database names like [master (Restoring…)]. I ran the create command on my test instance and had to drop the DB right away as it was giving me the chills.
https://sqlblog.org/2021/10/12/t-sql-tuesday-143-worst-metadata

Deborah Melkin – Deborah shows us a really useful debugging trick when creating stored procedures that use dynamic sql. Many of us have been lost in dynamic sql at some point, and this snippet is great at helping you see where you are.
https://debthedba.wordpress.com/2021/10/12/t-sql-tuesday-143-short-code-examples/

Kenneth Fisher – Kenneth shares a compendium of previous posts which all require some serious reading. My favourite was “all jobs that ran during a given time frame”
https://sqlstudies.com/2021/10/12/code-examples-t-sql-tuesday-143/

Jeff Hill – Jeff shared 4 great PowerShell snippets. True to this month’s request, they are short and incredibly useful. Want to know what version of Windows you are running on your Server or when it was last rebooted, look no further.
https://sqladm.in/posts/tsql-tuesday-143/

Chad Baldwin – Chad is a newcomer to T-SQL Tuesday and chipped in with a stellar first post. I must admit, I’ve never given much thought to how to format a result set as I’ll usually do it in the client, but when you need to; it is possible as Chad shows. But that’s only the start. There’s too much to discuss in this digest as he also covers tally tables, random numbers and overcoming the divide by 0 problem. Did I mention he also covers docker, monitoring/filtering log files and setting aliases. Cap doffed.
https://chadbaldwin.net/2021/10/12/tsql-tuesday-short-code.html

Andy Yun – Random numbers, random delays (I wonder if Scotrail use this script) and random strings. Thanks Andy for a great post. There are great scripts on their own and for building into more complex ones.
https://sqlbek.wordpress.com/2021/10/12/t-sql-tuesday-143-random-fun/

Kevin Chant – Kevin discusses just how to get the most out Glenn Berry’s diagnostic scripts, specifically in relation to missing indexes. He also shows a create table syntax and highlights how effective it has been for him in his training sessions around Dev Ops.
https://www.kevinrchant.com/2021/10/12/t-sql-tuesday-143-two-of-my-personal-go-to-scripts/

Andy Mallon – Andy stores all of his useful scripts in a DBA database. It’s a popular approach and I was hoping someone would mention this. Andy goes beyond this though and has converted a lot of scripts into Stored Procedures. Whilst having a local scripts folder is great; if you can put your code into a stored procedure in a database which you deploy to all the servers you manage, there is no need to panic and find the scripts when the pressure is on. I must admit I love this approach and I’ll be downloading Andy’s database to look further into it.
https://am2.co/2021/10/t-sql-tuesday-143-my-favorite-short-scripts/

Jason Brimhall – Jason talks about all things endpoints here and I found the code examples so handy. I’ve already used them. Not only can you use them to validate your endpoints, but you can also use them to fix some issues as well. On a personal note, just what I needed.
http s ://bit.ly/3lAOMmF

Tom Zika – Tom shares loads of useful snippets including regex and t-sql. Wow – one regex snippet shows us how to find table variable declarations and turn them into temp tables. You could make a killing selling this one trick to consultants. Tom also shares a mega handy way to check permissions using impersonation as well as a great method to find referencing objects.
https://straightforwardsql.com/posts/short-code-examples/

Mikey Bronowski – Mikey shows us a handy way to execute multiple queries including dynamic ones and also tells us about agent_datetime() function. I have to admit I’ve never used that function but it looks so useful for when you are interrogating those msdb agent job tables. I will definitely be adding it my list. Finally, he shares a useful query for pulling back table data with his added enhancement (however a nice little plug for DBATools hints he now has a better way of approaching this).
https://bronowski.it/t-sql-tuesday-143-short-code-examples/

Todd Kleinhans – Todd focuses on Python and he is the only person to do so. I won’t give away his one liner but it’s just the sort of thing I was looking for. Are you feeling Zen?
https://toddkleinhans.wordpress.com/2021/10/12/t-sql-tuesday-143-import-this/

Mala Mahadevan – Mala shares some top class queries for interrogating query store. Query store has so much useful data that knowing how to get started querying it will be a big win for some.
https://curiousaboutdata.com/2021/10/12/tsql-tuesday-143-short-code-examples/

Chad Callihan – Chad mentioned 3 handy t-sql snippets and then shared a gem for keeping Brent Ozar’s First Responder Kit up to date. (Hint he uses DBATools). DBATools and FRK are amongst the most essential free tools for any DBA and beyond. If you run anything like sp_blitz or sp_blitzcache, it’s worth keeping it up to date and this method shows how to do it in only a few lines of code.
https://callihandata.com/2021/10/12/t-sql-tuesday-143-handy-short-scripts/

Deepthi Goguri – Deepthi shares some of the best of the rest by highlighting some of her favourite community scripts. From help with migrations to troubleshooting replication, it just goes to show that there’s no need to reinvent the wheel when there’s a perfectly good script out there that meets your needs.
https://dbanuggets.com/2021/10/12/t-sql-tuesday-143-short-code-examples/

Steve Jones – Did you know that you could get a tally table with just 4 key strokes? Steve shows you how, leveraging on the power of SQL Prompt by Redgate. This is taking snippets to a new level.
https://voiceofthedba.com/2021/10/13/t-sql-tuesday-143-short-code/

Jess Pomfret – Aloha to Jess who squeezes her entry in on time due to the Hawaiian time loophole. Want to find out if certain accounts are local admins on remote servers? Jess shares a quick and efficient method for finding this out. Being Jess, of course she is using PowerShell to make her life easier. I for one will be stealing this.
https://jesspomfret.com/t-sql-tuesday-143/

Eitan Blumin – Eitan takes the opportunity to link to some of his past blog posts which are full of useful code however he doesn’t stop there. With a new entry for T-SQL Tuesday, Eitan shows us how to move database files to a new location in Always On Availability Groups without breaking HADR. Ok at 374 lines, it’s a bit more than a snippet but it’s really great code so we’ll let that one slide.
https://eitanblumin.com/2021/10/13/t-sql-tuesday-143-powershell-move-db-files-alwayson-availability-groups/

Shane O’Neill – Shane also mentions agent_datetime(). It’s a cool function for converting the very user unfriendly ms format that we see in msdb tables. Shane points out it might not be the most efficient function however when you don’t have much data to bring back, it’s much quicker than rewriting the thing. Shane being Shane (Big Powershell fan) also points out a few great PowerShell commands for formatting and sorting and shows how they can be used in conjunction with other commands that yield really useful results.
https://nocolumnname.blog/2021/10/12/t-sql-tuesday-143-short-code-examples/

P.S. I’ve taken every bit of care to check my comments and on twitter but if I have missed your post, please let me know and I’ll include it immediately.

T-SQL Tuesday #143 – Short code examples

4th October 2021 By John McCormack 27 Comments

T-SQL Tuesday this month is going back to basics and its all about code. I’d like to know “What are your go to handy short scripts”?

What are those little short bits of code that you can’t live without? I’m talking about little snippets that are only a few lines, that you may even have memorised. It could be T-SQL, PowerShell, Python or anything else you use day to day.

e.g. I manage a lot of SQL agent jobs. Quite often, I need to find out which job has a certain t-sql string in the command so I’ll run:

SELECT * from msdb..sysjobs sj 
JOIN msdb..sysjobsteps sjs 
on sj.job_id = sjs.job_id 
where sjs.command like 'backup log%'

Of course, there are many other ways to find this out including DBATools commands but sometime I just revert to memory for convenience.

Another one I like is to get the estimated completion rate of a backup or restore. Now there are better scripts than this but sometimes, nothing beats getting a quick estimation back from a couple of lines of memorised t-sql.

SELECT percent_complete pc,*
FROM sys.dm_exec_requests
order by pc desc

My invitation to you for this month’s #tsql2sday is…

I would like you to share with the community what your go to script snippets are and why you find them useful. By sharing these, you will undoubtedly be helping someone who hasn’t thought of doing it that way, and hopefully you’ll pick up some handy hints as well.

Any language is fine, not just t-sql
Please share as many as you wish
Perhaps you never do this and always work off saved scripts or convert your snippets to stored procedures? Tell us why this works for you.

*** The Rules ***

Your post must be published on Tuesday, October 12th 2021 (in any time zone).
Include the T-SQL Tuesday Logo and make it link to this invitation post.
Please add a comment to this post with a link to your own so I know where to find it.
Please tweet about your post using the #tsql2sday hashtag.

Thanks for taking part
John

Free SQL Tools to make your life easier

9th February 2021 By John McCormack 2 Comments

I’ve written and spoken about free SQL tools to make your life easier on a few occasions. It was the subject of my presentation at SQLBits 2020 so I was delighted to see Mikey Brownowski (b|t) choose tooling as the subject for February’s T-SQL Tuesday #135. #tsl2sday.

In this month’s t-SQL Tuesday, Mikey asks us to “Write a blog post about the most helpful and effective tools you use or know of.” I use quite a few free tools. I love the way the SQL Community embraces sharing and I know I wouldn’t have achieved a fraction of my career success without free tools. In my SQLBits talk, I discussed the following free tools:

For this post, I will focus on Statistics Parser, written by Richie Rump (b|t). My blog has info on some other free sql tools as well.

Statistics Parser

Legend

Legend has it that Richie Rump wrote it during a Brent Ozar conference session. I asked him about this and he told me:

Well, I started it in one of his training classes. It was in Atlanta. It was the last day and it was mentioned that there was an Excel spreadsheet that parsed out statistics io output. I found that as interesting but odd. So I started out writing out a web based parser right there in class. The core of it was finished on the plane ride home.
Richie Rump 2021

So what does Statistics Parser do?

If you run SET STATISTICS TIME,IO ON before you run a query in SSMS, you will get information back on how much data was accessed and how long it took. Things like logical reads, physical reads, CPU time and elapsed time etc.

If you are only querying one or two tables, it is easy enough to just read this in the messages window. But what about those complex stored procs or queries hitting multiple tables and views that return a very long list of outputs? The output can be long and intimidating and certainly hard to understand at a glance.

Statistics Parser is a web page which allows you to paste in the statistics time,io output from the SSMS messages tab, and it formats into neat tables showing how much IO happens for each table. It is immediately easier to read and you get a handy % column on the right hand side showing you which tables are being read the most. I find this really useful for query tuning because it lets me know where my biggest pain points are. For complex queries, which touch a lot of tables, it just makes it easy to see at a glance where you should initially focus your attention. It also shows worktable and workfile tables which serves as a handy hint that tempdb is in play.

A really handy feature is that you can open multiple browser tabs, but give each tab it’s own name. Then you can paste the output from the original query in to a tab you have named as “before” or “original”, then give a name to each tab as you try something new. e.g. “index_abc_added” or “fixed_cursors”. I like to do this when working through query tuning options against a restored copy of production. I use it for tuning CPU and reads. I quite often find that if I can make a big enough impact on the reads, the CPU will also come down.

via GIPHY

How to use Statistics Parser

Run the following query in the same SSMS window as a query you are about to troubleshoot:
1. SET STATISTICS TIME,IO ON
Run your query
Copy/paste the message from SSMS into statisticsparser.com
Click Parse button
Open more tabs as needed

Thanks Richie

Thanks for making a great tool, free and easy to use. And thanks for answering my questions.

FYI

Attendees of SQLBits 2020 can still login using their personal link and see all the recorded content so my talk is there. In it, I spend a few minutes demonstrating Statistics Parser. I’m not sure if they will make it free to everyone else in future, like they’ve done in previous years.

Featured Image

Image by Lu Lettering from Pixabay

Pool breaks to avoid burnout in IT – tsql2sday

12th January 2021 By John McCormack 2 Comments

Avoid burnout in IT

This month’s t-sql Tuesday is hosted by James McGillivray (b|t) and he says:

Breaks are critical for our mental health. Write a post about relaxation techniques, dream destinations, vacation plans or anything else relating to taking a break for your own mental health.

Covid-19 WFH burnout

When we were first locked down in our homes in 2020, due to the first wave of Coronavirus, I felt like there was not much to do other than work. Football was cancelled, my kids activities were cancelled, we couldn’t go to the pubs or restaurants. So I worked. Extra hours were available to me and I just did them, often at my desk from 8-8. I had a small spare bedroom office which suited me fine when I worked from home 1 day a week, but it wasn’t ideal for spending so much time in.

I don’t know if I reached burnout but I know I was a bit fatigued. Other than to work as it was paying the bills, I had no appetite to do much else online. Friends and family were meeting by zoom and I didn’t feel like I wanted to join in. My own blogging productivity mostly stopped. I had been wanting to create some training content and I just couldn’t face it. I had an expensive mountain bike gathering dust because I just didn’t want to go outside.

New House

Fast forward to September and we moved house. I moved to a larger house with a dedicated garage/office. I know I’m incredibly lucky to be able to do this but it also allowed me one more thing, my own pool table. I’ve wanted one forever, actually I would have loved a snooker table but I couldn’t squeeze one into the space.

white two story garage — Outside the garage

John playing pool to avoid burnout in IT — Outside the garage

My short term escape to avoid burnout in IT

When I’m taking a break, I like to knock a few balls around on the pool table. Having something to do in my own house has been a godsend with other facilities constantly being shut down to prevent the spread of covid-19. It’s great to have the option to do this at home.

Relaxation goals

I’ve made it one of my 2021 goals to take 28 days holiday in a warm climate this year. We have an extended family holiday booked for Florida at the end of June. I don’t know if this will happen but I hope it does. I’d also like to spend two other single weeks away somewhere like Tenerife. Having these planned will break up the year and motivate me to work hard the rest of the time.

My wife and son went to Tenerife last January with my sister-in-law and niece. I could have gone but we knew nothing of coronavirus at the time and I thought I would just work to bring in some money, since I was on a short term contract. I was going to take a break in March and also planned on going to London and Lingen for SQLBits and DataGrillen during the year. Missing that trip was my biggest regret of 2019 because I ended up going nowhere.

Even if you can’t go anywhere, it’s still worth taking time off work to help avoid burnout in IT. I just hope this year that I do get to enjoy some sunshine with my family and friends.