February 6, 2007 at 10:42 am
This is a strange one:
We copied our SQL Server 2000 installation and all databases/packages, etc to a new Server.
All works about the same, except a package that deals with a large (4 mil. rows) table. On the old server it ran in 5 minutes, on the new one it takes 90 minutes!
I can find (so far) nothing different between the 2 servers.
I have rebuilt the indexes on this large table, rebuilt the package, to no avail.
Any ideas on this one? I suspect it has something to do with the setup of SQL Server, or the Server set up.
Thanks Much,
Doug
February 6, 2007 at 1:32 pm
Plans are dependant on hardware,software version,statistics,...
Can you isolate the bad performing query (with sql profiler, ... ) and compare the execution plan?
February 6, 2007 at 3:35 pm
Thanks!
I did isolate the query-
and the Execution Plans are not the same!
What controls how an execution plan is put together? Why should it differ?
Would the Profiler tool be of help here? Other suggestions on figuring out why the huge difference in exec speeds?
Is there some tool to compare exec plans?
Thanks again, doug
February 7, 2007 at 4:21 am
statistics and indexes available are the main determinants for execution plans. Check stats are up to date, try updating them then checking execution plan.
degre of parallelism is another potential cause. check maxdop is same between the servers
---------------------------------------------------------------------
February 7, 2007 at 10:22 am
Sometimes the compiler gets it wrong, even with updated statistics.
Can you post the showplan estimates before and after? Might be able to figure it out.
February 7, 2007 at 2:59 pm
Thanks for the hints!
Where and what is maxdop? Where can I check that?
After a lot of studying the Execution Plans, I tried adding a clustered index to the large (5 mil rows) table- which brought the execution speed to 15 secs. (versus 90 mins of before!)
The Plan was using a Bookmark Lookup to do the join,, which was not being done on the 1st server (where it was fast).
Before changing the index I ran UPDATE STATISTICS on all tables (Is this the same as running sp_updatestats?) with no performance improvement. sp_autostats is On- but does this really ensure that the STATISTICS are updated automatically??
The question remains though, why did the execution plans differ so much?
Ideas?
Thanks again for your help!
February 16, 2007 at 7:39 am
maxdop = max degree of parallelism. To check can use sp_configure or in EM right click server, check properties, under processor tab you have parallelism, if use all processors is selected maxdop = 0 else maxdop = to the value specified in 'use processors'
would normally only be changing this to 1 (turn off parallelism) or = to the physical no. of processors in a hyperthreded SMP environment
If your fix was to add a clustered index I doubt this was your problem. Maybe it is quantity of data?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply