An adventure on SQL Server performance and features

CategoryTips&Tricks

How to populate a PowerBI Dataset from a REST API, using PowerBI’s REST API, without any code (via Microsoft Flow)

H

I was wondering if PowerBI could be used for reporting on Live data, not coming from my Database using DirectQuery (or the new super neat composite mode) with with a Streaming Dataset, which I never used before, so, I got to work. The Proof Of Concept is to take live data from Cryptocurrency value using REST API and pushing them into a PowerBI report.I’m using the API from...

Find who e when something was messed up in order to restore to a safe point using the Transaction Log

F

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

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

A

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

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

T

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

A clarification on the waitstat: SOS_SCHEDULER_YIELD

A

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

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

W

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

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

H

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

C

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

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