Voice of the DBA

Tag: syndicated

  • SQL University – Other Capacity Issues

    This is the final lecture this week at SQL University. We are covering the topic of capacity planning this week for SQL Servers. In the first lecture we looked at an overview of capacity planning and some general considerations for DBAs. Our second lecture dove deeper into disk capacity planning, examining both space and throughput requirements.

    Today we talk about a few other considerations for keeping control of your environment and planning for the future. By

    Disaster Recovery Planning

    One of the places where I find people skimping on capacity is with Disaster Recovery (DR) systems. Typically the hardware size for DR servers is some fraction of the regular production servers. This is often because DR servers aren’t often used, or maybe never used, by many companies. I know that I have not used them 99% of the time in my career, and as a result have typically not sized them to handle the full load of my primary systems. The logic being that in a disaster, likely some of our customers will also be affected, and most of them are likely to put up with slower performance if disaster strikes.

    That may not be true in your environment, and I know a number of companies that size their DR environment exactly as the same size as their primary systems. For your situation, please ensure that you handle this appropriately and size your systems, and set expectations for business people, based on what you do.

    However even if you size your DR system at the same level as your production server, you are not done. In three companies that I have worked with, they sized their DR server appropriately, but then forgot about them as they upgraded and expanded their live production servers. In all cases we were fortunate to discover this fact without a disaster.  A test in one case and audits in the others exposed the fact that a disaster would have been a true disaster with us not able to even restore because of a lack of disk space.

    Quite often management doesn’t want to spend more money on DR than absolutely necessary, and that often means less resources for those systems. If you are stuck in this situation, be sure that you quantify the issues for management and document differences. You should try to interpolate the possible performance issues, or test them, and come up with specific numbers where possible. Document differences like “The sales report will run in 40 minutes instead of 4”, using verbiage that your managers will understand. They might agree to spend more money on DR, or you might need to use this as justification for poor performance when you do fail over.

    Test and Development Servers

    For each database that you have in production, you ought to have a test instance with a copy of his database. Even for third party databases, you should still have some suite of tests run against patches or service packs.

    If you write code, then you also need to have a development instance, and if you have lots of clients, you might even have a User Acceptance Test (UAT) environment as well. I have also seen Customer Service instances in places that needed to replicate an issue quickly to work with a customer.

    In terms of capacity planning, there are two issues to consider with all these additional instances: disk space and performance. You also face the cost and resource issues with additional instances, as well as a more difficult argument to get appropriate funding for these environments.

    Disk space is always an issue as data sizes grow. If you imagine a 1TB database in your production environment, that usually means that you need a 1TB for each of your test and development instances as well as backup space. Since you often restore databases from a local backup file, this means that 1TB of data needs at least 2TB on each server, and across 3 environments (production, test, dev) that’s 6TB of space.

    Each time to manage space in your production environment and proactively grow databases, you should be aware of the impact on other environments. If you buy more disk space for your main database, be prepared to buy more disk space in other environments as well.

    Virtual Machines and cheap disks can ease the burden of setting up other environments and sharing hardware among some environments, but they might not be enough for some critical databases. No one may be concerned if that company birthday tracker runs a little slow, but a drop in performance on the financial analysis database could actually cost the company money.

    There are always some databases that truly need a duplicate environment in order to test changes and gauge their impact on performance. In these situations you must ensure that as your production environment requires more resources, you are also adding those same resources to the test or development environments.

    This seems obvious, but it can be easy to forget. Make sure that you have clear documentation and notes that remind you to order additional hardware resources for your other environments when you are adding production resources.

    Human Resources

    I have never really liked the term “human resources” as I think humans aren’t easily replaceable, and certainly aren’t interchangeable. Each person is unique and brings something to your environment

    One of the capacity issues that seems to constantly be an issue for many companies is the employee staffing to handle the workload. As environments grow, companies are often slow to hire additional resources, even when it would allow them to substantially grow their business and increase revenue. I actually think that this is a good philosophy, but it can become a problem over time.

    Each database server adds something to the workload of the administrators involved. If there is code to be written, even more load is added. While a smart DBA can be efficient, write code that helps him/her manage the servers as groups, and automate many of the tasks that need to be accomplished, there is still a limit.

    I have managed hundreds of instances myself, but only when everything is automated and most of them aren’t critical and overloaded. Usually I find there are a few instances that require most of my time, and I spend every week or every day working with them. If you have two or three instances with issues, you can easily be overwhelmed.

    As you grow your environment, you ought to have an idea of where you spend your time, and how much time you spend working on those instances that need lots of hand holding. If you find yourself regularly falling behind, or your hours constantly exceeding 50 in a week, you need help. You need to plan for additional people to help ensure you environment continues to run smoothly and you continue to function at a high level.

    Don’t burn yourself out trying to handle too large a workload. Examine the human capacity of your environment periodically and manage that just like you manage disk space or CPU cycles.

    Conclusion

    This lecture concludes the Capacity Planning week for SQL University. We’ve looked at capacity planning at a high level, dived deeper into disk issues, and talked about a few things that people forget when trying to manage their environments.

    If you have additional comments or questions, or would like to add your won notes to this week, please feel free to leave a comment or link to a blog/article below.

    Thanks for attending this week of SQL University and enjoy the rest of your semester.

  • Interviewed by Andy Leonard

    Last year Brent Ozar interviewed all the candidates for the PASS Board of Directors. I think it went over pretty well, and this year Andy Leonard (blog | Twitter) has taken up the cause and is interviewing each candidate.

    I sent in my answers, albeit a little slower than others, and today I’m featured on Andy’s blog. Check it out and let me know what you think.

  • SQL Saturday Advice – Communicate Often

    It seems that Thursdays are becoming SQL Saturday day here at my blog. I wrote a few posts about SQL Saturday recently after attending SQL Saturday #22, and have been scheduling them out. Since the first two were on Thursday, I decided to keep this moving forward. The previous posts in this mini-series are:

    People Don’t Read

    I was talking with one of the leaders of an upcoming SQL Saturday about one of his emails. There was nothing wrong with the email, but it didn’t include some information about a speaker change and a few other things. When I asked this person why he’d left it out, he said it was “to build some excitement.”
    Huh?
    While SQL Saturday is exciting to many of us, it’s not the all-consuming passion of many people. There are plenty of people that see a SQL Saturday as no more exciting than this:
    denversql
    Not that the Denver SQL Server User group isn’t exciting, but it’s not necessarily something that drives hundreds of DBAs in the area to attend each month.
    As a result, most people that get notifications like this don’t really read them. If you registered for SQL Saturday, and you get an email, I bet half of you do what I do:

    • skim lightly over the content
    • double check the date/time
    • think if I still want to attend or if there’s something better
    • move on with life.

    Of all the SQL Saturday’s I’ve attended, and of all the times I’ve gotten some reminder, I’ve typically ignored half of them. I might check my calendar or think about if I’ve done all the stuff I need to do, but I don’t often re-read each email.
    I do read a couple, which is what most people do. So don’t hold things back, send a good list of things every time. Move things around, make it interesting, but don’t assume anyone other than you is reading every email.

    Touching people often

    Hopefully none of you read that title in a bad way. If you do, shame on you, and probably me, for using a catchy sub-heading.
    There’s a reason that car companies and beer companies, and other companies use the same commercial over and over, often during the same program. It’s because people will remember what they see often, and because some percentage of the audience will miss one or more showings of the commercial.
    I think that Karla Landrum did a fantastic job of sending emails regularly to remind people, present information, repeat things, and just build some excitement around SQL Saturday #22. I think that more SQL Saturday organizers should do the same thing, and that we need to get a list of which emails to send, and when. There are some different ones, like for volunteers and speakers, so it helps to have a schedule set.
    But include relevant information about the event to each group, and try to touch them once a week or so. Change your subject, change the first line or paragraph around, ask for referrals, but touch people often.

  • SQL University – Disk Capacity Planning

    This week at SQL University we are talking about capacity planning. I am the guest lecturer for this topic, and covered a broad overview of what capacity planning means for DBAs to start the week. In this installment, we will look specifically at disk requirements and how to plan for them.

    Old Disk Capacity Analysis

    The traditional methods of sizing a database has been to compute the row size for each row, multiply by the number of rows, and then add some growth factor for the next xx months along with a bit of pad. This is an easy formula for each table.

    TS = P ( G ( RS * Re) )

    where

    • TS – Table size
    • RS – Row size (bytes)
    • Re – Number of rows, estimated
    • G – Growth factor, as a percentage. This can be a weekly or monthly growth.
    • P – Percentage pad of the growth. I have often used 15%, estimated as 1.15.

    If you sum this up for all tables, you can easily determine the size you need for your database.

    However there is one factor in there that tends to throw the calculations off. The estimates you make, both in growth, and in the pad, can dramatically throw off your calculations.

    There is also the fact that not all tables grow at the same rate. The growth rate for your Orders table probably is larger than that of your Customers table. You hope that each Customer places more than one order, so while each should grow, the Orders table should grow at a faster rate. Determining the growth rate for each table can be a cumbersome task.

    I used to have a system in place that counted the rows in every table in my database every night. We stored this number and calculated a monthly average and growth rate, and this allowed me to plan for disk usage, as well as understand the different growth rates of tables. Popular software packages like SAP or Dynamics have a fairly well known rate of growth for each table in their system and that can help you to determine how quickly each table will grow.

    This formula is only as good as your estimates, and while it is a very accurate way to determine disk capacity needs in the future, it is also overly complex in my opinion.

    Keep It Simple

    One of the guiding principles that I have tried to use in my career is to keep things as simple as you can. Not that everything is simple, but I try to keep it as simple as it can be.  Don’t add unnecessary complexity if you don’t need it.

    Over time I observed a few things in the databases I managed. One was that relatively few tables in a database contribute heavily to the disk usage of that system. There are often a few tables like order tables, fact tables (in warehouses) auditing tables, that use up most of the disk space. Lookup tables tend to use trivial amounts of space (relatively) and other tables tend to get dwarfed in size relative to your few large tables.

    I also noticed another correlation in my database disk usage. The larger the backup size, the more disk space I needed for my database. Since a backup contains mostly data (and some log records), it represents a fairly good estimate of the size of you data on disk. Since you also need to plan for backup space on your server, I found this formula to be very accurate for me.

    TS = d ( BS) + BS + p (BS)

    where

    • TS – Total data size on disk
    • d – days of full backups to keep on disk
    • BS – Full backup size
    • p = percentage pad of data size to keep

    Here I calculate my total data size on disk as being the number of full backups I keep multiplied by the backup size. I then add in my database by itself (the single BS in the middle) and finally a pad percentage of this size.

    What is even better is that each SQL Server already tracks backup size in msdb.dbo.backupset in the backup_size column. If you have been running backups on your server, you can calculate the average and the growth of this number over time. That will easily allow you to plan for future space needs.

    It’s simple, and an easy way to track disk requirements over time. I would recommend storing this data elsewhere as the msdb table periodically get cleaned out, and in the event of a DR situation, you still need this data to be restored on your new server.

    A Side Benefit

    I would also recommend tracking this on a daily basis, and if it grows an extraordinary amount over one day, you can investigate to see if something has fundamentally changed in the way your database is used, or if this was an unusual event. My rule of thumb was if a backup grew more than 15% of what I expected, I should investigate.

    This has alerted me to new development changes, the addition of new customer or partner data being loaded regularly, along with various other changes in my systems. This has allowed me to better manage growth and plan for new disk needs in advance of actually needing them.

    This is 15% in unexpected growth, not 15% growth. If I expect 2% growth in my backup file each data, then if I received 17% (15% unplanned + 2% planned growth) in one day, I would investigate to determine what had happened on that database.

    Gotchas

    There are a fwe issues with this method that you need to be aware of, and perhaps plan for separately. The first is dealing with partitions/filegroups, the second multiple disks, and the third is log space.

    I have typically worked on databases that had one filegroup. In this situation. That is a very simple server, but as data sizes grow, and more people take advantage of partition features in SQL Server, you might easily end up with very different sized partitions or filegoups on different disks. If your data tends to distribute fairly evenly, you can still use disk backup sizes to guesstimate future needs. However if you have tables growing at different rates, you will need to track the size of each partition or filegroup separately.

    There is also the issue of planning across disks. A best practice dictates that backups are stored separate from the data, on separate physical disks. This means that while the calculations above are relatively simple, you need to be sure that each disk you use has enough space for its needs. Don’t assume that backups can use the space on you data drive or vice versa. As you track space used over time, it needs to be broken out by logical disk drive.

    The last issue, which varies from server to server, is the capacity needed for your logs.  The amount of log space needed each day by a database varies with the actual load on that server. If you have very busy servers, you will generate more log records, and larger log backup sizes, than if you have less load. Unlike a full backup, whose size is similar on days when there is little activity (such as weekends for many businesses), log backup sizes can vary tremendously during low activity periods.

    My approach with logs has been to guess at log size, or leave it at the current size, schedule log backups, and then calculate the total log backup size each day. This tells me roughly how much disk space I need for log backups each day, and I can multiply this by the number of days I am keeping to determine log backup space needed.

    For the actual log size, there are numerous articles, but you essentially need space for your largest log backup plus some padding. This might be the space needed for a single log backup at 5:00pm that is twice the size of other log backups. You cannot take the total day’s size and divide it by the number of log backups. You need to account for the largest log backup, based on whatever interval you choose, and add some pad.

    Note that your needs can change dramatically if you change the frequency of your log backups.

    Padding

    I have mentioned padding numerous times in this lecture, however I have not given concrete guidance. That is because the amount of pad will vary, depending on your tolerance for issues, and the time you want to devote to this topic.

    If you only want to manage space once a year, you need a larger pad for your estimates than if you are willing to manage weekly. I prefer to proactively grow space on servers once a quarter. So I estimate (based on backup size) the usage for the next quarter, and then add in a 10% pad to that amount. If this is a critical server, or one with little history, I would increase that pad to 15% or 20% to ensure I do not have issues.

    Note that while I don’t want to grow space more than once a quarter, I have processes in place to track space daily and I investigate any unusual growths immediately.

    Conclusion

    Planning for the disk capacity needed can be a complex and confusing task. It involves a lot of variables, and ultimately is a bit of a guess on the part of the administrator. As a result, I try to keep it as simple as I can and track at the highest (most gross) level possible, which has been the data size in full backups.

    However you feel to track space, you should be doing it in some way and reviewing it periodically. I tend to manage space once a quarter, for all servers, proactively growing space as needed for each database. This requires that I have some idea of the past use so I can plan for the next quarter’s use, as well as be ready to order/allocate more as needed.

    If you have comments, or would like to add your blog to this lecture, please leave a note and link below. I will add interesting blogs to this lecture as a separate section. Next time we will conclude this week with a look at other issues to consider when capacity planning.