Author Archives: diligentdba

This month’s T-SQL Tuesday is hosted by Deborah Melkin(b|t), and she has an interesting topic. She wants us to write a rant on a scenario we encountered at a client that has the common ‘it depends’ stance go right out of the window..or in other words, something that is so bad that there is no way it should be sustained practice, ever.
Its been a few years since I left consulting. But the last gig I was at – we encountered something like this. We had a big client who had outsourced all their database development and manual update work (no not to us, to some third-party contracting company). These were contractors paid by the hour, and the turnover was really high. Our client did not want to issue windows based authenticated logins to these people for some reason (do not recall what). So every week, when the week started, the contractor working on a particular server would get a SQL Server authenticated login they could use. This was valid just for that week and would expire the next week. And, every weekend , it was our job, as the remote DBA company, to set up those logins. We would get an excel spreadsheet with *hundreds* of names, and what kind of server/database/level of access was needed by the person. We then had to get on the server and painfully create these logins with the right access. We had to set up the password to be changed to something else on first login and set up the login itself to expire in a week. This was an entire day, sometimes two days’ worth of work to set up. And they *paid* us to do that! We tried some degree of automation, like writing some code to read the excel sheet and automatically create these logins. But, it didn’t work too well – because there would be some access needed that wasn’t the norm before, or some new database, or something or the other. Surely, if you want to outsource your work and care that much about security – there could be better ways? Some of the conversations/reasoning we had with this client, trying to explain *why* this approach was so problematic even if they were crazy enough to pay us to do it – as below.
1 Outsourcing work to a place with so much turnover can cause a ton of problems. No one person knows or is responsible for what came before them.
2 Having this many SQL Server logins is also a huge problem – there is no guarantee that people aren’t sharing their passwords (we actually caught several people doing this).
3 Having two third-party companies (one was us and the other was the contracting company) – with you the customer in the middle is in itself problematic. Ultimately your data and its security is your problem, not either of these company’s.
4 The ‘it depends’ clause does not belong at all in a scenario like this. Invest in paying people well and they will take good care of your data. Operate on principle of least privilege and your data will be more secure. Don’t pay someone to do trashy work with the line assuming that they will do it for money.
Needless to say I was really happy moving on from doing this sort of work. And am extra careful when I hear of companies outsourcing their stuff for cheap – you can run into all sorts of ridiculous scenarios like we did here. Thanks for reading.

I am glad to be contributing to the 150th blog party started by Adam Machanic and has helped so many get our blogs going. This month’s T-SQL Tuesday is hosted by a dear friend Ken Fisher – Ken wants us to write on our first technical job.

The year was 1987. I was an engineering dropout who had learned some COBOL, and some DBASE at a tech school liked the latter better, and was looking for a job. It wasn’t an easy time to be a techie. Most tech jobs expected engineering degrees, it was much much later that non-engineers would be able to make their way into tech jobs. The transition from paper-based systems into the digital world was threatening to a lot of people. There were union-led strikes at banks and various government-run organizations with people objecting to the ‘new computers’ taking away their jobs. I made a few bucks teaching some COBOL on the side but was desperate otherwise to find a gig. In those days newspaper ads for jobs were very common. One ad caught my attention – a ‘Data Systems Manager’ role, at a textile company. I got my resume together and highlighted my experience with DBase and an accounts payable project that I had done learning it. Included some reports, printed on ribbon-based dot matrix printers, line by line – I can still hear them run in my head :). Two days later, I got a call for an interview. The interviewer was a 65-year-old man, an expat from the US, and getting ready to retire. He was the only person they could find who could ask me any tech questions 🙂 He asked me a few easy ones, and then asked – why DBase over COBOL? I like the GUI and ease with which I could play with data, I said. He was impressed. I got the job and started.

The first few months were fun, writing DBASE programs for their Accounts Payable, Accounts Receivable, and Payroll. After that, the pollution at the place got to me. They stored clothing, and there was a lot of dust. The building was old and there was plenty of mold. Two years later, I got really ill with severe allergies and lung congestion. I had to quit that job, learn more FoxPro and other software and move on to other stuff. I learned the hard way to never compromise on health for any job. It is like driving without time for gas. It never works.

That gig taught me about my passion for working with data though, and that carried me a long way. Thanks, Ken, for hosting.

This months’ T-SQL Tuesday blog party is hosted by Rie Merrit (t|b) as part of Azure Community Group lead. Her call is to pick one or two things that work for running a user group and blog on it.

I am co lead at two user groups – the Triangle SQL Server User Group and Data Platform WIT virtual group. I will write here on two topics – finding and keeping a consistent audience, which I have learned from Triangle SQL Server User Group, and finding what works to keep the group going – which I learned from Data Platform WIT group.

Lessons from Triangle SSUG: Finding and Keeping a consistent audience
The Triangle SQL Server User Group is primarily led by Kevin Feasel(b|t). The rest of us on the board are me, Tracy Boggiano(b|t), Mike Chrestenson, and Rick Pack(t). When I joined the team they were already going strong with 3 meetings a month – each themed around DBA/Advanced DBA and Data Science respectively. Each meeting had a different location and sponsor and their own audience, with some overlap between DBA and Advanced DBA. When Covid hit and in-person meets went away, we brainstormed on what our strategy would be, to keep the show going. We had the following challenges.

1 It was difficult to find speakers for the data science group. There were few people and the community around it was a bit scattered.
2 Networking with virtual meets was limited and that was a big value people got out of our in-person meetings.
3 We had to make virtual meetings attractive enough to keep the audience we already had.

The challenges with keeping a physical location and finding sponsors were no longer relevant, thankfully – although that might be back soon. But we made the following changes to how we operated.

1 We expanded the focus of the data science group to include BI topics. This made it easier to seek speakers from among the BI #sqlfamily community. It has been much less of a challenge and going well.
Lesson: Broaden your topics if you are not getting enough people.

2 Kevin created a bi-weekly chat show called Shop Talk – we get on the air and talk about various tech topics and address any issues the audience wants to talk about. The show has had a dedicated audience and has been going really well. Granted, I will readily admit that Kevin is the star of the show because of his ability to speak with ease on any data topic and offer expert-level advice for free – but my take is that even if you don’t have a Kevin, and you don’t want a regular show – you can try the occasional online chat and invite the audience to weigh in. People like engagement and like to listen to topics that interest them.
Lesson: Try something different, like chat shows, every now and then if not regularly.

3 We did a few day-long virtual events – we had some attendance but not a lot. It wasn’t worth the effort and we don’t do it anymore.
Lesson: Don’t waste time doing things that do not gain audience traction.

4 For regular ug meets – we work hard on finding diverse topics and speakers. We look at social media for talks that speakers have, and also on listings like the one on Azure Community groups and ask the speaker if they would like to talk for us. We keep a consistent time and day on which we have the meets – no compromises there. This helps the audience to plan their availability easily. These things have really paid off. Our average user group attendance is around 20 people and some talks have up to 40 people tune in – several from various parts of the world, not just ours.
Lesson: Find diverse topics and keep consistent timings so that audience knows when to tune in.

Lessons from DPWIT: Finding what works
I joined the DPWIT group last year when PASS dissolved and Kathi Kellenberger was looking for a co-lead. We are two years old as of this March. The primary goal of the group was and it continues to be to empower women in tech by highlighting what they do – via speaking or other means. For six months we continued to host tech talks as it had been in the past. We suffered a low audience – mostly because there was an abundance of tech talks, several were already recorded and on youtube. Then we decided to put on day-long tech events. This was a huge amount of effort and still didn’t get a lot of traction. Then, Kathi and I did some interviews with the women who were going to do pre-cons at PASS community summit. These interviews were spontaneous, a lot of fun, and to our surprise got significant viewership as well. So, this year, my new co-lead Leslie Andrews and I decided to stick with doing interviews with tech women – mostly those who were not very famous in the community and needed high lighting. We have done two so far and it has been going really well. We also continue to write the monthly newsletter, keeping up with what Kathi used to do in this regard.
Lesson: Experiment and find what works well to get a decent audience. There is a huge range of options. You may fail a few times before you succeed.

Last, but not least – make sure what you are doing as a user group lead energizes you. If not feel free to give it up. When I gave up running SQL Saturdays , which I ran for 12 years in a row at Louisville – I felt really low and thought would miss doing it a lot. I do miss doing it – but lots of other things have taken that place. It was right for a certain time, but no more. Life goes on. Stay energized with what motivates you, and stay connected to the community in ways that work for you!! Thank you, Rie, for hosting.

I am trying to get started on blogging again this year…T-SQL Tuesday came in handy with a great topic. This month’s host is Andy Yun(t |b). Andy’s challenge for us is to write about some notion ‘something you’ve learned, that subsequently changed your opinion/viewpoint/etc. on something. ‘

There are many things on which I’ve changed my opinion in my journey as a data professional. For this blog post, I picked a common one – where business logic should reside. During my years as a production DBA – there was (and probably is) a hard-held belief that databases are meant for CRUD operations only and business logic belongs in the application/middle tier. This belief has its place – DBAs don’t like debugging code that is application-specific, or be tasked with why data looks a certain way or what code caused it. Business logic can also be very complex and get deeply embedded in one place (the database). I have believed in this and fought for this at most places I’ve worked as a DBA.

Over time though, my opinion has changed. The fact that DBAs have to learn some degree of programming to survive today’s world has a lot to do with it. Also, I started to understand the nature of business logic better. I learned that a lot of things like foreign key constraints, not null constraints, triggers actually have to do with business logic and how the business defines its data. Databases usually outlive applications and less work is necessary when there is a move to a new application layer. It is much faster to make changes on the database side and roll it out compared to most application changes. It also offers single-point control of business logic, as opposed to it spread all over the place in applications.

And last but not least – learning business logic makes a person an important asset to the business. Most DBAs complain about being ‘just firefighters’…if we know business logic and have control over that, that makes us more than that – it makes us an important asset, in addition to helping us learn more programming as part of that process.

I do realize that there are cons to putting business logic in the database too. It can be very complex in some cases. The business may not have enough people to maintain it. It is a single point of control, therefore a single point of failure too if one looks at it that way. But now I believe there are more advantages to it than disadvantages. Thank you, Andy, for hosting.

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.