SQL Server Performance

SQL-Server performance

Discussion in 'Performance Tuning for DBAs' started by brimba, Dec 8, 2004.

  1. brimba New Member

    Hello!<br /><br />We have a server with 3 2.8GHz xeon cpu<img src='/community/emoticons/emotion-7.gif' alt=':s' />, 3GB ram.<br /><br />The server is running a web application and recently we made a change, but the results is not quite what I expected, can someone help me figure this out?<br /><br />We changed one table, so that we made the clustered index (also primary key) on that table only have one column instead of two. The change gave us better results in all of our queries, since all of our queries were just asking on one column instead of two.<br /><br />We run the performance monitor with a frequence of 900 seconds.<br /><br />Before the change our max on CPU were around 70%, and avarage was 42%. <br />After the change the MAX raised to 93% and avarage was about the same (43%).<br /><br />When I run a trace I clearly see the improvement of speed, since the queries that earlier were taking up time is now running very quickly.<br /><br />Before the change we had about 170 transaction per second, and after the change it was raised to 197 TPS.<br /><br />The biggest change is the user connection value. Earlier we had max 691 user connections, but after its 397.<br /><br />We still have the same amount of queries, and the same amount of users connected.<br /><br />I can see that the user connection probably is lower because of that the web pages loads more quickly and does not have to stay connected that long anymore.<br /><br />But why is the MAX CPU higher now? Earlier when the queries took much longer, shouldnt it have been working on the queries then? Or can it be that it was alot of waiting and other things that it had to handle before?<br /><br />The result for the end users is what we expected. Its alot faster, so that is good.<br /><br />This is a good example of that you should not only check the CPU load when trying to fix performance issues.
  2. Luis Martin Moderator

    Besides mail I sent to you, what in going on with Disk and memory?
    What Monitor Profiler show about that?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

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

  3. Twan New Member

    a query's duration is normally not just bound by the cpu time, often it is as a result of additional non-cpu work such as reading/writing stuff to disk/cache.<br /><br />So yes a quick query may be barder on a CPU, ideally you want the CPU to peak as high as possible as long as it can still cope well with the throughput. There is no point having a CPU peak at say 30%, if it is then you would have over spec'd the box quite severely <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Cheers<br />Twan
  4. brimba New Member

    LuisMartin, what counters are you interested of? Our database is rather small (1.8 GB), so we are lucky to have it all in the memory.

    Twan, yes of course, just courious why the cpu wasnt maxed out before, but I think I understand that now. Thanks!
  5. Luis Martin Moderator

    Performance Monitor:

    Physical disk: Avg. Disk Queue Lenght.
    Memory: Pages/sec.
    SQLServer Buffer: Buffer Cache Hit Ratio, Page life Expectancy.
    SQLServer Memory Manager: Target Server Memory, Total Server Memory.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

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

  6. satya Moderator

    How often you update the TABLE/INDEX statistics?
    How often you schedule the DBREINDEX jobs, if any?
    How about SQL Memory configuration, dynamic or fixed?
    How about SQL resource usage, any other applications sharing the tasks?

    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.
  7. brimba New Member

    The statistics are on "auto create/update".
    I recreate my index every night.
    Memory is dynamic (0 - 3071)
    The server is "stand alone" for and works for this database only.
  8. satya Moderator

    Collect the PERFMON stats as mentioned by Luis above for better assessment, I believe this could be issue on Hard disks (if you're using RAID configuration).

    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.
  9. Luis Martin Moderator

    If you are rebuilding index every night, statistics will update also, so to get a litle more performance turn auto update off.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

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

  10. brimba New Member

    LuisMartin: We run our maintenance plan with these parameters: "-RebldIdx 10 -RmUnusedSpace 50 10"

    I have tried to read the BOL to find that rebuilding indexes also update my statistics, but I cannot find that, however I did find a parameter called "UpdOptiStats", which updates the statistics.
  11. Luis Martin Moderator

    Rebldidx 10 means: Reorganize data and index pages and change free space per page to 10%.
    And that is for all database. Because you database size, I think is OK.
    When you have a big database, is better to reindex when is necesary (image 50Gbyte database, and reindex all database, may be will take more than 12 hours) i.e when fragmentation is below 80%. You can find some store procedures in this forum to reindex only in those cases.
    Anyway, rebuild mean also update statistics.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

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

  12. satya Moderator

    True while rebuilding indexes it will update the statistics on sysindexes which will be used for optimum performance.

    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.
  13. brimba New Member

    Where can I read that my statistics are updated when rebuilding my indexes?

    Not that I doubt you, just that I want to understand how this works since I cannot find anything about that in BOL.
  14. brimba New Member

    Hi!

    Here is my statistics. These results are over 24 hours with an update interval of 10 minute.


    SQL, pages/sec:
    31 min
    464 avg
    723 max

    Disk queue:
    0,875 min
    1,55 avg
    3,922 max

    Buffer Cache:
    99,776 min
    99,862 avg
    99,891 max

    Page Life exp:
    1911 min
    18243 avg
    33950 max


  15. Luis Martin Moderator

    There is no documentation, but after rebuild any index run:

    STATS_DATE
    Returns the date that the statistics for the specified index were last updated.

    Syntax
    STATS_DATE ( table_id , index_id )

    and you can see that.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

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

  16. satya Moderator

    I cannot reference the document as I read the same on one of the Technet resources, I will definetly refer the link if I get a chance to find it.

    To divide the topic, UPDATE STATISTICS is an intermittent solution to get optimum performance on the highly updated tables and REBUILD INDEXES is more independent on the INDex name(s) and as explained it will update the necessary information in SYSINDEXES table once the process is finished.
    DBCC DBREINDEX is an offline operation. While this operation is running, the underlying table is unavailable to users of the database.

    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.

Share This Page