June 29, 2018

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

By Emanuele Meazzo

5 Min read

1 comment

29June

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

Read onRead later

June 4, 2018

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

By Emanuele Meazzo

5 Min read

Add comment

04June

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

Read onRead later

May 16, 2018

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

By Emanuele Meazzo

3 Min read

Add comment

16May

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

Read onRead later

May 2, 2018

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

By Emanuele Meazzo

8 Min read

Add comment

02May

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

Read onRead later

April 25, 2018

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

By Emanuele Meazzo

5 Min read

2 comments

25April

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

Read onRead later

April 16, 2018

A clarification on the waitstat: SOS_SCHEDULER_YIELD

By Emanuele Meazzo

2 Min read

Add comment

16April

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

Read onRead later

April 4, 2018

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

By Emanuele Meazzo

4 Min read

Add comment

04April

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

Read onRead later

March 21, 2018

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

By Emanuele Meazzo

7 Min read

Add comment

21March

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

Read onRead later

March 20, 2018

SQL Server 2017 CU5 enhances troubleshooting for Intra-Query Parallelism Deadlock

By Emanuele Meazzo

1 Min read

Add comment

20March

Do you remember this post from not long ago? Apparently, other than still being an issue, with the latest SQL Server 2017 CU there are additional tools to see what’s going on and troubleshoot.
Check out the Microsoft post here.

Read onRead later

March 11, 2018

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

By Emanuele Meazzo

9 Min read

1 comment

11March

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

Read onRead later