Art of the DBA Rotating Header Image

Meme Monday

Meme Monday – Dumb Questions

Here I am, jumping in again for this month’s Meme Monday.  For June, Tom LaRock(t|b) is asking for our dumb SQL questions.

Can you disable the transaction log?

The answer is, emphatically, “NO”.  Most people I’ve run into ask this because they struggle with delays caused by locking and deadlocks, or they’re looking for ways to speed up inserts.  The facts are that without the transaction log and everything that comes with it, you lose the ACIDity (Atomicity, Consistency, Isolation, Durability) within the database.  In other words, you lose all the elements that control and maintain the integrity of your data while thousands of different statements are trying to get at it.

So the next time someone comes to you asking about just disabling this key feature, send ’em to this great article by Steve Jones.

Meme Monday – No, it’s not really the disk

Tom LaRock asked for 9 of your biggest non-disk problems for Meme Monday, so here we go:

1) Index Fragmentation
2) Memory over-consumption(stop running your apps on the SQL Server!!!!)
3) Run away queries
4) Network communication
5) ODBC/OLEDB drivers (Oracle, I’m looking at you)
6) Over-indexing (yeah, that table with 18 columns and 12 indexes….)
7) Lack of partitioning
8) Stale statistics
9) Locking (did you really have to use that exclusive table lock hint?????)

5 of these I’ve had to resolve in the last week. All of them within the last month. The good news is that most of these are pretty easy to identify and solve, but the bad news is most of these issues are the result of bad coding/querying practices and can be prevented with a little forethought. Which means I have more work to do with my developers…

Thanks for joining me on Meme Monday!

————————————

Edit: After a brief discussion with Jeff Smith(b|t), I thought I should clarify the “Lack of Partitioning”.  I know most people use this to spread the I/O load against a data file, but I found it as a HUGE boost for performance because it allowed me to segment out my table into manageable parts for maintaining indexes and improving select performance.  Take a look at my partitioning post for more info.

Meme Monday: SQL Server in 11 words

Stopped SQL during rollback, waited on recovery, and watched rollback again.

This is how I learned a fair amount about SQL Server and how it handles rollback.  The team I was on was working on a database with a huge table.  We’re talking billions of rows here huge.  Unfortunately, due to design and the growth of this table, it was all in a single partition.  And we were kind of stuck with it.

The biggest problem with this table was maintenance.  Sure, we could update statistics, but we couldn’t rebuild the index.  There simply wasn’t enough space on disk for the rebuild to happen.  How did we find this out?  Because the disk filled up in the middle of the job, causing it to fail and go in to rollback.

There we were, looking at a 14-hour rollback.  While the database was still functional, this table and all related tasks were blocked.  The smart thing to do was to wait it out and let SQL sort through the transactions.  Still, it’s easy to get a little antsy looking at that long of a rollback.  We decided that we could go ahead and bounce the SQL Service, causing SQL to go into recovery of all the committed work (i.e., BEFORE the reindex job died), but not process any of the uncommitted transactions.  Sure, we’d have to wait on recovery, but that couldn’t be as long as the rollback, right?

Well, not really.

The instance went into recovery, as expected, and didn’t take quite as long as the 14 hour rollback, but it still took a while.  After all, we were in full recovery mode and the transaction logs were still pretty sizable.  However, once it got done, we saw that the table was still locked.  It confused the heck out of us, because we didn’t see any processes locking the object when we ran sp_who2.  That is, we didn’t see any user processes.

You guessed it.  When we looked at the system processes, there was the locking processes.  SQL Server was still dealing with the rollback, it was just doing it under the covers as part of the recovery process.  That’s the other thing, this was all part of fast recovery, which is SQL Server allowing access to the database while it’s still finishing up it’s recovery processes after a crash.  Because even while some of those transactions may not have been committed, SQL Server still needs to sort through the chain and resolve the transactions in the log.  Including the one it was rolling back.

So Moral of the Story?  Let SQL Server do it’s thing when handling transactions.  Yeah, things may suck, but many times there’s not much you can do but wait it out.   There’s a reason these mechanisms are in place, so let the engine work.

Sorry there’s no script today, but thanks for joining me for Meme Monday.  It’s the brainchild of Tom Larock, the SQL Rockstar(blog|twitter), but I’m not tagging him.  Yet.  :)  However, I am going to tag three other bloggers I know:

Stop by their blogs and (hopefully) see their contributions.  Or just read their blogs and enjoy the knowledge!