SQL: SQL Server and Programming Frameworks

I have days where I can’t decide if I’m frustrated or sad about how I see SQL Server being used by applications, or if I’m happy that this keeps us in ongoing work.

Recently, I was looking at a system that was having performance issues. There were three key applications on the system. Each came from a different vendor and when I looked at how each one of them interacts with the server, it was just really frustrating.

So many issues are caused by application frameworks (including ORMs) that are used to talk to databases, but also the misconfiguration of those frameworks. In many cases, the frameworks can be configured to work far more efficiently.

Here’s an example:

System A that I was working with (name hidden to protect the guilty) never just issued a query to SQL Server. What it does instead is:

1. Issues a SET FMTONLY ON, then sends the command, then SET FMTONLY OFF
2. Creates a static cursor based on the command
3. Fetches from the cursor
4. De-allocates the cursor

This simply isn’t sensible. Apart from just slowing down the data access immensely, here are my issues with this:

SET FMTONLY ON returns the columns and data types for every code path through the code that you submit to it. That works ok for simple commands but is fragile for anything else.

Regardless, I can only presume that it’s performing step #1 as a way of creating an empty dataset/recordset/rowset in the programming language, without having to specify the columns and data types. This is concerning for multiple reasons. First is that SET FMTONLY ON is now deprecated but more importantly, it means that the system is being constantly asked to evaluate something that isn’t actually changing. What’s the point of that?

On the system I was monitoring, the application was asking SQL Server to do this over 60,000 times per hour, yet the answer isn’t going to change. And it is probably occurring just to simplify the creation of the rowset in code (i.e. a one-off one-time effort).

Creating a static cursor involves executing the query and materializing a copy of all of the data that was retrieved into tempdb. For almost all of the queries in question, tempdb has no need to be involved at all. The fetch command then retrieves the data from tempdb. The de-allocation then removes the copy of the data and the cursor structure from tempdb.

I also noticed that if parameters were required, that yet another whole layer of round trips and code was required. And in the end, this is all instead of just executing a single query.

It’s a tribute to SQL Server that it performs as well as it does, given how it’s often used or (more likely), abused. And if the developers ever looked at what is actually being sent to the database server, they’d be horrified. What is has to deal with, is simply ridulous.

2026-05-16