An adventure on SQL Server performance and features


Revisiting the classics: Minimizing the Impact of DBCC CHECKDB (by Aaron Bertrand) in 2018


SQL Server has a long history, it has been around since the ’90, more than 20 years have passed, which is a VERY long time as far as the IT world goes; Especially now that Microsoft has stepped up the release schedule for SQL Server (releases are now scheduled yearly) I thought that maybe it’s a good time to go back and revisit the classic articles and recommendations by the Gurus of...

What’s the best way to massively update a big table?


A thing that can happen once in a while in a DW is the need to massively update a column in a table, let’s find out. I’ll be using the same table as last post , the lineitem table of the tpch test by HammerDB, the three contenders are: Heap Clustered Index Clustered Columnstore Index No other NonClustered index will be included in the base table. You should already know that each...

Test: confronting various methods of bulk loading data from a table to another, what’s the fastest?


 Most of the support request I get involved with can be summarized with the following keywords: “slow” + “datawarehouse” + “ETL” + “Save us” What about thinking about ETL performance before it goes bad? Before the system is in production for some time , the data size reaches a decent size which wasn’t tested in DEV and you find out that the...

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

Scan Performance Showdown: INROW Predicate pushdown VS Batch Mode Adaptive Join vs Columnstore indexes


I was reading Paul White’s post about bitmaps in SQL Server and, other than realizing that I basically know nothing about anything (like a Socrate’s epiphany) I wanted to at least see how this good stuff added up in the latest 7 years. Down with experimenting, we’re doing this LIVE! (Well, it’s live for me, for you it’s still a blog post) I’ll start by creating...

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


If you work for a big company, you may have a bunch of instances/databases under your control, you ran whatever monitoring tool has been provided and everybody is happy; But if your’re like me and you’re working for a big number of customers, each one with a different version of SQL and own configuration/security policies, with no monitoring tool directly available, and have to answer...

How to get and decode callstacks in in extended events for recent versions of SQL Server


The other day I was reading about spinlocks and troubleshooting and it mentioned to get the callstack for a certain xevent and to decode it using the windows debugging symbols, however, that material was related to SQL Server 2012 and the steps weren’t basically replicabile for a modern version like SQL 2016/2017 (or even 2014 I think, but haven’tn checked personally), I searched...

Create an asynchronous multithreaded workflow on your SQL Server using the Service Broker


As any other *Server, SQL Server is born with concurrency in mind, a lot of users/applications can connect concurrently and do their job, as designed, perfect, but, what if you’re the only user? What if you’re in some kind of data warehouse configuration where you have to ETL, or you are in a maintenance window and have to do a bunch of stuff in that time, or simply you need to launch...

THREADPOOL, or, Why is SQL server not responsive even the CPU is basically idle?


It’s a late Friday afternoon, this means that somebody is going to tell you that the server is unresponsive, users are blocked and there is a imperative deadline for the business at 6PM (which you think is probably because they don’t want to be late to the happy hour), so you do what you do best: sigh and log on to see what’s going on with this instance. You fire up SSMS, and...

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