A Fine Slice Of SQL Server
Erik Darling ponders several techniques for solving the last page contention problem in SQL Server:
If one dare go looking, a list of Microsoft Approved™ solutions for dealing with last page contention exists.
I’m going to walk through my experiences with them in different scenarios, so you get a better understanding of which ones work best, and which ones can be tough to implement.
Read the whole thing.
Comments closedMelissa Coates has a video and an article for us:
Including documentation alongside the dataset is helpful for teammates who might need to work on this dataset at some point, or even for yourself in the future. It also means you won’t have an empty report, and you won’t need to delete the empty report once the file is published to the service.
Click through for plenty of useful information on the why and how, as well as what to watch out for.
Comments closedImke Feldmann has a few tricks for making pivot operations in Power Query faster:
Pivot operations in are a very handy feature in Power Query but they can slow down refresh performance. So with some bittersweet pleasure I can tell that I found a trick to speed them up. The sweetness comes from the fact that the performance improvement is very significant. But the bitterness comes from the fact that I could have used this for almost 4 years now, but was too blind to realize at the time when I first worked with the code.
Read the whole thing and learn why Imke is disappointed in not knowing this four years ago.
Comments closedTori Tompkins walks us through four methods for generating models for recommendation systems:
One of the most popular approaches to recommendation problems is Collaborative Filtering (CF).
This approach is based on the assumption that users who have agreed in the past, also tend to agree in the future. For example, if Alice likes Star Wars, Lord of the Rings and Harry Potter and Bob likes Stars Wars and Lord of the Rings too. Then it is likely that Bob would also enjoy Harry Potter. The collaborative in Collaborative Filtering is in relation to looking at the way that you multiple users interact with the same data and share the same commonalities.
Read on for a comparison of these four systems as well as a handy chart to help you figure out which system might work best for you.
Comments closedElizabeth Ricks dives into the properly-maligned pie chart:
I want to use a pie: how do I make a good one?
When using pie charts, here are a few quick design tips to make them more effective.
The best way to make a good pie chart is not to make a pie chart…
Elizabeth does a good job of being less dogmatic than I am on the topic while still explaining most of why I dislike pie charts so.
Comments closedImke Feldmann explains how you can perform paging with Power Query:
When you use an API with a paging mechanism (like the example from this blogpost), you’ll might work with a field that contains the address for the next page. You can use this to walk through the available chunks until you reach the last element. That last element in the pagination will not contain a next-field or that field will be null.
There is a bit of nuance to this, so click through and learn how.
Comments closedChris Adkin has started a series on SQL Server Big Data Clusters:
Proposing the idea of using virtual machines as Kubernetes cluster nodes to a Kubernetes purist is likely to be met with consternation. However, the different nodes in your cluster have different resource requirements. A master node can get away with as little as 2 GB of memory and 2 logical processors, worker nodes require much more resources. A best practice is never to run applications on master nodes in production. The view of the world from a Kubernetes purist, is that Kubernetes is designed to obviate the need for virtualization. Consider that you do go down the bare metal route, its unlikely that you are going to purchase blades or servers with 2 GB of memory and 2 CPU cores. At the very least consider the use of virtual machines to host master nodes on. For organizations that have standardized on a software defined virtualized infrastructure, Kubernetes will run perfectly happy on this. Also for the rapid provisioning of environments – virtualization provides the fastest means of doing this – simply create yourself a virtual machine template and base your cluster node hosts on this.
Click through for more guidance around what you need to know before you deploy a cluster.
Comments closedHugo Kornelis provides one example of misleading arrow widths in execution plans:
In my previous post, I talked about the Actual Number of Rows and Estimated Number of Rows properties, their visual representation in execution plans, and the most important ways in which this can be used.
But life is not always perfect, and Microsoft likes to remind us of that. Sometimes, these properties report values in weird and confusing ways. Sometimes the arrow width, as the visual representation in the execution plan, misleads us.
Let’s look at one such case.
Read the whole thing and vote for Hugo’s Feedback item to improve this experience.
Comments closedThe other day I had a random thought.
I wonder if there is a version of Azure Portal for Android.
Turns out there is!
This looks most useful for quick observations of machines and services, such as after receiving an alert. But as Kenneth points out, hooking a phone up to a monitor, keyboard, and mouse (which, at least on newer Samsung models, is definitely an option) means that you can hit that cloud shell and do most of what you need.
Comments closedRandolph West continues a series on how SQL Server stores values:
As we know from before, integers are whole numbers, or numbers with no fractions (i.e. no decimal places). This is going to be in the test later, so pay attention. In other words, the numbers 0 through 9 are integers, but a floating point or decimal / numeric value is not an integer. As soon as you add decimal places, it stops being an integer even if the fraction equates to zero.
Inside the storage engine, integers are mostly signed values (they can have negative values), and each integer data type has a fixed size. The exception is
TINYINTwhich only has positive values. Like many other data types, integer types are stored byte-reversed (known as little-endian).
Click through for some good information from Randolph.
Comments closed