PAGELATCH_UP and tempdb Contention

The first thing to remember about PAGELATCH_UP is that while it’s not a lock, it can block.

The screenshot above is from the SQL Observability Tool: Idera SQL Diagnostics Manager for SQL Server. It’s well worth the price.

An observability tool that takes to time to highlight particular activity with bright red backgrounds is definitely giving you a hint that an improvement can be made. In the screenshot above, the boxes with the red background are queries that are blocked by another query. And the reason for the blocking in outlined in the red blocks on the right side: PAGELATCH_UP on wait resources 2:1:1, 2:5:32352, and 2:3:48528.

So, what does that mean?

Well … I first needed to know, what is PAGELATCH_UP? Here are some resources to get started (at the time of this writing these links work; I sure hope they continue to work in the future):

• Diagnosing and Resolving Latch Contention on SQL Server (PDF)
  
  A 2011 whitepaper from Microsoft’s SQL Experts on how to work through latch problems.
  
  It starts out with a description of all the different latches, and describes PAGELATCH_UP and a type of hold on a page in the memory to read the page with a potential for updating it.
  
  A similar description can be found on the MSDN Doc, Q&A on Latches in SQL Server Engine.
  
  Near the beginning of the Diagnosing PDF, there is a section on likely scenario’s that will produce a PAGELATCH_UP contention. One of those scenarios is “High degree of concurrency at the application level”, which is exactly the situation that produced the screenshot above. That screenshot was produced by having around 1000 simultaneous users trying to read the from same table at the same time, repeatedly.
  
  For a “High degree of concurrent at the application level” problem, the whitepaper suggested reading Table-Valued Function and tempdb Contention (broken link: http://go.microsoft.com/fwlink/p/?LinkID=214993).
  
• Table-Valued Function and tempdb Contention / SQLCAT’s Guide To: Relational Engine (PDF)
  
  So, “Table-Valued Function and tempdb Contention” was a blog post on the Microsoft websites at some point in time. And, it has been referenced in a number of other blog posts. However, the original post is no longer available.
  
  But, in 2013 the Microsoft SQLCAT team took the blog post in it’s entirety and copied it into a new whitepaper they were writing, SQLCAT’s Guide To: Relational Engine. This is a fantastic blessing, but using google searches, it really felt like that knowledge had been lost.
  
  Within the whitepaper it describes some key information, PAGELATCH_UPs, PFS and SGAM.
  
  Less detailed information about these components can be found in TempDB Monitoring and Troubleshooting: Allocation Bottleneck.
  
  The important information that the Table-Valued Function and tempdb Contention whitepaper describes is that a PAGELATCH_UP block on Wait Resource 2:1:1 is a block that is waiting to read the first line of the first page of the Page Free Space (PFS) page. In fact, all of those Wait Resource reference are PFS pages. You can determine if a Wait Resource is a PFS page by dividing the 3rd number by 8088. If it is evenly divisible by 8088, then it is a PFS page. (32352 / 8088 = 4, 48528 / 8088 = 6).
  
  The guide then goes on to describe what is happening in the background: The system is looking to allocate some tempdb space in order to hold temporary results as it is processing through a query. In order for the tempdb to reserve some free space it will need to request the amount of space it needs from SGAM, which will in turn ask the PFS system where free space is available. At that point in time, the PFS is read and if it finds free space it will mark that space as allocated (requiring a PAGELATCH_UP handler to read/write the PFS information).
  
  The way the original PFS handler algorithm was written, it first searches from free space at the very first PFS page (2:1:1) and then sequentially looks through PFS pages until it finds free space.
  
• Recommendations to reduce allocation contention in SQL Server tempdb database
  
  SQLCAT’s Guide To: Relational Engine goes on to give a few suggestions on how to improve tempdb PFS contention. Contention has been around for a while and one of the ways that performance can be improved is to split tempdb into multiple files. In the wait resource above the 2:5:32352 number can be read as:
  
  2 = Database Id, 2 is always tempdb
  5 = File 5, databases can be split into multiple physical files on disk
  32352 = Page 32352, pages are 8-KB in size and they are the smallest allocated size of memory/space the system creates. Page 1 and every page evenly divisible by 8088 are PFS pages.
  
  The very commonly suggested way to approach to splitting tempdb in multiple files is to make a temp physical file for each logical core on the server. If you 8 cores, then make split tempdb into 8 evenly sized files. The reason for this, is that it can allow each core to search a different physical file looking for free space. This reduces contention on the files and their associated latches.
  
• PFS page round robing algorithm improvement in SQL Server 2014, 2016 and 2017
  
  Around May of 2018 the SQL Server team also came up with an improved way to search PFS files in order to reduce contention. Instead of always starting out with 2:X:1 as the first page read in each file, instead it would remember the last page it found space in and start the next search in the first page following that previously stopping point. This has an overall affect of evenly distributing tempdb allocations over the whole of the allocated space.
  
  Many years ago, spreading data out of the disk would have been a performance loss. Especially on spindle hard drives. This is the reason that defrag’s and dbcc shrink push the data next to each other on disk.
  
  However, I don’t know if using regular spindle drives on a SQL server is a very common practice anymore. If you’re running in the cloud or in an on-premise data center, it’s likely that the backing storage is a SAN array of some kind. It might be a SAN array of spindle disks, but it’s still probably a group of disks working together to make a high performance array. At that point, does it really matter if the data is grouped together? (That would be pretty interesting to look up.)
  
  If it doesn’t need to be grouped together, then leaving the tempdb allocations evenly spread seem like a performance increase without any downsides.

Hopefully, that long wall of text helps lower the amount of research needed to find out what PAGELATCH_UP is and why it might be blocking threads in a SQL Server database.

Functional Testing Harness in ASP.NET Core

The ASP.NET Core team has made testing a first class system within their ecosystem, and it really shows. One of the aspects they made a few steps easier is functional testing of web applications. Both ASP.NET and ASP.NET Core have similar goals of creating a TestClient (GetTestClient) which can be used to perform the actual functional testing, but the ASP.NET Core teams HostBuilder pattern makes it just a touch easier to configure your TestServer.

One of the very cool things with ASP.NET Core is that if you are writing functional tests for your web application, you can use your actual Startup.cs class to configure your TestServer. This saves a lot of configuration overhead that’s involved in setting up unit tests (which still should be created). With functional tests, you usually want to see how many parts of the system work together, but you still want to stub/mock some of the external connections.

So, how can you let the system wire itself up and then mock out just the external connections? The HostBuilder makes it pretty easy to do just that. You can use your normal  Startup.cs class to configure the system and then add on an extra .ConfigureServices() function which will add in your mocks.

Here’s what that might look like:

And here’s what some code that uses it could look like:

Octopus Deploy Configuration Transforms

For the last couple of years, I’ve worked with Octopus Deploy as our main Deployment system. Prior to Octopus Deploy, we used custom made Powershell scripts that were built as extensions to TFS XAML builds.

The nice part of having all of our deployment logic in Powershell scripts is that we were able to reuse those scripts with Octopus Deploy. However, there are many features within Octopus Deploy which we were happy to ditch the scripts for and use what “came out of the box”. One of those places is Octopus Deploy’s Configuration Transforms.

ASP.NET (Web.Config)

Configuration Transforms are more of a legacy feature at this point, since they are designed to perform XML transforms for ASP.NET Web.Config files. ASP.NET Core’s appSettings.json files (and it’s corresponding Configuration system) are fine without performing environment specific transformations.

To use the Configuration Transforms feature, you only need to update your Deploy a Package step to use the Configuration Transforms feature and then setup a search pattern to find the files you want transformed.

ASP.NET Core (appSettings.json)

Of course, with ASP.NET Core you don’t really need to do configuration transforms anymore. However, if you do need to provide the functionality to transform appSettings.json files you can do that with a Powershell script.

Quick sidenote: Using Octopus Deploy, you can use the JSON Configuration Variables to perform substitutions in JSON files. However, the feature is designed to have the substitution values provided by Octopus variables.

Here’s a quick Powershell script which will:

• Uses the Microsoft.VisualStudio.Jdt nuget package (within a Transforms.Console.exe application, Thanks Jason La!) to transform the appSettings.json file.
• Removes the environment specifics transform files.

Book Review: Mindset: The New Psychology of Success

This wasn’t the normal DevOps book that I enjoy reading, but it wasn’t too far from it either. The focus of the book was on creating a mindset such that your goal is to learn from the activities you do, rather than focus on winning or achieving a prize. This isn’t a new concept, but the history she relates helps paint a more detailed picture of how people can fall into a mindset where completing a task is the goal, rather than gaining the deeper understanding of how you complete a task.

Her description of how people fall into this mindset comes from a tremendous amount of research and first hand experience educating children. Her theory is that education systems that put pressure on teachers to teach towards a test often set an unfortunate precedence that passing the test is the highest priority. This changes the goals, or mindsets, of the teachers so that they can rationalize that if they get the children to memorize the answers and regurgitate them, then they have completed their task. That if the children pass the test, it corresponds with the false belief that the children have learned. Extending from that idea, this sets up classrooms where children are given tests throughout the year and they either pass or fail the test, and then they move on to the next subject. Which creates a psychological barrier that the children either know it or they don’t, and there’s no way that can possibly change. Creating a false impression that the test has decided, this is all they will ever know.

Instead, research from Dr. Dweck and others have shown that if you change the goal from testing into fostering positive learning experiences that can take root in children, then the children are self motivated to tackle hard problems using hard work, overcome discouragement through a desire to improve their own knowledge and abilities, and create a virtuous cycle by looking at knowledge gain as the real reward. This is very similar to the Lean practices of Continuous Improvement (Toyota Kata stresses continuous improvement as does Lean Startup), Dr. Westrum’s Generative Culture, and W. Edwards Deming’s thoughts on education (The New Economics: For Industry, Government, Education).

I truly enjoyed the many examples that she had working with children, and the advice she gave on how to encourage children to learn is equally applicable to adults; so there is a lot of value within those pages.

However, I would encourage others to skip the chapters which use sports examples. Dr. Dweck can be a bit single minded in her focus to connect success with a learning mindset. While a willingness to continually grow and improve are necessary to achieve great success, it’s often a breakthrough within a particular field or a combination of improvements that create a new framework which actually creates the success; it doesn’t come from just having a learning mindset.

Reference Microsoft.AspNetCore.Routing in Library

So, I’m confused by the Migrate from ASP.NET Core 2.2 to 3.0 documentation when it comes to the deprecated libraries.

In the Remove obsolete package references section, there is a long list of packages hidden under “Click to expand the list of packages no longer being produced”. The package from that list that I’m going to focus on is Microsoft.AspNetCore.Routing. That’s the package that contains IEndpointRouteBuilder.

The article explains that the removed packages are now available through the shared framework Microsoft.AspNetCore.App. So, let’s test out if that works. I’m going to:

• Create a new class library which will reference IEndpointRouteBuilder.
• Attempt to get that class library to successfully compile.

The examples in this post can be found on github at IEndpointRouteBuilderDemo.

Here’s the sample IEndpointRouteBuilderExtensions.cs class that will be used in our test:

So, let’s try it with the suggested .csproj file settings:

And the error we get back is:

C:\Program Files\dotnet\sdk\3.1.102\Sdks\Microsoft.NET.Sdk\targets\Microsoft.NET.Sdk.FrameworkReferenceResolution.targets(283,5): error NETSDK1073: The FrameworkReference 'Microsoft.AspNetCore.App' was not recognized

Okay … so, what can we do? Well, there is a closed issue on github about this problem: AspNetCore Issue #16638, Cannot find the AspNetCore Nuget packages for 3.0 (specifically routing). The response to that issue is to do what was demonstrated above. So, what else can we try.

That issue accurately describes that the Microsoft.AspNetCore.Routing dll is embedded in Microsoft.AspNetCore.App, which is located on disk at C:\Program Files\dotnet\packs\Microsoft.AspNetCore.App.Ref\3.1.2\ref\netcoreapp3.1\Microsoft.AspNetCore.Routing.dll. But, how can you get it referenced propely?

One way to get it referenced is to change the .csproj file to use the SDK of Microsoft.NET.SDK.Web and change the TargetFramework to netcoreapp3.1. Like this:

But, when you do that, you get a new error:

CSC : error CS5001: Program does not contain a static 'Main' method suitable for an entry point

Which, kind of makes sense. The project has been changed over to a netcoreapp, so it kind of expects to create an executable. The great part about executables is that they also create .dlls, which is what we are looking for in the first place.

We just need to get it to compile in order to get the .dll. To do that, let’s create a DummyMain.cs class which will provide the required static ‘Main’ method:

Which provides a successful compile:

Done building project "IEndpointRouteBuilderDemo-Compiles.csproj".

Build succeeded.
    0 Warning(s)
    0 Error(s)

Of course, this isn’t the ideal result. And, it would be hard to believe that the ASP.NET Core team expected this to occur. So, it’s most likely my misunderstanding of how to reference the Shared Framework correctly in order to prevent the need for the rest of these workarounds to occur.

Hopefully the Microsoft team will be able to shed more light on this in AspNetCore Issue #19481, Reference IEndpointRouteBuilder in a class libary (ASP.NET Core 3.1).

Which they did!

So, the documentation from Migrate from ASP.NET Core 2.2 to 3.0 doesn’t use the Microsoft.NET.SDK.Web SDK reference in the .csproj file. It uses the Microsoft.NET.SDK reference instead. Along with the Shared Framework reference, this allows for the class library to be compiled without needing the DummyMain method: