An adventure on SQL Server performance and features


How to lose hundreds of thousands of dollars by using functions in SQL Server


Ahh, functions, the greatest tool at a programmer’s disposal, they make code reusable and easy to read, they’ve been essential since the first function call was made in the last century… Unfortunately for the developers out there, when working for SQL Server, especially when tuning for performance, you need to get everything you knew about programming and throw it out of the...

The SQL Diagnostic Notebook: Update 2


Just a quick note, the SQL Diagnostic Book has been updated, here is the changelog: Updated FirstResponder’s Kit and Glen Berry’s ScriptsAdded SQL Assessment API NotebooksAdded sp_whoisactive installation and basic documentation notebooksAdded sp_pressure_detector as a Notebook That’s it! As always, to use it just download the folder of the Book from my GitHub (here is...

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...

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...

SQL Server: How to Find a safe restore point (and who messed up) by reading from the Transaction Log


Panic: Someone has messed up and deleted a super important piece of information in a table! Who did it? Nobody knows. When did it happen? “The data was there this morning” Super useful info, thanks brah. So you activate your DBA Magic Powers© and try to deal with this stuff yourself: you need to identify which is the exact point where the data has been deleted (in order to minimize...

Troubleshooting SQL Server by analyzing the deviation from average values of Waitstats and Perfmon counters


Counters and Waitstats are good boys and useful for understanding what’s going on in your SQL instance, but often are pretty useless by themselves if you don’t know what’s a normal value for your server; For example a value of 100 batch requests/Sec is a value to worry about? It depends ¯\_(ツ)_/¯ What can be a sign of an issue? Something that goes way out of range compared to...

Creating a Perfmon and Filestats reports in PowerBI [Part 2]


In this previous post I detailed the structure of the objects to be created to create a live report in PowerBI to analyze Perfmon and Filestats data (which in my case was already being gathered but not used by the FirstRespondersKit tool); Let’s get to the PowerBI stuff. Here is the final result that we’ll reach: I’ll be starting from the existing FirstRespondersKit PowerBI...

Can a single query deadlock itself? Apparently, yes: A curious case of Intra-query Parallelism


Imagine that you’re in a SQL data warehouse in the middle of the night, a single stored procedure is running, is nothing else, and it’s simply doing inserts and updates, one statement at the time, but then.. Deadlock. How can it be? Something else must be running, right? Someone launched something else, or a transaction was left open the day before, or the Russians were spying...

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