A Fine Slice Of SQL Server
Matt Allington shows how you can apply conditional formatting to non-numeric fields in Power BI:
The high level process is to:
1. Create a measure that returns a colour as the result1. It can be a word, such as blue, red, green
2. It can be a hex code for a colour, like #40E0D0″, “#FFA07A”
2. Use conditional formatting and use the measure to apply the formatting on the text as a rule.
Read on for a demo.
Comments closedAllan Hirt prefers ANSI-89 join syntax:
Pedro Lopes who is now on the SQL Server dev team wrote the blog post “T-SQL Misconceptions – JOIN ON vs. WHERE” which does a good job of explaining the “it depends” around the newer style of joins. Why link this article? Microsoft deprecated the OUTER JOIN operators in SQL Server 2008. Two other sources of information on this are here and here (the first is Ward Pond’s old technet blog, and sadly will probably go away soon). If you’re keeping score at home, WHERE clauses are not deprecated except if you’re using *= and =*). The changes people made were wholly unnecessary and as the author, the newer stuff is harder to decipher than what I originally did. They were putting their own biases onto things.
I personally do not like ANSI-89 syntax because it blurs the lines between filters and join criteria. Those are separate things serving different purposes and keeping them separate clarifies queries more than it obscures. Allan’s example doesn’t have any filters but in a more complex scenario with several filters and several join criteria, it can require extra care figuring out what’s going on, especially with multi-column join criteria and filters mixed in (meaning it’s not join criteria and then filters, but a mishmash of the two).
Comments closedGrant Fritchey shows us that you can force a parallel plan using Query Store:
I love the questions I get when presenting: Can You Force a Parallel Plan in Query Store. I haven’t a clue. The trick I think is going to be in setting up the test. Let’s try it out.
Reliably Getting a Parallel Plan
Because this is for testing, rather than try to build some crazy query that may or may not go parallel, I’ve decided to cheat. I’ll take advantage of a little functionality that ensures I see a parallel plan when I want to.
Read on to see Grant’s trick and also the results of this test.
Comments closedJohn McCormack tries out using the temporary drive on Azure VMs for tempdb:
Azure warn you not to to store data on the D drive in Azure VMs, but following this advice could mean you are missing out on some very fast local storage. It’s good general advice because this local storage is not permanently attached to your instance, meaning you could lose data or log files if your VM is stopped and restarted but what if you could afford to lose certain files? Say files that are recreated during startup anyway.
TempDB is the ideal candidate for this. No other database is suitable! Putting the tempdb data and log files onto D drive can be achieved quite easily with a little bit of effort. And you will most likely see a big improvement in tempdb read/write latency.
John ended up seeing much bigger gains than I did when I tried this, but with a difference that big, it’s definitely worth using the temporary drive for tempdb.
Comments closedJess Pomfret continues a series on Desired State Configuration in Powershell:
Once we have crafted the perfect configuration and shipped it out to our target nodes, it’s time for the magic to happen. The MOF file that we created by executing our configuration is translated and enacted by the Local Configuration Manager (LCM) on each target node. The LCM is the engine of DSC and plays a vital role in managing our target nodes.
The LCM on each target node has many settings that can be configured using a meta configuration. This document is written very similarly to our regular DSC configurations and then pushed out to the target node.I’m going to cover a few of the important LCM settings for use in
pushmode. This is where the LCM passively waits for a MOF file to arrive. The other option ispullmode- this is a little more complicated to set up and in this scenario the LCM is set to actively check in with a pull server for new configurations.
Click through to see some of those important settings.
Comments closedTo test FROID, which is the codename for Microsoft’s initiative to inline those awful scalar valued function things that people have been griping about for like 20 years, I like to take functions I’ve seen used in real life and adapt them a bit to work in the Stack Overflow database.
Read on for a case where Erik saw major improvements.
Comments closedHimanshu Gupta explains some of the benefits Apache Flink offers for stateful streaming applicatons:
The 2 main types of stream processing done are:
1. Stateless: Where every event is handled completely independent from the preceding events.
2. Stateful: Where a “state” is shared between events and therefore past events can influence the way current events are processed.Stateless stream processing is easy to scale up because events are processed independently. But Stateful stream processing is difficult to scale up because the “state” needs to be shared across the events.
Himanshu does point out alternatives, but this isn’t a comparison exercise.
Comments closedWe used a single topic for our write operations with a partition count set to either 3 or 6, depending on the number of brokers in each test cluster. As the test clusters were regular Aiven services, the partitions and replicas were spread out across availability zones.
Messages were produced via the librdkafka_performance tool with a message size of 512 bytes, a default batch size of 10,000 and no compression. Continuing our quest to simulate real-world use, client connections were made over TLS.
We used Kafka version 2.1 running with Java 8; as a side note, it’ll be interesting to benchmark Aiven Kafka running with Java 11 in future tests because we expect Java improvements to positively impact its performance.
During the test, we kept increasing the number of producing clients until we reached the maximum throughput rate each plan tier’s cluster could accept. To verify our readings, we left the load running for some time.
There are some interesting results here.
Comments closedAnnie Xu shows us how to build a Power BI dashboard on a streaming data source in Azure:
This post is about something new I have tried last week. The goal was to create simulated streaming data source, feed it into Power BI as a streaming dataset, create a report out of the streaming dataset, and then embed it to an web application. With proper directions provided by my teammates, I finished the implementation from end to end within 1.5 hours. I was super impressed by how awesome it is and how easy it is to implement so that I want to share those directions to you.
The source data is simulated but the process is the same with real data sets.
Comments closedJohn Morehouse shows that SQL Server data pages are the same in Windows as they are in Linux:
One of the new phrases coming out of Microsoft is that “SQL is just SQL” regardless of what operating system it resides on. This was echoed during the keynote at SQL Bits 2019 by the Microsoft team, which you can watch here. Later that weekend, I gave a session about database internals. My presentation is about how data is structured within a row and why that matters. Understanding the internals of table structures, even in today’s age of technology, include SQL Server 2019 (which will be released in Q3/Q4 of 2019) is important. During my session, a question came up about how a data page is structured if SQL Server is sitting on top of a Linux server, such as Ubuntu. Does the data page have the same size and shape in Linux as it does in Windows?
They do. Click through to see John prove it.
Comments closed