Who doesn't know these inquiries: "The application was slow yesterday, what was going on on the SQL Server?"
To answer these questions, we set up a "flight recorder" that regularly collects the data we need. Even if the SQL server provides all the necessary information about system views, it still lacks a practical way of collecting and processing this data. The "data collection" functionality has not been developed further for years and the "query store" focuses primarily on a single database.
We therefore use the functionality of the First Responder Kit, which we believe should be installed on every instance.
In this presentation, I will first introduce the basic approach to data collection and the actual setup. I will then give some examples of how we work with the collected data. I will not present the solution to the specific problems, but the way to make the problems visible in the data.