Voice of the DBA

Author: way0utwest

  • Referencing Remote Data

    Maybe if I searched more, I'd use synonyms

    One of the features added to SQL Server a few years ago were the ability to create synonyms and use those to reference other objects. The ability to create synonyms is something that I had wanted for years in SQL Server, but when they were released, I found them to be a tool that I rarely reached for. Whether it was because this was something I rarely needed to accomplish, or because my habits were too ingrained, I’m not sure, but I have only created synonyms for testing purposes and not for use in any production databases.

    When a developer needs to reference data in another database (or on another server), they have a variety of ways in which they can do this. Some people prefer a three or four part naming convention, others use a view local to the database, and still others might use synonyms. While they all work, from a maintenance standpoint, I think a view or synonym provide a nice layer of abstraction while minimizing the potential maintenance headaches of future changes.

    If you find synonyms more useful than local views, I would be interested in knowing why. They seem to almost operate in the same way to me, but for some reason I find views to be easier to track and manage. Perhaps it’s just an ingrained habit from years of making do with views, or maybe it’s my habit of browsing for objects, instead of using a tool like SQL Search.

    Whichever method you use, I do urge you to always consider a layer of abstraction. That other database you reference might be located on the same instance today, but in the future it might grow and require it’s own instance. If you have three part naming buried in all of your stored procedure or application code, it might not be as simple as a global search and replace to make changes. If it’s not, then you are wasting development time down the road by not having a layer of abstraction implemented at the beginning.

    Steve Jones

    Steve Jones

    The Voice of the DBA Podcasts

  • DBAs in Denver

    I have a friend looking for a production DBA in Denver, so if you’re interested, email me at SQLServerCentral.

    They need a production DBA in the South Denver area, backups, config, query tuning, replication, partitioning. SQL Server 2005-2008 R2.

  • Congrats, Yanni

    Sunday was an exciting morning for me and quite a few others in the SQL Server community. My morning was interrupted constantly as I was tied to my Twitter stream, looking for updates like this one:

    One of the updates I caught during my morning

    I had puppy duty, dinner to cook that required a trip to the store and 8 hours of cooking. I had a few chores to handle like horses to feed, and I found myself taking longer to do those things than I should have because I was constantly checking my phone. I wasn’t the only one as I saw 6 or8 other people in the SQL community, all cheering Yanni on. I’m sure there were others outside my twitter feed as well.

    We were all sending tweets that the Runmeter app will read to you over headphones. I’ve used the app and it’s cool when someone sends you a note when you’re running, even if it interrupts the music.

    I probably sent 20-30 tweets to try and help cheer Yanni on during her first marathon. I met Yanni last year, and I’ve been following her journey as she climbed a mountain, ran a half marathon (with other SQL people), and made the decision to run just a marathon, but a marathon and a half in a week. As someone that’s run a marathon, I can appreciate her effort, accomplishment, and I’m impressed.

    I’m also thrilled to be a part of the SQL Server community, which in many ways seems to be a tighter set of groups of people. Groups because we aren’t one big happy family, but we are a series of smaller ones that support each other, cheer each other one, defend each other, and laugh with each other. The biggest reason that I attend events is to meet people and spend time with them.

    Yanni is never without a smile on her face, and I’m sure part of it is the wonderful people who work with SQL Server in their spare time, when they’re not making life a little bit more interesting to each other, 140 characters at a time.

    I congratulate Yanni, and I urge you to do the same, with a comment on her marathon post.

    Update: This is the post race post.

  • Capacity planning for new hardware

    I get asked this question a lot: When getting a new application and database, what kind of hardware do I need?

    The answer is easy: it depends.

    It depends on

    • the load you will put on the SQL Server instance.
    • on the SLA and performance numbers you need.
    • the uptime you need to maintain (RPO/RTO factor in here)

    There are other factors, but essentially the server needs enough hardware to handle the workload in the time you need it to handle things.

    It doesn’t depend on

    • the number of users
    • the number of databases
    • the size of the databases*

    A slight asterisk on the last one. The size of the databases matter for the space you need to buy, but they don’t necessarily affect the RAM or CPU you need. The number of users and databases can contribute to load, but those numbers of a vacuum don’t affect the hardware. I have a test machine with dozens of databases, and it generates no load. Why not? No workload, or not much of one.

    The same thing applies to users. If the users do a lot of work, making changes, querying large data sets, it can be a loaded database. However I had a database one time that had 5,000 clients. It was updated by agent software on desktops in our company every hour. However each update was a singleton update to a specific row, and it was comfortably hosted on a 2CPU, 2GBRAM Standard SQL Server 2000 instance.

    How do I plan?

    Photo Jan 03, 8 50 26 AM

    This is a tough question in many ways. I have searched all over, and asked questions, and even written a SQL University week on it (overview, disk, other). There are two main scenarios here, but they both get similar treatment.

    You have to test your workload against hardware and see what happens. It can be a simulated one, or if you have an existing instance being upgraded, take a real workload.

    There are numerous replay tools or testing tools, but the bottom line is you have to simulate the way you use SQL Server on actual hardware. You can do some extrapolation, but don’t expect it to be a linear change.

    For example, if I have a dual core CPU of xx type, with 1GB of RAM and a 2 drive R1 array, I can’t assume I’ll get twice as much performance with a quad core of the same type with 2GB of RAM and 2 R1 drives. It almost becomes an art to examine the memory usage, the IOPS you are generating, and the percentage of CPU. As you scale up, the usage of those three values might changes and shift. More RAM can reduce IOPS and CPU, especially in reads, but not necessarily. If you have a 1TB table you’re constantly summarizing, you might not get much better performance going from 1 -> 2GB.

    Ultimately it’s a bit of a guess for most of us. Fortunately most of our workloads can be handled by hardware since most databases are relatively small. Test, make some guesses, and go a little big, especially on RAM and CPU. Those are the hardest to add later, from my experience. It seems people expect disks to fill up and we need more space, but it’s often harder to approve CPU/memory upgrades, especially if it requires newer motherboards.

    I wish I had a better answer, and there are numerous articles that don’t seem to me to do a better job, but read as much as you can, and try to learn how others view their systems. Then make your own guesses about what is best for your system.