Post reply

System Performance - High CPU utilization--> SQLSRV.exe

  • April 8, 2010 at 3:22 am

    #1148105

    dots67 (10/16/2007)

    >there are a lot of full scans going on

    Then why are you surprised that CPU is so high?

    There is no silver bullet or settings like

    SET CPU_UTILISATION LOW

    SET SQL_SERVER FAST

    🙂

    You need to optimize your queries.

    Good one!!! 🙂

    --------------------------------

  • April 11, 2010 at 10:29 am

    #1149726

    Everybody agrees that clustered index is needed (and usage of heap tables in amateurish).

    1) We know that clustered index (vs. heap) slows down the insert.

    Did anybody made serious measurements- what is this slowdown? 5% 10%

    2) We know that multy-column (composite) clustered index makes insert slow. By how much? 20%? 30%?

    3) We know that clustered index against long table (e.g. long varchar) makes inserts slower. How much? (ratio between size of the column vs. speed of insert)? Did anybody measured this?

    4) There are two school of thought: 4.1) one says: never make Clustered index on identity- so you will avoid "hot-spot" at the end of the table. Make a clustered index so to "uniformly distribute inserts and possible updates along the body of the table". 4.2) Other school says: "try not to build clustered index on UserID (plus timestamp or identity) etc- there will be subsequent defragmentation of the index with time". Use identity or timestamp as foundation of clustered index- and you are guaranteed against deterioration with time.

    Did any body actually MEASURED it? (behavior of "spread-out" clustered index vs."increasing"/going along the body of the table clust.index? What IS the difference?

    Both approaches above have advantages and drawbacks; approach 4.2 will protect us against full table scan - as long as we use column,that the clustered index starts with. As we know, clustered index is ALWAYS used - there is no expensive bookmark operation involved.

    Both approaches were listed in this thread, but somebody must attempt to quantify the speed.

    In my practice, I never had any problems with INSERT speed (and insert was always an order of magnitude faster then any update on the table).

    In activity log/error log tables I tend to avoid ANY updates by design.

    This makes it not only fast, but also robust (avoiding any common point of failure between processes).

    thanks for interesting discussion,

    Alexei Akimov


    Alexei

Viewing 2 posts - 31 through 31 (of 31 total)

You must be logged in to reply to this topic. Login to reply