Voice of the DBA

Author: way0utwest

  • Disk Partition Alignment–MCM Prep

    Does disk partition alignment matter to SQL Server? Without a doubt. When new disk partitions are created, there could be a reserved set of sectors that could differ across disks because of the way that the hardware interacts. In the white paper, Disk Partition Alignment Best Practices for SQL Server, there is an image that helps to explain this:

    Starting with Windows 2008, the disk partitions are automatically aligned to help improve performance. In a decade, it is unlikely that this will be a problem for most systems as most of the installed systems will be running Windows 2008 or later, at least for SQL Server.

    However now there are still lots of Windows 2003 and Windows 2000 systems out there at this time. For those systems, they could be experiencing a degradation of up to 30% according to the testing done by Microsoft. Which means that you can get a quick performance improvement in your systems, if they are disk I/O bound, if you can realign partitions.

    How can you do this? The hard part is that you must move everything off the partition (all data), delete, and then recreate the partition and restore data. That can be a time consuming exercise, but it is really a time effort. It doesn’t cost anything if you have extra disk to hold your data and can handle the downtime. In the white paper, there is a section that explains how to align your partitions in Windows 2000 and Windows 2003 using diskpar.exe and diskpart.exe, respectively.

    Note that this is mentioned in another white paper on SQL Server Best Practices. This gives you a number of I/O related pre-deployment best practices that you ought to consider before installing SQL Server on your systems. If you are building a system of any importance, this is a great article to understand, and apply many of these practices before SQL Server is installed.

    This can cause a delay in the deployment of a new server, but performing these tests and establishing a baseline cannot be done later, and discovering potential problems early can help you to build a better performing server from day one. Finding these problems later will ultimately result in way more embarrassment and hassle than implementing a short delay before deployment.

    If you have a group of people responsible for installing Windows and possibly SQL Server as part of your build process, have them review the article, or even give them a checklist that will help them to incorporate this testing and benchmarking into their routine.

  • Changing the Past

    The National Archives

    A few years ago my accountant caught a mistake in one of our tax returns. This was during the next year’s filing and so we had to amend to previous return, filing against almost two years later to correct an issue. Fortunately the error was in our favor and we ended up receiving a refund.

    In most businesses you typically do not change data that was completed in the past unless there is a severe error. For some types of data, such as audit data, you never want to change it. And in many cases, even if there were some change, because of the way that you have operated the business based on those past values, you might decide not to change things.

    However as more and more data is accumulated, and used in business decisions, legal proceedings, etc, it is likely that more data professionals will be faced with the questions about when it is appropriate to alter something. With that in mind, this Friday’s poll is:

    When would you change archived data?

    Are there circumstances that you are aware of where it is valid to change data that might be archived? Is it only to make corrections that were recorded incorrectly in the past? Is it to normalize data, so events like companies merging are able to run reports with pre-merge data?

    This is a thorny subject, but it seems like one that we should be thinking about as data professionals, looking to give guidance to our clients.

    Steve Jones

    (Originally published at http://www.sqlservercentral.com/articles/Editorial/72232/)

    The Voice of the DBA Podcasts

  • The Face of BI for SQL Server

    Donald Farmerannounced recently that he was leaving Microsoft for QlikView. For many people, Donald has been the face of BI for Microsoft. His many talks on all aspects of the platform have entertained and informed thousands of SQL Server professionals all around the world. However I am sure that many of us will continue to learn from Mr. Farmer in his new role at QlikView.

    Many people outside of Microsoft were sad to see Donald leave, including me. However I support and understand his decision, and I know that there is still an amazing team at Microsoft that will continue to work with the SQL Server BI pros everywhere.

    I felt the same way when others have left the SQL Server team in the past. Some have been good spokespersons or speakers. Donald Farmer leaves a hole that will be hard to fill, but we still have Amir Netz, among others.  Amir is someone that I would always recommend you go see speak, even if you’re not interested in the topic. Like Donald, Amir is entertaining and has an infectious enthusiasm that makes you enjoy the technology.

    There are plenty of other talented people working on SQL Server, and I am sure that we will find some other great speakers to help us learn more as SQL Server continues to evolve in the future.

    Steve Jones

    (originally published at http://www.sqlservercentral.com/articles/Editorial/72200/ )

    Podcasts

  • Skunk Works

    The SR-71 Blackbird cockpit

    The abstract at the top of this article is great: “Using SQL Server, you can build a BI platform without getting your organization’s top brass involved.” Not that I condone you going off on your own to do work without any communication with others, but funding and starting a BI project can often get shot down when too many resources are involved. It might be just as likely is the approval to tackle a long project often gets bogged down because too many people and too many objectives are involved.

    The release of SQL Server 2008 R2 didn’t excite me very much because I have always been a core database engine person and this version mostly had BI improvements. I like the database engine, T-SQL, and working with OLTP systems. However I do see the value in building BI systems, and the enhancements made to the SQL Server platform, mostly in SSIS, SSRS, and SSAS with Powerpivot, mean that you can build an incredibly powerful application to work with business data without stepping outside of BIDS and SSMS.

    A skunk works project is a great way to try out BI ideas, and make some useful prototypes that end-users can take advantage of. If you find that you have built something useful, then you can approach upper management with a request for more resources to build a better system. I would not go this alone, however, without at least getting some approval from your manager to spend time and resources to build something. I’d also be sure that you implement good security practices; after all, if you use production data, you need to be sure it is protected at the same level as the production systems.

    Steve Jones

    (originally published at http://www.sqlservercentral.com/articles/Editorial/72180/)

    Podcasts

    * Video Podcast – 17.1MB WMV
    * Video Podcast – 13.7MB MP4
    * Audio Podcast – 3.2MB MP3