An adventure on SQL Server performance and features

T-SQL Tech

An adventure on SQL Server performance and features


Latest stories

How to implement a Linear Regression Prediction in a PowerBI report using Python


Machine learning is the buzzword of the moment, so I wanted to talk about that sweet ML here too. For a project that I’m working on, I’ve been experimenting on basic ML prediction algorithms, in this instance I’ll show you a basic POC on how ML can be integrated right into PowerBI in a serviceable manner. It’s ugly but it works The data I just needed some whatever data to...

Get faster performance and lower network usage in SQL Server Loops by avoiding the “DONE Token” overload


FYI: You can get the Notebook for this article on my github and experiment yourself (opens with Azure Data Studio). Preamble Everybody knows that using loops in SQL Server is not efficient, if you’re able to write that same logic in a set-based statement it’s guaranteed to be faster.Still, devs can’t be helped, you just can’t seem to nail down the set-equivalent statement...

SQL Notebooks: On the right track to enhance Data Analysis on SQL Server


Since about a couple of versions ago, Azure Data Studio introduced Jupyter Notebooks support for SQL Server (and with SQL Server 2019 “Big Data Clusters” on the horizon, it was about time) What are Notebooks? “Notebooks” are a tool that our data science/machine learning colleagues know, love and are at their disposal since some time (Python, Spark, etc…); A notebook...

How to identify the unit of measure of Extended Events durations


Even if I use Extended Events almost every day, I always forget the unit of measure of each duration counter, since they’re basically arbitrary; Seconds, milliseconds, microseconds? Whatever, it depends on the dev that implemented that specific counter. That’s why I’ve added to Github repository the following code that extracts the descriptions from XE DMVs in order to...

Quickly move data between instances for testing using only PowerShell


Quick tip, how many times did a dev ask you to move the PROD/QA data to DEV to test a feature? Of course, not the whole DB, which would be too easy via a scripted backup/restore, but only a single table, or subset of table data? How do you deal with it usually? With the SSMS Import data Wizard? (ugh..) Exporting to flat file and reimporting? Linked Servers? External tools? Restore the whole...

Cast any string directly to XML with auto-escaping characters in SQL Server


Recently I had to look up the definition for a bunch of SQL objects and didn’t want to manually retrieve them manually in SSMS (with Create Scripts) or Visual Studio (by searching the object name in my TFS repository). Since lazyness and automation are the basis of a well done engineering work, I wanted to create a list, where I could basically click on the object that I needed and see the...

How to Show Better Execution Plans in Azure Data Studio


Azure Data Studio is starting to mature and some neat features are coming to the vscode-based (and now multidatabase and multiplatform) tool. We got query plans in the app since some time, but to be frank they’re not very pretty and have some issues with dark themes: Phantom Lines in plans with Dark Theme The new way Phil Scott built the extension for Azure Data Studio, which...

Load data directly in SQL Server from a xlsx file, without OLEDB or Excel, using Powershell


I don’t know you, but people from other BU are old fashioned guys that do everything by hand, and then send you an Excel file to “put this data in the database” or “find info related to this codes”. This is an hassle, especially if you don’t have the Microsoft.ACE.OLEDB provider installed in your machine (or for some reason SSMS doesn’t recognize it or...

A SQL Server Permission Report in PowerBI


What it is and how to get it When working with SQL Instances that have lots of databases, sensitive data and equally lots of users/applications that accesses those, it can get pretty troublesome to keep track of every user, for every database, down to the single column/action.That’s why I developed this report, in orde to be able to quickly determine the security status of each object or...

Why identity values can jump by the 1000’s ? Is it normal?


It must have happened you at least once to find identity columns with values that from one row to the next one jump by 1000’s rows, of course without having anyone deleting the rows in between, how this occurs? Identity columns don’t actually recalculate every time the next number to be inserted, SQL Server caches a number of values in order to obtain faster inserts and polls from...

An adventure on SQL Server performance and features

Emanuele Meazzo

My Social Profiles

If you're interested in following me around in the social world, here are some URLs for you