Voice of the DBA

Tag: administration

  • 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.

  • Ten Database Commandments

    I love the Notorious B.I.G. His music isn’t for everyone, but I enjoy it. This piece takes one of his songs (NSFW – Ten Crack Commandments) and applies the list to databases. Each item from the song is listed, along with the way it might be changed for database work. The list is from Ottertune, which is a service designed to help you optimize your use of AWS RDS and Aurora databases, with AI. If you believe their hype, their service will get better performance and lower costs for you.

    In any case, with regard to databases, I suspect that the author is having some fun with the rules, and a few of them made me smile. They are cloud-related, but a few will apply to any environment.

    The first one is about budgets, noting you should not disclose your budget, which is in general a good rule whenever you are buying something. Don’t disclose information, and someone working for a vendor of tools, I think that most budget conversations aren’t something technical people should worry about. Decide what value you get from some tool/service/process/etc. and if there are time savings. Then let someone else decide if the ROI is worth the cost.

    Rule 2 is a stretch, but I do think that looking at new tools can be helpful. The caveat here is that many people, don’t often learn enough about existing tools to understand if they will solve issues. There are a lot of things you can do with many tools, so don’t just look for some magical new tool. Learn to use your tools well, and if you want to try a new one, keep in mind you will need to invest some time both learning about it and practicing some skills before you really know if it’s helpful. The other thing to remember is every new tool increases the bar for new staff. Try to limit the number of tools you use to those you need.

    Security is important and rule 3 is remembering not to give out more permissions than needed, especially to developers. I love this quote: “You don’t want randos at your organization logging in and running queries that they found on Stack Overflow.”

    A number of the other rules have to do with performance, which is certainly Ottertune’s business, but these are good basic practices that I see organizations not following. Too often management wants to save money and technical people make decisions that compromise performance. Lots of people overload systems, and then they can’t handle the workload well. I get that many CFOs and others want to see high CPU usage, which means they’re using the resources they pay for, but databases can be very bursty with resource usage. At the same time, you don’t want to over-provision resources because you are scared. Learn to find a balance that doesn’t waste money, but handles your workload well.

    Perhaps one of the best things about the cloud is that if we do make bad decisions, we can often provision more resources. It can take time to move to a new tier of service, so don’t expect instance response up or down, but if you need to double the CPUs for month-end processing, you can likely plan for the time to scale up your database instance and then scale down again when all the work is complete.

    It’s a fun article, and if you’re looking for some fun Notorious B.I.G.-related database content, grab a cup of coffee when you need a break and a smile.

    Steve Jones

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

  • Running a Service

    Many of us work for some organization and we’re responsible for managing a database or coding some application or some other data related position. Perhaps we ensure reports work for our users or move data around. In many cases, we have customers, whether they are internal to our organization or perhaps external from another business or even the wider public.

    However, many of us see our jobs as a part of the org/business and not that we are actually providing some service to others that we are responsible for. While I’ve certainly felt that way, I learned at some point that if I were an app dev, I ought to treat people using my app as customers, even though they don’t pay me in currency. They often pay me in praise, or better yet, with praise to my boss. As a DBA, I treated both app devs and users as customers. That helped me take pride in my work and learn to view my position from other points of view.

    There was a post on learnings from running a SaaS service for a year. While few of us maintain a SaaS service, we could think of the app we build as Software-as-a-service, albeit without any revenue. We could see databases we manage as DBaaS (databases as a service) or PaaS (platform as a service) items.

    I think that the way we view the world, or view our situations, can be helpful to us in managing our workloads. We can better triage what is critical, what’s important, and what’s nice to have. We can decide when an issue deserves some to cause some stress and we might give an extra effort, and when something can wait until tomorrow. We can decide when we push back on demands and when we accommodate them.

    This approach has also helped me to think about ways to improve the service/software/platform I provide to others in ways that helps my customers, or makes my job easier. This way of thinking leads me to make the situation better when I can. It also helps me to discuss the decisions with others, especially management, in terms that can matter to them. Are we finding ways to improve the overall system?

    It doesn’t always work, but it often has for me.

    And if you’re interested in your own side project, the journey continues in part two. Perhaps you can spend an hour or two a day and build something that becomes your own business. That’s what happened to me with SQL Server Central.

    Steve Jones

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