Things like select count(*) are just fine as long as the where clause is indexed correctly.
DTA may or may not be of any use.
The actual and estimated plan are what you need to look at. It'll tell you if there are any indexes which are missing. The indexes which are used will be in the execution plan.
To be more precise, NOT FENCED routines run in the same process space as the database manager itself. The engine is written in C, so calling a not fenced routine is just like calling another C function from main(). This is where all the memory corruption and performance aspects come from: a not fenced routine has access to all the same resources -- memory, files, etc. -- as the database manager process itself.
For a FENCED routine the database manager launches a separate process (db2fmp), which in turn executes the routine code. As a result the operating system protection prevents a fenced routine to access any memory area or resource that belongs to the database manager.
SQL routines cannot be fenced, strictly speaking, because they don't "run", but they are even better than not fenced -- they are bytecode that the DB2 runtime engine itself executes, so there's no separate thread or process.
C and C++ routines can be fenced, in which case they execute in separate processes, or not fenced, in which case they are loaded into the database manager process space and called as functions.
Java routines can only be fenced by the fact that they need a separate process, Java virtual machine, to execute. If you declare them as NOT FENCED, the option is quietly ignored.
Having said all that, your only choice between fenced and not fenced is with C/C++ routines. Typically you would run the fenced for safety, changing to the not fenced mode only when you are pretty sure they can't harm the database manager and they need higher performance1.
1 - Performance difference between fenced and not fenced routines comes from the overhead associated with allocating a fenced process, as well as with interprocess communications between the database manager and the not fenced routine. Even then, the fenced process is not created every time a fenced routine is called; a pool of them will be created and reused for such calls. All this means that you might see the benefit of declaring a routine as fenced only if that routine is called very frequently (as in, tens of times per second or more).
Best Answer
No, there really isn't, for the same query taking the same parameters (though this hasn't always been the case - SQL Server wasn't always able to optimize at the statement level).
Now, if the ad hoc SQL you're sending to SQL Server varies by quite a bit (say a lot of similar but not identical queries), then you could have a lot of plan cache bloat due to single-use plans. You can reduce this somewhat by using the optimize for ad hoc workloads server setting. And depending on the parameterization techniques you're using, you may want to play with dynamic SQL vs. "kitchen sink" style queries (
WHERE (c1 = @p1 OR @p1 IS NULL) AND (c2 = @p2 OR @p2 IS NULL) ... etc ...), and also parameterization forced vs. simple.If you're looking for opinions with justification, I prefer stored procedures for a variety of other reasons - including the fact that you can drastically reduce the number of calls (and the amount of data) you send over the wire, you can also simplify the hardest parts of transaction management and error handling in a single layer, and the database people can optimize the SQL without having source code access and without having to re-compile and re-deploy the application code (be it the front end or middle tier - trust me, we're not good at that, just like developers are stereotypically not very good at writing good SQL queries :-)).