The art of simplicity

I think that is the clearest blog title I used in years. Why do I mention this? Let me explain... Some time ago we stumbled over a performance issue in our applications. The root cause was a fragmented index, caused by the usage of a standard guid instead of a sequential guid. While looking for the right fix, I had to revisit one of my own posts: Sequential GUIDs with .NET 9 . In that post I mentioned that you can use Guid.CreateVersion7() to create a sequential guid. That is technically correct, but it is NOTa solution for SQL Server. Why does a random guid hurt? A clustered index in SQL Server is a sorted B-tree. When the key is random, every insert lands on a random page. If that page is full, SQL Server has to split it, which leaves you with half-empty pages, a fragmented index and more I/O. A sequential key always appends at the end, so pages fill up and stay put. Why is a version 7 guid not sequential for SQL Server? A UUID version 7 (RFC 9562) starts with a 48-bit U...

After upgrading our SQL Server Reporting Services (SSRS) environment, we noticed some isuses: reports were slow, users were complaining, and we had no idea where to start. The good news is that SSRS has been quietly collecting detailed execution data the whole time — and a set of built-in views makes it surprisingly easy to query. This post walks through the ExecutionLog views, what they contain, and how to turn that data into actionable performance insights. What are the ExecutionLog views? SSRS logs every report execution to the ReportServer database. Three views expose this data at different levels of detail: ExecutionLog — A simple view covering the basics: report name, user, start time, and duration. Good for quick lookups. ExecutionLog2 — Adds AdditionalInfo , an XML column with richer metadata such as estimated row counts and data source connection details. ExecutionLog3 — The most complete view. Breaks execution time into three distinct phases — TimeData...

With the Data API builder, you can easily generate an API on top of an existing database. However typing out the configuration settings in the dab-config.json isn't much fun. The auto-entities features I talked about before can certainly help, but that is not always the right solution. With the integrated GUI in the MSSQL extension for Visual Studio Code, you can replace the manual JSON configuration with a visual interface that handles entity selection, CRUD permission mapping, API type targeting, and Docker-based local deployment — all without leaving the editor. This post covers exactly what the UI does, what it generates, and where it falls short. Entry points The DAB configuration view is accessible from two places: Object Explorer — right-click a database node → Build Data API (Preview)... Schema Designer — Design API button (top-right toolbar) or the Backend icon in the left panel Both open the same configuration surface. Entity selection Tables a...

Data API builder 2.0 (currently in public preview) is a major release focused on MCP and AI integration. Among its headline features is the ability to expose stored procedures as custom MCP tools , making them discoverable and callable by AI agents. No glue code, no middleware, no extra plumbing. In this post I'll walk through how the feature works, and show a practical example: wiring up a full-text search stored procedure as its own dedicated tool that any MCP client can discover and call by name. The idea: a dedicated search tool By default, DAB's SQL MCP Server exposes tables and views through generic DML tools — things like list_books , get_book , and so on. These are useful for straightforward CRUD, but they're not designed for complex operations like full-text search. With custom-tool: true , you can go further. Set that flag on a stored-procedure entity and DAB dynamically registers the procedure as a named, purpose-built tool in tools/list . The AI agent di...

If you've been maintaining a large dab-config.json , you know the pain: every table, view, and stored procedure needs its own entities block. Schema grows, config grows. Someone adds a table and forgets to update the config, and suddenly your API is silently missing endpoints. DAB 2.0 Preview introduces autoentities — a pattern-based approach that discovers and exposes database objects automatically, every time DAB starts. This post covers how it works, how to configure it from the CLI, and what to watch for. Getting started As DAB 2.0 is still in preview, you first need to install the preview version: dotnet tool install microsoft.dataapibuilder --prerelease Note: MSSQL data sources only, for now. Initialize a new dab-config.json file if it doesn't exists yet: dotnet dab init Remark:  Notice that we prefix dab with dotnet to avoid collisions with the globally installed release version. How it works Instead of defining each entity explicitly, you define one ...

With all this AI features available, you would expect that you no longer loose time on stupid issues. Unfortunately, we are not there yet. I lost a chunk of time today to a behavior in SQL Server that, once you know it, is totally obvious — but until then is absolutely maddening. I'm sharing it here so hopefully you don't lose the same time I did. The setup I had a script meant to be idempotent: create a database user if it doesn't exist, or update it if it does. Standard stuff. Here's a simplified version: Looks fine, right? Run it once — works. Run it a second time and SQL Server throws an error saying it can't find usr_SampleDB_reader . The user you just created. In the same database. With the same script. What's actually happening When you run ALTER USER [...] WITH LOGIN = [...] , SQL Server renames the user to match the login name — by default, silently, without a warning. So after the first run, usr_SampleDB_reader no longer exists. It's...

Between all this AI craziness, we often forget to appreciate the small tools and features that make our lives easier. Such  a feature is Central Management Servers (CMS) , a built-in SQL Server feature that lets you manage a whole fleet of instances from one place. Let's walk through what it is, how to set it up, and when it'll actually make your life easier. So, what is a Central Management server? At its core, CMS is a SQL Server instance that acts as your hub for organizing and talking to other SQL Server instances. You register your other servers under it, group them however makes sense (by environment, team, region — you name it), and then query all of them at once. The metadata about your registered servers gets stored in the msdb database on the CMS host. Nothing fancy — it's just a central directory that SSMS knows how to use. Setting it up in SSMS Here's how to get going in SQL Server Management Studio: Step 1: Open the Registered Servers panel ...

The timeout appeared without warning. A migration pipeline that had run fine in staging suddenly ground to a halt in production, throwing lock wait timeouts across multiple worker threads. The application was querying and writing to the same table, and somewhere in that dance of reads and writes, things had seized up entirely. Here is a walk through the investigation, from the first diagnostic query all the way to the fix — along with an explanation of why it worked. Find the blocker The first tool in any SQL Server lock investigation is sys.dm_exec_requests joined against sys.dm_exec_sessions . This query shows you every session that is currently blocked, who is blocking it, and what SQL both parties are running: A second query dug into the exact lock modes held on the specific table in question: The output told the story The results were unambiguous. Three sessions. One blocker. Two victims. Blocking Blocked Wait type ...

Every developer has felt that moment of panic: "Did I just run that DELETE statement on production?" A colleague (thanks Jef!) pointed out a nice trick in SQL Server Management Studio to avoid such kind of heart-stopping moments. Turns out there is simple yet powerful feature in SQL Server Management Studio (SSMS) called Connection colors that does exactly what the name suggests. Let’s find out how to use this feature… The problem: Context switching gone wrong When you're managing multiple SQL Server environments—development, staging, and production—it's surprisingly easy to lose track of which connection you're working with. All it takes is one misplaced query execution, and suddenly you're: Dropping tables in production Running test data scripts on live systems Executing resource-intensive queries during peak hours Modifying critical data without proper backups The consequences can range from embarrassing to career-threatening. Th...

I got contacted by someone from my team that INSERTs were failing on one of our database instances. A look at Application Insights showed the following error message in the logs: System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. For an unknown reason, the transaction timeout before it could be committed resulting in a rollback operation and a failing INSERT.  Time to open SQL Server Management Studio and first take a look at the Resource Locking Statistics by Objects report: Indeed, multiple sessions share a lock on the same table. If you want, you can also check the User Statistics report to find out what these sessions are related to:   To fix the issue, I killed the sessions that were causing the lock: KILL 236 KILL 210 KILL 257 After doing that, I could confirm that the locking was gone by refreshing the report: More information Sql...

A colleague created a stored procedure that returns some data from a specific table. Nothing special would you think and you are right. The only reason we were using a stored procedure here is that we had a very specific requirement that every attempt to read data from this specific table should be logged. Here is a simplified version of the stored procedure he created: What I want to talk about in this post is the usage of a (comma separated) string parameter that is used to construct the filter criteria for the query. Remark: This version of the stored procedure is already better than the original version that was using dynamic SQL to construct an IN clause: Using Table-Valued Parameters We can further improve the procedure above by using a table valued parameter. To use table-valued parameters instead of comma-separated strings in your stored procedure, you can follow these steps: Step 1: Create a Table-Valued Parameter Type First, you need to create a table-valued...

I’m currently migrating data between 2 systems. Therefore I build a small migration tool (using the great TPL Dataflow library). While everything worked fine during development, I noticed that the migration failed on production with the following exception: 'Microsoft.Data.SqlClient.SqlException' in System.Private.CoreLib.dll ("Transaction (Process ID 153) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.") Before I showed you how I fixed the problem, let me first give you some hints on how to investigate this issue. Investigating a deadlock The first thing I did was opening up my SQL Server Management Studio and checking the ‘Resource locking statistics by object’ report (check this link if you don’t know where to find this report): In the report above I could see that both the doc.Document and doc.DocumentInfo tables were recently locked. That made sens...

Athough EF Core is a developer friendly Object-Relational Mapper (ORM), working with it isn't without its challenges. One error that we encountered during a pair programming session was: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value In this blog post, we will delve into the causes of this error and explore ways to resolve it. "Constructing a database in the 18th century" - Generated by AI Understanding the error This error typically occurs when there is an attempt to convert a datetime2 value in SQL Server to a datetime value, and the value falls outside the valid range for the datetime data type. datetime : This data type in SQL Server has a range from January 1, 1753, to December 31, 9999, with an accuracy of 3.33 milliseconds. datetime2 : This newer data type, introduced in SQL Server 2008, has a much broader range from January 1, 0001, to December 31, 9999, with an accuracy of 100 nanoseconds....

Last week I was interviewing a possible new colleague for our team. During the conversation we were talking about database optimization techniques and of course indexing was one of the items on the list. While discussing this topic, the candidate made the following statement: An index doesn’t help when using a LIKE statement in your query. This was not in line with my idea. But he was so convinced that I decided to doublecheck. Hence this blog post… Does a ‘LIKE’ query benefits from having an index? Short answer: YES! The somewhat longer answer: Yes, a LIKE statement can benefit from an index in SQL Server, but its effectiveness depends on how the LIKE pattern is constructed. Let’s explain this with a small example. We created a Products table and an index on the ProductName column. Let’s now try multiple LIKE statement variations: Suffix Wildcard (Efficient Index Usage) This query will benefit from the index because the wildcard is at the end: Prefix Wil...

At one of my customers we store files in the database in varbinary(max) columns. To debug an issue we wanted to read out the files. Of course we could create a small program to do this, but we wanted to do it directly from the SQL Server Management Studio. We created a small database script that uses OLE Automation procedures: To use this script for your own purposes, replace the query after the cursor creation: DECLARE FILEPATH CURSOR FAST_FORWARD FOR <Add your own SELECT query here> The first time that we executed this script, it failed with the following error messages: Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1 [Batch Start Line 0] SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more...

In one of our ASP.NET Core applications, I added a new feature to cleanup old data. My implementation was simple and used a BackgroundService to run a cleanup script on periodic intervals: All worked fine during development and testing, but when I deployed it to production it brought the whole application to a halt. What was happening? First, as this was the first time the script was run on production, there was a lot of old data. So while the query executed and completed quite fast on other environments, on production it impacted millions of rows. What made the problem even worse is that the table that should be cleaned up contained a large amount of binary data. This made the transaction log grow in size and further increased the query duration. My first attempt to improve the performance of this query was to delete the data based on the primary key. A suggestion I found here: How to Delete Large Amounts of Data – SQLServerCentral However the impact of this change was m...