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.
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".
Wow, that's nasty too.
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?
Whenever I find stuff like this I open http://thedailywtf.com hoping to cheer me up by readmin epic tales of misinfortunes. Both of these should be submitted there.
Personally I just feel like reaching for the heaviest, nastiest Cluebat there is
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.