An adventure on SQL Server performance and features

CategoryTroubleshooting

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

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

T

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]

C

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

C

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

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

C

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

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

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

T

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