A Fine Slice Of SQL Server
Gilbert Quevuavilliers cuts out the middleman:
The goal of this blog post is to show you how to create a subscription which sends the data in an Excel attachment.
To do this I will first create a paginated report off an existing dataset.
Once the paginated report is created, I will then create a subscription with the Excel attachment.
Read on for each of the steps.
Comments closedErik Darling has some thoughts:
First, I’m not suggesting that anyone should be using the default value for Cost Threshold For Parallelism. It’s old and moldy and not a good fit for most workloads functioning on modern hardware.
My apologies to Azure SQLDB users who can’t change this setting and leave it up to Microsoft to maybe manage it for them based on ???
Some people out there really like fiddling with settings in a usually ill-informed reaction to Some Script They Found On The Internet, without reading the fine print.
Erik’s thoughts are reasonable overall. My recommendation is to use Michael J. Swart’s technique for tuning cost threshold for parallelism as a starting point, as it gives you a basis for what the net effect of your changes are.
Comments closedAndrea Allred has a script for us:
I recently needed to know which tables in my database were partitioned. I tried a bunch of queries and some got incredibly complex. I finally found one that I like:
Click through for the script and for the assumption Andrea makes (which is a reasonable one).
Comments closedAdrian Tam continues a series on R:
One of the most popular plotting libraries in R is not the plotting function in R base, but the ggplot2 library. People use that because it is flexible. This library also works using the philosophy of “grammar of graphics”, which is not to generate a visualization upon a function call, but to define what should be in the plot, and you can refine it further before setting it into a picture. In this post, you will learn about ggplot2 and see some examples. In particular, you will learn:
- How to make use of ggplot2 to create a plot from a dataset
- How to create various charts and graphics with multiple facades using ggplot2
It takes a little while to understand the grammar of graphics approach that ggplot2 takes, but once you do, you realize just how good this library is for generating static images.
Comments closedIn this video, we introduce DVC, a tool for version control management of data science and machine learning artifacts. We learn why Git isn’t the best place to store those large data files, how DVC integrates with Git, and how you can save your files in Azure Blob Storage.
Click through for the video, as well as a variety of links which helped me put it together.
Comments closedBill Fellows has a public service announcement:
The concat function is super handy in the database world but be aware that the SQL Server one is way better because it solves two problems. It combines everything into a string and it does not require NULL checking. In the before times, one had to down cast to a n/var/char type as well as check for NULL before appending strings via the plus sign.
The point of difference is so important that Bill busted out the marquee HTML tag. Which now leads me to wonder, was marquee or blink the bigger evil in the mid-to-late ’90s web?
Comments closedDavid Fowler performs one of the early steps of a penetration test:
Since witnessing a rather nasty cyber attack around a year ago, I’ve been thinking quite a bit about security. Do we really know how secure our SQL Servers are? Penetration testing is a great way to find out where our weaknesses and vulnerabilities are. Ideally you probably want to be getting regular pen tests conducted by external companies (although in my experience, some are better than others. I’ve known some who argue totally pointless issues and miss glaring holes which I know exist, but that’s a whole different story) but wouldn’t it be useful if we could conduct some of these tests ourselves?
In this series of posts, I’m going to try to knock together a little pen testing toolbox so that we can hopefully find some of these vulnerabilities. I’m no pen testing expert and this is never going to replace getting a professional pen tester in to test your setup but it might go some way to helping us understand some of our vulnerabilities and identify them.
Click through to see what David did, as well as an alert which helped pick out this port scanning operation.
Comments closedReza Rad makes a recommendation:
Microsoft Fabric uses OneLake as the single logical layer for storage management across the Fabric. OneLake offers Domains and workspaces to work with Fabric Items, and amid all those structures, you may require a table or file from one Domain to be used in another. OneLake’s Shortcut feature will give you this option without duplicating the data. In this article, I will explain what Shortcuts are and their importance in building an analytics solution.
Click through for a video and an article.
Comments closedJay Robinson lays out a pattern:
Dynamic SQL (aka Ad Hoc SQL) is SQL code that is generated at runtime. It’s quite common. Nearly every system I’ve supported in the past 30 years uses it to some degree, some more than others.
It can also be a particularly nasty pain point in a lot of systems. It can be a security vulnerability. It can be difficult to troubleshoot. It can be difficult to document. And it can produce some wickedly bad results.
Click through for Jay’s process as well as recommendations and an example. It’s certainly worth thinking about.
Comments closedBrent Ozar recommends you check your cloud provider’s VM listings:
If you’ve been in Azure or Amazon for a few years, you’re probably on old, slow hardware.
In the last 3 weeks, I’ve had two clients who’d both been early cloud adopters. When they’d migrated to the cloud, they both used Azure Ev3 VMs – at the time, a good choice for SQL Server due to its relatively high amount of memory. When the Ev3 VM types were announced in 2017, they were hosted on Intel Broadwell and Haswell processors with 2.3-2.4GHz processing speed.
Also, even if you’re locked into a 1-year or 3-year deal, I know that at least Azure is usually willing to switch your VM class registration if you contact your support person. I’m not positive if AWS does the same but it wouldn’t shock me.
Comments closed