locked
different cpu read write RRS feed

  • Question

  •  

    I have a query that runs for 10 sec on one database( A) and 5 min on another database(B) even though two database have identical scheam, tables, index and statistics..

     

     

    I ran a profiler and got the below information

                        CPU    READ   Write

    Database A: 92051   711956    8774

    Database B: 91812   7621589   315822

     

     

    A query runs on database has a significant larger read and write.. I don;t understand why this is happening? even though these two database have the same structure?? it has the same execution plan as well..

     

     

    How can i solve this issue?

    Tuesday, April 15, 2008 2:11 AM

All replies

  • You can start by rebuilding your indexes and updating the statistics with full scan on Database B.  What is different about the hardware and system configurations between the two database servers or are they on the same server?  Do the databases contain identical amounts of data, and are they the same size? 

     

    If Database B is on a server with less memory, then you could be seeing the results of cache flushing as SQL has to read data into memory and dump it back out to get more data.  If one server is 64 bit and the other is 32 bit, you could see more work being done on the 32 bit server due to the hardware differences.  If the disk configuration or Raid level is different that could contribute some.  If there is physical fragmentation of the hard disk this could drive up read/write IO.  If there is more data in one database than the other, it will definately play into how much work sql has to do even under identical execution plans. 

     

    There is a lot that can play into this.  Can you provide answers to the above questions so we can help a bit more?  Is one server SQL 2000 versus SQL 2005, or are these databases on the exact same server?  You can also try to flush your cache to make sure that you are getting fair comparison with

     

    Code Snippet

    DBCC FREEPROCCACHE

    DBCC DROPCLEANBUFFERS

     

     

    This will unload the buffer cache for data and procedure cache for execution plans.

    Tuesday, April 15, 2008 2:29 AM
  •  

    thanks for the prompt response.

     

    Both databases are on the same server... and they are the same size.

    I've tried rebuild index and statistics on database B... but still getting hte same response time..

     

    I am not sure why there are big difference in terms of query response time , read/write IO between two databases.

    Tuesday, April 15, 2008 2:57 AM
  • Since they are both on the same server, what you are potentially hitting is a caching issue as I mentioned previously.  Try flushing both caches out before running your tests with the DBCC statements above.  Run those in DatabaseA, then run your test.  When it completes, log your results.  Then Run the test on DatabaseA again without running the DBCC Statements two more times, logging the results again.

     

    Then flush the caches again with the DBCC Statements, and run your test on DatabaseB.  Log the results, and run the test two more times, and log those results.  Then compare the 3 runs between the two databases.  If they are still this drastically different, let us know the results.

     

    Tuesday, April 15, 2008 3:03 AM
  • i did what you talk me to do

     

    Database A: takes 8 sec with and without DBCC

     

    Database B: it takes now 3 min instead of more than 5 min..

     

     

    Tuesday, April 15, 2008 3:30 AM
  • strange things is i rebuild index and update statistic on database B.. after that the query runs more slow..

    Tuesday, April 15, 2008 3:34 AM
  • Look at the options set on both databases.  Make sure that they are set the same.  Auto Shrink should be off, so should Auto-Close.

     

    Tuesday, April 15, 2008 3:37 AM
  • I don;t understand why database B has so much bigger read/write even though it uses the same execution plan and indexes?

     

    Tuesday, April 15, 2008 3:40 AM
  • I have asked the Answerers and Moderators to look at this and offer up some ideas of where to go from here.  Perhaps they will have something I have missed here.
    Tuesday, April 15, 2008 3:51 AM
  • Are both the databases sitting on the same drives? Have you checked the physical disk fragmentation? How about the compatibility of the databases?

     

    Tuesday, April 15, 2008 6:00 AM
  • Are the databases in the same SQL Server instance? Or are they on two different instances on the same physical hardware?

    Joe

    Tuesday, April 15, 2008 1:01 PM
  •  Joe Webb wrote:
    Are the databases in the same SQL Server instance? Or are they on two different instances on the same physical hardware?

    Joe

    I agree with Joe here.  We need to know whether the database are in the same instance or seperate instances.  There could be differing server options, e.g. The second instance could have lesser allocated memory.

    Tuesday, April 15, 2008 1:20 PM
  • yes , they are in the same instance and same drive ( same RAID controller).it;s 32bit machine no manual memory setting , so it use 2 GB memory for SQL server...

     

    only difference I found so far is longer query database has more free spaces  than quicker query.. does this mean longer query database has more page break?..

     

    they have the same database options.. same sieze of data and structures.. only thng I did was rebuild the whole index and statistic on longer query database,

    Tuesday, April 15, 2008 2:49 PM
  • Is DatabaseB, a restored backup of DatabaseA?

     

    Tuesday, April 15, 2008 2:51 PM
  • yes.. and after that there is a ETL job that load data into both database A and B.
    Wednesday, April 16, 2008 5:28 AM