Firstly, This is not going to be a long, detailed blog post. I would like to share an issue with Query Store that I experienced today after Windows Updates on SQL Server hosts and a quick fix for that.
Average Wait time is too high on SQL Server
I was doing sanity check after windows updates and noticed severe wait times on a server, caused by a specific database. This very database had a problematic select query which confuses optimizer to choose a wrong execution plan. Query Store helped me to address the issue in the past, I forced the right plan and move on.
I immediately went after the same query and realized it is the one that causes high wait times and lock issues. I checked Query Store and everything looked fine. My forced plan was still there and according to query store statistics, average execution time was 10 miliseconds for problematic query. However, I can see that query takes 10-15 seconds by checking system dmvs. Obviously query was not running with right execution plan. So how can that be and what’s wrong here?
Query Store stopped collecting data
It took some time for me to realize Query Store statistics stop around 7 AM in the morning, coinciding Windows Update time. For some reason, Query Store stopped working and collecting data. That’s why, it was not reflecting what is going on currently and I was blind.
I checked Disk usage and Query Store Space, both were fine. Then I tried to unforce and force the same plan, it didn’t help either. I couldn’t restart the instance or the database so I needed something else to provide quick fix.
FIX – Purge Query Store Data!
I correctly assumed that purging query data would trigger a mechanism internally to restart Query Store and decided to go for it. Voilà! Right execution plan is picked up right away by Query Optimizer, all locks are released and database performance went back to normal. I am not sure why query store stopped working after server reboots for Windows updates. To be able to identify the relation and what was the underlying reason, one needs a proper deep-dive investigation.
You can purge query data by right clicking on database, going into “Properties” and then jump on “Query Store” tab. There you will see a button at the bottom as also shown in below image.
Bahti Samet Coban 