August 16, 2009 at 3:29 am
Hi All,
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.
Thanks,
Bhimraj
August 18, 2009 at 12:13 am
Am I wrong in my observation about query response time on multiprocessor machines ?
Has anybody faced similar situation like this ?
August 18, 2009 at 2:09 am
Unless I've missed something, then apart from slight differences in the estimates, those plans look the same.
If that's the case, then you should be looking at the differences in the hardware... I/O and memory, as opposed to CPU.
Also, the Core2 machine is running SQL Server 2005 RTM, but it looks as though the single processor machine is running SQL Server 2005 SP2.
August 18, 2009 at 7:34 am
Hi,
Are those estimated or actual plans?
It's best to supply the actual plans.
Could please also supply the graphical plans attached as .sqlplan in a zip file.
Thanks
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 19, 2009 at 4:04 am
Christopher Stobbs (8/18/2009)
Hi,Are those estimated or actual plans?
It's best to supply the actual plans.
Could please also supply the graphical plans attached as .sqlplan in a zip file.
Thanks
Yes..Those are actual plans. I am attaching exec plans in graphical format. Please check.
About hardware, I understand that my server hardware configuration is much high compared to my local machine. But I can see same reponse time (as dev server) on my colleagues machine database which has dual processor (other hardware conf is nearly same as my machine).
August 19, 2009 at 8:57 pm
I have seen it happening several times. A very powerful production machine vs. my 3 years old laptop or my home lab, 5 years old, single core, single processor, low end Power Edge. Quite as you say 1:2 in performance. It turned out, I believe, to be a SQL server version issue mostly. My laptop and the home lab server were running developer edition of sql server 2000 (so basically the Enterprise version). The production machine was running sql server 2000 standard and it could never use the memory it had! I could reproduce an issue on other developers machines. All had developer editions :-). Both my laptop and my home server could utilize more than 2GB memory (3GB switch ON). So, I think it was the biggest reason for what was happening. In all the cases I was able to get queries down to more acceptable time (well below 30 sec .. which is default timeout for most applications .. by redoing them to a point that both production machine and my computers started to get the same run times).
So, maybe you can have the same issue. SQL servers are not the same (regardless of the hardware). Maybe you can also still work out the queries. I noticed that production machine was getting more affected by parameter sniffing that those less powerful machines. And ..there used to be issues with degree of parallelism, so it might be worth digging into still.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply