This is an honest review of Brent Ozar Mastering courses. I finished all of them some months ago.
I couldn’t write this a couple of years ago. Working with databases was a subject I avoided at all costs. Even to the point where I traded database-related tasks with an ex-coworker at a past job.
Avoiding database concepts cost me painful lessons. Like the day I wrote a query with a function around a column in the WHERE and it almost took the server to its knees. That query was poorly written, and the table didn’t have good indexes. It ended up scanning the whole table. Arrgggg!
But, it changed a couple of years later while working with one of my clients. They asked me to investigate the performance of some critical parts of the app. And, I ended up Googling what to do to speed up SQL Server queries and compiling six SQL Server performance tuning tips I found.
In that search for performance tuning advice, I found Brent Ozar and his mastering courses. These are the three things I liked after taking them.
1. Realistic Labs and Workloads
As part of Brent’s courses, we work with a copy of the StackOverflow database. Yeap, the same StackOverflow we all know and use.
After every subject in each course, we have labs to finish. Labs with bad queries, no indexes, blocking issues, etc. For the last course, Mastering Server Tuning, we have an emergency to fix. A server is on fire, and we have to put down the fire and lay out a long-term fix.
Often, some labs have easier alternatives. Either focus on a particular issue or run a workload and assess the whole situation.
2. Constraints to Solve Labs
As we progress throughout the courses, we start to have constraints to solve the labs. For example, “no index changes allowed” or “only query-level fixes.”
But, the exercise I like the most is the “Let’s play being performance consultant.” We have to fix a workload under 30 minutes with as few changes as possible. The closest thing to a real-world situation. That’s from Mastering Server Tuning again. I would say that was my favorite course.
Of course, there are more courses. They’re four in total. There’s one course solely on indexes, another one about query tuning, one to fix parameter sniffing issues, and, my favorite, the one on server-level fixes. Each course sits on top of the previous ones.
3. Popular wisdom and guerilla tactics
All over the courses, Brent shares his experience as a consultant. I have these pieces of advice on my notes like “when working with clients.”
For example, he shares to build as few indexes as possible and provide rollback scripts for index creation, just in case. Also, to provide a prioritize list of actionable steps to make SQL Server fast.
Also, he shares personal anecdotes. Like the day he went to consult wearing jeans, and everybody at the place was wearing jackets and ties. That story didn’t have a happy ending for the company. But, I won’t tell you more.
Voilà! These are the three things I liked. My biggest lessons are to focus all tuning efforts on the top-most wait type and make as few changes as possible to take you across the finish line. Often, we start to push buttons and knobs expecting SQL Server to run faster without noticeable improvements. Making more harm than good.
I will take the second lesson to other parts of my work, even outside of performance tuning context. Focus on the few changes that make the biggest impact.
I have read a couple of times about the Great Resignation. I guess the world isn’t the place after the pandemic. And that is reflected in the job market too. “A toxic corporate culture is the single best predictor of which companies suffered from high attrition in the first six months of the Great Resignation.”Read full article
What’s in a Good Error Message?
I don’t know how many times I have debugged the “The given key was not present in the dictionary” error. In fact, I wrote two C# idioms to avoid exceptions when working with dictionaries. From the article, to write better error messages: give context, show the actual error, and tell how to mitigate it. Read full article
Why don’t I have a blog?
I have always heard: “start your own blog.” Well, I started my own blog…you’re reading it. But this is a post about the opposite: why not have a blog. The author points out is that posts only rephrase Reddit or StackOverflow’s comments. But, I learned from Show Your Work that nothing is completely original. We all have some sort of inspiration. And, precisely that, and our own voice make our blogs unique. Read full article
The best advice to learn something is to learn by doing. Finding project ideas is often difficult. Another TODO app, really? This article shares a learning activity: Write a wrapper for a library or an API you’re already familiar with. Instead of jumping to the documentation, start writing a wrapper in the language you want to learn. Read full article
Career Advice Nobody Gave Me: Never Ignore a Recruiter
These days, anyone with a LinkedIn profile and “Software engineer” in the headline is getting lots of spam messages and connection requests. I bet you already got a message between these lines: “Hi X, I have one company interested in your profile. Are you available for a quick call to share more details? Please, send me your CV at this email.”
This article shares a template to reply back to these spammy messages. If you use it, you will be asking for a company name, seniority, and compensation before starting any conversation. That would be enough feedback for recruiters too. Read full article
Voilà! Another five reads! Do you answer spammy messages or connection requests on LinkedIn? Do you have a template to answer recruiters? What strategies do you use to learn new programming languages?
These days I was working with a database migration tool. This is what I learned after debugging an issue for almost an entire day.
In one of my client’s projects to create or update stored procedures, we use a custom migrator tool. A wrapper on top of DbUp. I’ve already written about Simple.Migrator, a similar tool.
To avoid updating the wrong version of a stored procedure we rely on checksums. Before updating a stored procedure, we calculate the checksum of its definition at the database using a command-line tool.
How to find object definitions in SQL Server?
By the way… To find the text of a stored procedure in SQL Server, use the OBJECT_DEFINITION() function with the object id of the stored procedure.
To upgrade the database, the migrator compares the checksums of objects in the database with the checksums in header comments. If they’re different, the migrator displays a warning message and stops the upgrade.
Here comes the funny part. When I ran the migrator on my local machine, it always reported a difference. Even when I was grabbing the checksum from the migrator tool itself. Arrggg!
After debugging for a while and isolating the problem I found something. On the previous script for the same stored procedure, I started the script with CREATE OR ALTER PROC. There’s nothing wrong with that.
But, there’s a difference in the object definitions of a stored procedure created with CREATE and with CREATE OR ALTER.
CREATE PROC vs CREATE OR ALTER PROC
Let me show you an example. We’re creating the same stored procedure with CREATE and CREATE OR ALTER to see its object definition.
/* With just CREATE */CREATEPROCdbo.testASSELECT1GOSELECTLEN(OBJECT_DEFINITION(OBJECT_ID('dbo.Test')))ASLength,OBJECT_DEFINITION(OBJECT_ID('dbo.Test'))ASTextGO/* What about CREATE OR ALTER? */CREATEORALTERPROCdbo.testASSELECT1GOSELECTLEN(OBJECT_DEFINITION(OBJECT_ID('dbo.Test')))ASLength,OBJECT_DEFINITION(OBJECT_ID('dbo.Test'))ASTextGO
Here’s the output.
Object definition of a stored procedure with CREATE and CREATE OR ALTER
Notice the length of the two object definitions. They’re different! Some spaces were making my life harder.
The migrator compared checksums of the object definition from the database and the one in the header comment. They were different in some spaces.
I made the mistake of writing CREATE OR ALTER, and the migrator didn’t take into account spaces in object names before creating checksums. I had to rewrite the previous script to use ALTER and recreate the checksums.
Parting thoughts
But, what’s in this story for you? We should create processes to prevent mistakes in the first place. Scripts to make sure developers commit the code formatted properly. Checks to avoid applying data migrations to the wrong environment. Extensions or plugins to follow naming conventions. Scripts to install the right tools and dependencies to run a project. Up to date documentation for internal tools.
Often, code reviews aren’t enough to enforce conventions. We’re humans, and we all make mistakes. And, the more code someone reviews in a session, the more tired he will get. And, the more reviewers we add, the less effective the process gets.
Voilà! That’s what I learned these days: read object definitions from SQL Server, polish my debugging skills and build processes around our everyday development practice.
As we learned last time, when I covered in-memory caching with ASP.NET Core, a cache is a storage layer between an application and an external resource (a database, for example) used to speed up future requests to that resource. In this post, let’s use ASP.NET Core IDistributedCache abstractions to write a data caching layer using NCache.
What’s NCache?
From NCache official page, “NCache is an Open Source in-memory distributed cache for .NET, Java, and Node.js applications.”
Among other things, we can use NCache as a database cache, NHibernate 2nd-level cache, Entity Framework cache, and web cache for sessions and responses.
NCache comes in three editions: Open Source, Professional, and Enterprise. The Open Source version supports up to two nodes and its cache server is only available for .NET Framework version 4.8. For a complete list of differences, check NCache edition comparison.
One of the NCache key features is performance. Based on their own benchmarks, “NCache can linearly scale to achieve 2 million operations per second with a 5-server cache cluster.”
How to install NCache on a Windows machine?
Let’s see how to install an NCache server on a Windows machine. For this, we need a Windows installer and have a trial license key. Let’s install NCache Enterprise edition, version 5.2 SP1.
After running the installer, we need to select the installation type from three options: Cache server, remote client, and Developer/QA. Let’s choose Cache Server.
Cache Server Installation Type
Then, we need to enter a license key. Let’s make sure to have a license key for the same version we’re installing. Otherwise, we will get an “invalid license key” error. We receive the license key in a message sent to the email address we used during registration.
Enter NCache license key
Next, we need to enter the full name, email, and organization we used to register ourselves while requesting the trial license.
Enter User Information
Then, we need to select an IP address to bind our NCache server to. Let’s stick to the defaults.
Configure IP Binding
Next, we need to choose an account to run NCache. Let’s use the Local System Account.
Account to run NCache
Once the installation finishes, our default browser will open with the Web Manager. By default, NCache has a default cache named demoCache.
NCache Web Manager
Next time, we can fire the Web Manager by navigating to http://localhost:8251.
NCache’s official site recommends a minimum of two servers for redundancy purposes. But, for our sample app, let’s use a single-node server for testing purposes.
So far, we have covered the installation instructions for a Windows machine. But, we can also install NCache in Linux and Docker containers. And, we can use NCache as virtual machines in Azure and AWS.
How to add and retrieve data from an NCache cache?
Now, we’re ready to start using our NCache server from a .NET app. In Visual Studio, let’s create a solution with a .NET 6 “MSTest Test Project” and a class file to learn the basic caching operations with NCache.
Connecting to an NCache cache
Before connecting to our NCache server, we need to first install the client NuGet package: Alachisoft.NCache.SDK. Let’s use the latest version: 5.2.1.
To start a connection, we need the GetCache() method with a cache name. For our sample app, let’s use the default cache: demoCache.
Let’s start writing a test to add and retrieve movies from a cache.
usingAlachisoft.NCache.Client;usingMicrosoft.VisualStudio.TestTools.UnitTesting;namespaceNCacheDemo.Tests;[TestClass]publicclassNCacheTests{[TestMethod]publicvoidAddItem(){varcacheName="demoCache";ICachecache=CacheManager.GetCache(cacheName);// We will fill in the details later}}
Notice we didn’t have to use a connection string to connect to our cache. We only used a cache name. The same one as in the Web Manager: demoCache.
NCache uses a client.ncconf file instead of connection strings. We can define this file at the application or installation level. For our tests, we’re relying on the configuration file at the installation level. That’s why we only needed the cache name.
Adding items
To add a new item to the cache, we need to use the Add() and AddAsync() methods with a key and a CacheItem to cache. The key is an identifier and the item is a wrapper for the object to cache.
Every item to cache needs an expiration. The CacheItem has an Expiration property for that.
There are two basic expiration types: Absolute and Sliding.
A cached item with Absolute expiration expires after a given time. Let’s say, a few seconds. But, an item with Sliding expiration gets renewed every time it’s accessed. If within the sliding time, we don’t retrieve the item, it expires.
Let’s update our test to add a movie to our cache.
usingAlachisoft.NCache.Client;usingAlachisoft.NCache.Runtime.Caching;usingMicrosoft.VisualStudio.TestTools.UnitTesting;usingSystem;usingSystem.Threading.Tasks;namespaceNCacheDemo.Tests;[TestClass]publicclassNCacheTests{privateconststringCacheName="demoCache";[TestMethod]publicasyncTaskAddItem(){varmovie=newMovie(1,"Titanic");varcacheKey=movie.ToCacheKey();varcacheItem=ToCacheItem(movie);ICachecache=CacheManager.GetCache(CacheName);// Let's add Titanic to the cache...awaitcache.AddAsync(cacheKey,cacheItem);// We will fill in the details later}privateCacheItemToCacheItem(Moviemovie)=>newCacheItem(movie){Expiration=newExpiration(ExpirationType.Absolute,TimeSpan.FromSeconds(1))};}[Serializable]publicrecordMovie(intId,stringName){publicstringToCacheKey()=>$"{nameof(Movie)}:{Id}";}
Notice, we used two helper methods: ToCacheKey() to create the key from every movie and ToCacheItem() to create a cache item from a movie.
We used records from C# 9.0 to create our Movie class. Also, we needed to annotate it with the [Serializable] attribute.
Retrieving items
After adding items, let’s retrieve them. For this, we need the Get<T>() method with a key.
Let’s complete our first unit test to retrieve the object we added.
If we try to add an item with the same key using the Add() or AddAsync() methods, they will throw an OperationFailedException. Try to add a unit test to prove that.
To either add a new item or update an existing one, we should use the Insert() or InserAsync() methods instead. Let’s use them in another test.
usingAlachisoft.NCache.Client;usingAlachisoft.NCache.Runtime.Caching;usingMicrosoft.VisualStudio.TestTools.UnitTesting;usingSystem;usingSystem.Threading.Tasks;namespaceNCacheDemo.Tests;[TestClass]publicclassNCacheTests{// Our previous test is the same[TestMethod]publicasyncTaskUpdateItem(){ICachecache=CacheManager.GetCache(CacheName);varmovie=newMovie(2,"5th Element");varcacheKey=movie.ToCacheKey();varcacheItem=ToCacheItem(movie);// Let's add the 5th Element here...awaitcache.AddAsync(cacheKey,cacheItem);varupdatedMovie=newMovie(2,"Fifth Element");varupdatedCacheItem=ToCacheItem(updatedMovie);// There's already a cache item with the same key...awaitcache.InsertAsync(cacheKey,updatedCacheItem);varcachedMovie=cache.Get<Movie>(cacheKey);Assert.AreEqual(updatedMovie,cachedMovie);}// Rest of the file...}
Notice we used the InsertAsync() method to add an item with the same key. When we retrieved it, it contained the updated version of the item.
There’s another basic method: Remove() and RemoveAsync(). We can guess what they do. Again, try to write a test to prove that.
How to use ASP.NET Core IDistributedCache with NCache?
Up to this point, we have NCache installed and know how to add, retrieve, update, and remove items.
Let’s remember the example from that last post. We had an endpoint that uses a service to access a database, but it takes a couple of seconds to complete. Let’s think of retrieving complex object graphs or doing some computations with the data before returning it.
Notice we used Newtonsoft.Json to serialize and deserialize objects.
NCache and the IDistributedCache interface
Now, let’s use a .NET 6 “ASP.NET Core Web App,” those extension methods on top of IDistributedCache, and NCache to speed up the SettingsService.
First, we need to install the NuGet package NCache.Microsoft.Extensions.Caching. This package implements the IDistributedCache interface using NCache, of course.
After installing that NuGet package, we need to add the cache into the ASP.NET dependencies container in the Program.cs file. To achieve this, we need the AddNCacheDistributedCache() method.
// Program.csusingAlachisoft.NCache.Caching.Distributed;usingDistributedCacheWithNCache;usingDistributedCacheWithNCache.Services;var(builder,services)=WebApplication.CreateBuilder(args);services.AddControllers();// We add the NCache implementation here...services.AddNCacheDistributedCache((options)=>{options.CacheName="demoCache";options.EnableLogs=true;options.ExceptionsEnabled=true;});services.AddTransient<SettingsService>();varapp=builder.Build();app.MapControllers();app.Run();
Notice, we continued to use the same cache name: demoCache. And, also we relied on a Deconstruct method to have the builder and services variables deconstructed. I took this idea from Khalid Abuhakmeh’s Adding Clarity To .NET Minimal Hosting APIs.
Back in the SettingsService, we can use the IDistributedCache interface injected into the constructor and the extension methods in the DistributedCacheExtensions class. Like this,
usingDistributedCacheWithNCache.Responses;usingMicrosoft.Extensions.Caching.Distributed;namespaceDistributedCacheWithNCache.Services{publicclassSettingsService{privatereadonlyIDistributedCache_cache;publicSettingsService(IDistributedCachecache){_cache=cache;}publicasyncTask<SettingsResponse>GetAsync(intpropertyId){varkey=$"{nameof(propertyId)}:{propertyId}";// Here we wrap the GetSettingsAsync method around the cache logicreturnawait_cache.GetOrSetValueAsync(key,async()=>awaitGetSettingsAsync(propertyId));}privatestaticasyncTask<SettingsResponse>GetSettingsAsync(intpropertyId){// Beep, boop...Aligning satellites...awaitTask.Delay(3*1000);returnnewSettingsResponse{PropertyId=propertyId,Value="Anything"};}}}
Notice, we wrapped the GetSettingsAsync() with actual retrieving logic around the caching logic in the GetOrSetValueAsync(). At some point, we will have the same data in our caching and storage layers.
With the caching in place, if we hit one endpoint that uses that service, we will see faster response times after the first call delayed by 3 seconds.
A few miliseconds reading it from NCache
Also, if we go back to NCache Web Manager, we should see some activity in the server.
NCache Dashboard showing our first request
In this scenario, all the logic to add and retrieve items is abstracted behind the IDistributedCache interface. That’s why we didn’t need to directly call the Add() or Get<T>() method. Although, if we take a look at the NCache source code, we will find those methods here and here.
Voilà! That’s NCache and how to use it with the IDistributedCache interface. With NCache, we have a distributed cache server with few configurations and a dashboard out-of-the-box. Also, we can add all the caching logic into decorators and have our services as clean as possible.
To follow along with the code we wrote in this post, check my Ncache Demo repository over on GitHub.
To read more content, check my Unit Testing 101 series to learn from how to write your first unit tests to mocks.
I wrote this post in collaboration with Alachisoft, NCache creators.
Today I learned how to use constants in SQL Server stored procedures. While getting a stored procedure reviewed, I got one comment to remove literal values. This is how to bring constants in T-SQL.
SQL Server doesn’t have a keyword for constants. To introduce constants in stored procedures, write literal values next to an explaining comment or use single-row views with the constant values as columns.
Don’t use variables as constants
From C# and other programming languages, we’ve learned to use constants or enums instead of magic values all over our code. Often, we bring constants to our T-SQL queries. But…
T-SQL doesn’t have a keyword for constants. And, SQL Server engine doesn’t inline variables when executing stored procedures.
The first thing we try by mistake to emulate constants is to use variables.
For example, let’s find all StackOverflow users with two reputation points. That’s not a popular reputation among StackOverflow users. Something like this,
/* An index to speed things up a bit */CREATEINDEXReputationONdbo.Users(Reputation)GOCREATEORALTERPROCdbo.usp_GetUsersASBEGIN/* This is how we often emulate constants */DECLARE@ReputationINT=2;SELECT*FROMdbo.UsersuWHEREu.Reputation=@ReputationORDERBYu.CreationDateDESC;ENDGO
This is the execution plan. Let’s keep an eye on the number of estimated users.
Execution plan of finding users with 2-point reputation
But, there’s a downside. Variables inside stored procedures trigger a different behavior in SQL Server.
Variables and execution plans
When executing a stored procedure, SQL Server creates an execution plan for the first set of parameters it sees. And, SQL Server reuses the same execution plan the next time we run that stored procedure. We call this behavior Parameter Sniffing.
SQL Server uses statistics (histograms built from samples of our data) to choose the shape of the execution plan. Which table to read first, how to read that table, the number of threads, the amount of memory, among other things.
But, when there are variables in a stored procedure, SQL Server builds execution plans, not from statistics (e.i. samples of our data), but from an “average value.”
Variables make SQL Server build different execution plans, probably not suited for the set of parameters we’re calling our stored prcedures with. That’s why variables aren’t a good idea to replace constants.
Literal values and comments
The simplest solution to constants in T-SQL is to use literal values.
To make stored procedures more maintainable, it’s a good idea to write an explaining comment next to the literal value.
Let’s rewrite our stored procedure with a literal and a comment.
This time, we're back to a literal value and a comment
Do you remember the estimated number of users from our example with variables? Now, we have a more accurate estimated number. SQL Server isn’t using an average value. It has better estimates this time.
We even have an index recommendation in our execution plan. By the way, don’t blindly follow index recommendations, just listening to them. They’re only a list of columns to consider indexing.
Create a view for constants
The hardcoded value and an explanatory comment are OK if we have our “constant” in a few places.
A more maintainable solution to literal values is to create a single-row view with columns named after the constants to declare.
With that view in place, we can replace the hardcoded values in our stored procedure.
CREATEORALTERPROCdbo.usp_GetUsersASBEGINSELECT*FROMdbo.Usersu/* The views with our constant */INNERJOINdbo.vw_ConstantcONu.Reputation=c.InterestingReputationORDERBYu.CreationDateDESC;ENDGO
A more maintainable alternative while keeping good estimates.
Voilà! That’s how to use constants with a view in SQL Server. I found a proposal to introduce a constant keyword in SQL Server. I learned about the trick with views while getting my code reviewed. But I also found the same idea in this StackOverflow question and in this one too.