Voice of the DBA

Tag: sql server

  • Patches for SQL 10 and Techniques for SQL 11

    SQL Server 2008 R2 gets a few patches this week

    A little over a week ago saw a number of patches released for SQL Server, including the much awaited Service Pack 1 for SQL Server 2008 R2. While I wouldn’t caution people to wait for SP1 any longer when a SQL Server version is released, there are people that still wait, so I would expect that adoption of SQL Server 2008 R2 might jump over the next few months. There were other patches for SQL Server 2008 coming out as well, though none of the fixes were critical. If you are having issues, however, check the CU KB articles to see if something you are experiencing is fixed.

    The last few years, with the regular every other month CU patch cycle have gone very smoothly, and it seems that quite often there are less and less fixes being included. There are still bugs, but overall it seems the quality of the software has risen, a trend that I hope continues in SQL 11, AKA Denali.

    Speaking of Denali, CTP3 was released recently and there are quite a few people out there testing and experimenting with the changes. I have no idea when the final product will be released, but usually CTP3 is fairly complete and I’d expect an RC0 or even RTM later this year, depending on feedback.

    This week I found a general overview of Denali, as well as a look at SSIS, Powershell, SSAS, and more from different people that have different focuses on the SQL Server platform. There’s even a look at the projected certification overhaul that should be taking place for the SQL 11 release. It’s a step in the right direction, and I look forward to seeing how the exams change to better measure skills, not memorization.

    Steve Jones

    Don’t forget that SQLServerCentral is sponsoring a track at SQL Connections this November. A great conference to come learn about SQL Server as well as many other Microsoft technologies.

  • The Cost of Page Checksums

    What’s the overhead for a page checksum? I’ve seen various numbers thrown out, but I thought that Paul Randal had written at one point that it was around 1-2%. I can’t find a reference, but in this post, Paul doesn’t dispute that.

    In any case, it’s better to find corruption than save 2% of your CPU. I’d argue the same thing for performance monitoring, which I’ve often seen referenced at 5% of the load. If you don’t have 5% overhead, you have other problems.

    You need to measure things.

  • SQL Server Backup – Inadvertent Striping

    When I started working with SQL Server I got bit in the rear one day while I was testing backups. I was in the process of making a quick backup before I deployed some changes. I think this was in the Enterprise Manager days of SQL 2000, but it could have been v6.5. In any case, I had a dialog similar to this one that I’ve shown in Management Studio. For simplicity I’ve recreated this with AdventureWorks:

    stripebackup

    No biggie, right? I click add, and enter my new file for the backup:

    stripebackup2

    I accept this, and highlight me new file and click “OK” to do the backup. That will work, won’t it?

    stripebackup3

    Actually it won’t. or rather, it will but not in the way I expect.

    What this will do is create a striped backup (search in this article for “striped). I will have my data in a backup, but I will need both of these files in order to do a restore as each will only have half my data.

    It’s a rookie mistake, one that’s not possible with a script, which is why you should backup with scripts, not the GUI.

    However if you notice this, delete all the files, add yours back, and make a full backup right away. Chances are that you might have broken your recovery chain. And make sure you let everyone know this is not what you want to encounter.

  • DRI or No DRI?

    This editorial was originally published on Feb 19, 2006. We are reprinting pieces this week as Steve is traveling in the UK.

    There is a great debate going on in the forums about using PKs/FKs to enforce RI, in other words, declarative referential integrity (DRI) as opposed to using code logic to ensure that proper relationships are maintained in a schema. The opening post is a DBA stunned that developers in his new company do not use DRI. The reason given is performance is degraded, and that’s one that I can buy. There is additional overhead for checks with DRI, but it’s minimal and actually David Poole has a great article about this topic coming soon.

    It’s interesting that one of the questions is about how many 3rd party applications do not use DRI in their databases. Who knows why, though my suspicion is that they can easily “fix” issues with the applications with backend updates rather than maintaining good DB design practices. Or that they can easily alter the application to meet changing needs at various customers’ sites.

    I think it’s mostly the latter reason. That and laziness. I think the vast majority of developers are lazy by nature and dealing with DRI constraints when building an application is a pain. It’s a pain for me, but I still think it’s a good idea. Just like stored procedures add some overhead, so does DRI and many developers I have seen, both building shrink wrap and corporate software, don’t want to deal with the overhead.

    Personally I like having DRI implemented for the same reason that developers use objects, libraries, functions, and other consolidation techniques. It centralizes the “rules” about your application and ensures that they are always used. I agree there is overhead, but it’s a minimal amount and if you are seeing this on most servers then you are probably underpowered.

    My big concern is that often multiple applications or even multiple modules of a single application often need the same business rules: like no orders unless we have a valid customer number. If you depend on the application to enforce this, then you are gambling that every developer will do it correctly and the same way. In small, tightly controlled and managed environments, this works great. But as you grow your development teams, then it is easy for someone to forget to implement some RI rule or implement it differently than others.

    And those bugs are hard to find.

    I don’t think that every application needs DRI setup in it, but if you choose to not implement it, be sure you understand the consequences of your actions. And your boss does as well.

    Steve Jones