A Fine Slice Of SQL Server
Devin Knight’s series on Power BI custom visuals continues with the force-directed graph:
Key Takeaways
Shows relationships between different entities in your data.
The width of the line that separate each entity represents the strength of the relationship.
Click through for the discussion and a video. I’m not too sure that I’d use this in a real dashboard, but it’s available.
Comments closedI’ve written before about the concept of cargo cult data professionals. They see one issue, one time, and consequently extrapolate that to all issues, all the time. It’s the best explanation I have for why someone would suggest that a sub-query is flat out wrong and will hurt performance.
Let me put a caveat up front (which I will reiterate in the conclusion, just so we’re clear), there’s nothing magically good about sub-queries just like there is nothing magically evil about sub-queries. You can absolutely write a sub-query that performs horribly, does horrible things, runs badly, and therefore absolutely screws up your system. Just as you can with any kind of query. I am addressing the bad advice that a sub-query is to be avoided because they will inherently lead to poor performance.
There are times not to use subqueries, but this post is absolutely correct: understand the reasons why things may or may not perform well, and don’t be afraid to try things out.
Comments closedKenneth Fisher discusses synchronous versus asynchronous in programming terms:
Synchronous – Code that runs one one line at a time. Each line of code is completed before the next one starts. If an external call is made then it is completed before the next line of code runs.
Asynchronous – Code that is launched and runs separately from the initial code. If a SQL job is launched from inside a batch of code (using sp_start_job for example) then the job is running in parallel (at the same time as) to the remainder of the batch of code.
Understanding which operations are synchronous versus asynchronous, and which operations are blocking versus non-blocking versus semi-blocking, will do wonders for improving application performance.
Comments closedLadies and gentlemen! That’s quite a difference to SQL Server 2014!
We better check the total length of the transaction log to see the final result: 384.032 bytes! Ok, that is significantly more than for the rowstore heap table for sure, but what about the comparison to the SQL Server 2014 ? Did this minimal logging bring any improvement ?
Well … 🙂
In SQL Server 2014 we had 1.255.224 bytes spent on the transaction log – meaning over 1.2 MB, meaning around 3 times more, for the Delta-Store insertion! For such a simple table, this is a huge improvement, but let’s take a look at the total length of the transaction log entries in both environments (SQL Server 2014 & SQL Server 2016)
This is worth a careful read. If you’ve spent time working with 2014 clustered columnstore indexes, there are a few changes which might affect you. The most interesting thing for me was that the deltastore is no longer page compressed.
Comments closedChris Webb shows how to create nested variables inside DAX:
There aren’t any performance benefits to doing this, although of course it helps with code readability and organisation (thanks to Marius for confirming this).
Even so, click through to see an example of how to do this.
Comments closedConstantine Kokkinos has a list of automatic variables in Powershell:
Been a few days of learning since I last wrote one of these, but I have come back to the automatic variables page on the PowerShell documentation enough times that I think I should just blog the important parts for myself.
$?– TRUE/FALSE if the last thing you did succeeded.
$_– Something everyone uses in posh, current pipeline object.
$Args– all the undeclared params passed to a function, try to avoid.
$Error– the array of error objects that represent a stack of the most recent errors. use $Errors0 to get the most recent error.
Read on for more variables.
Comments closedMelissa Coates argues that you should upgrade to the latest version of Management Studio:
SSMS is supported for managing SQL Server 2008 through 2016 (except for SSIS instances which sadly still require a version-specific SSMS at the time of this writing). If you manage numerous servers on different versions, this unification is fantastic. There is partial support for managing pre-2008 instances. And, of course as you’d expect, the newest SSMS release supports various new features in SQL Server 2016 such as Query Statistics, Live Query Plans, Compare Showplan, Security Policies for row-level security, and so on with all the new 2016 goodies we have.
SSMS also supports managing Azure components such as Azure SQL Database and Azure SQL Data Warehouse, as well as hybrid cloud features such as StretchDB and Backup to URL. This additional unification is really, really handy.
I have a copy of SSMS 16 for reading Query Store, but not all of my plugins have been updated yet, so I’m still living in SSMS 2014 for now.
Comments closedRichie Lee discusses what makes for a good pre-model script in an SSDT project:
Idempotent: big word, and is used in maths. But don’t let that put you off. What it means in this context is that a script can be re-run and the same result happens. So in the context of altering a primary key, the pseudo-code would look something like this:
if database exists then
if table exists then
if primary key name eq “oldname” then
drop primary key then
add primary key with “newname”
I’m a huge fan of idempotent scripts. Releases become so much easier when you can guarantee that you can re-run the scripts, and idempotence is a necessary property of continuous integration.
Comments closedCody Konior explains how testing is harder than it seems in the demos:
We should have 4 backup jobs
Definitely 4, not 3, and 5 is right out.
Unless of course someone builds a 500GB database with SQL CLR UDTs which slow down DBCC to a crawl, so you move the backups and maintenance for that database into their own jobs or steps with different flags, and possibly start staggering some stuff so it finishes on the weekend without dripping into Monday.
Then you’ve got more. But just kind of sweep them under the carpet. We have file system backup checks so that covers most of it…
These are just administrative test examples; when you have to start testing queries and procedures, it’s a whole new level of fun, as it feels like you’re building a castle on sand.
Comments closedIn this presentation, we are going to discuss how Elasticsearch handles the various operations like insert, update, delete. We would also cover what is an inverted index and how segment merging works.
Click through for the slides; they helped me firm up a few thoughts I had about Elasticsearch.
Comments closed