DB OPTimize

Query Store: Your MSSQL Must-Have Feature (Part 2 of :”What a Shock! When You Suddenly Start Working With Other Databases..” series)

I have been working with Oracle database for so many years (as a DBA, as Application DBA, as a developer, as an architect, as a consultant, you name it), till suddenly, few years ago, I was practically forced to solve application performance issues happening in MSSQL for one of my customers who two-out-of-two of his SQL Server DBAs had left. A vacuum was created there, I was around, and I was requested to step into it and fill that vacuum. Well I do have many years of experience with tuning system and application, and it just happen that it needs to be done now on a different database than Oracle. No big deal, right? How different can that be?

( You can read here Part 1 of this series: What a Shock! When You Suddenly Start Working With Other Databases… (Part 1) )

And when I am saying I had to solve application performance issues I mean tuning the complete application. Not just 1, 2, 3 or 5 top SQLs. I needed to practically tune everything. This instance was constantly demanding more CPU, more memory, more from everything. And when the application and the business is yelling and requesting more, the DBAs tend to provide it. I have to say that I strongly disapprove this approach! I am so against the approach of adding more and more resources without tuning the application. It might be the easiest way, since you don’t have to drill deep into what the application is doing, but it is also the most expensive way for an organization. Resources costs. CPU costs. Memory costs. Storage costs. And who uses those resources? The application. Therefore we must first tune it as much as we can, and only then add resource, and only when it is truly needed.

Back to where we stopped: So first I had to find out where I can explore things in MSSQL. There were the regular tuning aspects of seeing who are my top SQLs, what are their execution statistics, how to see their execution plan, how to know if they run in parallel, where to see what they are waiting on, get familiar with MSSQL wait classes, what are the wait event and what does each means, and so many more. I managed to find most of them after a whole bunch of internet digging. There are some “dm” views (Dynamic Management Views) that are exposing that. But “dm” views are basically showing statistics since instance startup. That is simply not enough for a complete tuning process.

There was another crucial aspect I felt missing. The history aspect. The ability, for example, to compare the performance of a statement over time. The ability to see if today’s application activity is somehow similar to what it was before. The ability to compare the amount of CPU time and the amount of wait time to what it was before. How can I tell how the statement performed an hour ago? How can I tell how a statement was performing after a customer is now complaining that yesterday, between 10:00 – 10:30, there was some slowness? How can I tell which plan it used? How can I tell if plan is changing and that what caused the performance degradation? I was basically looking for something which might resemble Oracle’s DBA_HIST_ACTIVE_SESS_HIST and DBA_HIST_SQLSTAT, but in MSSQL.

Without that info, it felt like I had to work with both my eyes covered. It felt as I am casting about in the dark. And then, after a while, after lots of digging here and there, I run into the magnificent MSSQL 2016 new feature called Query Store. And suddenly it felt like the cover on my eyes had been remove. Like someone lighted a candle in the dark. I suddenly “have eyes” into what the database is doing. Even though Query Store is still way behind Oracle as far as capturing and saving every tuning aspect is in concern (for example v$active_session_history and dba_hist_active_sess_history which gives the ability to see what is going on in the database for every single second) but it’s a great start. It is much better than nothing, and gladly, I was out of the dark. It made me truly and incredibly happy (I know, the strangest things can make me happy…😊 )

OK. Enough with feeling and emotions. Let’s move to the real thing.

So assuming you are using SQL Server 2016 and above how do you start working with Query Store? (in case of you run an older version of MSSQL, I offer you my sincere regrets). Continue reading here: