SQL Server Performance

performance in huge database is faster than in Empty Database

Discussion in 'T-SQL Performance Tuning for Developers' started by shireef.adel, Jul 22, 2008.

  1. shireef.adel New Member

    After a period our database was in production it slowed down , We make a lot of tunning.
    Now we have a testing server we put a copy of the production database (full one) , the perfomance is better , BUT

    when we use a new empty database on the same server the performance slower than the huge one!!!![:^)]
    we have made some reaserch and experiments like the follwoing:
    1- delete all indexes from the empty database.
    2- make the two databases on the same physical disk
    but nothing we have.
    plz any ideas?
  2. martins New Member

    The problem could be outdated statistics, but I would also check the execution plans of the long-running queries to see if they are different.
  3. shireef.adel New Member

    Thank you for your response.
    The execution plans for the same queries on both the large and small DB are the same. Is this an advantage or not?
    Then, how I know if the statistics are updated or not? If not, How I update them?
    I used SQL profiler to see the duration of the queries. Many queries on the small DB take 15 milliseconds. where the same queries on the large DB take 0 millisecond.
    Why?
  4. martins New Member

    Well, if the execution plans are the same then the same indexes etc will be used which means we are comparing apples with apples...and that is good.
    Have a look at "update statistics" in Books Online. You will find all the info you need to see when it was last updated, as well as how to update statistics.
    The difference in performance can be because of many factors...hardware and software related. And it isn't easy to just identify the reason without know all the facts about the environments etc.
    I suggest you look at the statistics first...and if that does not help please give more details as to the hardware and software configurations of both servers so we can try and help.
  5. shireef.adel New Member

    Note that the two databases have the same schema and both are on the same server but one has large data and the other is almost empty.
    empty one is slower ,
    I realy do now "Update Statitics" on the slower database "small one" but the performence is still the same.
  6. SQL2000DBA New Member

    Hi,
    What do you mean by empty database?.
    Try rebuilding all indexes and see the performance. If performance till remain the same then take one long running query and investigate the issue.
  7. shireef.adel New Member

    I mean the critical tables whitch have the most importance of our maesure contains small amount of data, I ve already tested with and without indexes
  8. satya Moderator

    When was the last time REBUILD or REORGANIZE indexes performed on these 2 databases?
  9. shireef.adel New Member

    from 3 days ago I dropped all the indexes from the empty database
    but the huge database (faster) I didnot change it.
  10. shireef.adel New Member

    is there a differnce between using Update Statistics Command on all the database and updating state for each of the critcal tables separetly
  11. martins New Member

    Updating statistics on the database will in effect update statistics for all tables/indexes within that database. So yes, there is a difference.
    It is good practise to update statistics on all tables/indexes periodically.
  12. satya Moderator

    Size does matter and in this case statistics for both the queries should not be same, you should run UPDATE STATISTICS on the tables that are involved in this query in order to optimize the query.
    BTW, if that is the empty database how many rows are you expecting to return?

Share This Page