Query Store Trace Flags

Microsoft maintains a list of supported trace flags and I noticed that there are two new ones related to Query Store: 7745 and 7752.  The descriptions for these Query Store Trace Flags are pretty straight-forward, but for those of you not familiar with Query Store, I thought I’d provide some context and details.

7745

When you enable and configure Query Store, one setting to consider is DATA_FLUSH_INTERVAL_SECONDS.  This setting defaults to 900 (15 minutes), and it determines how frequently Query Store data will be flushed to disk.  As a reminder, some Query Store data is stored in memory as an optimization.  If Query Store had to flush data to disk every time a query executed, Query Store would quickly become your biggest performance problem, therefore data is temporarily stored in memory.  The DATA_FLUSH_INTERVAL_SECONDS setting controls the maximum amount of time SQL Server will wait to flush data from memory to disk, but know that data could be flushed more frequently (also as an optimization).  When I first learned about Query Store, it was my understanding that this setting was a tradeoff between “how much Query Store data are you willing to lose if your server unexpectedly shuts down” and “how much of a performance impact do you want to introduce by having Query Store write data to disk (internal tables in the user database) more frequently”?  Because the Query Store data is not business critical, my advice has been that 15 minutes is a good balance.  However, I now know that when you initiate a shutdown of SQL Server, by default it will wait to write that Query Store data to disk – and I’ll be honest in that I don’t know how long it will wait.  In some cases (e.g. a fail over in a HA/DR scenario), you might not want to wait any longer than absolutely necessary for SQL Server to shut down.  In that case, you can either use SHUTDOWN WITH NOWAIT (not what I would recommend at all) or trace flag 7745 to bypass writing any Query Store data still in memory to disk.  Using 7745 means that you can lose some Query Store data.

7752

When you start SQL Server, it loads some data from the Query Store internal tables into memory (again, this is an optimization to make specific capabilities of Query Store complete quickly).  In some cases this is a small amount of data, in other cases, it’s larger (potentially a few GB), and as such, it can take seconds or minutes to load. As a result, if you execute a query before the Query Store data has finished loading, the query will not execute until the data has been loaded.  To load the Query Story asynchronously, and still allow queries to execute, use trace flag 7752.  With this trace flag enabled, queries can execute while the Query Store data is being loaded asynchronously in the background, however, Query Store is in a read-only state.  No query information will be written to Query Store until after the data has been loaded into memory.  If you don’t know if Query Store is taking a long time to load and thus impacting your queries, set up an Extended Events session that captures the WAIT_INFO event, filtering on the QDS_LOADDB wait_type, and have it run at startup (and also have a job that stop the XE session 10 or 15 minutes after SQL Server starts up – you don’t need to continuously run this session).  After one or more restarts, check the XE output and see if you have any events for the QDS_LOADDB wait type, and if so, add up the total duration for the wait type and compare it against the startup duration for the instance.

Finally, know that the behavior of trace flag 7752 may become the default behavior in a future release – that’s yet to be determined – but if it did, there will ideally be a status change to Query Store to signify that during the asynchronous data load it is in a read-only state.

My thoughts

I always expected that I could potentially lose Query Store data, and so I’m still ok with that.  When I ask SQL Server to shut down, I want it to shut down as quickly and efficiently as it always has, so I’m pretty sure I’m going to be enabling 7745.

I will also enable trace flag 7752.  While I can check to see if I’m experiencing the QDS_LOADDB, if this will become the default behavior in the future, I might as well adopt it now!

Update September 2019: The behavior of trace flag 7752 will be the default behavior in SQL Server 2019.

Thoughts on public speaking / presenting / teaching

A colleague of mine asked me this on Twitter the other day:

When you started speaking did you know straight away that it was something you loved doing?

My answer: No.

It’s a really good question, and I said I’d go more in depth.  We have to go way back in time.  In asking the question, I believe my colleague was thinking about speaking in the SQL Server community, but for me it started before I found the SQL Server community.

I don’t think there are many people that love public speaking from the get-go.  At the University of Michigan I had to take Communications 101 (a public speaking course) in order to graduate.  I dreaded it.  Most people did.  But I took in in the fall of my sophomore year and got an A.  (Yes, I went and checked my college transcript.)

But the first time I really spoke to a group of peers and professors to explain or teach something was my first year of graduate school.  We had a day to celebrate the accomplishments within the Kinesiology department, and I had been working on a grant that tested the effects of Botox on children with cerebral palsy.  My advisor, Dr. Brown, wanted me to present our initial findings.  I had 10 minutes.  I created 10 slides and had a one minute video to show.  I remember Dr. Brown telling me that she used talk about one slide for 10 minutes, she had no idea how I’d get through all 10.  I was terrified I’d finish in 5 minutes.

I have hazy memory of my talk – I remember what I wore, I remember thinking my voice was shaking, I remember feeling nervous, I remember nodding at Dr. Watkins to start the video…and that’s it.

I can’t remember any feedback, but I do remember thinking I didn’t want to do that again.

Flash forward a couple months to Dr. Brown’s idea that I could teach the motor control section of the Movement Science 110 course.  Teach to freshman and sophomores.  People who were PAYING a lot of money to go to school at Michigan.  Again, I was terrified, despite Dr. Brown’s logic: I’d get paid, I would experience teaching, and it gave me a chance to learn the material even better.  I didn’t even have to create the content – I could just use what she had already been using.  I don’t know if I even tried to argue, I probably knew I wouldn’t win (Dr. Brown was pretty persistent).  So in the fall of 1997, I started teaching.  On the first day I had student argue with me about theories.  THEORIES!  I was teaching science.  I wanted to quit, but I didn’t.  I taught that class for two years, and I probably learned more than my students did.

Fast-forward a couple years to my first job in technology, at a software company, providing technical support.  I was soon asked if I was interested in training customers as well, as there was only one other person who handled training at that time.  I said yes – voluntarily this time.  I learned the software, I learned how to teach other people how to use it, and I got better.

By the time I worked in the Database Services department at Hyland I sought out opportunities to teach.  Every year there was a user conference, and during my first year on the team I asked a senior member of management if I could help with his presentation.  Now, I don’t remember the impetus, but we started co-presenting, until the year that he looked at me and said: “You can do this without me, I’m about to retire.”  I taught that class at multiple conferences over the next few years.  I asked to add database classes to the conferences and I developed and delivered those.  I provided internal training and recorded material to be viewed by partners and users online.  By then, I loved it.

When I discovered the SQL Server community and found out there was a conference every year (the PASS Summit) my initial thought was, “I want to present at that!”  And so I worked my way up.  I presented to my user group in the winter of 2010, and then at the Cleveland SQLSaturday in February 2011.  My first Summit was that same year, with a lot of other SQLSaturday events in between.

I’ve now been “presenting” off and on for about 20 years.  And I put presenting in quotes because I don’t think of it that way; I think I’m always teaching.  I’ve gotten a lot of experience in those years, and as a result I’ve gotten comfortable in front of a crowd and have developed my own style.  And while I’m proud of what I’ve accomplished, I still work to improve.  I tweak every session trying to figure out how to make an explanation even clearer.  I change demos all the time, trying to get them *just right* so they easily demonstrate a concept.  I continually read an audience and make adjustments on the fly when I can.  It doesn’t end, and I’m ok with that.  I do enjoy presenting/teaching now, but I didn’t when I started…because it was uncomfortable, because it was hard, because I didn’t know what I doing.  Because like everything else, it takes practice to become good, even if you have a knack for it from the start.

The greats weren’t great because at birth they could paint
The greats were great cause they paint a lot
~Macklemore and Ryan Lewis