• What version and service pack of SQL Server? I ask because CXPACKET waits have changed. They used to be noise. Now they're not. Here's an article from the Tiger Team that's not getting nearly enough attention.

    Seeing everything go down during the slow running query is actually a sign of contention believe it or not. The blocking and/or waits that are occurring is preventing other work from getting through.

    It's probably not the schema locks, and yeah, we're partly to blame for those, it's an artifact of monitoring.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning