Last week, I was fortunate enough to be on SQL Skills’ Immersion Event on Performance Tuning and Optimization Part 1 with Paul S Randal and Kimberly L Tripp. I’d highly recommend the course, which was excellent albeit a little tiring with five full in depth days diving into SQL Server’s internals.
So, here are the top five things that I learnt (or were reminded of) on the course:-
1. Availability Group Readable Secondaries use Snapshot Isolation – and this means that those 14 bytes get added to your Primary too!
This one is a bit of a stealth way of introducing fragmentation into your tables, and was something I was aware of but hadn’t really considered. My current system has some very large tables with incremental clustered index keys, entirely fixed length datatypes in the row; so there’s no chance of page splits there, right? Afterall, there will be NO inserts into the middle of a page, because we’re only inserting to the end of the table, and as all the columns are fixed length, there’s no chance of those getting bigger. So, to ensure there’s no wasted space on the page, I whack the fillfactor up to 100%.
And then I turn on my secondary readable. Now, whenever any of those rows are updated, SQL Server has to add in the 14 bytes required for Snapshot Isolation to work on the secondary, and to ensure there’s space, it has to do it on the primary too. Boom. Page splits ahoy, and sad pandas being sad. Paul’s blog post on sqlperformance.com goes into further details. Worst of all, every time I re-index, and whack the 100% fillfactor back on, it removes the extra 14 bytes! Sad times, those pandas are really crying now. FYI – that fillfactor setting on those tables has been changed….
2. LOP_DELETE_SPLIT can be used to track “bad” page splits.
The Page Splits/sec counter in SQL Server is a bit misleading, because it counts new pages as page splits. I don’t really care if I’m creating new pages, that’s kinda the point of storing stuff in my database. However, a real page split, one that goes and gets half my page and splats it onto a new one, kills performance and gives me nightmares at night. Seriously, I think there’s a page split monster hiding under my bed.
Anyway, Paul also demonstrated that we can use the LOP_DELETE_SPLIT log record to track when the nasty page splits are happening, as per his blog post here. I’m setting up an extended event to track these right now; no more sleepless nights for me!
3. The only true “point in time” isolation level in SQL Server, is Snapshot Isolation.
I always had serializable isolation down as the king of being accurate when it comes to reads. However, this isn’t strictly true. As objects accessed under serializable isolation level are only locked when they are accessed, this means that I could read from one table (this is now locked) and someone else could update another table to something totally different from when I started my transaction, and I’d read their update (and lock that table). This would be problematic, especially if you have a concept of time that’s warping the way the current series of Game Of Thrones is….
4. The SQL Server implementation of update locks, can result in double counting in READ COMMITTED isolation level.
Everyone knows that READ UNCOMMITTED (and hitherto, NOLOCK) is bad when you want accurate results. We all use READ COMMITTED, though, right so we’re all safe. Right?
Uh oh, perhaps not, as Kimberly’s blog on “Inconsistent Analysis” shows. Yep, we can go ahead and update that row we’ve already read, and then read it again! I’d always thought that this was caught up with the infamous Halloween Problem, but it appears this isn’t the case.
5. SQL Server professionals are a lovely bunch.
‘Nuff said, really! I met a load of really nice SQL DBAs, developers and BI guys; was great having a well earned beer after the hefty sessions!