Voice of the DBA

Category: Blog

  • SQL University – Capacity Planning Week

    I am honored to be a guest lecturer this week for SQL University. There have been some amazing professors helping guide you through SQL Server this semester, and I hope that this week meets your expectations.

    The topic for this week is capacity planning. While not a critical task for DBAs on a regular basis, the failure to properly plan for the resources needed can easily become a critical problem at the worst possible time: when your systems are busiest.

    What is Capacity Planning?

    Capacity planning involves making estimates of the resources that will be needed in some area or system and then ensuring that you can acquire those resources to meet demand. The resources can be time, money, hardware, people, or just about anything that is used in your environment.

    The traditional method of capacity planning deals with the resource requirements that your systems need. For SQL Server this has primarily been CPU, memory, and disk resources for each instance. While this seems to be a fairly benign and perhaps boring topic, it has enough moving parts that it quickly can overwhelm a DBA tasked with planning the resources needed and budgeting for them appropriately. There are other potential issues, and some of those will be discussed on Friday.

    As our systems become more complex, include more interconnections between them, and become more important, the ability to properly plan for, and meet, the demand for your services is an important part of the data professional’s job. Even if you have server administrators, SAN administrators and other professionals responsible for physical hardware, if your database is not available or does not scale, you will be blamed.

    Server Sizing

    People new to managing database servers constantly ask the question “how big a server should I buy?” as if there is a standard size server for a particular number of users or databases. There isn’t one, and these factors don’t really apply to proper server sizing:

    • number of users
    • number of databases
    • number of transactions
    • number of records in your largest table

    While these numbers do seem to increase as the load on your server increases, resulting in the need for more resources, these aren’t the way that you can size a particular server.

    Ultimately for new systems you can only take a guess at what level of CPU, memory, and I/O you will need. Likely it will be a bad guess, and so most DBAs and server administrators try to buy the largest server hardware just in case database use is far beyond what they expected. Or they economize and wind up with a severely underpowered server and a large percentage of unhappy clients.

    For existing systems, you should have some idea of the load being placed on your systems. From this load, you can extrapolate to make predictions about future loads and plan accordingly to meet the demand.

    Baselining

    The only really good way to properly size a system is to extrapolate future needs based on the past usage of the system. For existing databases, it helps to have a process set up that determines a baseline of not only the performance of your server, but also the capacity peaks and averages over time. These will help you extrapolate for future demand on the database.

    For a brand new database, you can only take a guess. As good as your guess might be, you will still likely be wrong, but having a baseline process set up at the beginning can help you quickly decide by how much your estimates are wrong and adjust the hardware accordingly.

    There are a number of articles written, and any number of product to help with the setting up of a baseline for your servers. The important thing to remember is that this is unique to each server, each company, and each database. The way that Microsoft Dynamics runs on your server instance will be different from mine, even if we have the same number of users. Don’t examine the scores from hardware manufacturers or software vendors as a way of determining what capacities you need to plan for. Those are useful for comparing systems in a theoretical way, but you need something more concrete for your instances.

    You need an affordable baseline system that you can deploy to all your server instances. I have had good luck with home grown systems at most of the places I have worked. I have also used commercial off the shelf packages (COTS) with as BMC’s Patrol and HP’s Openview to record data, but often had to extract that data to run my own extrapolation calculations. In SQL Server 2008, Microsoft introduced the Management Data Warehouse, which does a lot of this monitoring for you as well, if you have Enterprise Edition. Technet has a performance baseline white paper that gives some good basic guidance on what to monitor in order to measure the performance of your system.

    The specifics of how you gather and store this information are not that important. Use whatever method is convenient for you. The important thing is that you keep this data over time. You do not need extremely detailed information over time, but rather broad averages that allow you to determine if your resource requirements are increasing.

    I used to track the performance at 5 minute intervals for all server instances across a month, and average that down to hour increments for the past year. This easily allowed me to extrapolate for future planning by loading the data into Excel and performing a linear regression analysis. This will produce a line graph that best fits your data points.

    Third Party Tools

    As mentioned, there are a number of products that can help you with baselining and measuring the performance of your systems. Any tool that measures the various Performance Monitoring counters can be used if you can store those measurements over time. What’s Up Gold, Unicenter, even SQL Response (from my employer, Red Gate Software) can help you determine a baseline for your system.

    While it can be a relatively simple task to build a monitoring system on SQL Server, there are a lot of factors to consider, and building a new piece of software might not be the best use of your time. There are often other servers in your environment that also need capacity planning. File server, mail (Exchange) servers, and more. Ask your system administrators if they have a system in place, or software available that can measure the load of your servers over time. It might make sense to use a system already available or purchase one rather than building your own.

    Moving Forward

    That’s all we have for this lecture. Please feel free to chime in on the topic capacity planning and specifically any systems or software that you have preferred for tracking a baseline over time.

    In the next installment of SQL University, we will examine disk capacity specifically as this is an area that most often administrators struggle with managing.

    If you have other links, your own blog, or comments to add to this topic, please feel free to leave a note/link below.

  • SQL Source Control Webinar

    SQL_68x68_SrcCon One of the new products that Red Gate Software has released recently is SQL Source Control. I saw a demo of this product back in March as it was nearing completion, and I thought it was pretty cool. I have struggled with source control for years, and build a process that was mainly manual, and one that I’ve seen others use over the years.  It worked well, but it was prone to errors unless I was allowed to chastise developers on a regular basis for not adhering to the process.

    friends_of_rg_logo Today I acted as emcee and moderator for a semi-private webinar that demo’d the product to the Friends of Red Gate, a group that is by invitation only, but lets some customers interact with Red Gate similar to how the MVP program grants some people access to Microsoft.

    This was the first time I actually acted as a moderator, having been an attendee or presenter in the past. It went well, I think, though I definitely had a little dead air and struggled to keep up with questions and find a smooth way to interrupt the presenter. I would rather be the one presenting and let someone else handle the other work for sure.

    I think we’ll be scheduling more of these on a regular basis for some Red Gate products to get the word out more and show how they can be used to solve problems. This is one of the few that I’d purchase myself if I was doing DBA work, so we’ll start here and see what happens.

    I’m also hoping to do some other more regular webinars from SQLServerCentral, on more technical topics, across the next year.

  • Influence

    Do you influence people? How influential are you?

    I saw a link to The Influence Project, put on by Fast Company, looking for how much influence people have in 2010. The link actually takes you to my profile, which means I’ve influenced you to click the link. I got pulled in when Andy Leonard posted a link and I added to his influence.

    It’s an interesting idea, though I’m not sure what accuracy rating I’d assign to it. Only people that find it interesting will go through the links, so it’s a self-selecting group, not measuring anything absolute about how influential someone is in the world at large.

    However it did get me thinking about implementing something in the SQL Server community. I wonder if people would be interested to see who is contributing a lot to the SQL Server community, maybe by measuring some level of activity (blogging, forum posts, speaking, etc.). It would almost be like some sort of Que Score for SQL Server.

    I wonder how it would correlate to the MVP award. Would there people that had a high score that weren’t MVPs or vice versa? It might be a fun project to get going.

  • Syndication on SQLServerCentral

    I have a few people ask about this recently, so I thought I’d blog about it, and maybe get an explanation out there.

    When we started accepting bloggers on SQLServerCentral, it was a challenge to get people to move their blogs. We had some people that just started blogging with us, but others that were already blogging elsewhere. At first I wasn’t sure what to do, but when SQLServerPedia started syndicating bloggers, using feeds, I requested similar functionality for SQLServerCentral.

    We built it, and while it’s a little crude, and we’ve modified things a few times, here’s how it works:

    • You send us a note with
      • Your SQLServerCentral registered email
      • A feed URL
    • You blog in English (sorry, English only)
    • We read the feed and publish your blog on SQLServerCentral.

    The Scary DBA has syndicated his blog, so you can see how the different things look on our site v the source. Some things don’t come across cleanly, and I’m working on getting some of those things fixed.

    Frequency

    We used to read feeds once a day, now we do it once an hour. So if you blog about something, it ought to be on the SQLServerCentral site in under 2 hours. If you think there is an issue, please contact the webmaster.

    Hints

    I use Live Writer to actually publish this blog on Blogspot. It lets me test things, but also gives me a backup of my blog and control over what I write. I think it’s a good idea to own your content, as well as keep a backup of it on your own blog.

    Live Writer is a great piece of software for drafting blogs, scheduling them, working offline, etc. I actually sync my Live Writer folders  across my desktop and laptops. That gives me some additional capabilities to draft stuff on one machine and finish it on another.

    I highly recommend people tag stuff they want to syndicate with a specific tag and then send us the feed of that tag. That allows you to still blog about something that you want to publish, but not syndicate it if you don’t want to. I know some people do this to publish other stuff in other languages, or they mix their personal/career blog and don’t necessarily want to send everything to SSC.

    Setup

    If you want to get setup, send a note to the webmaster at SQLServerCentral.com and we’ll get you syndicated.