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.
Spinlocks are a testament to the level of engineering complexity in the SQLOS model. I appreciate Ewald’s explanation of the topic.
Jovan Popovic has a couple of posts on JSON. First, using OPENJSON to generate a tally table:
Problem: I want to dynamically generate a table of numbers (e.g. from 0 to N). Unfortunately we don’t have this kind of function in SQL Server 2016, but we can use OPENJSON as a workaround.
OPENJSON can parse array of numbers [1,25,3,5,32334,54,24,3] and return a table with [key,value] pairs. Values will be elements of the array, and keys will be indexes (e.g. numbers from 0 to length of array – 1). In this example I don’t care about values I just need indexes.
Well, that’s one way to do it.
You might notice that table scans take majority of the query cost. Cost of the FOR JSON (JSON SELECT operator) is 0% compared to others. Also, since we are joining small tables (one sales order and few details), cost of the JOIN is minor. Therefore, if you processing small requests there will be no performance difference between formatting JSON on client side and in database layer.
This comment was actually due to a bug in the AdventureWorks CTP 3 database. The good news is that JSON isn’t obviously slow performance problems, but I’d like to see some more thorough performance tests.
Both posts via Database Weekly.
Filtering events from an Extended Events file is even easier. Open the .xel file within Management Studio, then select Extended Events | Filters (you can also select the Filters icon in the Extended Events toolbar).
This may be the only case in which XE is easier than a trace…
Meagan Longoria is keeping a list of all the things changing in Power BI. It’s a long one:
Since it’s part of my job to stay on top of the latest Power BI features and capabilities (and because I like lists), I decided to keep a list of the features released for easy reference. It’s much more convenient for me to have a single place to reference when I’m discussing specific capabilities and updates with clients and colleagues. I’m now sharing my list with you.
I’m glad that somebody’s keeping up with everything changing in Power BI. I’m equally glad that person isn’t me.
Paul Turley takes a look at how SSRS and Power BI are maturing. One of the key grafs for me:
In SQL Server 2016, Reporting Services is getting a significant face lift on several fronts. The HTML renderer has been completely rewritten to emit pure HTML 5 to produce consistent output in every modern browser on every device. This capability is in the current CTP today.
I hated having people install executables to view SSRS reports, hated how Firefox and Chrome displayed reports differently than IE, and hated the occasional insoluable error brought about by these two things. SSRS was due for a modernization, and I hope to look at it again in 2016. Between these two tools, R support, and PolyBase, SQL Server 2016 is really shaping up to be a huge release for BI teams.