Voice of the DBA

Tag: sql server

  • DBA Support

    There was a time when I managed two production databases on SQL Server. Two. I had a development version of one database where we paused development for testing, and only two production databases to manage. Since I had to also handle development, application support and hardware repair/replacement, that seemed like plenty to me. I was the accidental DBA, with database administration being the lowest priority of my day.

    After that I moved on to administer databases in a number of jobs, sometimes as a priority, sometimes not, but in each case, I learned to work more efficiently and effectively. My goal was to automate as much as possible of the routine work so that I could spend my days adding value to the company. I learned to use scripts, alerts, jobs, and more to keep systems running while I was doing other work.

    I’m sure many of you work in a similar manner, or at least I hope you do. This Friday I wanted to ask you at what scale do you need to become efficient, based on the size of your organization. The question this week is:

    How many databases does each DBA in your organization manage?

    I know some of you manage lots of databases in raw numbers, but also let us know if you need to do much with these databases. Is maintenance automated, or is there much active management you need to do in order to ensure these databases are running on a weekly basis. Let us know the size of your load as well, perhaps the amount of data is a better way of measuring the DBA load.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Two Steps Ahead

     

    think ahead
    Are you thinking ahead? Using the data from your systems to be proactive?

    Exceptional DBAs do more than respond to events and issues in their environments. In many cases, I think they even go beyond using metrics that detect problematic activity on their systems before users notify them. I think the best DBAs will actually mine the information they have about their systems to anticipate problems in advance.

    In the past I’ve had monitoring systems that would respond to issues, and I had alerts setup on the system to notify of unusual events, like an unexpected data growth. What I had started to do before I became a manager was start to write system checks that anticipated future problems and allowed me as much lead time as possible to prepare for issues. An example of this was a set of queries I wrote that calculated data growth for all databases on an instance and then used that to calculate how many days would elapse before I ran out of space on the data drives.

    You can write similar queries to look for other trends. Tracking the execution times of often-run queries, or those queries which are important to the application can allow a DBA to find potential issues. If the execution times are growing, the DBA can anticipate a problem occurring in the near future and begin taking action to rewrite, tune, change indexing, or some other measure. A broad spectrum of queries taking longer might be an indication that hardware needs to be upgraded. There’s even a site devoted to metrics.

    Instrumentation is important in understanding, analyzing, and predicting system performance. More and more tools are being released to gather detailed metrics on .NET code, in browsers, and more, but there is a wealth of information inside SQL Server on the performance of the platform. A little work can help you track and monitor the performance of your system and proactively maintain performance before your users complain.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • A SQL Server Log Reader

    SQL Log Rescue
    It would be great to have a log reader built in. If you’re still on 2000, here’s a free one.

    One of the regular problems that data professionals have to deal with is the whoops disaster, or some type of data entry error. It could be the DBA running a delete without a WHERE clause, or a user updating a bunch of data incorrectly. No matter what the cause, this is a problem that you will deal with multiple times in your career. There are a few tools available that will read the SQL Server transaction log and build reversing transactions, but there are few, mostly because there’s just no money in the tools. People don’t need the tools often, and when they do, they are likely to just download an evaluation, fix their issue and not purchase the tool or struggle to undo the damage. As an aside, if you are still working with SQL Server 2000, Red Gate has released our Log Rescue tool for free.

    With every new version of SQL Server, there’s a lot of effort spent on building new features and enhancing old ones. Marketing and sales drive a lot of resource decisions in product development, and that makes a lot of sense. But it doesn’t mean that there isn’t value in plugging holes inside of the platform and making SQL Server easier to work with.

    A log reader tool would not be that hard to build and include in SQL Server. I’m sure Microsoft could build and release limited versions that might deconstruct certain types of statements, or might only read transaction log backups. I bet this would be a great intern project, allowing the very talented youth that spend a semester or two at Microsoft the chance to make an actual impact on the product. Back-ports to previous versions of SQL Server could be done with very little cost to Microsft, provide customers with a valuable tool, and allow college students to prove they are worthy of a future job working on the product.

    The ecosystem around Microsoft products is important, both for the growth of the platform as well as filling customers’ needs. Log readers, however, are not economical separate products. They ought to be incorporated into the platform, filling a hole that would greatly benefit lots of DBAs and developers who deal with incorrect data updates every day.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Trace Flag 2371 and Statistics

    One of the issues that I see published often on forums like SQLServerCentral is the advice to update statistics on your tables if you have strange performance issues, or sudden changes in performance. Statistics are important for the query optimizer, and you should understand the basics of how they work.

    However there’s a problem with statistics. They get out of date. SQL Server will automatically update statistics, but it doesn’t do this constantly. It does it after 20% of the table changes (by default). If you have 1000 rows, that means 200 rows changed (or added) can trigger the update. If your table has 50 changes every couple days, you’ll get statistics updated every week.

    If you have 1mm rows (think large, historical data here), then those same 50 changes won’t trigger statistics updates for a long time. 200,000 changes will be needed then.

    There’s a trace flag, 2371, that can help with the minimum needed to trigger a stats update (or you can do this with your own jobs). By choosing this, you can lower the minimum for triggering an update.

    What you do really depends on your issues. If you find poor performance in queries, look for wildly incorrect estimates of rows in your query plans. If you find that your statistics aren’t being updated, or not updated enough, you might enable the trace flag, or create your own job to update statistics manually.

    Note that if you are rebuilding indexes, you don’t need to also update statistics on the columns in the index. They are done as part of the index rebuild.