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:

Convenient Rebinds for ASP.NET Core DI

ASP.NET Core’s Dependency Injection system is a solid implementation and can provide solutions for most DI needs. But, like most DI systems, it is an opinionated API (and rightly so). I moved to the ASP.NET Core implementation from Ninject and, in doing so, there were a couple of Ninject methods that I really missed. Especially the .Rebind functions.

These are the functions that will take a interface-to-implementation binding in the system, and remove the old binding and set up a new one; with new lifetime scoping and new configuration/implementation details. With ASP.NET Core’s system, they really want the developer of the application to setup exactly the bindings that they desire at the very beginning of the program. And, the first binding that’s put in place should be the last binding made for that interface-to-implementation approach.

Their approach is well reasoned and it has it’s merits. It should lower overall confusion and needed knowledge when trying to figure out what bindings are being used. If you start reading Startup.cs’s ConfigureServices and you find a binding declaration, that should be the correct binding which will be resolved at runtime.

However, because of Ninject’s .Rebind functions, I am stuck in the mind set that bindings should be flexible as new subsystems are added. If you make a library, MyLib, that has a default caching implementation that uses InMemory caching, then your library will most likely setup a binding of IMyLibCache to MyLibInMemoryCache. If I then create an add-on library that implements caching using redis, MyLib.Redis, then I want to be able to swap out the binding of IMyLibCache with a new binding to MyLibRedisCache.

With the prescribed API of ASP.NET Core’s DI system, the way you would do this in code would look something like this:

But, that just feels backwards. When you were writing your original code, you would have to know upfront that someone in the future would have a need to use a different caching system. So, you would have to have the forethought to create the binding using .TryAddTransient() instead of .AddTransient().

It would feel much more natural if it was written like this:

So, that’s the Ninject thinking that is stuck in my head. And, because of it, here are a few convenience overloads which can make working with IServiceCollection a little bit easier:

Implementation Efficiency Frustration Tiers

For me, a lot of stress and anxiety about working efficiently comes from the momentary feelings of being ineffective. If I need to accomplish a work item, how long will it take to complete that work item? How many sub-tasks do I need to complete before I can complete the work item? How many of those do I understand and can do with a minimal amount of effort, and how many do I need to do research for before I can even begin implementing them? The more time, energy, and amount of knowledge that has to be gained to complete a work item, the more stressful it becomes to complete it.

So, I wanted to take a moment and start to break down those feelings into categories. I read Scott Hanselman’s Yak Shaving post years ago, and it has become a part of the shared language among the development teams I work with. Before reading that post, I had described the act of Yak Shaving as “speed bumps”; but I would have to explain it every time I used it. Hopefully, getting this written down can help me define a language so I can communicate this feeling more easily.

At the moment, the feeling of implementation efficiency can be broken down as:

Tier 3

This is when you need to implement something, but in order to do it you are going to need to learn a new technology stack or a new paradigm in order to complete it. The task you’re trying to complete could be something as trivial as adding Exception Handling to an application, but in order to do it, you’re going to research APM solutions, determine which best fits your needs and then implement the infrastructure and plumbing that will allow you to use the new tool.

An example of this might be your first usage of Azure Application Insights in an ASP.NET Core application. Microsoft has put in a tremendous amount of work to make it very easy to use, but you’ll still need to learn how to Create an Application Insights resource, add Application Insights into an ASP.NET Core application, re-evaluate if you created your Application Insights resource correctly to handle multiple environments, and then most likely reimplement it with Dev, Test, Prod in mind, determine what common parameters which are unique to your company should always be recorded, and then work with external teams to setup firewall rules, develop risk profiles and work through all the other details necessary to get a working solution.

Tier 3 is the most frustrating because you have learn so much yourself just to get to your end value. So, for me, it’s the one that I also feel the most nervous about taking on because it can feel like I’m being incredibly inefficient at doing so much work to produce something that feels so small.

Tier 2

This is when you already have all the knowledge of how to do something and you understand what configuration needs to take place, but you are going to have to do the configuration yourself. When you know at the beginning exactly how much work it will take to complete, there is a lot less frustration because you can rationalize the amount of time spent for the end value that’s achieved. The moment this becomes frustrating is when the extra work that you’re putting in is a form of Yak Shaving. For example, when you are dealing with a production issue, and you realize that you’re going to need to implement component X in order to get the necessary information in order to solve the problem, that’s the moment you heavily sigh because you realize the amount of hand work you’re going to have to put in place just to get component X working.

This level of efficient usually happens when your working on the second or third project that you’ve used a particular technology stack with. Let’s use Application Insights as the example again. You’ve probably already developed some scripts which can automatically create the Application Insights instances, and you’re comfortable installing the nuget packages that you need, but you still need to run those scripts by hand, and setup permissions by hand, and maybe even request firewall rules to be put in place. None of these tasks will really take up too much time, but it feels like wasted time because your not producing the real end value that you had in mind in the first place.

Tier 1

This is when the solution is not only well known to yourself, but your organization has developed the tooling and infrastructure to rigorously minimize the amount of time spent on implementing the solution. This doesn’t come cheap, but the peace of mind that comes with having an instantaneous solution to a problem are the moments that make work enjoyable. The ability to stumble upon a problem and think, “Oh, I can fix that”, and within moments you’re back to working on whatever you were originally doing creates a sense that any problem can be overcome. It removes the feeling that you’re slogging through mud with no end in sight, and instead that feeling is replaced with confidence that you can handle whatever is thrown at you.

It’s rare that you can get enough tooling and knowledge built up in an organization that Tier 1 can be achieved on a regular and on going basis. It requires constant improvement of work practices and investment into people’s knowledge, skillsets, and processes to align the tooling and capabilities of their environment with their needs.

When creating working environments, everyone starts out with a goal of creating a Tier 1 scenario. But, it seems pretty difficult to get there and maintain it.

This one of the pieces I can find very frustrating about security. There is a lot of information available about what could go wrong, and different risk scenarios, but their just isn’t a lot of premade tooling which can get you to a Tier 1 level of Implementation Efficiency. People are trying though: OWASP has the Glue Docker image, and Github automated security update scanner is fantastic, and NWebSec for ASP.NET Core is a step in the right direction. But, overall, their needs to be a better way to get security into that Tier 1 of Implementation Efficiency zone.