Query performance inconsistency across servers

[takaoki]

I have 2 servers, presumably identical but I suspect not.

One one server, a certain update statement takes about 15 minutes... on the other other server the same update never finishes. It can run for several hours without ever finishing.

I ran an execution plan on the query. 35% of the cost is spent updating a clustered index as a result of the update statement. So I dropped the index on the problematic server. However, the query still ran for 7 hours and never finished.

Is there a way for me to determine what differences exist between these two servers? And what differences might be contributing to the slowness of the query on one of them?

Thanks

 

[JoeAtWork]

Are the two servers truly clones? I.e. same hardware, same software and patches, same database with same amount of records?

One guess is that your first server has a trivial amount of data in it, while the one that is slow has a whole lot of data in it. Very inefficient queries or stored procedures may not be evident until there is a lot of data.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[mrdenny]

Are you sure that the servers have the same indexes? Do they have the same amount of data is cache? Is one server stagnant, while the other has users using it all day long?

With databases, there is pretty much no such thing as two identical servers.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[takaoki]

Right, they're probably not identical. That's what I'm trying to determine. What the differences are.

Is there some kind of report I can run that will show the "vital statistics" as it were? sp_dboption is one I know of.

I don't know about cache. Where I would look for this?

btw, I got the query down to 6 minutes by tuning a statement, but that still doesn't explain why they perform so differently across servers... I'd still like to determine this.

Thanks

 

[SQLSister]

Dropping a clustered index is often a bad idea as other processes may rely on that index being there.

You don't want to speed up your insert and bring all querying of the system to a dead halt.

How many records are being inserted on each server?

Are you by chance doing this update in a cursor?

"NOTHING is more important in a database than integrity." ESquared