Sql-server – Collecting trace info of SQL Server 2005 using SQL Server 2008 R2 profiler

I don't know which version of NH you're using, but I'm using 3.1 with SQL 2008 and it does not have any special features to handle Parameter Sniffing. I know this because I've suffered quite a bit trying to overcome this issue.

I don't know if you use HQL/Criteria/QueryOver/LINQ, but I found two ways to (kind of) solve this:

• Appending "option(recompile)" on the query -> with an hook on the query execution if you're using the likes of QueryOver/LINQ.

Although this works, it no longer fetches existing query plans from the cache, and every query has to build it ground-up.

• Conditionally, add a dummy predicate to the query

Or something that would force a different query plan to be fetched. For example, if the date interval is less than a month add a .Where(x => true). It would then have a different plan for large intervals and a plan for small intervals.

Almost every time I've had an SP installation issue on a stand-alone server, it was because the resource database was moved or it was not in the same directory as the master database.

Many articles I'm finding correlate your error to this exact conclusion including this one.

I would start there and if you find it to be the case, move the system databases back to where the resource database is.

If that doesn't work, try re-applying SP2. If all of the components weren't patched during the SP2 upgrade, you could experience this issue.

Lastly, verify service account permissions and permissions of the account you are running the SP4 with.