Voice of the DBA

Tag: sql server

  • Checking Up on Developers

    This editorial was originally published on May 8, 2009. It is being re-run as Steve is on vacation.

    The other day I was reading Brad McGeHee’s post on duplicate indexes, and it struck a chord with me. That same issue is one I’ve seen many times with in house applications. Developers don’t understand indexing, they create one clustered index (CI) as the PK, and then they create the same index as a nonclustered (NCI) one. Either they don’t realize that the PK is an index, or they are ignorant of the differences between a CI and an NCI.

    What’s worse is that I see it in third party applications as well. If you’re selling a product, I think you ought to know how to tune it for a database. If you don’t, I’d like to see some recourse for clients. Maybe some common settlement in a lawsuit. A few of those and I bet you’d have more DBAs hired by software firms.

    Or a little more training for developers.

    For this Friday’s poll, I wanted to pick on developers a little. I like developers, I appreciate the work they do creating some great applications I use, but I also think they make some silly database mistakes. So this week:

    What are common mistakes developers make in SQL Server?

    Indexing is one area, but what other things do you see a lot from developers? I’m thinking a good list of mistakes here might be a nice checklist to go over with developers as they complete an application. It could be in code reviews, QA, pre-purchase requirements, RFPs, any number of places.

    I’ll add one more indexing issue that I’ve seen often in third party applications. Too often I’ve seen developers create a separate index on every single field in a table. For all tables! I realize that indexes improve query performance, but it does create an issue with inserts/updates, and space. Space isn’t an issue as much as it was in the past, but as data sizes get larger, I would not be surprised to see this becoming a problem in some applications. More indexes also impact backup time and size.

    Give us a response this Friday of the common mistakes that you see developers making, and that you wish they’d learn to avoid.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are available atsqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.

    You can also follow Steve Jones on Twitter:

    Overall RSS Feed:  or now on iTunes! 

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

  • Core T-SQL

    Often I find myself wondering about the minimum bar we expect people to clear to be competent in some field. Almost all fields are evolving these days as technology and new ideas are put into practice in medicine, art, construction, law, etc. It seems as though a Renaissance is taking place with the speed and variety at which new information is spreading, usually due to advances in technology.

    In our field, working with SQL Server, we certainly see new ideas and enhancements taking place all the time. However there are also a number of core skills that evolve, but at a slower pace. For example, T-SQL grows with each new version of SQL Server, but the core language remains, and many people continue to use the knowledge they have had for years when writing code.

    This week I’m curious if we can debate about, and compile, a list of core skills with T-SQL that we think someone ought to understand to be considered competent as a database developer. I’m asking:

    What core skills should someone have with T-SQL?

    I’ll start the list, but feel free to add to it or give me your thoughts. I think someone ought to be able to understand these items and write code to solve problems that involve:

    • finding duplicate rows (grouping, joins)
    • returning aggregates of single or multiple columns (MAX, MIN, SUM, COUNT)
    • return aggregates in groups, or islands. (grouping and aggregates, windowing)
    • join multiple tables together on matching, multiple columns (joins)
    • find data in one table that doesn’t have matches in another (outer joins)
    • filtering data (WHERE)
    • subqueries and complex CTE joins of data
    • create row numbers and join back to a table without numbers (APPLY)
    • pivoting data from rows to columns

    That’s a starting list from a number of questions I’ve seen, but feel free to add your own skills you think people need.

    Steve Jones

    Video and Audio versions

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    Follow Steve Jones on Twitter to find links and database related items and announcements.
    Steve Jones Windows Media Video ( 20.3MB) feed

    MP4 iPod Video ( 23.4MB) feed

    MP3 Audio ( 4.8MB) feed

    Feeds are available at iTunes and Mevio

    To submit an article, rant or editorial,
    log in to the Contribution Center

  • What is SQL Server not good at?

    I was reading a post that had this quote: ” The MongoDB docs tell you what it’s good at, without emphasizing what it’s not good at. ”

    This isn’t to pick on MongoDB, but the post did make me wonder what SQL Server isn’t good at? Should our docs, and BOL specifically, have warnings about when there might be problems with code? In addition to the remarks, should there be warnings about various features, functions and code? I image that the CREATE FUNCTION page might have some warning about scalar UDF performance in many situations, and I’m sure many of you would think of warnings that might be added for other features.

    I understand Microsoft might not like to point out flaws, but in the interest of building better code and applications, shouldn’t various versions of BOL, and perhaps all docs, warn the client about potential issues with using a feature in a certain way? Documenting the potential problems with using a feature in particular situations isn’t a flaw; it’s guidance about how misuse might introduce other issues.

    It’s not likely that we’ll start seeing more warnings in the official documentation, but for those of you that would like to improve the situation for others, there are always the “Community Additions” sections on all BOL pages. I don’t know if Microsoft would post your warnings there, but if they allow them, we might be able to help others understand the pitfalls of using a particular technique.

    Steve Jones

    Video and Audio versions

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    Follow Steve Jones on Twitter to find links and database related items and announcements.
    Steve Jones Windows Media Video ( 15.6MB) feed

    MP4 iPod Video ( 18.3MB) feed

    MP3 Audio ( 3.8MB) feed

    Feeds are available at iTunes and Mevio

    To submit an article, rant or editorial,
    log in to the Contribution Center

  • Backups and Consistency

    I wrote about transactional consistency recently. Why do we need this?

    Imagine that I have an orders table and an ordersdetail table in my database. The orders table has the order header (date, customer, etc) and the detail table includes information about the items purchased (product, qty, price). Let’s say my orders table has 10 orders and my detail table has 5 items for each order for a total of 50 rows in the detail table.

    If I want a backup that is consistent, I need to have all of the orders and details included in the backup. I don’t want partial orders, so all my inserts for orders and details are included in a transaction. That means my backup needs to contain a complete, new order, or no new order at all. Anything else wouldn’t be consistent.

    The backup process you choose needs to account for this. It cannot include partial orders from a transaction inside of it. Could that happen?

    Sure, and let’s see how. Backups proceed linearly and it takes time for them to complete. Imagine that I have a process that starts backing up my Orders table. It reads orders 1 and 2.

    While this is happening, someone inserts a new order, #6, into the system. If my backup system is reading pages, it might continue through orders 3-6 and then start on details. Imagine now the user rolls back the new order. When my backup completes, it will include (and restore) an order #6. That could be a problem.

    The other problem could be that the backup process gets to the details table and starts backing up the first 5 details from order#1. While this is happening, a user inserts a new order and order details in a transaction (order #7), and it completes. The backup process moves through the details table and gets all the details, including the new detail records inserted during the backup. However the orders table has already been backed up. The restore will include details for order #7, but not the header (Orders table).

    That’s bad.

    Don’t use file backups, no open file managers, no exports, no BACPACs as backups for your data. You might have serious data issues on restore, which get worse as your workload is busier on the system.

    Make sure that your backups are transactionally consistent. Don’t export, don’t use open file managers, don’t use anything that doesn’t respect transactions. The native SQL Server backup process does this. If you want a few other features, my employer makes SQL Backup Pro, which also respects transactions.