One of the tenets of Devops is that we learn from the feedback we get from our system. This means that we actually need to include some instrumentation in our application as well as a way to gather, store and analyze this data. There are various frameworks to do this in application code, but we also need to include the database in here. This might mean we need to add some logging or messaging in our T-SQL code, whether to some table or file, integrating with extended events, or something else. I wish to I had a best practice for you here, but there are multiple ways to do this and the best way for different environments can vary. At the least, you need some sort of resource monitoring if nothing else.
We don’t want to just collect all the data we can, nor do we just pick some random items. We want to think about how our system works and then choose useful data. The key to useful metrics is that they are actionable. We can change behvaior or alter the system based on the information. In other words, we can take some action, positive or negative, based on what the metric tells us about our system. Without this, it might just be useless data that we’re collecting and storing, for no particular reason.
Of course, we might not know how useful some metrics are for awhile, so we may need to collect data that we can’t use right now. If we find that’s the case later, perhaps we cease gathering useless data after we can’t find value over some period of time, maybe months. However, we do need to perform some analysis and investigation to determine if there is a way to extract information from our metrics.
Ultimately I tend to err on the side of gathering a bit more data than I need, even if its value is suspect. I’d rather have more data that I need to delete than find myself wanting later and wishing I’d just collected a few more metrics. The key thing is to actually take some time to look at your data and use it to make decisions. Once you find the data helping to drive the behavior of the organization, I think you’ll start to find ways to gather more and more useful information in other systems.