There might be following reasons for different timings:
1. Different hardware configurations CPU, RAM, etc. on different servers Note: If we're running the same query on different servers, we can not expect the same time, but we may expect similar times, so the huge difference is a case for investigation.
2. First time SQL Server caches the execution plan and next time it re-uses it so it will be faster next time.
3. It is always the best practice to not use function in column based on which you are filtering.
4. SQL Server is using different Execution plans based on scenario or case, please compare the actual execution plans for all run.
5. Also other process or query might be using the table at the same time use NOLOCK hint on table to save that 6. There may be change in clustered index after new data insertion in the tables involved in the SQL Query. 7. There might be new Materialized views create after last run that case causing the delay. 8. Any index is removed from the tables involved in the sql query. Waiting for your comments.
Naomi N edited Revision 1. Comment: Minor edit
Naomi N edited Original. Comment: Minor edit
I haven't changed the content of the article, but I think some of the reasons are not really reasons, but just best practices and some have nothing to do with the problem we discuss - say, CAST or CONVERT makes no difference in the execution time
Thanks Naomi for your comments, Edited it, feedback welcome for making it better
Prajesh, here's some more great feedback to check out and go through:
Point #5 is not clear - do you suggest to use NOLOCK hint? If so, is that good advice? Maybe not.
Point #8 is not clear either - what does it mean "index removed" - please elaborate
Finally, point #3 is a general good practice but how is it related to this topic?.
There is a very good article on the topic the author tries to put his thoughts on:
www.sommarskog.se/query-plan-mysteries.html
Please check it out and see what you think. We can link to it from this article if it's related.
Thanks!