Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Query performance.

    I am working at a client site with an accounting database, and we are dealing with a simple query:

    select count(*)
    from Contract
    inner join Charge on Contract.ContractID = Charge.ContractID

    Oddly, when we run this on our production server it takes 60 to 90 seconds to complete, but on our test server it completes in 2 seconds.
    The problem becomes worse with more complex procedures, such as our statement run that can exceed seven hours on the production server but completes in 45 minutes on our test server.

    Our test server is an exact restore of our production server.

    Contract and Charge are both indexed on ContractID, and each has about 500,000 rows.

    The execution plans on both servers are identical, the longest step being a hash-match/Inner-join between the two tables (probably a result of the cardinality of ContractID in the Charge table).

    Our production server is more powerful than our test server, with quad-processors and 3.5 gigs of RAM, while our test server has only 2 gigs. Both servers are set to dynamically configure memory, and I haven't seen our production server's memory use top 1.8 gigs.

    Running trace through query analyzer shows one discrepancy: out test server performs only 24 reads to execute the query, while our productions server requires 220.

    The problem occurs on our production server regardless of whether other users are using the system.

    I have a couple questions:

    1) Any ideas on why the query would run so much slower on our production server?

    2) Would running SQL Profiler give me any additional information, and if so what settings should I trace (I've only used profiler once or twice). Also, is there anything I should be concerned about in running profiler on a production system? I've heard that it can have some impact on performance.

    3) As part of the execution plan the optimizer performs a step called Bitmap/Bitmap Create prior to the hash. I can't find any documentation on this in Books Online or on Microsquashes website. Anybody know anything about it?

    Thanks!

    blindman

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Running trace through query analyzer shows one discrepancy: out test server performs only 24 reads to execute the query, while our productions server requires 220.
    ----------------------------------------------
    Check indexes for fragmentation.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I tried dropping and recreating the indexes, to no effect.

    I also just tried setting ContractID as the clustered index on the Charge table to see if that would change the execution plan, but it had no effect.

    I am suspecting that there might be performance or contention issues with the drive, though supposedly there are no other services running on the serve besides MS SQL.

    blindman

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by blindman
    I tried dropping and recreating the indexes, to no effect.

    I also just tried setting ContractID as the clustered index on the Charge table to see if that would change the execution plan, but it had no effect.

    I am suspecting that there might be performance or contention issues with the drive, though supposedly there are no other services running on the serve besides MS SQL.

    blindman
    Try to use performance monitor - very useful thing. Compare results for servers.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    uhhh...bounce the box?

    This is MS BTW

    Can you be down for a couple?

    What's in the Error Log?

    Did you do DBCC CHECKDB?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Oct 2003
    Location
    Ireland
    Posts
    54

    Re: Query performance.

    blocking ?

    Originally posted by blindman
    I am working at a client site with an accounting database, and we are dealing with a simple query:

    select count(*)
    from Contract
    inner join Charge on Contract.ContractID = Charge.ContractID

    Oddly, when we run this on our production server it takes 60 to 90 seconds to complete, but on our test server it completes in 2 seconds.
    The problem becomes worse with more complex procedures, such as our statement run that can exceed seven hours on the production server but completes in 45 minutes on our test server.

    Our test server is an exact restore of our production server.

    Contract and Charge are both indexed on ContractID, and each has about 500,000 rows.

    The execution plans on both servers are identical, the longest step being a hash-match/Inner-join between the two tables (probably a result of the cardinality of ContractID in the Charge table).

    Our production server is more powerful than our test server, with quad-processors and 3.5 gigs of RAM, while our test server has only 2 gigs. Both servers are set to dynamically configure memory, and I haven't seen our production server's memory use top 1.8 gigs.

    Running trace through query analyzer shows one discrepancy: out test server performs only 24 reads to execute the query, while our productions server requires 220.

    The problem occurs on our production server regardless of whether other users are using the system.

    I have a couple questions:

    1) Any ideas on why the query would run so much slower on our production server?

    2) Would running SQL Profiler give me any additional information, and if so what settings should I trace (I've only used profiler once or twice). Also, is there anything I should be concerned about in running profiler on a production system? I've heard that it can have some impact on performance.

    3) As part of the execution plan the optimizer performs a step called Bitmap/Bitmap Create prior to the hash. I can't find any documentation on this in Books Online or on Microsquashes website. Anybody know anything about it?

    Thanks!

    blindman

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Bouncing the server did speed up the processing the other day, but the performance quickly degraded again.

    Problem with buffer pages, perhaps?

    Nothing in the Error Log.

    DBCC checks done regularly, and databases are spittin' images of eachother.

    No blocks detected, and problem occurs regardless of whether other users are logged in.

    blindman

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How big are the tranny logs?

    How about the allocation to tempdb?

    Is it a high level of OLTP?

    But..
    The execution plans on both servers are identical, the longest step being a hash-match/Inner-join between the two tables (probably a result of the cardinality of ContractID in the Charge table).
    Should be an Index Scan...shouldn't it?

    And 45 minutes is a long time.....(7 hours is an eternity)...

    Can it be the network? Are you executing localy on dev and remotley to PROD (even that shouldn't matter)

    I'll keep thinking....

    You can try the brain trust at:

    http://www.sqlteam.com/Default.asp
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I know SQL Server is supposed to be doing this on its own every now and then, but what happens when you run

    update statistics table with fullscan

    on both tables? Kind of reaching here, because the test database is a restore of the other (and therefore identical). How does the hardware stack up?

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    How does the hardware stack up?


    ...And if I read the REST of the post... Disregard that last bit, Blindman.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •