T-SQL Tuesday #124: Using Query Store (and why you might not be…yet)

T-SQL Tuesday

T-SQL Tuesday

 

This month’s T-SQL Tuesday topic is courtesy of Tracy Boggiano and it’s related to using Query Store!  Tracy asked people to write about their experience adopting Query Store (or not, if you haven’t tried it yet or have had issues). I have so much to share… 😊

I can spend a full day talking about this favorite feature of mine, but will do my best to succinctly summarize the usage patterns I’ve seen with customers.

Successful Adoption

We have several customers that turned Query Store on the moment they upgraded to SQL Server 2016 or higher and were off and running. We have one that uses Enterprise Edition and also uses Automatic Plan Correction, and there are some plans that we force manually as well.  Those customers have parameterized queries and procedure-based workloads.

Delayed Adoption

There are a few customers that upgraded to SQL Server 2016 or higher and had concerns about Query Store. In one case they already had a third-party monitoring tool in place and didn’t think they needed it. Other customers were very hesitant to make changes to the environment, and wanted to do testing with it – after the upgrade – to make sure there were no adverse effects. In all cases, the customers finally implemented Query Store, with success.

Successful Adoption…but not without some struggles

Last fall we had a previous customer reach out for help after they had to wait 45 minutes for a database to come online after a server reboot. The database queries were blocked by QDS_LOADDB waits. There were three things in play here – the first was that they had CAPTURE_MODE set to ALL, and it should be AUTO. Second, they didn’t have trace flag 7752 enabled (the behavior of which is now the default in SQL Server 2019). And the third was that their Query Store was 100GB in size. The workload was fairly ad hoc, so these three things together caused the problem initially described. They implemented the TF, made multiple changes to the settings (set CAPTURE_MODE to AUTO, changed MAX_STORAGE_SIZE_MB to 10GB, decreased CLEANUP_POLICY to 3 days), and then Query Store was usable for them.

Failed Adoption

Lastly, there are a few customers that have been unable to implement Query Store because of their extremely ad hoc workload. For one customer, it was very frustrating because they benefited from plan forcing. At the time, they were running the latest version of SQL Server 2017 but ran into the bug where the transaction log would fill up (fixed in 2017 CU16 and 2016 SP2 CU8). Even after applying this CU and having optimal settings, they were still unable to use Query Store because of their workload.

Query Store is *still* a possibility

For anyone with an ad hoc workload that is at the point of dismissing Query Store, I recommend that you verify you are following best practices, and it might be necessary to look at SQL Server 2019 or Azure SQL Database. There is new option, CUSTOM, for QUERY_CAPTURE_MODE that allow you to control exactly what queries are captured based on execution frequency, compile CPU, or execution CPU. Further, the SQL Server team continues to actively evolve the Query Store feature and address issues as they are found. If this is a feature you want to use, and you are running into a problem, you need to open a User Voice item.  The SQL Server team can only fix the problems and limitations they know about.

#TSQL2sday: My Life Hacks

 

T-SQL Tuesday

T-SQL Tuesday

Jess Pomfret (newly awarded MVP!) is hosting month’s T-SQL Tuesday, and asked us to share our favorite life hacks – something that makes our day easier.  I have two that I consider indispensable.

The first is Toggl, a free app that Kendra Little introduced me to years ago.  As a consultant, I have to track my time so we can accurately bill customers.  Initially I tried to write it down on paper, but I ended up with MANY pieces of paper scattered across my desk.  Organizing those at the end of the week was not fun.  Then I tried Excel.  Nope, too much work.  Enter Toggl:

Tracking Time in Toggl

Tracking Time in Toggl

 

It’s extremely easy to use, it synchronizes with my account so the information is available online if don’t have my laptop for some reason and need to know the time spent on a project, and as I said, it’s free.  There is a paid version and it has a lot of cool features that I don’t even use or know about it.  It just works.

My second hack is movies.  If I’m not on a call or dial in to a customer, I have a movie playing in the background.  It’s always a movie I know well, and a movie I really like.  A lot.  I mean, I’ve listened to some of these hundreds of times.

A few movies...

A few movies…

When it’s completely quiet, I find it harder to focus.  I need something in the background to keep some part of my brain just a little bit distracted, which somehow makes everything else clear.  I just play it on my phone, no TV, no fancy speakers.  There is probably science behind it – sometimes music works, but mostly you can find me listening to any Marvel movie, Apollo 13, The Martian, Miracle, Top Gun, A Few Good Men, or Band of Brothers (not a movie, but close enough).

While typing, I thought of a third hack…my standing desk mat.  I have a “plain” one, but my husband got me this one a year or so ago and I really like because it has different shapes and textures for my feet.  Sometimes it’s the little things.

 

T-SQL Tuesday #41: Presenting and Loving It

I’ve been on a TSQL Tuesday hiatus, which was completely unintentional.  When I read Bob Pusateri’s topic for this month I knew I had to end my sabbatical and get back in the swing of these posts.  Bob’s question was, “How did you come to love presenting?”  Oh Bob…how much time do you have?  🙂

It goes back to graduate school.  I’ve blogged before about my mentor, Susan Brown, and in my original homage I mentioned that I would not be the speaker that I am today, were it not for her.  And I said that then, and still believe it now, because she found and created opportunities for me to speak publicly, and she provided feedback and encouragement – two things absolutely vital for any speaker to improve and succeed.

During my first year of graduate school the School of Kinesiology held a research day, designed to bring the entire department together to showcase our research efforts.  It’s very easy to have no idea what other researchers are doing not just within the University, but even within a small department like Kinesiology.  The idea was to explain our research, what we’d learned, and share ideas.  I gave a 10 minute session on the research we were doing with botulinum toxin (yes, Botox before it was cool for cosmetic reasons) and its effects on upper limb function in children with spasticity.  I was terrified.  I had spoken in front of groups before – I took a Communications (read: public speaking) class my junior year, I was a leader in my sorority (yes, you read that right) and spoke often, and I had done campus tours during my senior year (Bob has a great story about tours in his post).  But speaking to hundreds of people, who were my peers and professors?  That was a whole new ballgame.

I can’t remember how many slides I created, at least 10, before Susan told me that she typically used one slide for each 10 minutes of a talk.  I remember thinking she was crazy…talking for 10 minutes in front of the entire department (and many other researchers from different areas of the University) seemed like an eternity.  [What’s ironic is that I can’t always finish recording a SQLskills Insider Video in less than 10 minutes these days.]

At any rate, I remember standing at the front of the room in the Michigan League Ballroom feeling incredibly uncomfortable.  Not only were there hundreds of people there, but I was wearing a dress (if you know me, you’re laughing).  I made it through my 10 minutes with one slight timing issue – I had someone play a video, which taught me the importance of having the entire presentation under my control – and I cannot remember if it was great or horrible.  But I didn’t walk away thinking, “I’ll never do this again.”

Soon after Susan asked if I would like to take over teaching the Motor Control portion of the introductory Movement Science course required for all Kinesiology students.  The course was broken into three sections, Motor Control, Biomechanics and Exercise Physiology, with students rotating between the sections and a different instructor for each.  This meant I would teach the same material three times in a semester, which sounds boring but was ideal as a first time instructor.  And I would get paid.  I said yes.

Susan gave me all of her materials, and I converted all of her overheads (yes, overheads) to PowerPoint.  Then I started reading.  While I had taken the same class myself as a sophomore, had taken many advanced Motor Control classes since then, and was getting a master’s degree in Motor Control, teaching the course was something else entirely.  You have to know the information at a different level.  During those early days I often thought of the learning model followed in medicine, “See one, do one, teach one.”  I’d learned it, I’d been tested on it, now it was time for me to teach it.

Some may state that teaching is not the same as presenting.  If you get down into the details, that’s probably true, but it’s not a topic I want to debate here.  To me, they are one and the same.  For me, when I present at a User Group, a SQLSaturday or a conference like the PASS Summit, I am teaching.

And that is what I love: teaching.  And I realized it in graduate school, when I was teaching that introductory Movement Science course.  It happened in the first semester, in the very first rotation.  I cannot remember the name of the student, but she grasped what I was teaching, she understood.  She asked questions during class, she took notes, and she aced the quizzes and the test.  I taught, she learned.  That was pretty cool.

Now…do I believe that I had that much of an impact on her learning?  No.  Do I believe that if I weren’t such a fantastic teacher that she wouldn’t have done so well?  Absolutely not.  She was a smart kid, motivated, and interested in the material.  She would have done well no matter what.  But in those few weeks I realized that I had something to teach those who wanted to learn, and I realized that I wanted to be good at teaching – for them.

As a student, I appreciated good instructors.  Not every instructor is fully “on” every single day – teaching is hard, and the semester is long.  But there were many instructors whose classes I enjoyed, not just for the material, but for the way they explained it.  Susan was that type of instructor.  I wanted to be that type of instructor.  So I worked at it.  For some, teaching and presenting come naturally.  For many, we have to work at it.  And to work at it, you practice.  I taught that same section of that same course for two years.  Yes, 12 times.  But that experience established a foundation upon which I’ve been building ever since.

In my first technology job I wore many hats, and one of them was software trainer.  In my next job, I sought out opportunities to teach others, and eventually, I found the SQL Community and realized that I could present at User Groups, SQLSaturdays and conferences, like so many others.  And here I am.  I still love teaching, I love it when you see the light bulb go on for someone. I love it when you hear that someone took what they learned and applied it to their environment, and then learned something new.  And I really appreciate it when attendees come back and tell me what they learned – as I have not seen every use case and am always, always learning myself.

One of the things that I value most about SQL Server is that it’s vast and it’s always changing.  As such, my learning never ends, and the opportunity to find new things to teach never ends.  As my good friend Allen White ( b | t ) always says, “I can learn from all of you” (and that means you, dear reader).  If you want to share what you learn, I encourage you teach.  Don’t think of it as presenting – that word can be scary.  Think of it as teaching.  Everyone has great and interesting experiences.  Learn how to tell a story, and share what you know.