A Fine Slice Of SQL Server
Kevin Wilkie does a bit of analysis:
Now that we know so much about getting data from Snowflake using the query history, we should be able to get all kinds of data that will be helpful for us. In the next few queries, I want to provide some queries I use that should make your Snowflake usage a bit more useful.
Click through for those queries.
Comments closedThe short story: SQL Server 2022 finally saw some growth this quarter! Two years after the release, 1 in 10 SQL Servers is finally running the latest version.
The long story: ever wonder how fast people are adopting new versions of SQL Server, or what’s “normal” out there for SQL Server adoption rates? Let’s find out in the summer 2024 version of our SQL ConstantCare® population report.
As always, this is my reminder that we’re looking at a particular sample of the SQL Server population—users of Brent’s service—and will likely have some skew. That said, even within the context of this population, it is interesting to see these trends over time, and Brent covers that in the post.
Comments closedWhen you’re designing the back end of a database, people will have all sorts of ideas.
Normalization, partitioning, referential integrity, and, usually, trying to figure out what to do when you have more than one client.
If your application is user-focused (like Stack Overflow), you don’t have to struggle too much with the idea of isolation. But when your application is geared more towards supporting multiple entities that have one or more users, things change. Sort of like how Stack Overflow manages all the other Stack Network sites.
Were you to ask me which model I prefer, it would be every tenant getting their own database. Your other options are:
- Everyone all mixed in together like gen-pop
- Using separate schemas inside a single database
Definitely read what Erik has to say. My prior job was a hybrid multi-tenant environment: for the main transactional database, there were several dozen SQL Server instances. Each instance had anywhere from one to a few dozen copies of the transactional database, and each database hosted one or more customers’ data. There’s not a lot of tooling out there to support that kind of strategy, so we had to build a lot of it in-house. But that said, it did work out reasonably well without having hundreds or thousands of databases on a single instance.
Comments closedSemab Tariq uses a Postgres extension:
The file_fdw (Foreign Data Wrapper) is a PostgreSQL extension that lets you access data stored in flat files, like CSV files, as if they were regular tables in your PostgreSQL database. This is useful for integrating external data sources without needing to import the data directly into your database.
file_fdw is a contrib module, meaning it’s an additional feature included with PostgreSQL but not part of its core functionality. Contrib modules provide extra capabilities and enhancements beyond the core database system.
At first, I was going to write that the mechanism looks a lot like PolyBase in SQL Server. But in actuality, it’s more like a hybrid of PolyBase and OPENROWSET, as there’s no definition of external data sources or file formats, but there is the creation of an external (“foreign”) table.
Kendra Little warns us not to let a person named General Failure run your military:
This post demonstrates two related bugs with plan forcing in Query Store which increase the likelihood of slower query execution and application timeouts in SQL Server environments.
These bugs are most likely to impact you if:
- You use the Automatic Plan Correction feature in SQL Server, which automatically forces query plans.
- Anyone manually forces query plans with Query Store.
- You have slow storage, which can increase your likelihood of having longer compilation times.
The General Purpose tier of Azure SQL Managed Instance and Azure SQL Database feature both slow storage and Automatic Plan Correction enabled by default. So, weirdly enough, your risks of suffering from this problem are high if you are an Azure SQL customer.
In the words of the great John Madden, that’s a heckuva bug.
Comments closedSandeep Pawar tries out AI Skills in Microsoft Fabric:
In the last blog I wrote, I showed how to call the AI Skills endpoint in a Fabric notebook. Being able to call the endpoint programmatically creates many opportunities to integrate AI Skills in different applications. One that I thought of was using AI Skills as a function.
Function CallingorToolsis a specific use case in Gen AI to create structured output based on a function or behavior instructed by the user. I am not referring to that as AI Skills can only return a table. Instead, what if you created a number of these AI Skills that are grounded in your data with specific functions built to get the intended output? You could serve/share these with end users who can call these functions to generate data/results. Think of these as macros in Excel.
Click through for an example of how it works.
Comments closedShayon Mukherjee recommends against a particular technique:
As a developer, you might have encountered situations where creating an index in PostgreSQL fails due to lock timeouts. In such scenarios, it’s tempting to use the
IF NOT EXISTSas a quick fix and move on. However, this approach can lead to subtle and hard-to-debug issues in production environments.
Click through to learn more about how concurrent index creation works in Postgres and why the use of IF NOT EXISTS might not work the way you want.
Kurt Buhler points something out:
Effective visualizations provide context so that you can interpret the numbers and what they mean to you. Is this number bad or is it good? This is particularly important for visuals that aim to provide a quick, 3-second overview, like cards, KPIs, and simple trendlines. You can provide context by comparing to a target, but if no target is available, you can also compare to a measure of central tendency, like the average or median. However, instead of comparing to an aggregate, you might also want to compare to other categories.
Consider the following example, which shows the desired end result for this article: a plot which highlights a selected value so that the user can compare it to all others. This example uses some DAX and formatting with a line chart and scatterplot to achieve the result of a joint plot atop a jitter plot. If you want to learn more about what a joint plot or a jitter plot is, we gave an overview of these and similar chart types in a previous article.
This is something I find frustratingly difficult with Power BI. Kurt does a great job of showing how to get there, but it seems like it should be a lot easier to do.
Comments closedJosephine Bush packs up and moves down the road:
I wanted to migrate some connections without recreating them and wondered how to do that in Azure Data Studio. It turns out to be easy. I post about whatever I’m working on, and here’s a short one for you today.
Read on to see how to do this on Windows and MacOS. But that leaves the most critical question: what about on Linux?
Linux follows the same pattern as Windows: File –> Preferences –> Settings, and then Data –> Connection.
Comments closedChad Callihan shares an experience:
It’s time to follow up on a post from a few months ago where I said I’d give my thoughts on Start-DbaMigration from dbatools after picking up more experience. Along with Start-DbaMigration I picked up some experience with Copy-DbaDatabase to migrate databases from one server to another. Below are some thoughts on the good and bad.
Read for Chad’s scenario and some of the good and the bad of Start-DbaMigration.
Comments closed