An adventure on SQL Server performance and features

T-SQL Tech

An adventure on SQL Server performance and features

T

Latest stories

A script to automatically align columnstore indexes to enhance segment elimination (and hence performances)

Columstore indexes are a “new” neat data structure that I like, even if technically they’ve been around for years, only recently they’ve become usable by most customers. Let me recap a little bit what we’re talking about, so the point of this will be clearer: The table is divided in rowgroups of about one million rows max, then each column is stored by itself in a...

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

Tracking Dynamic SQL performance automatically with a wrapper on sp_executesql (Dynamic SQL Inception)

in some projects I have a lot of Dynamic SQL going on, some queries are fast, some queries are slow, it depends on how the dynamic statement is actually put together ¯\_(ツ)_/¯ Tracking the performance of such statements is usually difficult because you don’t really know “why it’s slow” and you have to interpolate data from the app and the plan cache (if the plan is still...

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

A clarification on the waitstat: SOS_SCHEDULER_YIELD

Are you one of the people that says “we have a CPU related issue” whenever it sees SOS_SCHEDULER_YIELD popping up? Let me explain why you’re wrong. In the books online, SOS_SCHEDULER_YIELD is defined simply as: Occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed. Well, since the SQL...

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

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