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.
The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.
NEW product launch: SQL Clone - live stream this March!
Data Platform MVPs Grant Fritchey and Steve Jones show how to create database copies in seconds using MBs of disk space with Redgate’s NEW database provisioning tool SQL Clone! Tune in to this live stream session on March 29th 2017. Register now.
Once you have Query Store enabled on your databases, runtime statistics are generated for your queries; but what about the natively-compiled stored procedures and memory optimised tables that come with In-Memory OLTP? Do you get the full range of runtime statistics? This is an intriguing question that Enrico explores and answers. More »
This stored procedure will find all the SQL Logins on a server and tell you the age of the logins password and it will also check for blank passwords and passwords that are the same as the login name. If you create a common password table, it will also check the logins passwords against those in your table. The common password table format is:
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.