August 20, 2005 at 10:03 am
Hello All,
I have a production and development SQL 2000 database running on Win2k sp4. If I run a query against that database on development using Query Analyzer from a remote machine, the query takes 10 min to complete. But if I run the same against the production database from the same remote machine, it takes 2 hours to complete. But if I run the same query on the production server, it only takes 8 minutes to complete.
The databases are the same on both dev and prod and they are on the same subnet including the server that I ran the queries from. Basically, all three servers are in the same data center on the same subnet.
Any ideas? I would automatically think network, but it would have to be isolated to just the prod server. Any suggestions as to what I can look for?
Thanks,
Ronnie
August 20, 2005 at 11:46 am
1)It is very likely your network is congusted, because your query launched locally just runs fine.
Any hubs remaining? Working on a WAN,dial-up?
Returning a very large dataset ?
ps Sure that SET NOCOUNT ON is in your query?
all small tidbits help
August 20, 2005 at 11:19 pm
What are the differences in the execution plans? Do you see a plan where there are a few millions rows transfered where there should be only a few 100K?
August 22, 2005 at 7:47 am
If it's network related (which seems to be the obvious), it's only specific to the production server because the dev server is also on the same subnet in the same data center.
Is there any additional security checks that would cause such a difference in time?
All three queries return a little over 800k records. So, the integrety of the data appears to be solid. Whatever the problem is, it's isolated to the production server. The hardware on the production server consist of the following:
Compaq DL740
8 x 2.0GHz CPU
8GB RAM
300GB HD/150GB free
This server is much more powerful than the dev. The only other difference is there are 8 separate instances on prod with 3 being the most active and one of the 3 instances is where I ran my query (They complianed the most).
August 22, 2005 at 7:51 am
Seems to be network related... but did you see any difference in the execution plans???
August 22, 2005 at 7:58 am
Can you explain what you mean by execution plan?
August 22, 2005 at 8:06 am
SET SHOWPLAN_TEXT ON
GO
--query goes here
GO
SET SHOWPLAN_TEXT OFF
GO
then post the results for the fast and slow queries (with clear identifications).
August 22, 2005 at 8:09 am
Remi - Good info!! I'll give that a try and track my results.
Thanks!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply