Overview

Day two of the SQLskills Immersion Event is over and I have listed the interesting parts below. The food, again, was great, except for the lack of danishes during breakfast (I really like those). Today I got see Kimberly’s teaching style. She’s a fast talker, so you better keep up. Sadly I was a bit more sleepy in class today, though that’s not a reflection on Kimberly. I keep waiting for Kimberly to go into a Joan Cusack type rant, she just has those mannerisms. To tell you how much this couple cares about teaching and the value of whiteboards… they shipped their own 2 boards down here from Washington state, even through what seemed like a pretty big hassle.

Tonight also saw SQL Sentry demo their free Plan Explorer tool. A really cool tool that’s worth checking out. I learned a few tips for using it that I didn’t know before.

Logging, Recovery, and Log File Maintenance

• Updates request update locks on objects, when all locks are granted locks change to eXclusive as they are updated.
• There is no such thing as a non-logged operation (in a user database)
• fn_dblog()
• Crash recovery is logged as well (compensation records)
• Fixed width columns are grouped in a log record
• Variable width columns get individual log record
• *Committed* transaction log records are always written to disk
• Uncommitted transaction log records *could* be buffered. Checkpoint causes these to flush to disk.
• DBCC LOGINFO
• Nested COMMIT TRANSACTION does nothing other than decrement @@TRANCOUNT
• Nested ROLLBACK rolls back everything
• Don’t use nested transactions, use save points instead
• BULK_LOGGED recovery model is kind of useless, your save log space, but not log backup space.
• If you want to use it, BACKUP LOG, switch to BULK_LOGGED, do op, switch back to FULL, BACKUP  LOG.

Locking & Blocking

• Fix your code
• Optimize your indexes
• Watch out for more than one incompatible lock on an object.
• UPDATE … @var = col1 = col1-@debit (added in 6.0) Really neat way of updating a row and getting a value back in one query

  • CREATE TABLE testNum (id INT PRIMARY KEY)
     
    INSERT INTO testNum VALUES(5)
     
    DECLARE @id INT
     
    UPDATE testNum
    SET @id = id = id + 5
    WHERE id = 5
     
    PRINT @id

• UPDATE … OUTPUT (to table variables)
• No such thing as a nested transaction
• KB 271509 – sp_blocker_pss08

Nested transactions (no such thing. Though you’ll want to learn about save points)

BEGIN TRAN T1
    BEGIN TRAN T2
        SAVE TRAN T3
            ...
        ROLLBACK TRAN T3
    COMMIT TRAN T2 ==>@@TRANCOUNT = 1
COMMIT TRAN T1 ==>@@TRANCOUNT = 0

Snapshot Isolation

• If you use row versioning, use a FILLFACTOR to prevent page splits
• Versioning will double your writes (once to data file, once to tempdb) and likely increase tempdb space, although not necessarily.

Table Design Strategies & Partitioning

• Vertical partitioning could be useful depending on the access patterns and page density
• Get your data types right
• If LOBS aren’t used often, considering pushing them off row