Month: October 2015

Anatomy and psychology of a SQLOS spinlock

SQL Server spinlocks are famously elusive little beasties, tending to stay in the shadows except when they come out to bother you in swarms. I’m not going to add to the documentation of where specific spinlock types show up, or how to respond to contention on different types; the interested reader likely already knows where to look. Hint: Chris Adkin is quite the spinlock exterminator of the day.

In preparation for Bob Ward’s PASS Summit session, I figured it would make sense to familiarise myself a bit more with spinlock internals, since I have in the past found it frustrating to try and get a grip on it. Fact is, these are actually simple structures that are easy to understand, and as usual, a few public symbols go a long way. Being undocumented stuff, the usual caveats apply that one should not get too attached to implementation details.

Spinlock structure

It doesn’t get any simpler. A SQLOS spinlock is just a four-byte integer, embedded as a member variable in various classes, with two states:

  • Not acquired – the value is zero
  • Acquired – the value is the Windows thread ID of the owner

Continue reading “Anatomy and psychology of a SQLOS spinlock”

The road to UTC is paved with DatetimeOffset

Here is a dirty little secret: most of us who live in single-time-zone countries are not comfortable with time zones. Hardly surprising given that we tend to treat wall clock time as a law of nature that only get adjusted twice a year. Now relating one’s day-to-day life to local time is all very well, but is this a good way to store timestamps in your data?

The simple solution is to store all times in UTC, and many systems are set up like that. The beauty of UTC is that it can be unambiguously translated to anything else you’d want as display time. However, once you are in the habit of storing local time – and more importantly, thinking in it – making the move could be a tricky culture shift.

We have a peculiar pitfall here in the UK, where local time happens to correspond to UTC during the winter months. This means that code written during the winter has a chance of showing up with a time zone bug when summer rolls around, either through straightforward confusion about what the code is persisting or as an integration problem where you thought both systems were speaking local time but it turns out that that one was UTC and one was local. Make no mistake, consistent use of UTC is a solid, simple solution, but that is easier said than done outside of greenfield development.
Continue reading “The road to UTC is paved with DatetimeOffset”