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)
- 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
- 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