none
How can I identify Hot Spot bottlenecks? RRS feed

Answers

  • How can I identify Hot Spot bottlenecks?

    Basically, if you've upgraded from SQL 6.0 to anything newer, you should be fine!

    SQL 6.5 had some kind of hot spot optimization, and since SQL 7.0, generally speaking row locking eliminated the issue.  If anything, what caused hot spots in SQL 6.0 is now considered a virtue , the same page gets inserts instead of needing to bring in different pages to avoid blocking.

    But I suppose if you find a lot of blocked processes queuing up to a single page lock, you would somehow have recreated a good old hot spot problem even on newer SQL Servers.  But if that used to happen with 10 inserts/second in SQL 6.0 running on a 60mhz Pentium server with 64mb of RAM, it would probably take over 1,000 inserts/second on any modern multicore server before you had any serious hot spot issues to deal with.

    Josh

     

    • Marked as answer by Mr Shaw Wednesday, March 2, 2011 9:19 AM
    Wednesday, March 2, 2011 12:17 AM

All replies

  • How can I identify Hot Spot bottlenecks?

    Basically, if you've upgraded from SQL 6.0 to anything newer, you should be fine!

    SQL 6.5 had some kind of hot spot optimization, and since SQL 7.0, generally speaking row locking eliminated the issue.  If anything, what caused hot spots in SQL 6.0 is now considered a virtue , the same page gets inserts instead of needing to bring in different pages to avoid blocking.

    But I suppose if you find a lot of blocked processes queuing up to a single page lock, you would somehow have recreated a good old hot spot problem even on newer SQL Servers.  But if that used to happen with 10 inserts/second in SQL 6.0 running on a 60mhz Pentium server with 64mb of RAM, it would probably take over 1,000 inserts/second on any modern multicore server before you had any serious hot spot issues to deal with.

    Josh

     

    • Marked as answer by Mr Shaw Wednesday, March 2, 2011 9:19 AM
    Wednesday, March 2, 2011 12:17 AM
  • Hot spots typically show up with the multiple requests waiting on the same resource with a PAGELATCH_XX waittype.

    Example: http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/monotonically-increasing-clustered-index-keys-can-cause-latch-contention.aspx


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://www.troubleshootingsql.com
    Twitter: @banerjeeamit
    MSDN Blog: http://blogs.msdn.com/sqlserverfaq
    Wednesday, March 2, 2011 6:19 PM