Need your help again. I am working on query optimization and was able to tune some of the queries to great extent - ofcourse with help from SSC experts.
I usually work on local machine (with single processor) for performance changes and then finally upload the thinngs on development server. Most of the times, there is difference of 2-3 secs on local machine and dev server response times. But I am ok with that and understand that it may be due to dev server enviornment.
I optimized one SP which was taking 35 secs earlier to 16 secs (on local machine). When I uploaded and executed that SP on dev server (Core 2 duo), it was taking 29-31 secs. Then to test out the things, I took out one base level query from SP and ran on two machines. I was able to see response times differences again (9/10 secs VS 22/24 secs). Please note that both dev server and local machine has exactly same database.
I compared databases with SQL compare to make sure everything is same, rebuild indexes, updated statistics on all tables with full scan etc. At that time, I was not sure why there is difference. So I restored same database on colleagues machine and it gave same response time as dev server. When I checked machine configurations, only differnce I found was single processor on my local machine and 2 processors on my colleagues/dev server machine. I dont know much about how machine configuartions can affect performance - but can imagine that one with high configuration should run fast. Here query is running slow on multiprocessor machines.
I am attaching query and execution plans on local and dev machine. One main difference btwn exec plans is 'degree of parallelism' value. I tried using Option hint MAXDOP setting to 1 on dev sever - but without any luck.
I am sure, there is something else that I am not able to figure out. Performance issues - really has taught me many interesting things in SQL but didnt expect such kind of issues ahead of release. :crying:
Your help is truely appreciated. Thanks in advance.