Using the options attribute of the SQL Server Producer you can specify extra (platform specific) options on (platform independent) methods.
For instance were you in the need to specify the RECOMPILE option on a stored procedure you could do it as so:
<cf:method name="LoadByName" body="LOAD(Name) WHERE Name = @Name"
cfps:options="WITH RECOMPILE" />
Please note that the options attribute isn’t inferred, you could specifying an option here simply copies/pastes it in the output code.
By the way, you can also set hints as shown in this post.