Voice of the DBA

Author: way0utwest

  • Time for a ROWID?

    Do we need a ROWID structure in all SQL Server tables?

    One of the things I’ve been doing lately is looking forward to future versions of SQL Server. Not SQL 11/Denali, which has CTP 3 out now, but more towards the future. SQL 12 and beyond, and wondering how the platform can advance and incorporate new ideas and knowledge from other areas such as NoSQL or NewSQL. I also have been trying to decide which parts of SQL Server could be improved to be more robust of scalable, or address some failings in the platform.

    Our systems seem to be growing larger, and storage seems to cost less all the time. While this doesn’t always result in cost savings, it does mean that we may be ready to increase the page size in SQL Server once again, perhaps growing to 16k or even 64k. If that follows as I/O transfer sizes grow, then is it time to add in a rowid marker of some sort that gives us a unique handle to every row on a page?

    I know this adds some overhead, and in reading about the internals of current pages, that overhead can be significant. However it seems that I find people that regularly have non-unique clustered indexes, or heaps, and could require SQL Server to differentiate the rows internally. With our busses increasing in size and larger sectors on disks, perhaps the overhead isn’t out of the question.

    This would allow for some interesting benefits for replication, with the possibility that any object could be replicated, no matter what the structure. It also might allow us to very efficiently eliminate duplicates and perhaps improve the efficiency of some T-SQL commands.

    What do you think? Is it time for a true ROWID? Time to increase the page size for SQL Server?

    Steve Jones

     

    The Voice of the DBA Podcasts

  • NewSQL Can Save the World

    Michael Stonebraker, a database pioneer and researcher, recently said that Facebook’s implementation of MySQL was “a fate worse than death,” and they ought to rewrite their entire infrastructure. I think that analogy is a bit extreme. That statement also shows me that relational databases can scale to some dramatic heights with creative engineering as Facebook as shown with MySQL. I suspect that Facebook could easily use SQL Server and complex sharding of data to power the site if they wanted to in place of MySQL.

    Mr. Stonebraker has another solution for the “oldSQL” problem of scaling to very high volumes of transactions while maintaining ACID principles. He has a company that makes VoltDB, a database that lives in memory, complies with ACID principles, and can scale to very high transaction volumes.

    Can VoltDB solve the RDBMS scaling issues?

    That’s part of a wave of products that are classified as “NewSQL”, or even ScalableSQL, solutions. These databases embrace the power of SQL and even relational databases, but build scalability and high availability directly into the products. It’s interesting to see SQL Azure on the list of NewSQL products, and it makes me think that Azure is much more than just a VM in the cloud running SQL Server. At least I hope it is and includes some scaling features beyond what a single VM can provide.

    Running in memory scares me, but perhaps if you expect failure of nodes, and you have some sort of replication of the data among many nodes, it can work. I do worry about the consistency across the nodes and the latency that can affect this, but perhaps these products do a good job of addressing these concerns. I still think a large scale power failure could be disastrous, so I assume these products do write to disk at some point.

    I do agree that the relational model can scale, and I’m sure that we will see SQL Server grow to encompass and accommodate the concerns of customers that need to run large scale databases in the future. I’m just not sure how they’ll do it.

    Steve Jones

    The Voice of the DBA Podcasts

  • Leave Developers Alone

    Working in my own litlte world

    A computer deals with interrupts all the time. They are the mechanism by which it can simulate multi-tasking among many different programs in a modern operating system. However those interruptions have a price, and too many of them can affect performance. As hardware grows larger, we have other issues from interruptions that we try to mitigate with techniques like soft-NUMA affinity or multiple pipelines built into hardware. All of these are designed to prevent a computer from spending any significant time on non-productive tasks because of interruptions.

    In the real world, many of us deal with regular interruptions from work. They might be emails, instant messages, phone calls on a cell phone, or the old fashioned someone-stopping-by-your cube-to-chat. All of these things add up to less productivity, especially for developers. One study finds a 10 point IQ drop from regular email and phone interruptions. I don’t know about you, but I’m not sure I can afford a 10 pt drop in IQ when I’m working.

    Some companies are starting to realize that developer’s brains are a scarce resource, andinterrupting them can dramatically impact productivity. I have found some places, like this one, that are setting aside quiet time for developers to work without being bothered. Similar to the technical debt that Steve McConnell has talked about, there seems to be an interruption tax that some development shops are loathe to pay.

    Even if you don’t gain any productivity, or have fewer bugs, or ship more often, I think that your developers will appreciate it. It could be an easy way to increase happiness, improve retention, and even sell your company as a good place to work. And it’s easy to implement: just leave people alone a few hours a day.

    Steve Jones

    The Voice of the DBA Podcasts

  • 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.