While working on optimizing performance of SQL Queries, its very important that the right numbers are compared.
Performance numbers might vary with each execution mostly due to SQL Server cache and buffer.
Below are the steps that can be taken to make sure that you are seeing right numbers.
– Clear the cache by running the above Script provided in this Link
– Run the query that you are trying to performance tune and note the Statistics. (a)
– Run the above script again to clear the cache.
– Now execute the tuned query and note down the Statistics. (b)
Query Statistics (a) and (b) would now be comparable as both of them are executed over the clean cache.