SQL Server Query Optimization Woes

I had a complex view that returned, among other things, a foreign key ("spId").  I then had a select statement of the form SELECT * FROM theView WHERE spId = ?.  If I executed the query interactively, it returned the result in a couple of seconds.  But if I ran the query using sp_prepare/sp_execute, the query took a very long time to complete.  This was inconvenient since the query was actually being executed through jTDS which, by default, does the prepare/execute thing (although it can be disabled using the connection parameter prepareSQL=2).

After some research, I concluded that I was the victim of the SQL Server optimizer's sensitivity to the exact parameter value (aka "parameter sniffing").  I found some web pages that discussed this:

• A technique for ensuring plan stability in SQL Server 2000, on Ken Henderson's blog
• Tips, Tricks, and Advice from the SQL Server Query Optimization Team, on the SQL Server Query Optimization Team's blog

In short, if a query is compiled in the absence of specific query parameter values (such as in the context of sp_prepare), the execution plan may be radically different from that generated when values are available (such as when an ad hoc query is being compiled) .  Solutions range from:

1. wrapping the query in a stored procedure that gives more clues as to the parameters type and/or value (on Ken's blog, he goes so far as to set a value on a never-supplied optional proc parameter and reference that parameter in the query)
2. forcing a recompile of the query immediately prior to running it -- e.g. using SQL Server 2005's OPTION(RECOMPILE)
3. giving the optimizer a hint as to parameter values -- e.g. using SQL Server 2005's OPTION(OPTIMIZE FOR (@P=1))

As I was using SQL Server 2000, I could only use the first option (which worked).  I didn't have to resort to setting up a dummy parameter -- it was enough to create a minimal proc wrapper:

CREATE PROCEDURE dbo.zot @P INT AS SELECT ... AND spId = @P

I tried to avoid the proc by changing my inline query to something like this:

DECLARE @P INT
SET @P = ?
SELECT ... AND spId = @P

but, surprisingly, the optimizer ignored the strong hint that @P was an INT.  I also had no luck with:

SELECT ... AND spId = CAST(? AS INT)

Parameter-sniffing exploits statistics, so my problems might have been caused by stale or misleading statistics.  Here are some useful diagnosis commands:

• show statistics for a column: dbcc show_statistics('dbo.projectFilter','spId')
• show cached plans, etc: select * from master.dbo.syscacheobjects where sql like 'dbo.projectFilter'