Post reply

Slow Database Queries

  • August 20, 2005 at 10:03 am

    #88635

    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

    #583761

    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

    #583775

    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

    #583895

    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

    #583897

    Seems to be network related... but did you see any difference in the execution plans???

  • August 22, 2005 at 7:58 am

    #583904

    Can you explain what you mean by execution plan?

  • August 22, 2005 at 8:06 am

    #583930

    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

    #583932

    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