none
Transact SQL RRS feed

  • Question

  • What is mean by Hotspot in SQL code?

    How to Find hotspot in SQL code ?(Stored Procedure)

    Wednesday, September 11, 2019 6:37 PM

All replies

  • Hi Steve,

    please check the link where you can the hotspot tables:

    http://www.dbafire.com/2014/05/26/identify-top-ten-table-hotspots-sql-server/

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Wednesday, September 11, 2019 6:47 PM
  • Hi Steve,

    This will give you the top 50 most used procs and the statements in the stored procedures with additional info like execution_count,last_execution_time.

    Please check below link:

    https://stackoverflow.com/questions/3505752/quickest-way-to-identify-most-used-stored-procedure-variation-in-sql-server-2005

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Wednesday, September 11, 2019 6:50 PM
  • Hi Steve,

    Hotspot in sql:

    Hot spot occurs when many queries try to read or write data in the same area at the same time.

    Please check this link:

    https://stackoverflow.com/questions/5302540/avoid-creating-a-clustered-index-based-on-an-incrementing-key

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Wednesday, September 11, 2019 6:53 PM
  • I need to check hot spot individually to each stored procedure not for all SP at same time.

    Do we have any way to check it ?

    Wednesday, September 11, 2019 7:27 PM
  • you can use this:

    select * from 
    (
    SELECT * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,
      execution_count,s2.objectid,
        (SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
          ( (CASE WHEN statement_end_offset = -1
      THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
    ELSE statement_end_offset END)- statement_start_offset) / 2+1)) AS sql_statement,
           last_execution_time
    FROM sys.dm_exec_query_stats AS s1
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
    WHERE sql_statement NOT like 'SELECT * FROM(SELECT coalesce(object_name(s2.objectid)%'
    and OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1
    and exists (SELECT 1 FROM sys.procedures s
    WHERE s.is_ms_shipped = 0
    and s.name = x.ProcName ) 
    ) t
    where t.ProcName='your_sp_name'	--GIVE YOUR SP_NAME HERE
    ORDER BY t.execution_count DESC

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Wednesday, September 11, 2019 7:34 PM
  • Hi Steve,

     

    (1) What is mean by Hotspot in SQL code?

    To put it easy, hotspot refers to data for the last page with a whole lot of requests such as insert, select ,update…etc.

     

    (2) How to Find hotspot in SQL code ?(Stored Procedure)

    You can refer to Arulmouzhi' code, it's great.  And also, you can find all the procedures in the list:

    Select [NAME] from sysobjects where type = 'P' and category = 0

    When I was searching online, I found a great blog which may give you some ideas.

    How to identify and resolve Hot latches in SQL Server 

    Hope it could help.

    If you have further questions, feel free to let us know.

    Sabrina 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 12, 2019 3:18 AM