Category Archives: TSQL Tuesday

I decided to resume tech blogging after a long break and this tsql-tuesday came in handy. This month’s blog part is hosted by John McCormack (B|T). He would like us to blog about handy scripts.

I use Query Store a lot where I work – and I’d like to share queries I use on Query Store DMVs that I find incredibly useful.

My favorite is one below, which I use to see stored procedure duration. It comes with other information including plan id, start and end time – all of us help me see red flags right away if there is any query not performing as well as it should.

SELECT q.object_id,object_name(q.object_id),q.query_id,max_duration, avg_duration, max_rowcount,
   p.plan_id,i.start_time,i.end_time
FROM sys.query_store_runtime_stats AS a
JOIN sys.query_store_runtime_stats_interval i
ON I.runtime_stats_interval_id = a.runtime_stats_interval_id
JOIN sys.query_store_plan p on p.plan_id = a.plan_id
JOIN sys.query_store_query q on p.query_id = q.query_id
WHERE q.object_id = object_id(‘dbo.myproc’)
order by i.start_time DESC

My next favorite one is one I use to find a plan based on text in the query.

SELECT c.plan_id, cast(c.query_plan as xml) , c.last_execution_time
FROM sys.query_store_plan C INNER JOIN sys.query_store_query B
ON C.query_id = b.query_id
INNER JOIN sys.query_store_query_text A ON
B.query_text_id = A.query_text_id
WHERE A.query_sql_text like ‘tablea’

The last one is duration of specific queries over time.

SELECT TOP 100 avg_duration/1000000.0 avg_dur_sec
FROM

sys.query_store_runtime_stats WHERE plan_id = 4962438
order by runtime_stats_id DESC

If you are reading this and not using query store yet – you must. Consider signing up for Erin Stellato’s precon too at the upcoming past community summit. It may be a good use of your time and money.

I am hosting 2019’s last T-SQL Tuesday of the year. I chose to ask people to write on gifts they had received – by way of community, relationships, or even tech features. A rather loose definition , as the one and only Aaron Bertrand calls it 🙂

I have a lot to be thankful for this year –

1 I took a strong liking to the graph data table feature in SQL Server and actually planned a book around it with another very popular author and mentor-friend. But the feature didn’t quite take off as I thought it would. But in the process, I relearned a lot of graph theory that I had learned in school, and also quite a bit about graph databases and how they work. Overall, it was a gift, although it didn’t quite pan out the way I thought it would.
2 I got more comfortable into my new role as dev/architect, which was a huge step away from DBA roles I had worked in for 20+ years. It has taken me a good two years to get comfortable with using GIT, working with devops as practiced at work, relearning TSQL and all that is needed in my job. It has been tough and very rewarding in the process. I consider the gig a gift, and have nothing but gratitude for the people I work with – in particular I would like to single out my colleague and dear friend from Spain – Efraim Sharon, our DBA team lead Mark Wilkinson, data science team lead Kevin Feasel and fellow dbas Andrew Pruski and Derek Fitzpatrick-Jolley. It is impossible to find a better group of people to work with.
3 I got back into speaking in a small way – i did two talks, one for the local user group and one for the PASS WIT chapter. Eugene Meidinger was my speaking mentor – he helped me with many tips and tricks, in addition to reviewing my slide set and giving valuable feedback. I consider it a true gift to have Eugene as my mentor-friend.
4 I was suspected of a serious illness and declared clear after many tests. It renewed my gratitude for the fragile gift that is life, and a renewed effort to appreciate every day I get and live it to the fullest.
5 I enjoyed hiking and exploring national parks in North Carolina and the Colorado mountains with my dear sister.
6 I attended my PASS Summit #16 and renewed many friendships, as well as made new ones. It remains to me the one place I go to recharge and refocus my professional life every year.

These are a few of my gifts for this year. I will end this post with the following quote and I believe this is important:
“When it comes to life the critical thing is whether you take things for granted or take them with gratitude.” ― G.K. Chesterton

This month’s T-SQL Tuesday is organized by Mohammed Darab(t|b). Mohammed has a really interesting topic – things you would say to yourself if you were 20. I am well past 20, and this is something I think about often. There are many,many things I wish I’d known earlier, that would have significantly changed my life for the better. But, life is intended to be just that – a series of obstacles and lessons we learn as we go along. If we knew everything to start with, there would be no obstacles and that’s not how it is intended to be. But, I would absolutely put down things I’ve learned the hard way that younger people or even older people with dissimilar challenges can learn and understand from. Below are a few of my answers, the answers that age taught me, and things I’ve learned, as a woman, a person of color, an immigrant and a woman in tech. Below are some things I’d have done totally differently.

1 Pick my battles. We live in a conflicted world. There is no way we are going to end up fixing every single thing we see that is wrong. I’d be annoyed or get turned off by a number of things starting with people violating traffic rules to the bro culture at work.It didn’t make my life any better to get involved in all of them. I’d say, now, to pick one or two that really matter, and understand that a lot of life is about just getting along.

2 Watch my back at work. I was given to being very open, and very candid about a lot of things, and often times went into battles without anyone looking out for me. Work situations are complicated, and people are in it mainly to make a living, not fight social causes. Now, I speak up on issues where I am assured of some sort of support. And if I am in unethical or immoral situations I just leave as quickly as I can.

3 Try less to belong, more to be who I am. As a somewhat lonely child I desperately wanted to belong. I’d often times hang out among people who were not like me in any way, or ponder too deeply on why some people are cliquish and others more accepting..on and on. I still sometimes have those feelings. Truth is that you can’t belong among people who are too different than you, and you can do absolutely nothing about other people having their cliques. Being who I am has attracted way more positive energy and like minded people.

4 Networking is about helping people. God, this lesson has taken a few decades. I am not extroverted, and for many years I had no clue what people said ‘networking’ was. I thought it was about brown nosing, going to golf, or visiting bars after hours..on and on. My volunteer work in the SQL community has led me to believe that quality networking is nothing more than honest, open availability to help people, and listen. I cannot even articulate how many doors opened for me after I learned this.

5 Nothing helps your career more than consistent efforts in any direction. As techies there are a billion different ways our paths can go, and most of us constantly worry about if we are doing this or that, compared to those other people who are. I worried a lot about not traveling and speaking as much as most friends do, not being an MVP, or a rockstar blogger..any number of such things. Truth is that consistent efforts in any one or two of those things pay rich rewards and we can’t be doing all of it. Spreading too thin is in fact a serious issue with many people. I’ve limited my efforts to things I enjoy – blogging, occasional speaking, writing, and volunteering wherever I can. That is how much I can do with my limited spare time. And the rewards it yields have been good enough.

6 Set small goals – I was given to setting very big, ambitious goals – like one blog post every week, getting through all certifications, and so on. Big goals are a form of self sabotage, albeit subtle. Few people get to achieve them, and somehow we all feel setting smaller goals is a lowly thing to do. It isn’t. Set small, measurable, easy goals, and document progress. It is true of every single thing – diet, exercise, tech learning.

7 Take vacations seriously – I spent a few decades of my youth working. I was a die hard workaholic, and the need to impress people was very strong. I canceled a vacation with my now late mom because of a reorganization at work. I never got to vacation with her again. We work to live, not the other way around. Vacations matter. Spending time and energy with loved ones is why we work.

8 Maintain community – I can’t really claim this is entirely something that I learned with age. Partly yes. As someone living many thousand miles away I learned the hard way that bonding between people, even people in your own family, needs effort and maintenance. Taking people for granted means you can’t guarantee the responses or regard you need will be available when you need it. It is the same with professional ties and with friends. It is partly why I make it a point to go to the PASS Summit every single year, when I have choices of other conferences. Meeting people in person is important. Saying hello, even once a year, and checking in on them matters and makes a difference. And no, just saying hi online is not quite the same thing. Take time for the people in your life. Invest in relationships. It is important.

9 All good things come to an end – This year marks my 16th year at PASS Summit. For the initial 12 years – I have nothing to show by way of photographs or memories. Some of those people are no longer around. I also don’t have any pictures of many people who were friends with me at earlier gigs, or neighbors or any number of things. These things matter. Nowadays I make it a point to capture those memories. It won’t be too long before many of us are old enough – those will be the only things that remain to remind us of good times.

10 Forgive and let go – Last but not the least. Don’t dwell on disappointments and resentments too much. I spent a LOT of emotional energy on this. One of my favorite quotes attributed to the Buddha is that holding a hot coal hurts the hand the holds it. I’ve basically learned that there are two kinds of people – those who are not worth it, and those who make mistakes and come around. The former are just that, let go of them when you learn it. The latter, forgive, include, accept. We are all human and mistakes happen. When you see that the person has a clear heart, and if he/she does, let go of the small stuff. The others, just not the right people for you, that is all.

I have learned all these lessons the hard way. But I have learned them. And it has made my life better. Thank you Mohammed for hosting!!

The last T-SQL Tuesday of this year is hosted by Jason Brimhall – one of my long-time friends in the SQL community. Jason’s topic is ‘To influence and to be influenced’ – call out times when something you did influenced someone , and you were in turn influenced by something that person said or did. It is a positive way to end the year on a tone of gratitude to people who have influenced you – and it is doubly positive/uplifting to see/hear of what you have done to other people too. 

What I did to inspire – This year was a big one for me because I became a published author with my first book – Data Professionals at Work. My book is a collection of stories of people in the data industry – their journeys, careers and choices they made along the way. I had a great time putting it together, and I did not expect anything more than a ‘thank you’ (which I got) from all the people who were part of it. I was, however, tremendously moved by how big it was for some people – particularly those who had never had their name in a book before. I saw them post proudly on social media about being featured in a book, and their loved ones/friends respond on how proud they were. It made me feel tremendously humbled and thankful to have done it. I was also very humbled by how many celebrities at the PASS summit wanted my book – some even with autographs on it. These were people I have been following for years, and had many books/awards to their credit. 
What I was inspired by – I always attend the board Q&A when am at PASS summit, as am interested in the direction the organization is going and the answers they have for many people all over the world. Sometimes the answers have left me feeling good, and other times it has been lack lustre. During the past few years I’ve seen an increase in arguments and debates, and even watched people leave openly angry/unhappy and so on. This year was a good year – exceptionally good, in fact. Questions were posed in civil, polite but candid ways – and answered to-the-point by everyone up there. The meeting ended on a hugely positive note (I don’t mean to say everyone there was happy – there were things people were not happy about but the atmosphere was one of civility and optimism). I was inspired the people on the board who made it happen. It is not an easy task, being there – and they are all volunteers with jobs/families and responsibilities like the rest of us. They were able to put on a great show and a create a civil environment with positive interaction. It was inspiring. 

Thank you Jason, for a good topic to finish the year with. Happy 2019 to all.

This is November and T-SQL Tuesday time,
I hope you are ready to read the summary – I am the host and it’s mine.
I’d like to begin by saying thanks to you two –
Adam Machanic and Steve Jones,
to whom this is due.
Thanks for keeping this blog party going,
Thanks for letting us write,
It matters, to many, to bring their thoughts to light!

The topic is what you are learning,
on not just SQL Server, but other things too,
with the data world ever-expanding,
there are more things to learn and do.

Bert Wagner documents how to learn rather than what.
It is useful to know that too, there are many who do not.

Eugene Meidinger talks of learning Power BI,
Knowing how to visualise data is important –
learn it now or you will be left out knowing not why.

SQLKohai talks of ‘can Powershell help out here?’,
Of course it can, in most places, learn it or you will soon disappear!

Andy Levy explains why he wants to learn Postgres on AWS – that sounds tough,
and with MongoDB,DynamoDB,CosmosDB – all the NOSQL stuff!

Shane O Neill likes to learn something called Pester,
Know it and you write better tests for powershell – an important skill to master!

DBAGooner has something non tech to say,
He talks of going back farming and gardening,
As people desk bound to machines, many times for pay,
that is a brave thought for sure – and is very heartening.

Rob Farley from down under – has Powershell again,
he recommends Excel too to excel in,
they are both foundational gains.

Jeff Mlaker talks of learning Linux and Python,
he explains why he chose Red Hat, the advice is right-on!

Kevin Chant explains learning Linux for a Big Data project,
It helped him get Hadoop running on RedHat and CenTOS and all that!

Jon Shaulis talks of learning Python and how-to,
He’s into analytics, and big data, and machine learning too!

Todd Kleinhans is into virtual reality,
with Oculus Medium and Unreal Engine,
it provides escape and is quite a speciality!

Ken Fisher who is a blogger prolific,
talks of broadening the ‘T part’,with Azure, AWS, Managed instances
and anything specific!

Glenda Gable talks of learning to be more hardware-aware,
and learning about monitoring,
that makes a DBA non-compare!

Jess Pomfret talks about mastering Powershell DSC,
containers and Kubernetes – lets learn it now, shall we?

Meagan Longoria has on her list – presentation skills to master,
her cool talk on visualisation made it to the top talks at PASS summit this year!

Drew Koballa is that rare sysadmin-cum-developer,
he is learning on Developing Azure functions for Restful APIs,
sure sounds terribly wise!

Matt Gordon is doing more with Azure Logic apps and Cognitive Services,
he’s going to teach us more ,
for coordinating workflows – let us wait for his talk to see how they serve us!

Kamil Nowinski talks of PowerBI, Azure Data Factory, Data Bricks and Azure Datawarehouse –
He also explains how you get there, make use of it, be an Azure power-house!

Jason Brimhall is after Powershell too – he adds in knowing MySQL,
keeping skills honed, sharp and true.

Chris Hyde is an awesome BI and Analytics guru,
he wants to learn more of Azure, Python, and Seaborn too!

Sander Stad is into Continues Development/Integration,
He likes Octopus Deploy, and more automation!

Janice likes statistics and data science – she has book recommendations too,
Check it out to see if it is right for you.

SQLZelda and me, we were the last to write,
She wants to learn Docker and Containers,
I want more of R,
our future is bright!

So the summary is here – Azure, MySQL, Powershell, R, Python, Linux and PowerBI,
Learn for your jobs, learn as life goes by,
learning is fun too, lets keep learning until we die!

Thanks for participating!

 

• The primary application of knowledge is at work. You want to think of upcoming projects or opportunities to apply this knowledge. For most people this comes up at a performance review that happens early in the year. Many people are also not comfortable making it public. If it is not bloggable that is ok – but if it is bloggable do consider sharing it. 
  
• What are the chapter meetings, events you plan to speak at? If that is too much detail, consider how many of those you’d want to do.
• What is the frequency of blogging you’d like to maintain?
• Are you planning on writing books or coauthoring any?
• Are you  planning on participating in forums to answer questions – such as on SQL ServerCentral.com?
• Are you planning on any other group contribution – such as Idera’s #sqlchat on twitter or even answering #sqlhelp questions on twitter?
• I’d put certifications and tests too in this category as they give a name to what you learn and add a credential.

This month’s T-SQL Tuesday is hosted by Ewald Cress, and the topic is ‘Folks who have made a difference’. This is an opportunity – and right around thanksgiving – to give a shout-out to people who have made a difference in our careers and in the data community. To me this list is so long that I would need several blog posts to write about every person on it. But am going to pick one person, and for a very good reason. That person is Andy Warren(b|t) – the founder of SQL Saturdays.
Back in 2008, I was actively running the local user group here at Louisville, and volunteering for PASS in several different capacities. I was always looking for other opportunities to bring training to the local community, that was more than one hour talks we had at user groups. Part of the reason I wanted to do this was because the vast majority of people I worked with and attended user groups were folks who had never attended any kind of formal training.Very few companies paid for them to go, and the idea of investing your own time to learn was very new, back then. You learned what your boss wanted you to learn, and if he gave you time/money for it. That was the general attitude, and one that struck me as very wrong, although I didn’t get very far trying to say that to most people. I met Andy at the networking dinner that he and Steve organized, at the summit. He asked me if I had heard of SQL Saturdays. They were free day long trainings, and could be held anywhere. It was interesting, but I was apprehensive. People attending training on saturdays? In a little town like this? I wondered about it and when I got home – I called my contact at a local school on a whim, just to find out if they had any classrooms they could lend to us for free on a saturday. The answer was a very emphatic ‘yes’, of course they could. Their rooms were small but they had 4 rooms, plus a nice big hallway, and small private rooms too. They’d like the exposure in community and actually wanted to do it. This looked like something that wanted to happen.
I registered on Andy’s site, picked a date and then looked to him for guidance on the rest. He guided me through it every step of the way, with constant reassurance that ‘if you build it ,they will come’. SQL SAturday #1 was event #23 for PASS. It was held at New Horizons, with 60 people in attendance(I expected about 40 people), 4 speakers(Arie Jones, Allen White, Kevin Kline and Dave Fackler) and two sponsors. I think we had about 3k as sponsorship money to run it. Almost every attendee said emphatically about how much they loved it. New Horizons wanted to host it yet again. The upcoming year, 2018, will be our year #10 running SQL Saturdays, in a row. Every year, we have been getting bigger and better. Hundreds of people have received free training, so many careers and lives have been touched.
I would never have done it if it was not for Andy talking me into it. I think every one of you who has been part of SQL Saturday Louisville need to be. Thank you Andy, for showing us what we could be.

This month’s TSQL Tuesday is organized by Kennie T Pontoppidan(t) – the topic is ‘Daily Database WTF‘ – or a horror story from the database world.  As someone who has worked databases for nearly two decades, there are several of these – I picked one of my favorites. During my early years as a DBA, I was doing some contracting work for a government agency. At one of these agencies I encountered a rather strange problem. Their nightly backup job was failing on a daily basis. The lady DBA in charge was very stressed trying to solve it. It was a TSQL script written by someone who was no longer with them. According to their process, every failure had to be extensively documented on paper and filed for supervisors to review. So she had a file on this particular job failure that was bigger than the yellow pages, on her desk .She tasked me with finding out the reason for this job failure, as my first assignment.
I dug into the script – it was simple – it pulled an alphabetical list of databases from system metadata and proceeded to back them up. It didn’t do this one simple thing – leave TEMPDB off the list. So when the backups got down to TEMPDB, they promptly failed. Now as a smart person – I should have just communicated this to her and had it fixed quietly. But, I was young and rather hot headed at that time. It amazed me that a DBA with several years of experience did not know that TEMPDB cannot be backed up. So, I waited until the team meeting the next day. And when the said job failure came up again – I said that I knew the reason and stated why. I also added that this was a ‘very basic  thing’ that junior DBAs are supposed to know. I was stopped right there. It did not go down well. Her face was flaming red because a consultant showed her up in front of her boss in a bad light. She said she would talk to her boss and collegues the next day (several of whom were nodding their heads disapprovingly at me) and meeting was abruptly adjourned.
On my way back, I heard several people whisper that she does not like or do dissent, and I did a very unwise thing by pointing out something against her in public. I told myself that working with such a person would not be the right thing for me. When I came in to the office the next day, the HR girl was waiting at my desk. I was escorted to her office and told that my behavior was not acceptable coming from a consultant, and that my contract was terminated. I still recall being walked to the door with my jaw half open. Before she shut the door on me I said ‘there are still issues with that script, if you want to know’. Well sure enough they didn’t, and I went home. I really, honestly felt amused and free, despite the fact that I had no job to pay my bills.
In another week’s time, I had found another gig and moved on. I heard later about the issue I had not talked about. The databases that lined up after TEMPDB alphabetically were never backed up. In months. They had had a massive system failure and found out that they had no backups to recover from it. I don’t know if the lady in charge or her friends suffered any consequences, it was a government job after all and they could not be easily fired. But I do know that they found out the hard way and somebody probably paid for it.
I always narrate this incredible story to people who get fired for things that they did right, and also to watch your back in politically sensitive situations.