Press "Enter" to skip to content

Category: Internals

Analyze Transaction Log Hierarchy

Published 2016-05-02 by Kevin Feasel

Paul Randal has written code to determine how much log an operation has generated:

Over the weekend there was a discussion on the MVP distribution list about the sys.dm_tran_database_transactions DMV and how one cannot use it to accurately determine how much log an operation has generated because it doesn’t provide a roll-up of the sub-transaction metrics to the outer transaction. This makes the output somewhat non-intuitive.

The discussion prompted me to write some code I’ve been meaning to do since 2012, when SQL Server 2012 introduced a field in LOP_BEGIN_XACT log records that tracks the transaction ID of the parent transaction, allowing the hierarchy of transactions to be investigated.

The actual code is at the bottom of the article, and is available in a zip file here.

It provides two stored procs, sp_SQLskillsAnalyzeLog and sp_SQLskillsAnalyzeLogInner, with the former making use of the latter, and the latter calling itself recursively.

I’d consider this squarely in the advanced troubleshooting realm.  Definitely read the whole thing and Paul’s code.

Comments closed

Updated Scheduling

Published 2016-04-01 by Kevin Feasel

The CSS SQL Server Engineers team talks about a new scheduling algorithm in SQL Server 2016:

SQL Server 2016 gets a scalability boost from scheduling updates.   Testing uncovered issues with the percentile scheduling based algorithms in SQL Server 2012 and 2014.  A large, CPU quantum worker and a short, CPU quantum worker can receive unbalanced access to the scheduling resources.

Take the following example.  Worker 1 is a large, read query using read ahead and in-memory database pages and Worker 2 is doing shorter activities.   Worker 1 finds information already in buffer pool and does not have to yield for I/O operations.    Worker 1 can consume its full CPU quantum. 

On the other hand, Worker 2 is performing operations that require it to yield.  For discussion let’s say Worker 2 yields at 1/20th of its CPU, quantum target.  Taking resource governance and other activities out of the picture the scheduling pattern looks like the following.

I’m going to have to reserve judgment on this.  It’s been in Azure SQL Database for a while, so I’m not expecting bugs, but I wonder if there are any edge cases in which performance gets worse as a result of this.

Comments closed

Blocking Operators

Published 2016-03-17 by Kevin Feasel

Gail Shaw explains how operators like sort can reduce the actual row count:

A non-blocking operator is one that consumes and produces rows at the same time. Nested loop joins are non-blocking operators.

A blocking operator is one that requires that all rows from the input have been consumed before a single row can be produced. Sorts are blocking operators.

Some operators can be somewhere between the two, requiring a group of rows to be consumed before an output row can be produced. Stream aggregates are an example here.

Gail ends by explaining that this is why “Which way do you read an execution plan, right-to-left or left-to-right?” has a correct answer:  both ways.  This understanding of blocking, non-blocking, and partially-blocking operators will also help you optimize Integration Services data flows by making you think in terms of streams.

Comments closed

Finding A Page

Published 2016-01-20 by Kevin Feasel

Kenneth Fisher is looking for the database page tied to a particular row:

It’s not one of those things you have to do frequently but every now and again you need to know what page a particular row is on. It’s not terribly difficult. There is a virtual column called %%physloc%%, but unfortunately it’s not terribly useful on it’s own.

Finding a record’s page probably isn’t something you want to do every day; this is more one of those “once in a great while” activities which can help with troubleshooting.

Comments closed