THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

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.

Published Thursday, July 11, 2013 2:28 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



T said:

I'm required to work with a tool that does something similar - before running and SQL or calling an SP, it runs the same code with what it considers to be "dummy parameter values" - e.g. passing "0" to date parameters.

The it calls the code with "real" parameters. As you might imagine, these queries compile with the "dummy" values, and then performance for real execution absolutely tanks. We're forced to work around this, often by adding option recompile to the SPs.

I do hate it when developers think doing stuff like this is "clever".

July 11, 2013 2:42 AM

Greg Low said:

Wow, that's nasty too.

July 11, 2013 7:08 AM

Andrew G said:

Ah, but how about an app (3rd party) where the approach is;

"Stored Procedures are more efficient. ... so ... "

Connect to database

Generate sp with required SQL, no it's not what you're thinking.  It's a parameterised sp.

Execute sp with appropriate parameters

Drop sp

for everything.  Everything that hits the server from the app (it's on it's own box, it's not allowed to play with anyone else.  ever)

Performance is as one might expect, well, probably even worse given that it's doing other mad stuff.  Incidentally, has anyone else ever seen a runaway TempDB log?!  It was a new one on me I can tell you.

Do I win?

July 11, 2013 10:47 AM

Þröstur Jónasson said:

Whenever I find stuff like this I open hoping to cheer me up by readmin epic tales of misinfortunes. Both of these should be submitted there.

July 11, 2013 7:35 PM

Andrew G said:

Personally I just feel like reaching for the heaviest, nastiest Cluebat there is

July 14, 2013 3:48 PM

Emmett C. said:

I actually look forward to cloud based solutions because the metered use of resources will curtail the foolishness.  Those that can think will use resources efficiently while those that can't think will get a huge bill.

July 15, 2013 9:13 PM

Leave a Comment


This Blog



No tags have been created or used yet.


Privacy Statement