Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLskills Immersion Event on Internals and Performance – Day 2

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

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.