I need clarification about the exact effect of OPTION (RECOMPILE) at the end
of query inside SP:
1) Does it help "Parameter Sniffing" happen even if we have used local
variables in query?

I have created this simple SP in SQL Server 2005:
------------------------------------
use northwind
go

  We are on Win 2003 and SQL 2k, SP4 standard edition. We have been seeing
such requests in our errorlog.
"2007-04-09 11:36:28.29 spid62    SQL Server has encountered 148
occurrence(s) of IO requests taking longer than 15 seconds to complete on

 I have a win2003 system (4 GB and 4 CPUs) with sql 2k standard edition
running on it. I believe AWE cannot be enabled on a standard edition. a) Is
that true ?
b) SQL server is the only one running on that windows server (no other

I'd like to get some pointers on how I might expect SQLServer to use my
indexes.  For example if on a table I have IndexA on Field1 and a compound
index IndexB on Field1, Field2 can I expect SQL to perform just as well for
searches on Field1 if I eliminate IndexA?

Any help on this would be greatly appreciated. My environment as follows:.
Win 2003 R2
SQL 2005 x64 EE
12 GB RAM, AWE Enabled

Damn I really want to be able to post a JPG of the query execution plan.  
Suffice to say the query with the index hint is vastly superior.    Have
included the text showplan at the bottom, it may not wrap very well!
The table has a primary clustered key on ID; 4 million records, ...