DaveWentzel.com            All Things Data

February 2012

How to Upgrade Win32 Apache

I tried googling this and couldn't find anything regarding how to upgrade Apache Win32 from 2.2.14 to 2.2.22 (or really from any minor version to another).  I downloaded the latest msi from Apache and kinda just assumed the installer would realize I have an older version of Apache httpd already and offer to upgrade it.  But it didn't.  The installer then failed saying two instances of 2.2.x could not co-exist on the same machine.

Making Prepared Execution Calls Easier to Trace

Hopefully you never have to deal with determining the real procedure that is being called under-the-covers with Prepared Execution and sp_execute.  If you don't understand the issues then this is a pretty good post to get you started.  I've also written about this in detail many years ago.  For standards reasons it seems like more JDBC drivers are making prepared execution the default (or only) method of making stored procedure calls.  

The issue is what do you do when Support Guy calls you up and says, "I see 'sp_execute 70,param,param' is performing horribly in Profiler, can you fix it?"  If the problem is already manifest at your customer sites you have a real problem.  You really should have caught this in your performance tests, but hopefully even earlier when you defined your supportability goals.  

Possible fixes:

  1. if your driver/application can turn Prepared Execution off, do it.  
  2. if you can write a custom wrapper around the sql statements in your Data Access Layer to instead construct dynamic sql that generates an "friendly" RPC that you can see in Profiler.  The biggest problem here is sql injection, remembering to double-up the single quotes, and all of the other issues with dynamic sql.  
  3. Add an optional last parameter to all of your stored procedures called @ProcName.  Change your data access layer to always pass the name of the procedure into the last parameter.  In Profiler you'll see something like this 'exec sp_execute 70,param1,param2,'MyProcedure'.  
  4. if you run a "24x7" sqltrace then (using the example above) find the most recent call to 'sp_prepexec 70' on the same spid and it will tell you the actual query.
  5.   If the plan is still in cache you can probably get the information from the DMVs.  Be aware...this method is not always reliable.  DMV information is transient in nature.  Try this:
select text
from sys.dm_exec_requests
cross apply sys.dm_exec_sql_text(plan_handle)
where session_id = 213

I'll expand upon all of these possible solutions in the future.  

Table Valued Function Performance

In a couple of different posts I've done over the years (here, here, and [Freelinking: unknown plugin indicator "Pattern"]) I've mentioned that multi-statement table valued functions tend not to perf