Voice of the DBA

Tag: Performance

  • Living Moore’s Law

    I’m not much of a hardware geek these days. For the most part I just want my computers to work. Actually if I think about it, I really just want my services to work. I don’t care what specs they have, or the technology used.

    If I went back to managing databases, I would be looking for databases to work on, not individual machines. I think that’s one reason that the idea of cloud database services appeals to me. Not necessarily clouds on the Internet, but as a series of machines in my data center that I could just plug together and have SQL Server run various services across them.

    In reading a little about eBay and their new data center, I think it might be very cool to work there. The director of their data center talks about living Moore’s Law, with the new data center in Utah. While I’m sure many of the large internet companies like Microsoft, Google, Rackspace, etc. would also be interesting, I’m not sure how many of them are on a two year cycle, replacing all of their systems on a rotating basis.

    I live on a 35 acre ranch, and it’s a lot of work keeping it up. That can help me appreciate the scale of the new eBay data center, which is a 60 acre campus. Their phase one approach has 7.2MW of server load in the building with some racks generating over 30,000W of heat. That’s quite a hot aisle!

    At that pace of change, I have to think that it’s both exciting and a challenge to keep systems running on new hardware and also modifying code to take advantage of the new capabilities offered by hardware changes. It’s a challenge that I would love to tackle at some point in my life.

    Steve Jones

  • NoSQL Is Not Everywhere

    How many places really use NoSQL? Facebook, one of the largest sites on the Internet, has had their Cassandra database service receive  a lot of press over the last year. In pointing to them as an example, many technologists use the argument that if Facebook uses Cassandra to run their extremely data intensive business, then it ought to be good enough for the rest of us.

    Cassandra might be great for your business, but is it the best choice? How does that argument apply when Microsoft.com, also one of the largest web sites in the world, serves millions of users a day with SQL Server as the back end? Twitter also uses an RDBMS to store it’s tweets. It uses the MySQL database, an RDBMS, though they are investigating and integrating Cassandra in places.

    The point is that NoSQL databases, like .NET assemblies or J2EE applets are just technologies. They’re just tools used to build an application, and they can be built well, or built poorly. Is Cassandra bad because it didn’t work well for Digg? No, just like those companies that have switched off SQL Server to some other platform aren’t necessarily doing so because SQL Server can’t meet their needs.

    It’s up to the architect to put together a great platform for an application, using whatever tools they choose. Hopefully they are choosing those tools because the fit the problem, or the skills of the developers. I’d be disappointed in an architect that chooses something because it’s new, or discards another technology because of some personal bias.

    Steve Jones

  • Big Data and SQL

    I ran across this article on 8 Big Data Deployments in Detail, which was funny since the detail consisted of an old system, a new one, a capacity, a date, and a paragraph. If that’s detail, then I wonder what a synopsis would be. Only one of the 8 was using SQL Server, and that had even transitioned to a ParAccel system. Most of the other systems used Oracle previously and they ranged from 7TB (not big, IMHO) to 2.5PB (which is big).

    Despite that lack of any real information on what these companies were  doing, I did see some interesting things listed.  There were a few notes that mentioned compression in a few places, often column store based compression that dramatically sped up processing for the systems.

    However the one really amazing fact that I noticed cam from Cabela’s, the sporting goods retailer. They had a short note that retraining their statisticians to be “more SQL-based” and made them more effective. They reduced the need in one area from 7.5 full time equivalent people (FTE) to 1.5 for the same work.

    Hopefully that didn’t mean that 6 people lost their jobs, and instead they were able to focus on other work and find ways to better help the company. That is fairly telling, that training people to understand SQL, and maybe write better queries and analysis themselves can make them much more efficient.

    It makes me wonder if the same thing might happen with developers. Some of you out there write software for a living with some OOP language, but are talented using SQL as well. Do you think that building those skills is  a worthwhile investment for other developers?

    I think it is. Now if I just had a way of convincing more developers this would help their careers.

    Steve Jones

  • Searching for Plans

    I was re-reading Conor Cunningham’s “Does Join Order Matter?” recently and found this quote in the post that stood out. As an aside, read this post. It’s fantastic.

    “…SQL Server’s QP is actually not set up to give you the best plan.  Instead, it is set up to give you a good enough plan quickly (where good enough is very close to “best”).”

    I get that, and it makes sense. Why spend 4 hours optimizing a query that runs in 2 minutes to get it down to 1:50 (Conor’s example) when the optimizer can spend 1 sec and have it execute in those two minutes. If the optimizer looked for the best plan, I’d be very angry as a customer. Especially if this happened with any frequency.

    Or would I?

    I’m not suggesting that we ought to optimize every query, or that I’d prefer the optimizer didn’t try to normalize queries, or that I want to see the internal tree and try to figure out if I can rewrite the query. I might, however, want to do that for some queries. There are queries that I might want to find the “best” plan for, either because they take a long time to run, or they are run often. I could then use that in something like a USE PLAN.

    Computers are cheap these days, as is disk. If I were to somehow able to copy my database onto an instance of SQL Server on a cheap PC, wouldn’t it be cool if I could set that optimizer loose on my query and let it try ALL possible plans? I’d be happy if it spent that 4 hours and gave me back an optimal plan that I could carry back over to my expensive, busy server, and shave that 10sec off a query that I run 10,000 times a day.

    I could see some value in a “test” instance of SQL Server that just looked for optimal plans. Now if I could just get Microsoft to build it.

    Steve Jones