Voice of the DBA

Author: way0utwest

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

  • Rights for one table

    I ran across a thread that was asking how to grant rights to a person for one table only, and not other tables.

    My response is simple:

    CREATE ROLE MySingleTableRole
    GO
    GRANT SELECT ON     dbo.MyCustomers TO MySingleTableRole
    GO
    EXEC     sp_addrolemember 'MySingleTableRole', 'Steve'

    That’s it. By default users do not have rights to any tables even if they have rights to the database. If you have a user that needs rights to one table, just grant them rights to that table.

    If the user is a member of a group that has rights to other tables, you should probably remove them from the other group/role. Then build another group for them, or for the other users. If this is the case, then your group is incorrectly being used as you have people in the group needing different permissions.

  • Top 5

    I’m back in the top 5 for the first time in a long time. I’ve been spending too much time in the forums lately, avoiding other work, but trying to answer some questions for people and help them out.

    top5

    I used to always be in the top 5, but this year it seems we’ll get a bunch of people that are working on the Question of the Day, and they’ll do a lot in a short period of time. Since those can be 1, 2, or 3 points, they can quickly amass hundreds of points in a 30 day period.

    Still, this is a good reminder to spend some time on other things, like my upcoming presentations.