Tuning SQL Server Transactions – and Meetings

I hate meetings.

Meetings are like bad blocking queries – they tie up precious resources and stop other people from getting work done.  Today, let’s talk about how to optimize both of ’em.

Minutes of typical corporate meeting

Minutes of typical corporate meeting

Do Your Homework Ahead of Time

If you’re going to hold a meeting, build the list of decisions or tasks you want to accomplish during the meeting.  Send out that list ahead of time before you schedule the meeting, and you might be pleasantly surprised at how much work gets done without the meeting, because:

  • Some tasks really only need one person to accomplish them
  • Some decisions are only made by one executive (without input)
  • Some research can’t be done during a meeting
  • Some people will do just about anything (including extra work) to avoid meetings

When designing your SQL Server transactions, put together an agenda.  Out of the list of tasks, try to move everything out of the transaction.  Can you run some of the queries ahead of time before BEGIN TRAN?  If so, that lessens the number of locks you need for your transaction, and makes your transaction run faster.  When examining each query in the transaction, ask yourself:

  • If this data changed between the time I queried it and the time the transaction committed, would it be a problem?  If you’re fetching the customer’s bank balance before you update it, that would be bad.  If, however, you’re checking the date the customer’s account opened, it probably isn’t going to change while the transaction runs.  That might be a good candidate to move out of the transaction.
  • If I had to rollback the transaction, would this query be affected?  If it’s just a select, the answer is probably not – but your mileage may vary.

This is especially important for transactions that run frequently, like hundreds of times per minute.  In one engagement, we found a common transaction to update an employee’s details.  In the beginning, it checked the employee’s supervisor’s status before updating the employee record.  By moving that query out of the transaction and doing the work ahead of time, we cut the transaction execution time by over 90% without affecting the integrity of the query.

Minimize the Number of Objects Involved

Know why most meetings and transactions are like an OK Go video?  There’s way too many things involved.

And if you think that machine is complicated, think for a second about the process of filming it.  Everything had to work perfectly, the camera had to smoothly move throughout the entire operation, the guys had to move from one position to the next to hit their mark in time, and the whole thing is choreographed in a way that you don’t see the guys moving from spot to spot.  If you like this, check out their marching band version or their famous treadmill video, and note that they’re all done in one very long take with no camera changes.  (The Rube Goldberg one does have a cut in it, though.)

The more people involved in a meeting, the tougher scheduling gets, and the more people sit around twiddling their thumbs during the meeting.  If you only need someone for 5 minutes of the 60-minute meeting, try to accomplish that goal ahead of time and free up 55 minutes of their time.

When designing your SQL transactions, be acutely aware of every table you’re inviting to the party.  SQL Server has to schedule these objects because you’re not the only one who wants to use them.  The less locks you incur, the faster your transaction can get in and get out.

Have Less Meetings (and Transactions)

Do you really need to tie up multiple resources at the exact same time in order to get your work done?

Or are you just taking the lazy, selfish approach and grabbing lots of resources just because you can?

Coworkers and tables seem like easy resources to commandeer at a moment’s notice because you don’t pay the price initially.  As your work piles up, though, performance suffers.  Making the right call ahead of time pays off in the long run.

Previous Post
The PASS Summit Location Survey Results
Next Post
Who’s Your Target Audience?

2 Comments. Leave new

  • Great job killing 2 birds with one stone. Unfortunately I tend to be the one “twiddling my thumbs” during poorly planned meetings.

    I’m sure I’ve been guilty of wasting resources in a SQL Server transaction though.

    Good points all around.

    Reply
  • “The more people involved in a meeting, the tougher scheduling gets” – True! I am never a believer of meetings. It’s anti-productivity.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}