Voice of the DBA

Tag: administration

  • What Metrics Do You Collect?

    One of the hot terms in software these days is observability. There are a few definitions (Splunk, RadixWeb), but essentially this is the insight into how your software runs and performs using metrics, logs, traces, etc. In DevOps, we do this with an eye toward improving performance and identifying the root cause of issues. The focus is slightly different from monitoring, where we often focus more on resources and health. We need both, but often in trying to improve software and the behavior for users, developers need observability. Infrastructure people responding to acute issues and looking to ensure we have the capacity, availability, and other x-bilities, that need monitoring.

    Today I’m wondering if you collect a variety of types of metrics for your software that might tell you how your system is running. What things are important to you in order to better serve your clients? If you’re a DBA/sysadmin, what is important to you? If you are a developer, are there different types of data you want?

    Certainly, you might collect various resource measures (CPU, IO, reads, etc.), but there are many more things. There are logs, which could include the SQL Server error log, but I’d hope that you had a more in-depth way of measuring the activity on your system. Do you have custom xEvent traces running? Can you collect application logs easily when you’re looking at issues? Do you spend time trying to solve chronic issues? Do you look for potential future problems?

    Most software applications should include some sort of basic logging of major functions, but I would hope that there are various levels available. If problems are reported or noticed, can you increase the detail of logging? Can you correlate this with logs from different systems, such as the database? Can I get execution data that matches calls and trace down the potential issues that users are experiencing?

    I know that we have problems in applications. Some (many?) of these are data-related, which might be easy or hard to trace down. That often depends if the data changes too quickly or is static enough for someone to investigate a report. Some errors are logical code errors, which might indicate a lack of testing early in the software process, but we ought to be able to determine this quickly from logs. If there are performance issues, and we have a lot of these, how easily can we verify a problem?

    Let us know what types of metrics help you solve issues. I certainly think that you should have some sort of monitoring and observability system in place that helps you dive deep into the database, especially concerning execution plans at the time of the issue. The situation can change quickly inside a database, so capturing data regularly is important. If there is something you wish you had, let us know as well. Maybe someone else will have a neat solution for you.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • DBCC CLONEDATABASE is Semi-Discontinued

    I saw a tweet that DBCC CLONEDATABASE was being discontinued for production databases, which both scared me and didn’t make sense. I’ve used this a few times for a quick copy of a database and like how it works. Discontinuing it seemed strange to me.

    Then I read the blog post, which notes that it’s not being supported for production deployments. The post doesn’t explain why, but I’m guessing this is because all the stats and other metadata moves, and this might mess up the optimizer if different data is added. I don’t know who deploys production databases like this, but I could see people who have federated or sharded databases using this to create a new blank copy and then uploading data into it. Or maybe people who need new databases that are distributed onto remote office/edge devices used it? If you use this to create production dbs, let me know.

    This command will still be used for generating schema-copies for diagnostic and troubleshooting purposes, which is what it’s there for. I assume this means Microsoft Support will support you using this to investigate strange query issues or if you create a database at their request. However, there isn’t a mention of this being used for development and test environments, which is where I use it.

    Specifically, I’ve used it quite a bit lately with Redgate’s subsetter utility. I need a target that’s shaped like the source, and dbcc clonedatabase gives me that. I don’t really care about stats or anything else, I just need schema set up to move data around. It’s useful there. It’s also useful for a quick test of a deployment, where I can ensure I get the latest production schema and then run a deployment against it (hopefully using Flyway), looking for errors.

    If you use this command, don’t worry. The tool isn’t going away, and the restriction against production copies doesn’t take place until March 1, 2025. That’s nearly a year from now to change your process. If you’ve never used it, well, I don’t have a good reason to start using it, but you ought to be aware of how it works. Learning about new features is handy, as you might discover a problem the knowledge can solve. However, if you don’t know it exists, it will never enter your mind as a solution.

    Be curious and try things. It’s easy, and it’s fun.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • Who Still Uses SP_ for Naming?

    An interesting post recently from Chad Callihan on the reasons why someone shouldn’t use sp_ to prefix their stored procedures. This is advice that I’ve seen for years, but I haven’t run into this lately with many customers. It seems I do see some usp_, but not sp_. I think that’s good, and if you’re not sure why, read Chad’s post.

    I think my main reason for not doing this is that I sometimes create procs in master. Not because it’s a good idea (it isn’t) or I want to, but because I’ll make mistakes. I’ll accidentally connect to master and deploy a script, because I’m not paying enough attention. Usually, I’ll catch this right away, but if not, soon after I’ve told a client things are working, they’ll let me know they aren’t. Then I connect to the right database.

    In a vacuum, or even as you sit reading this today, it’s easy to criticize someone else for making the mistake of connecting to the wrong database, or even forgetting to delete the object in master after I’ve created it in the right one. However, all of us are human. We’ll make mistakes. We’ll be pressed for time or sick or tired or want to leave or some other situation where we aren’t using all of our faculties. Even if none of those are true, we might depend on muscle memory because we’ve done this 1,000 times (or may 12 times in the last hour) and then don’t notice we’ve connected to the wrong database.

    What might be even worse if when we’re updating objects and potentially update in one place but not another. What if v7 of the proc is in the user database, but v5 is in master. Who knows if we (or the client) will notice things aren’t working as expected right away.

    Mistakes are one of the reasons I try hard to get everyone to follow a DevOps approach. Put your code in version control (so you know where it is when you make mistakes), use automation and CLI calls to deploy code. Whether you use something like Flyway or your own SQLCMD scripts, make sure things are automated. Then ensure you have logs with commands run, and if needed, output from the scripts.

    We’re human, we make mistakes, so figuring out how to automate a process and then ensuring that automation is running results in repeatable, reliable deployments. Something we should all want, especially the control-obsessive DBAs.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

  • Be Careful with Missing Index Requests

    One of the things that has been interesting to watch over time is how the SQL Server platform has expanded the amount of information that we get back about the performance of the query optimizer and query processor. While it’s not perfect, and there is room for improvement, the advances made with intelligent query processing are helping many systems run faster. Not all queries, but some.

    As I’ve done a little work on other platforms, there are ways to look for potential missing indexes in PostgreSQL and MySQL, but these aren’t built into tools, nor are they easily accessible to developers or DBAs. There’s work to be done on many platforms, though I’m not sure if there is more work than required in SQL Server. On all these platforms, you need to dig into queries and understand why they are slow, though the tooling for SQL Server, with graphical plans in SSMS (or with Plan Explorer) can make the job easier.

    One thing SQL Server does is provide missing index recommendations in the query plan. You can find information on this in the docs, but you should make sure you read the limitations section. The recommendations returned should not just be run. I should repeat that for junior DBAs, accidental DBAs, and developers:

    DO NOT JUST RUN THE MISSING INDEX RECOMMENDATIONS WITHOUT TESTING LOTS OF QUERIES.

    I hate using all caps, but that is important. As an example of why, watch this short video from Erik Darling, where he shows that the simplistic view of the missing index is helpful, but not as helpful as it can be. In case that’s not enough, there are other issues that Brent Ozar, Phil Factor, and Aaron Bertrand share some of the problems they’ve found.

    There is a wealth of information that is available about queries in SQL Server and how they are processed. It will help you in your career to learn more about performance tuning and how to evaluate queries. We have articles here, there are more on Simple Talk, and Erik Darling produces information every week and also has training to help you learn to tune queries better. There are plenty of others that will help teach you as well.

    Maybe the best benefit of learning about tuning is that you can learn to write better queries the first time, which means no rework, no effort responding to complaints, and a cheaper bill if you move into the cloud. That might be something you point out to your boss and ask him or her to fund a little education to help you and your employer.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.