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.

Today I’ve been looking at a system that’s having performance issues. There are three key applications on the system. Each comes from a different vendor and when I look at how each one of them interacts with the server, it’s really frustrating. I’ve come to the conclusion that it’s mostly due to application frameworks that are being used. Here’s an example:

System A that I’m working with (name hidden to protect the guilty) never just issues 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. 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.

It’s a tribute to the product that it performs as well as it does, given how it’s often used or (more likely), abused.

2013-07-11