I have a query that takes from 2 to 7 minutes to run, depending on the specifics of the search criteria. It pulls from a lot of tables. I read in a forum post about using OPTION (RECOMPILE) and WITH (NOLOCK) to make queries faster.
I added WITH (NOLOCK) on every single JOIN.
I add OPTION (RECOMPILE) at the bottom of the query.
I ran it in sql server management studio and compared the speed to the query without those changes.
Exactly the same.
SQL Server 2012 version.
Why did nothing happen? Other people said it made theirs so much faster?
It would make sense it should have sped it up because of the NOLOCK, but it didn't.
Do I need a server setting? Maybe a server setting overrode it or something else?