A Fine Slice Of SQL Server
Randolph West has a public service announcement:
On behalf of every database administrator everywhere, I implore you not to run
CHKDSKon a system that has a live database installed and running. This includes, but is not limited to:
- SQL Server
- PostgreSQL
- MySQL
- MariaDB
- SQLite
- Oracle
- DB2
Read on to learn why.
Comments closedChris Webb digs into the numbers:
In the past I have blogged about how important the number of connections from a Power BI DirectQuery semantic model to a data source is for performance. It’s also true that in some cases opening a connection, or some of the operations associated with opening a connection, can be very slow. As a result it can be useful to see when your semantic model opens connections to a data source, and you can do this with Log Analytics.
Click through to see how you can do this and some of the information it provides.
Comments closedPatrick Gruenauer contrasts two options in Powershell:
Predefined .NET classes: PowerShell makes certain predefined .NET classes directly available without you having to load them with “Add-Type”.
You can simply use “New-Object, to create instances of these classes. This includes many commonly used classes such as “System.String”, “System.DateTime”, “System.IO.FileInfo”
Read on for a few examples of this, as well as when you would want to use Add-Type instead.
Kendra Little explains how storage can affect query compilation time:
Up till now, I’ve thought of compilation time in SQL Server as being dependent only on CPU resources– not something that requires fast storage to be speedy. But that’s not quite right.
Slow storage can result in periodic long compile time in SQL Server. And long compile time not only extends the runtime for the query, it can also result in blocking with waits for compile locks.
Click through for more details, as well as a video by Erik Darling on compile-time locks.
Comments closedSandeep Pawar takes a look at a new preview capability:
The public preview of AI Skills in Microsoft Fabric was announced yesterday. AI Skills allows Fabric developers to create their own GenAI experience using data in the lakehouse. Unlike Copilot, which is an AI assistant, AI Skills lets users build a validated Q&A application that queries lakehouse data by converting natural language questions into T-SQL queries. It’s only available in paid F64+ SKUs. You can watch the below video for Copilot, AI Skills and Gen AI experiences in Fabric:
Read on for more details on how it works.
Comments closedSteven Sanderson goes looking for patterns:
Welcome to another exciting blog post where we walk into the world of R programming. Today, we’re going to explore how to check if a string contains specific characters using three different approaches: base R, stringr, and stringi. Whether you’re a beginner or an experienced R user, this guide will should be of some use and provide you with some practical examples.
Read on for those three examples.
Comments closedErik Svensen knows what day it is:
Last week I posted on LinkedIn an example on how we can utilize the matrix as a month calendar slicer.
It got a lot of attention so why not share an example file where you can see how I built it.
Erik has put the sample file in a GitHub repo, so click through to check that out.
Comments closedDennes Torres gets around a limitation:
Real-Time dashboards are a great feature in Real Time Intelligence experience to monitor our data. However, by default it’s made to work only with Kusto Databases. The options to create a real time dashboard or to define its data source only accept Kusto Databases.
What if we would like to see in real time the information we have in a lakehouse as well? Let’s discover a solution for this.
Read on for the solution.
Comments closedAndy Brownsword deletes some files but wants to keep other files:
A hierarchy of directories which contain files. That’s how we typically think about file storage. That’s not quite the same everywhere. In Blob Storage a file can appear to be in a directory, but when it’s removed so is the directory.
This can occur when using Lifecycle Management to help purge legacy blobs, which can be unexpected. Let’s look at a way we can help remediate this.
One important thing to remember about Azure blob storage accounts and S3 buckets is that there’s really no concept of a directory structure. It’s all keys, where your key might be dir1/dir2/dir3/file.txt. This is a bit different for Azure Data Lake Storage Gen2 and its notion of hierarchical namespaces (i.e., folders). But Andy does walk through some of the consequences of this and how to work with lifecycle maintenance policies to delete only certain sets of files.
I noticed in
sys.column_store_segmentsthemin_data_idandmax_data_idcolumns store very large bigint values in the segments fordatetime2columns. After doing a bit more googling and tinkering, I found forbit/tinyint/smallint/int/bigintit stores the min/max of the actual values rather than dictionary lookup values. So I assume it’s likely doing the same fordate/time/datetime/datetime2and storing some sort of bigint representation of the actual value.This post is going to focus on
datetime2(7)datatypes mainly because that’s what I was dealing with. Though I’m sure it wouldn’t be much work to figure out the other types.
Click through to learn more about the datatype and see how this wraps into a discussion of temporal table cleanup and columnstore indexes.
Comments closed