Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    15

    Unanswered: What is sql_handle?

    Im trying to learn about the new views:

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/61b8ad6a-bf80-490c-92db-58dfdff22a24.htm

    but really not getting what the sql_handle is?
    Thanks in advance.
    ChrisR

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You will need to post more information. That is not a navigable link.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I will guess you mean sql_handle from the sysprocesses table? Then you should look up fn_get_sql. In 2005 that may be sys.dm_exec_sql_text.

  4. #4
    Join Date
    Oct 2003
    Posts
    15
    Quote Originally Posted by MCrowley
    I will guess you mean sql_handle from the sysprocesses table? Then you should look up fn_get_sql. In 2005 that may be sys.dm_exec_sql_text.

    Yes, I am referring too sys.dm_exec_sql_text.

    What does sql_handle do in that?
    Thanks in advance.
    ChrisR

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    sql_handle is a binary key value of sorts. sys.dm_exec_sql_text uses it to retrieve queries from the procedure cache. The key values show up in several views (sys.dm_exec_requests, sys.dm_exec_query_stats). Like so:

    http://msdn2.microsoft.com/en-us/library/ms181929.aspx

  6. #6
    Join Date
    Nov 2004
    Posts
    128
    Trying to run this example, the message is returned.

    SELECT s2.dbid,
    s1.sql_handle,
    (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,
    execution_count,
    plan_generation_num,
    last_execution_time,
    total_worker_time,
    last_worker_time,
    min_worker_time,
    max_worker_time,
    total_physical_reads,
    last_physical_reads,
    min_physical_reads,
    max_physical_reads,
    total_logical_writes,
    last_logical_writes,
    min_logical_writes,
    max_logical_writes
    FROM sys.dm_exec_query_stats AS s1
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
    WHERE s2.objectid is null
    ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;

    Msg 321, Level 15, State 1, Line 23
    "sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

    So what is sql_handle? What value should go in it's place? How would you make this query work?

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Quote Originally Posted by Error Message
    ensure that your database compatibility mode is set to 90.
    Have you ensured the compatibility level?

  8. #8
    Join Date
    Nov 2004
    Posts
    128
    Doh!!! Way too easy.

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •