SQL Server Performance

Database Performance after moving it from one sql server 2000 to another sql server 2000

Discussion in 'Getting Started' started by SQL Student, Mar 12, 2009.

  1. SQL Student New Member

    I have this crazy problem. I moved a database from one SQL server 2000 to another. The old one is a much slower server with a single processor. The second one is new fast and has 8 Processors. Generally the server perform better. Specially with my websites. However, one query in particular which is the most complex one is very slow.... 10 to 16 times slower. I can't figure why. I rebuild the indexs and both servers are running server 2003 enterprise edition.
    Rebuilding indexes improved the performance overall, but not that query. Slow server gets back results between 1-4 seconds... the fast new server gets back results in about 16-16.785 seconds. Any clue why? Apperantly my servers settings must be different or memory cash id different but I can't tell exactly the problem. Small queries with large results are much faster.... but complex queries with similar results are slow!!
  2. melvinlusk Member

    You may want to update the statistics on the affected tables, the queries could be using incorrect execution plans.
    Look in Books Online for other options with UPDATE STATISTICS.
  3. johnson_ef Member

    HI,You can do one more thing, try to analyze the Execution Plans of both the server for the same query. Try to identify the costing difference.Performance issue may happen due to hardware issues like driver mismatches while configuring controllers, this may cause IO. You can identify this adding performance counters in both the servers and compare it.-Johnson
  4. SQL Student New Member

    Ok. I have done the suggested ideas. It didn't really make a difference for the statistics. So I looked closely at the execution plan. I found out that changing the number of processors to use in the parallel execution plan mad a difference. When I sent it to 1, 2, 3, 4 it didn't make a difference. My number of seconds is 5. When I have it to use more than 4, like Max number of processers or another number, I see a drop. Especially when I go over 5. Why I don't understand it. If the server knows that this query uses under 1 second time, why does it make a difference of how much I set the # of Parallelism processors? why does it even care from going to .3 seconds to 16.5 seconds by using more processors? am I still optimized for using all my processors?
    The execution plan shows difference between when it uses Parallelism or not between 6 processors or under. Any good explanation?
  5. geebee2 New Member

    Is there a stored procedure involved?Stored procedures can sometimes be badly optimised, with the cure being to take a local copy of parameters.
  6. SQL Student New Member

    I never heard of a SP being a problem. But no, it is just a query. It is very optimized. It has the correct indexs, and it run very fast on a very slow machine. In fact, one of the eight processors in the new machine has more power than the old machine. Also, the old machine has a total of 1.5 GB of Ram, the new one has 8. Disk drive is faster.... just whole a lot better.
  7. geebee2 New Member

    Ok, sounds like it is mainly a problem with too much parallelism. Have a read herehttp://www.mauvais.com/publish/ZD-Parallel.htmPossibly you should also check whether you have disk fragmentation.I also wonder about the memory cache of the new machine.http://en.wikipedia.org/wiki/CPU_cacheMaybe you could post details of the old and new hardware.Possibly the large number of processors has some drawbacks.
  8. SQL Student New Member

    Ok. The first article you sent looks pretty good. This is probably the problem. But I have to wait until Monday to try it. The reason is b/c nothing can be done on the servers until Monday. I will let you know. I have not read the second one yet. What I have to do is to enable AWE on the sql server and then allow the Enable the Lock Pages in Memory Option on the operating system. I noticed that my server never runs over 2 GB which may be the problem. He has 8 GB. Parallelism appears to need more than 2 GB to run.... so To that I have to run the sql server under the sql server account on the domain. It is currently running under the SA account. I don't know what that will involve with it. What kind of issues I will run into once I do that. Any ideas? Is it a problem changing the account the sql server runs under?
    I do think it is too much parallelism and that is why when I dropped the number of max Proccessors to use it did ok. However, I still should be able to use Max parallelism and not affect anything. I can't reduce the performance of my SQL server b/c one query in one database is not correct! Don't you think so?
    Now this is what in the Article (http://www.mauvais.com/publish/ZD-Parallel.htm ) what made me thinking that is the problem... this section:
    The server must have sufficient memory available because executing a query in parallel requires significantly more memory than executing the same query serially. The higher the DOP, the more memory required. If the server doesn't have enough memory available, the Query Optimizer backs off the DOP-all the way down to 1 (serial execution)-until it finds the server has sufficient memory to run the query. You can see the additional memory requirements for yourself by examining the Granted Workspace Memory counter of the SQL Server:Memory Manager object in Performance Monitor.
  9. SQL Student New Member

    OK. I enabled AWE, and ran the sql server under an account that has Enable the Lock Pages in Memory Option (Windows) as this article talks about: http://msdn.microsoft.com/en-us/library/ms190730.aspx
    Sadly enough not results. I didn't not see any performance improvement when I switch back to Use Max Parallisim. I have to go to 4 or 5 Processors only.
    The old computer specs are:
    Intel(R) Pentium(R) III
    927 MHZ, 1.5 GB of Ram
    It was running server 2000 Standard
    and the page file Min = 2046 Max = 4096
    the new computer hardware are:
    Intel(R) Xeon(R0 CPU
    E5405 @2.00GHz
    2.00 GHz,7.99 RAM
    Physical Address Entension
    running windows server 2003 Enterprise edition SP2
    and the page file Min = 12279 Max = 12279. It is on the Log Partition. C partition has no pagging. F partition which has the databases has no pagging.
    Any Idea what in the world is going on?????? Why I have to use less processors to get as good performance as the old machine that running one.
  10. Adriaan New Member

    Do not change the server setting for maximum parallellism on account of a single query.
    On the problem query, add OPTION (MAXDOP 1) as the very last part of the query statement. This will force this particular query to be executed by a single processor.
    Without any details about the problem query and its underlying tables, there isn't much we can suggest otherwise.
  11. SQL Student New Member

    Adriaan... I think you got this idea confused. I don't know why you are saying do not... In fact it seems that I should set it to 1 unless in the query it will work better then we can ad the option to use more cpus... so MAXDOP on the server should always be one. Only if the query has showed better performance then it should set it to higer number in the query not in the server. Unless you explain to me why I should not change it... then it sounds like a person just saying something to sound smart.
  12. Adriaan New Member

    I disagree. From what I've read on the thread, there is only one query that has issues, and the rest all seems to benefit from the new server.
    So you only need to tackle the parallellism issue for that one query.
    If you change the server setting, it may negatively affect all the other processes.

Share This Page