Voice of the DBA

Author: way0utwest

  • Roadtrip! SQL Saturday #53 – Kansas City

    kansas_city_mo After speaking this coming weekend in Denver at SQL Saturday #52, I’m heading to SQL Saturday #53 in Kansas City the following weekend, to give my Modern Resume presentation again and meet some SQL pros in another city.

     

    It’s not just me, however, that is making the trip. Chris Shaw (blog | @sqlshaw), Marc Beacom (LinkedIn | @marcbeacom), and Carlos Bossy (LinkedIn | blog | @carlosbossy), all will be speaking with me in Denver (our home) and then making the Roadie to KC.

     

    No, there’s no tour bus (hmmmm, maybe next year), but we will all be heading to KC along with some great out of towners. Arie Jones (blog | @programmersedge), Wendy Pastrick (LinkedIn | blog | @wendy_dance), and Jorge Segarra (blog | @sqlchicken) are coming from out of town. And for a long plane trip west, @TheSQLGuru, Kevin Boles (LinkedIn, @thesqlguru), is flying his plane from Alambama to come and share some great T-SQL tips with the crowd.

    I’m excited for two reasons. One is that I get to meet a whole new group of people in a city that I’ve only driven through, never spent any time in. It’s always great to meet new SQL professionals, and I’m looking forward to Kansas City, and finally shaking hands with a few people that I correspond with on a regular basis.

     

    kansas_city_royals_field-9435 The second is baseball. The speaker’s dinner on Friday night is at the Royal’s game, and I’ve never been to that stadium. My goal is to attend them all one day, and I’ve been to a lot (NYC, San Diego, Seattle, Denver, Baltimore, Arizona, Boston), and this will be one more.

     

    I’m looking forward to the trip, and looking forward to meeting a few people, enjoying some BBQ, and learning about SQL Server.

  • 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

  • Large Chunks of Data

    I saw a post recently where someone talked about trying to get better performance from a report. They were selecting 5mm rows from a table and wanted to see if there was a more efficient way to chunk out this data so that the instance would not report memory errors.

    My first question is what kind of report has 5mm rows of data? That’s just too much data.

    How long can a report be?

    From your experience, talking with people, looking at what they analyze, how big is a report? How much data can you really display on a report and make it useful for users?

    I’m thinking here in terms of the raw data you show. A pivot table can summarize millions of records, but realistically I thin kit becomes hard to examine more than a few hundred data points on a page. Whether they are raw data or aggregates of other data, it seems there’s some limit to what a report should provide.

    After all, that’s why we have drill-down 🙂

    Let us know this Friday what you think; what you have observed? Maybe we’ll  help others to build better reports that are more practical and useful to end users, as well as easier to develop.

    Steve Jones

  • ACID

    What’s ACID in databases? I was asked this in an interview a long time ago and I couldn’t remember the exact meaning. For a (supposedly) senior DBA, that’s bad. I could, however, tell what it means and what it enforces in the database, I couldn’t remember the actual words.

    In building a presentation recently, I needed this, so I looked it up on Wikipedia. Isn’t everything on Wikipedia? And isn’t it true?

    In this case, they do a good job of explaining the various terms. ACID stands for

    • Atomicity
    • Consistency
    • Integrity
    • Durable

    I won’t repeat their explanations, but try to give my own take on this.

    Atomicity – If you have a transaction that makes some change, it has to all succeed or all fail. No partial transactions. That doesn’t mean that each statement succeeds, but everything wrapped in a transaction, whether 1 or 100 statements, all get completed or all rolled back.

    You can appear to muck with this by nesting transactions, but not really. SQL Server follows this rule of enforcing transactions as a complete unit or work. Either all committed, or all rolled back.
    SQLdependencytracker[1] 
    Consistency – This means that the database essentially enforces consistent change to the database from transactions, and that the database is logically consistent at all times. So references are enforced, cascades take place as part of transactions, etc. It’s a strange concept, but it really means that the database enforces all rules defined.
    Note that one funny thing here is the internal sysreferences aren’t always consistent. That one bugs me, though my employer, Red Gate Software, has a tool that finds these: SQL Dependency Tracker

    Isolation – I always get this one wrong, thinking it’s integrity, but really that’s the consistency piece. Isolation means  you cannot access data that is changed but still in an uncommitted transaction state. This essentially ensures that you get a consistent, accurate view of data. SQL Server allows you to bypass this with dirty reads, and lots of people do this to improve performance, but I think it’s a bad idea in general.

    Durability – If you have any type of failure, usually hardware, this ensures that all committed transactions can be restored. Or that uncommitted transactions are rolled back.

    SQL Server enforces this with its roll back/roll forward process when a database is started. This uses the write-ahead log to ensure that the database is in a durable state when users access the data.