Voice of the DBA

Author: way0utwest

  • How Often Do I Backup my Log?

    Do you know how often to back up your transaction log in SQL Server? Most of us have stock answers, hopefully answers that have time intervals like every hour. I worry that most people have a time interval of “never” because their answer is “I make full backups and don’t need to backup logs” or “What’s a transaction log?” That’s a different discussion and if those are your answers, I have an article for you to read.

    Managing a transaction log is a bit tricky and not straightforward. I think far too many people manage their logs based on the space they are trying to maintain on disk. However that’s not the way you decide when to back up logs. The way you decide how to back up a log has nothing to do with space. It’s best stated in this quote from Gail Shaw: “the log backup interval should be less than the allowable data loss for the system.”

    Simply put, decide how much data you can afford to lose. Or how much loss will exceed your tolerance for being yelled at. That’s your log backup interval. Make a log backup after that much time passes. Schedule that interval into your maintenance operations and observe how big the log backups are. That will determine your log size, not the other way around.

    As with most guidance and advice, this is based on a time and place in the platform’s lifecycle. This is the advice I’ve used from SQL Server 7 to 2012, but it’s subject to change, so make sure it’s still valid if you’re reading this in 2020.

    And, of course, make sure that you also understand how to restore these backups in case you do have an issue. That’s probably the most important thing to know.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and Mevio . feed

    The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

  • T-SQL Tuesday #51 – Place Your Bets

    tsqltuesdayIt’s T-SQL Tuesday time again and Jason Brimhall is hosting this month’s event. He used to live in Las Vegas, and with that theme in mind, he calls for you to Place Your Bets.  That’s a time when someone has gambled, or risked something on an application, venture, process that relates to SQL Server? When have you done something that caused an issue you could have avoided?

    It’s an interesting topic, and I’m looking forward to reading what people write.

    T-SQL Tuesday is a monthly event, the second Tuesday of the month, started by Adam Machanic (blog|twitter). You can participate by setting up a blog and writing on the month’s topic. Check Twitter for the #tsql2sday hash tag or look for the blog postings with a search.

    If you want to host, contact Adam.

    The Big Bet

    I didn’t place this bet, but a bunch of application designers and management did. I came in to the company late, and realized we had issues, but wasn’t able to effect change in any short order.

    I worked for a large software company. Large as in thousands of employees, and certainly hundreds of developers. Most were engaged in writing the software we sold, but there were plenty of people engaged in managing our internal systems and writing software that we were using internally. One of these was our internal Intranet site for employees.

    This was a large project, replacing a number of applications, and designed to be a central point of information where employees could turn and various departments could publish information. Being a software company, we knew that it’s worth paying for software that’s written and we chose a framework that would cover many of our needs, but allow us to customize parts of the site for use by different departments. All in all, a CMS-type system that would fit our needs.

    The project started before I did, and no one consulted me or my group, as the production DBAs, as to the architecture, hardware spec, or anything else. It’s not that we should have, or would have, led the way, but we could have offered opinions about how things might perform.

    The day of the roll out came. I was told which day it was and never participated in any testing. The global policy was changed to set everyone’s default browser to the intranet, and I came into work expecting to find a new site where I’d see information posted, resulting in (I hoped) less emails from various departments. Not long after I arrived, I was pulled out of a meeting because of performance problems.

    I had no idea what to look at on the site, but we found CPU pegged and very high I/O on the server. I decided to run a short Profiler trace from my machine to get an idea of what the workload was and what we might look to run. I found fairly simple queries, lots of SELECT * to a few tables. What was interesting was that the queries were scrolling rather quickly on the screen. I found that the default trace values in SQL Server 2000 were storing around 25MB/sec worth of data on my local hard drive.

    In 2001, this was a fairly large load on a SQL Server 2000 instance. When we checked the tables and calculated result sets, we found very little data from each query. There were missing indexes, but even adding those to tables that were a few hundred rows of data didn’t help. The problem was simply that so many employees, each hitting the server by default, with a series of sidebars on the web pages, each of which created its own connection, ran its own query, and ran lots of them on each page, was simply overloading the hardware.

    If was bad design, poorly tested, and not well thought through. Even simple caching mechanisms as Brent Ozar has talked about would have dramatically removed a lot of the load. In the end, those weren’t really needed. What we found was that the majority of the information being returned by queries changed less than once a week. A few weeks of development time removed most of those queries and had them replaced with static XML files that were loaded by the application for display, and could be updated from the database by an administrator.

    Ultimately the site worked well, and we did get less emails from various departments since announcements could be made on the Intranet, which most of us learned to check once or twice a day.

  • The Data Platform Vision

    Recently the Microsoft Data Platform blog published a piece on what drives Microsoft’s vision. It’s an interesting look at the ways in which data is being used in some businesses as well as the variety of types of situations that must be dealt with. There is this idea that the value derived from having lots of different data was once only used by specialized companies or applications. However today, it’s being more widely analyzed and accepted by more businesses. Of course, the vision is that Microsoft can help those businesses.

    In the cloud.

    I know this piece is more marketing than technical, but I have concerns that Microsoft is not driven by what customers want and need, but is instead seeking to drive customers to their own tooling and offerings in the Azure cloud. While I do like many things about the cloud and think it has a place in the future of many companies, I also think that there are still lots of opportunities, as well as demand from customers, for on-premises solutions.

    I worry that the focus of Microsoft, which can be tightly bound in one idea as they eye more sales revenue, is driven with the idea that everyone, and all applications, will some day be in the cloud. I can’t see that in the future for many businesses, and certainly hope that Microsoft recognizes this. Otherwise many of our hybrid solutions that push some data into the cloud might be backed by PostgreSQL or some other platforms as the on-premises database.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.2MB) podcast or subscribe to the feed at iTunes and Mevio . feed

    The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

  • Performance Tuning Through Panic

    We’ve all been there at one time or another: panicked by a performance issue. I once upgraded a FoxPro for DOS application to a VFP front end with a SQL Server back end. We had a much more powerful server, generations ahead of the previous file server share. When the first users logged in and searches ran slower, I was in a panic. My team worried about the size of our inventory data in memory and quickly upgraded our 4GB, 1996-era server to 8GB of RAM, hoping for improvement. We also hurriedly split out queries and separated some data in an effort at quick tuning. It wasn’t much above a knee-jerk reaction, but we had users, and management, yelling at us on an hourly basis.

    I’m sure many of you have had other performance problems that resulted in some panic during your career. I’ve had more since then, and will probably have more in the future. Many of us do, and many of us perform knee-jerk performance tuning. However you can, and should, avoid tuning in a panic, as pointed out by Paul Randal. Doing so usually doesn’t fix the problem, and when it reoccurs, it’s more embarrassing with people less likely to listen to your next great idea.

    Tuning a system is a science, but it’s a complex science. Without experience, without having knowledge about all the complex interactions, and a deep understanding of how all the parts fit together, it often looks like a hopeless task. Who can keep all that information in their head and relate all the various components of the application? The network, the OS, the SAN, clients, all can cause problems that appear to be in the database.  As Paul points out, failing to understand the actual root cause can lead you to make changes that will have no effect on the actual underlying issue. You’ll often realize this when the problem re-occurs in a short time.

    I think one of the best reason to hire consultants is that they see so many problems that they can recognize patterns in performance that many of us don’t see. What they do appears to be an art because they can seemingly understand what information is relevant, and what isn’t. They walk down a list of potential issues, gathering evidence in a logical way, but one that can appear random to someone with much less knowledge and experience.

    All of us can learn more, we can read about problems others have had and learn how they solved the issue. However we often face the same reaction that many consultants do: no one wants to implement the advice. Until clients and vendor are willing to tune their code, many problems will only be masked by hardware.

    For a time. Then they’ll reappear.

    Steve Jones