A Fine Slice Of SQL Server
Greg Dodd follows Betteridge’s Law of Headlines:
When SQL Server caches your plan, it caches it with the parameter values that you pass through the first time, and it assumes that the same query plan will be the best one for any parameter you pass in next time.
But does SQL Server always cache your parameters? Does it always keep track of what you pass in?
Click through for a demonstration good enough to give you a conclusive answer.
Comments closedErik Ejlskov Jensen takes us through auto-pluralization with Entity Framework Core:
If you are using the EF Core console command
dotnet ef dbcontext scaffoldor Package Manager Console in Visual StudioScaffold-DbContext, you can hook up pluralization via code in your project.First you must add a reference to the Microsoft.EntityFrameworkCore.Design package from your startup project.
Click thorugh for a code sample.
Comments closedAndy Mallon explains why you should never shrink tempdb data files:
I recently wrote about growing, shrinking, and removing tempdb files. In that article I explained that SQL Server won’t move a page that contains an internal worktable object, and thus trying to shrink tempdb files can be futile. Today, I’m going to explain how attempting to shrink tempdb files can actually be harmful.
Andy has good advice for tempdb here. Shrinking other database is generally bad but sometimes necessary; shrinking tempdb can lead to all kinds of problems.
Comments closedAlan Yu announces the April 2020 release of Azure Data Studio:
KQL magic extension support is now available in Azure Data Studio Notebooks. It allows you to connect, query and explore Azure Data Explorer (Kusto), ApplicationInsights and LogAnalytics data using kql (Kusto Query Language). If you are using Log Analytics today for your Azure SQL DB as described here, you can now do log metric analysis using KQL magic in Azure Data Studio Notebooks.
KQL magic package can be downloaded from Manage Packages in Python Notebook or using pip install. In a Python Notebook in Azure Data Studio, load KQL magic using (%reload_ext Kqlmagic). Start connecting, querying, and exploring using %kql or %%kql for multi-lines.
KQL magic allows you to see tabular results similar to SQL Notebook, where you can also have the benefits of exporting outputs to other formats (csv, Excel, JSON, XML) and using the Charting functionality. You can also take advantage of rendering charts directly with plotly for richer interactivity.
There are several fairly big changes in here, so check them all out.
Comments closedAndy Levy has an admission to make:
It turns out I was doing this all wrong for months.
We know, but we thought it polite not to say anything about it.
Read on to see how Andy uses Test-DbaBuild to look for new CUs of SQL Server.
Matt Allington has the need for some financials:
A few weeks ago, Danielle Stein Fairhurst from Plum Solutions contacted me and asked if I would speak at her financial modelling meetup group (a topic of my choice related to Power BI). I decided to speak about creating a P&L using Power BI and DAX. I first learnt how to do this way back in 2015 when I met Derek Rickard. I was still fairly early on my DAX journey at the time and certainly didn’t have any experience with financial accounting using DAX. Derek shared with me how he built his P&L using Adventure Works. I have used these principles many times to help customers, and I thought it would be a great time to pass on that knowledge, and the skills I have learnt along the way on my blog.
Click through for a video and the measures used.
Comments closedIn one of my most popular posts So, what is AI really? I showed that Artificial Intelligence (AI) basically boils down to autonomously learned rules, i.e. conditional statements or simply, conditionals.
In this post, I create the simplest possible classifier, called ZeroR, to show that even this classifier can achieve surprisingly high values for accuracy (i.e. the ratio of correctly predicted instances)… and why this is not necessarily a good thing, so read on!
The nuanced answer here is that with classifiers, accuracy is not in itself a great measure in the case of class imbalance. The more balanced your classes are, the more likely it is that a model with high accuracy is a good model. That’s where other measures such as specificity and sensitivity, positive & negative predictive value, etc. come into play.
Comments closedDavid Smith walks us through what’s new in R 4.0:
R 4.0.0 was released in source form on Friday, and binaries for Windows, Mac and Linux are available for download now.
As the version number bump suggests, this is a major update to R that makes some significant changes. Some of these changes — particularly the first one listed below — are likely to affect the results of R’s calculations, so I would not recommend running scripts written for prior versions of R without validating them first. In any case, you’ll need to reinstall any packages you were using for R 4.0.0. (You might find this R script useful for checking what packages you have installed for R 3.x.)
And I just got 3.6 into production yesterday. Them’s the breaks…
Comments closedIt used to be that the only way to use SQL Server Management Studio (SSMS) against Azure SQL Database Managed Instance (SQLMI) was to create a VM on the same VNET as SQLMI and use SSMS on that VM. That VM was usually called a jumpbox (see instructions here).
But about a year ago Microsoft added a way to use SSMS without using a VNET (announcement) by allowing you to enable a public endpoint for your SQLMI. This made it easy for me to access a SQLMI database on my laptop.
That change enables what James shows us.
Comments closedGrant Frichey answers a question:
Questions absolutely drive my blog content and I really liked this one: how does the T-SQL CHOOSE command affect performance.
On the face of it, I honestly don’t think it will affect performance at all, depending on where and how you use it. However, the answer is always best supplied by testing.
Grant’s post ended up being much more interesting than I had anticipated—my anticipated answer was “It’s a CASE statement so it behaves like a CASE statement.” But there is some nuance that I’ve left out.
Comments closed