SQL Server Performance

sql query slow on a quad processor

Discussion in 'Performance Tuning for Hardware Configurations' started by supporttja, Aug 25, 2004.

  1. supporttja New Member

    We have 3 SQL server



    - 2.4Ghz 1 g ram

    - dual xeon 2Ghz 4 g ram

    - quad xeon 3G hyper trade 8 g ram



    the faster machine for our query is 2.4 single processor and the slowest is the quad



    can you tell me why ????



    eg: we have 12 store proc.

    On the first : 10 secondes and 1 secondes everytime we run those SP after

    On the third : 14 secondes and 14 secondes everytime we run those SP after



    Thanks

  2. bradmcgehee New Member

    First, is your testing fair? In other words, are the databases exact? Are the SQL Server setting exact? Have you updated statistics on all the databases recently? Is the load the same on all the servers during testing?

    If everything is identical, are the execution plans the same, or different. If everything is identical, the plans should be identical. If they are different, then something is different among the servers, and by taking a close look at them, you may be able to see what differently is going on.

    Also, if the SP runs on a single thread, multiple CPUs won't help performance, and the faster CPU speed will win out. So, if the single CPU server has a faster CPU than the other servers, and the SP only generates a single thread, then it should run faster than the multiple CPU servers, assuming everything else is the same.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  3. joechang New Member

    i would first disable HT, run the tests again, then disable parallel processing and rerun the test
  4. satya Moderator


    Joe is right on shot, correct the problem rather than adding up resources.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. derrickleggett New Member

    <RANT>
    SET MAXDOP = 1 on that particular query. Disabling HT across the board doesn't fix the problem. It fixes the symptom. Can you post the procedure you're using to test this?
    </RANT>


    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  6. supporttja New Member

    Hi,

    i disabled the HT, good for that our performance is better

    how can i disable parallel processing ??

    and SET MAXDOP = 1 no difference between the other.


    thanks
  7. Luis Martin Moderator

    Enterprise Manager, Database properties, processor, set 1 processor to parallel processing.<br /><br />BTW<img src='/community/emoticons/emotion-2.gif' alt=':D' />errick and Joe, I see severals post about HT and both have differents opinions. Please would you development each idea, because I don't know about it.<br /><br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br />
  8. derrickleggett New Member

    What OS, SQL Server version, OS Service Pack, and SQL Server Service Packs are you running supporttja?


    Luis, the reason I'm so adament about this is because we spent weeks studying it, reading white papers, and testing it on our systems. Enabling hyper-threading caused us some problems as well. Having the right service packs and updates on everything fixed some of them. Tuning other queries and batch processes fixed others. By tuning out queries that had problems with it and overall tuning/monitoring of the system, we were able to see a substantial increase in the overall performance of our database system with hyperthreading. Now, it's not even a thought to turn it off.

    There are some processes that might run slower with hyperthreading enabled. Overall though, you will see an increase in the performance. SQL Server is one application that really utilizes well a multi-processor environment. It will try to push things through high and wide utilizing both memory and processors to achieve the best overall system performance. Note that some single processes might suffer. That's a fair trade for the overall increase in performance though.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  9. Luis Martin Moderator

    Thanks Derrick.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  10. pfgm New Member

    Derrick, Have you tested the Full-Text Search with HT ? I'm running an SQL server that is dedicated to a Full-Text Search activities It has Windows 2000 Advanced Server SP3 , SQL 2000 Enterprise Server SP3.
    Do you think it might improve the performance if I upgrade it to Windows SP4 ?




    Saludos, Pablo
  11. joechang New Member

    HT & parallel execution plans together are seriously bad news, otherwise, i am not convinced HT leads to meaningful performance gains, the System CPU decreases, but thats because its being averaged across all logical CPUs, there is no clear proof the system throughput increases with HT across a broad range of queries.
  12. Luis Martin Moderator

    Thanks Joe.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  13. derrickleggett New Member

    I think a lot of things might improve with an upgrade to Windows SP4 Pablo. You really shouldn't be running SQL Server on a server that doesn't have the latest OS Service Pack. BTW, for the people who have had trouble with HTT, have you tried increasing your max worker threads? You might want to give that a try and see the results.

    --there is no clear proof the system throughput increases with HT across a broad range of queries.

    Are you kidding me???? Proof, proof, proof. Please????

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  14. pfgm New Member

    Derrick,

    I know it's allways a good thing to be updated, but these are mission critical servers, and they're running just fine, I you could point me to a fix or feature in SP4 that enhances the performance it would be great.



    Saludos, Pablo
  15. derrickleggett New Member

    Enhances the performance????? Applying service packs has absolutely nothing to do with fixing performance. It's about securing your server and being a responsible administrator. Please read the release notes and apply the patch.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  16. Janus Morthorst New Member

    The Question: Parallelism Must Be Set To Automatic to get full advantage of SQL Server 2000.

    SQL Server 2000 cannot use Parallel Query Processing, this is an unofficial bug, not confirmed and by doing this the queries (transactions) proceeds without any delay or errors will not occur.

    So the recommendation is to run the Transact SQL script below or use the Enterprise Manager.

    sp_configure 'show advanced options', 1
    GO
    RECONFIGURE
    GO
    sp_configure 'max degree of parallelism', 1
    GO
    RECONFIGURE
    GO


    Please post if this solved your problem?


    BooksOnline:
    Parallel Query Processing
    max degree of parallelism Option
    Setting Configuration Options
    How to configure the cost threshold for parallelism

    Best Regards
    - Member of The Professional Association for SQL Server, SQLPASS Denmark -
    - Stability instead of incremental improvements -
  17. cosaco New Member

    what disk and controller (memory, etc) has each server ?

    since is imposible to be slowest in your quad machine, it appears to be a io problem

    you have run it one or two or three times, to see if the cache is loaded ?

  18. gkrishn New Member

    Can any one tel me wht is QUAD processor???

    Rajiv
    SQL-DBA
  19. thomas New Member

    It means a server with four processors. Quad = 4, like if your wife is unfortunate enough to have quadruplets, it means you've got 4 babies.

    Tom Pullen
    DBA, Oxfam GB
  20. aawara New Member

    The best way to do your test is by clearing your cache (DBCC FREEPROCCACHE) and knowing syscacheobjects very well. YOu have to have intimate details about your cache to do ay such testing. I would highly recommend this article but pick what you need:-
    http://www.sql-server-performance.com/rd_data_cache.asp. Thanks
  21. aawara New Member

    And pay close attention to compiled vs iteraions of execution plans in syscache.... table
  22. derrickleggett New Member

    quote:Originally posted by Janus Morthorst

    The Question: Parallelism Must Be Set To Automatic to get full advantage of SQL Server 2000.

    SQL Server 2000 cannot use Parallel Query Processing, this is an unofficial bug, not confirmed and by doing this the queries (transactions) proceeds without any delay or errors will not occur.

    So the recommendation is to run the Transact SQL script below or use the Enterprise Manager.

    sp_configure 'show advanced options', 1
    GO
    RECONFIGURE
    GO
    sp_configure 'max degree of parallelism', 1
    GO
    RECONFIGURE
    GO


    Please post if this solved your problem?


    BooksOnline:
    Parallel Query Processing
    max degree of parallelism Option
    Setting Configuration Options
    How to configure the cost threshold for parallelism

    Best Regards
    - Member of The Professional Association for SQL Server, SQLPASS Denmark -
    - Stability instead of incremental improvements -

    I talked to Gert Drapers at PASS and specifically asked him about this. He said while Windows 2003 was more efficient handling parallel processing and hyperthreading, there were still great benefits to using it with Windows 2000. You just have to tune for it.

    So.......this is a bunch of rubbish. Facts please. Don't tell people to set parallelism to 1 on a multi-processor server. That's stupid; and anyone who has used a multi-processor server knows this. To sound like a parent....shame on you for posting things like this!!!!

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  23. Janus Morthorst New Member

    quote:Originally posted by supporttja

    Hi,

    i disabled the HT, good for that our performance is better

    how can i disable parallel processing ??

    and SET MAXDOP = 1 no difference between the other.


    thanks


    Have you tried disabling parallel processing, and what was your result?

    Best Regards
    - Member of The Professional Association for SQL Server, SQLPASS Denmark -
    - Stability instead of incremental improvements -
  24. Janus Morthorst New Member

    quote:Originally posted by derrickleggett


    quote:Originally posted by Janus Morthorst

    The Question: Parallelism Must Be Set To Automatic to get full advantage of SQL Server 2000.

    SQL Server 2000 cannot use Parallel Query Processing, this is an unofficial bug, not confirmed and by doing this the queries (transactions) proceeds without any delay or errors will not occur.

    So the recommendation is to run the Transact SQL script below or use the Enterprise Manager.

    sp_configure 'show advanced options', 1
    GO
    RECONFIGURE
    GO
    sp_configure 'max degree of parallelism', 1
    GO
    RECONFIGURE
    GO


    Please post if this solved your problem?


    BooksOnline:
    Parallel Query Processing
    max degree of parallelism Option
    Setting Configuration Options
    How to configure the cost threshold for parallelism

    Best Regards
    - Member of The Professional Association for SQL Server, SQLPASS Denmark -
    - Stability instead of incremental improvements -

    I talked to Gert Drapers at PASS and specifically asked him about this. He said while Windows 2003 was more efficient handling parallel processing and hyperthreading, there were still great benefits to using it with Windows 2000. You just have to tune for it.

    So.......this is a bunch of rubbish. Facts please. Don't tell people to set parallelism to 1 on a multi-processor server. That's stupid; and anyone who has used a multi-processor server knows this. To sound like a parent....shame on you for posting things like this!!!!

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.

    First of all I can see you have been talking with Gert Drapers and I know he will be informed of this issue by several important SQL persons. I was aware that this subject could cause reflections, but your way handle it I can't approve, that's why I haven't got further comment's. Ask Gert Drapers again when he have been in Denmark.

    Best Regards
    - Member of The Professional Association for SQL Server, SQLPASS Denmark -
    - Stability instead of incremental improvements -
  25. thomas New Member

    It should be added that parallelism is not without problems. It can cause self-deadlocking. Microsoft do not guarantee that it will work all the time. For some people, it is more cost-effective to switch it off server-wide, and do without the potential benefits of it, than to go through every single piece of code that runs on their server and check that it works correctly under parallel execution.

    Tom Pullen
    DBA, Oxfam GB
  26. aawara New Member

    I agree with Tom 100%. Its all about testing, testing and testing. I wish there was a simple answer to it?
  27. Janus Morthorst New Member

    It can use parallelism, but not always and you have to test a lot for finding the best way for your transactions to run without any delay if you do not use serial processing.

    The way I see the "Bug":

    After reading/collecting and by knowledge of cases I have to say that the SQL Server have a problem:
    The problem is that the SQL Server 2000 mainly uses serial processing and when the load increases it uses parallelism and this can cause several problems, cause it seems like the SQL Server have problems finding out which threads to process unless you realy have a powerfull machine, which can handle kind of "concurrency batchjobs", and you know by lots of analyzing that a given process can make use of the parallelism functionality it could be an advantage.

    I say that the SQL Server 2000 have problems finding out when to use "serially" and "parallelly" processing and even the "cost threshold for parallelism", fails.

    Microsoft tells in Books online the following about using "serially" and "parallelly" processing.
    "
    What is the number of concurrent users active on the SQL Server
    installation at this moment?
    SQL Server monitors CPU usage and adjusts the degree of parallelism at
    the query startup time. Lower degrees of parallelism are chosen if CPU
    usage is high.

    Is there sufficient memory available for parallel query execution?
    Each query requires a certain amount of memory to execute. Executing a
    parallel query requires more memory than a nonparallel query. The
    amount of memory required for executing a parallel query increases
    with the degree of parallelism. If the memory requirement of the
    parallel plan for a given degree of parallelism cannot be satisfied,
    SQL Server decreases the degree of parallelism automatically or
    completely abandons the parallel plan for the query in the given
    workload context and executes the serial plan.

    What is the type of query executed?
    Queries heavily consuming CPU cycles are the best candidates for a
    parallel query. For example, joins of large tables, substantial
    aggregations, and sorting of large result sets are good candidates.
    Simple queries, often found in transaction processing applications,
    find the additional coordination required to execute a query in
    parallel outweigh the potential performance boost. To distinguish
    between queries that benefit from parallelism and those that do not
    benefit, SQL Server compares the estimated cost of executing the query
    with the cost threshold for parallelism value. Although not
    recommended, users can change the default value of 5 using sp_configure.

    source: parallel queries (books online)


    Anyway:
    I should have written: "I have noticed that it could cause problems using Parallelism", but I wanted to see people's reaction and I was hoping on a fair discussion.

    I know that this subject soon will be discussed by several important SQL DBA in Denmark, including Gert Drapers which works for Mark Souza in SQL Development Team. I'll promise to make a followup if anyone are interested?

    Best Regards
    - Member of The Professional Association for SQL Server, SQLPASS Denmark -
    - Stability instead of incremental improvements -
  28. aawara New Member

    Its good to be logical. However, I have found that there are always some mysteries hidden in this wonderfull world of RDBMS. In case of sql, its double folded as OS is closly intergrated with the Relational engine. So its best to leave this a mystry. I have seen issues with parallel esp when it is related to HT when the OS could not diffrentiate with logical/shadow CPU. So again, it all boils down to trial and error(unless you are a group of people who knows everything about the wonderfull world of computing). I wish it was easy to explain these mysterious myths.
  29. Janus Morthorst New Member

    This article gives a clearly notice of the "Parallelism" problem.

    http://www.winnetmag.com/SQLServer/Article/ArticleID/42675/42675.html:

    When sharing TPC-C full disclosure information with my customers, I refer most to the configuration setting for max degree of parallelism (MAXDOP). Many customers are surprised that almost every published Microsoft TPC-C score has the MAXDOP set equal to 1. This setting means that SQL Server won't use a parallel execution plan for any query. You might ask, "Aren't parallel queries faster than a serial counterpart for an execution plan?" The answer to that question, of course, is, "It depends." The TPC-C benchmark measures performance for an online transaction processing (OLTP) workload, and most OLTP workloads don't benefit from parallel queries. For example, if a particular expensive parallel plan decides to chew up all eight processors in the middle of a peak transaction-processing time, your overall throughput can dramatically drop. I usually recommend that my customers set the MAXDOP value equal to 1 (disabling parallelism) for most OLTP workloads. I recommend you do the same unless you've performed serious in-depth testing to prove that keeping parallelism enabled is the right choice for your environment. Even then, your testing becomes obsolete and meaningless if you introduce new queries, which can change your well-thought-out plans. It's better to disable parallelism for OLTP workloads.

    I also speaked with Kimberly Tripp from SQLskills, she told me it is commen to use MAXDOP=1, which would be the same as using the TSQL I suggested in the first place:

    "
    sp_configure 'show advanced options', 1
    GO
    RECONFIGURE
    GO
    sp_configure 'max degree of parallelism', 1
    GO
    RECONFIGURE
    GO
    "

    Derrick Leggett: you are more than welcome to ask Gert Drapers again if you still mean that I am stupid and should be ashamed...

    Best Regards
    - Member of The Professional Association for SQL Server, SQLPASS Denmark -
    - Stability instead of incremental improvements -

Share This Page