front-page Archives - Page 2 of 15

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

How to change the slow query log threshold on RDS

19th August 2021 By John McCormack 1 Comment

What is the slow query log

Before we discuss how to change the slow query log threshold on RDS, let’s quickly establish what the slow query log is.

The slow query log will record all queries which are above the threshold level. The default value is 10 (seconds) but you can set it higher or lower depending on your requirements. It is useful for finding slow queries and allows you to pick out candidates for tuning.

If you set the threshold too low, it can increase I/O overhead on your instance and use a lot of valuable disk space. If you set it too high, it might not capture enough useful information.

AWS Web console

If you are doing this change as a one off, it might be simpler to just use the AWS web console.

Log into AWS
Select RDS
Find out which parameter group your instance is in
1. Select instance then click configuration.
2. Scroll down to see the parameter group
3. It will be a hyperlink so just click on it
In the parameter group page, use the search box and search for slow_query_log
1. Ensure it is set to 1
2. If not, click edit parameters and change the value to 1.
Change search box to long_query_time
1. Set it to any value between 1 and 31536000
2. Save changes

AWS CLI

aws rds modify-db-parameter-group --db-parameter-group-name "primary-mysql-5point7" --parameters "ParameterName='long_query_time',ParameterValue=1,ApplyMethod=immediate" --profile dev
# If you don't have multiple profiles set, leave out --profile dev.

AWS PowerShell

$HashArguments = @{
    DBParameterGroupName = "mysql5point7-monster-param-grp-ci"
    Parameter=@{ParameterName="long_query_time";ParameterValue="10";ApplyMethod="Immediate"}
    Profilename = "Dev"
    }
Edit-RDSDBParameterGroup @HashArguments

No restart needed

Changing either slow_query_log or long_query_time parameters can both be safely done without the need to restart your instance. This is because RDS defines them as Dynamic parameters. Take note when changing parameters that are defined as Static as they will require a restart. This either means short but immediate downtime or waiting until your next maintenance window.

Balancing work and life – The COVID years – tsql2sday

10th August 2021 By John McCormack Leave a Comment

For this month’s T-SQL Tuesday, Tjay Belt (b|t) asks about balancing work and life. He lists some options and I decided to write about What rules have you implemented that made it easier to balance?

Balancing work and life

The initial shift to working from home didn’t phase me. I thought it was just a short term measure to keep people safe. I found that my team became more productive and there were high spirits initially. I was happy to work overtime and spend time on community work. (Looking back, I spent far too long at my desk)

The period up to July/August I was very productive with blogging and creating content for online presentations and YouTube videos. I then felt a little flat and my productivity dropped off a cliff. Similarly I had a small boost in February when I felt motivated. So I had to come up with some rules.

Striking the balance – The rules

I have adopted a few rules and try to stick to them but I don’t stress out about it. If I need to work on, or I don’t want to go outdoors, it’s not a big deal.

Try to finish work at 5:30
Spend 30 minutes outdoors every day
Have some fun

Try to finish work at 5:30

Honestly: finish at finishing time. Don’t work for free. It’s my number one tip for balancing work and life. The work will always wait for you. Now I’m not saying this is possible 100% of the time but it should be your aim. For example, I’ve had to deal with production issues that can’t wait but on the whole, I’m referring to the ticket queue and requests from end users. I’ll also take time back if I’ve had to work on at a time that suits me and my company.

My employer also introduced a new perk called Summer Hours where all employees get every Friday afternoon off during July and August. It’s such a fantastic employee benefit which is worth 4 extra paid days off per year.

Spend 30 minutes outdoors

I attended an online seminar at my work (Monster.com) talking about mental health. I was inspired by one of the speakers who had set up a podcast and blog called humansoutside. She described that simply spending 30 minutes outdoors every day had had a profound effect on her’s and her husband’s mental health. You should give the podcast a listen.

View this post on Instagram

A post shared by Humans Outside® (@humansoutside)

So now I try to spend some time outside each day. Whether is working in the garden, going a walk or just taking refuge from the rain under my Gazebo to drink a coffee, it is a refreshing habit.

Have some fun

I mentioned on a previous post that I had got a pool table at home. I’m lucky to have the space and I really use it all the time. I aim to clear the balls once a day. If I can play more and get my kids involved, even better.

John playing pool to avoid burnout in IT — John playing pool

I’m also a bit of a telly addict. During lockdown, I re-watched the series Curb you enthusiasm. It’s cringe comedy but I love the pedantry. I also re-watched Mad Men which is one of the all time greatest box sets (imho).

I try to have a box set on the go with my wife as well that we only watch at the pace where we can both manage it. (No sneaking ahead, even although sometimes it is tempting)

I hope this helps. Do whatever works for you but do try to do something actively to ensure you strike a work/life balance that works for you.

Why I certified to become more than a SQL DBA

11th May 2021 By John McCormack 2 Comments

For this month’s T-SQL Tuesday, Andy Leonard (b|t) asks How Do You Respond When Technology Changes Under You?

It’s a great topic and inspired me to write a T-SQL Tuesday post this month. I’ve missed the last couple due to a combination of just not feeling like it, and the previous topics weren’t best suited to me. Hopefully this can inspire me to get writing again.

Hey, I thought I was the SQL DBA!

When I got my start with SQL Server, I was just that – A SQL DBA. I worked as a junior member of a big team, we had a large SQL Server estate and I just wanted to learn to become as experienced as the senior folk. I was able to learn and be mentored and was enjoying making progress. Then I heard we were going to start using “The Cloud”. Not only that but I was going to be responsible for the operation of our Kafka clusters, EMR and ElasticSearch.

A lot of these technologies were developer led, they knew why they wanted to use them and from an operations perspective, it was about making sure things didn’t fail, and errors were handled. I had no background in them, and didn’t really understand them well. Simply learning to use PuTTY to log into a linux VM was one of my early successes.

AWS Certifications FTW

The move to the cloud, in this case AWS was going ahead with or without me so I had to embrace it. Of course, I learned and collaborated with colleagues and got to an acceptable standard but it was only when I decided to do AWS certifications that things really took off. I found that by studying for the AWS Cloud Solutions Architect certification that I got an appreciation for all cloud services, not just the data ones like RDS. Studying for the AWS certifications improved my networking skills; I learned about object vs block storage and of course I learned all of the different data products (often managed services but not always).

I approached the certification process from a genuine perspective of learning the technology well, rather than just passing the exam. Simply passing the exam does not do much for you. Even if it gets you a job, you will likely be found out pretty quickly.

A further benefit to learning one cloud technology well is that when we started using Azure, I found the concepts were the same. I understood the networking, the security groups and PaaS vs IaaS. It made this transition much easier and meant that when I moved to a job that was predominantly based in Azure, I didn’t have the huge knowledge gap that I would have had, if I had not done my AWS certifications.

So for me, a structured learning approach is best. It’s not for everyone but I am certainly glad that my job is no longer just “SQL DBA”.

You don’t need to run CHECKDB on Azure SQL DB

12th February 2021 By John McCormack 2 Comments

right or wrong sign for dbcc checkdb — Featured image by Tumisu from Pixabay

CHECKDB on Azure SQL DB?

This is a selfish post because I’ll probably forget and go googling for info on this topic, so I though I would link to an authoritative answer on the question, do you need to run DBCC CHECKDB on Azure SQL DB?

https://azure.microsoft.com/en-gb/blog/data-integrity-in-azure-sql-database/

Microsoft distinguished engineer Peter Carlin says you don’t need to, it is monitored by Azure on your behalf. And if something was to go wrong, they will also work with you to put things right.

Although Peter says you can run them if you want, his post gives me enough peace of mind to stop running these on my Azure SQL DB instances. Let me know if you agree in the comments.

For more Azure content, check out my Azure tag.