how to get the entire sql statement while sql statment is over 4000 characters

  • I use the following sql script to capture which sql statement taking more resource, but if the execution sql statement is long (over 4000 characters),it can get the entire sql statement. how to get the entire sql statement while the sql statment is over 4000 characters ,thanks

    SELECT TOP 100

    total_elapsed_time/1000

    ,total_worker_time/1000

    ,total_physical_reads

    ,total_logical_reads/execution_count

    ,total_logical_reads

    ,total_logical_writes

    ,execution_count

    ,creation_time

    ,last_execution_time

    ,SUBSTRING( st.text, (qs.statement_start_offset/2) + 1,

    (

    (CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2

    ) + 1

    ) N'SQL Statement Executed'

    ,qp.query_plan

    FROM sys.dm_exec_query_stats AS qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

    WHERE

    SUBSTRING(

    st.text,

    (qs.statement_start_offset/2) + 1,

    (

    (CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2

    ) + 1

    ) not like '%fetch%' --- and st.dbid=db_ID()

    AND total_logical_reads/execution_count>99

    ORDER BY total_logical_reads/execution_count desc, execution_count DESC;

     

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • anyone help me with the said question, thanks so much!

  • when you get really big strings like that, and want to visualize them in SSMS, you can TRY_CONVERT to xml; then when you click it , it opens in a window that can support 2GB or some huge string size.

    I see this in results form sp_whosiactive, and other scripts like that all the time.,

    edit, the forum stripped my tags, trying to repost.

    try this:

    SELECT TOP 100
    total_elapsed_time/1000
    ,total_worker_time/1000
    ,total_physical_reads
    ,total_logical_reads/execution_count
    ,total_logical_reads
    ,total_logical_writes
    ,execution_count
    ,creation_time
    ,last_execution_time
    ,

    TRY_CONVERT(XML,'< a >'+ SUBSTRING( st.text, (qs.statement_start_offset/2) + 1,
    (
    (CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2
    ) + 1
    )+'< / a >') AS N'SQL Statement Executed'
    ,qp.query_plan
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    WHERE
    SUBSTRING(
    st.text,
    (qs.statement_start_offset/2) + 1,
    (
    (CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2
    ) + 1
    ) not like '%fetch%' --- and st.dbid=db_ID()
    AND total_logical_reads/execution_count>99
    ORDER BY total_logical_reads/execution_count desc, execution_count DESC;

     

    • This reply was modified 7 months, 2 weeks ago by  Lowell.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell wrote:

    when you get really big strings like that, and want to visualize them in SSMS, you can TRY_CONVERT to xml; then when you click it , it opens in a window that can support 2GB or some huge string size.

    I see this in results form sp_whosiactive, and other scripts like that all the time.,

    edit, the forum stripped my tags, trying to repost.

    try this:

    SELECT TOP 100
    total_elapsed_time/1000
    ,total_worker_time/1000
    ,total_physical_reads
    ,total_logical_reads/execution_count
    ,total_logical_reads
    ,total_logical_writes
    ,execution_count
    ,creation_time
    ,last_execution_time
    ,

    TRY_CONVERT(XML,'< a >'+ SUBSTRING( st.text, (qs.statement_start_offset/2) + 1,
    (
    (CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2
    ) + 1
    )+'< / a >') AS N'SQL Statement Executed'
    ,qp.query_plan
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    WHERE
    SUBSTRING(
    st.text,
    (qs.statement_start_offset/2) + 1,
    (
    (CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2
    ) + 1
    ) not like '%fetch%' --- and st.dbid=db_ID()
    AND total_logical_reads/execution_count>99
    ORDER BY total_logical_reads/execution_count desc, execution_count DESC;

    Thank your kind help! Lowell

    try_convert can be supported SQL Server 2014 or above.

    and the said script can't get execution sql statement, can you help me with it? thanks!

     

    Attachments:
    You must be logged in to view attached files.
  • because of the forum, the two tags i added: < a > and < / a > must have the white space removed. with whitespace, it's not valid xml and gets dumped to NULL by the try-convert, sorry.

    that's the core gotcha with what i pasted.

     

    you can change the TRY_CONVERT to CONVERT for earlier versions of SQL, i just tested and it worked fine as well.

     

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I had published an article for doing such a thing and a fellow by the name of Orlando Colamatteo gave me the idea of the following iTVF.  I use it all the time and the parameter can be changed to use NVARCHAR(MAX) if you need to.  Usage example included in the flowerbox

    CREATE FUNCTION [dbo].[ShowLongString]
    /**********************************************************************************************************************
    Purpose:
    Display a string of more than 8000 characters. The string can be Dynamic SQL, XML, or just about anything else.

    Usage:
    --===== Example with Dynamic SQL
    DECLARE @SQL VARCHAR(MAX);
    SELECT @SQL = '
    SELECT somecolumnlist
    FROM some table with joins
    ;'
    ;
    SELECT LongString
    FROM dbo.ShowLongString(@SQL)
    ;
    --===== Example with a call to a table or view
    SELECT sm.Object_ID, Definition = ls.LongString
    FROM sys.SQL_Modules sm
    CROSS APPLY dbo.ShowLongString(sm.Definition) ls
    ;
    Revision History:
    Rev 00 - 20 Sep 2013 - Jeff Moden - Initial creation and test.
    **********************************************************************************************************************/
    --===== Declare the I/O for this function
    (@pLongString VARCHAR(MAX))
    RETURNS TABLE WITH SCHEMABINDING AS
    RETURN
    SELECT LongString =
    (
    SELECT REPLACE(
    CAST(
    '--' + CHAR(10) + @pLongString + CHAR(10)
    AS VARCHAR(MAX))
    ,CHAR(0),'') --CHAR(0) (Null) cannot be converted to XML.
    AS [processing-instruction(LongString)]
    FOR XML PATH(''), TYPE
    )
    ;

     

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden wrote:

    I had published an article for doing such a thing and a fellow by the name of Orlando Colamatteo gave me the idea of the following iTVF.  I use it all the time and the parameter can be changed to use NVARCHAR(MAX) if you need to.  Usage example included in the flowerbox

    CREATE FUNCTION [dbo].[ShowLongString]
    /**********************************************************************************************************************
    Purpose:
    Display a string of more than 8000 characters. The string can be Dynamic SQL, XML, or just about anything else.

    Usage:
    --===== Example with Dynamic SQL
    DECLARE @SQL VARCHAR(MAX);
    SELECT @SQL = '
    SELECT somecolumnlist
    FROM some table with joins
    ;'
    ;
    SELECT LongString
    FROM dbo.ShowLongString(@SQL)
    ;
    --===== Example with a call to a table or view
    SELECT sm.Object_ID, Definition = ls.LongString
    FROM sys.SQL_Modules sm
    CROSS APPLY dbo.ShowLongString(sm.Definition) ls
    ;
    Revision History:
    Rev 00 - 20 Sep 2013 - Jeff Moden - Initial creation and test.
    **********************************************************************************************************************/
    --===== Declare the I/O for this function
    (@pLongString VARCHAR(MAX))
    RETURNS TABLE WITH SCHEMABINDING AS
    RETURN
    SELECT LongString =
    (
    SELECT REPLACE(
    CAST(
    '--' + CHAR(10) + @pLongString + CHAR(10)
    AS VARCHAR(MAX))
    ,CHAR(0),'') --CHAR(0) (Null) cannot be converted to XML.
    AS [processing-instruction(LongString)]
    FOR XML PATH(''), TYPE
    )
    ;

     

    Thank you for your kind help! Jeff Monden,  I had a try, but it ran very slowly, I just get top 5 pieces of record, it ran more than 1 hour but didn't finish yet, is there any better way to improve the speed ? thanks!

  • Considering that my last name contains only 5 letters and you couldn't even spell that right, I suspect a PICNIC  problem on your part.  The code runs nasty fast in all cases that I've used it and you claim that it's running terribly slow but you have not posted the code that demonstrates how you're using it. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden wrote:

    Considering that my last name contains only 5 letters and you couldn't even spell that right, I suspect a PICNIC  problem on your part.  The code runs nasty fast in all cases that I've used it and you claim that it's running terribly slow but you have not posted the code that demonstrates how you're using it. 😉

    Jeff Moden, very sorry for mistaking your name! very sorry !

    firstly, I used your code to create a function, then I tried to run the following scripts three three times, it seems very slow, thank you for you kind help !

    first time, based on your script in function, I ran the script below,

    SELECT TOP 100

    ls.LongString ,

    total_elapsed_time/1000

    ,total_worker_time/1000

    ,total_physical_reads

    ,total_logical_reads/execution_count

    ,total_logical_reads

    ,total_logical_writes

    ,execution_count

    ,creation_time

    ,last_execution_time

    ,SUBSTRING( st.text, (qs.statement_start_offset/2) + 1,

    (

    (CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2

    ) + 1

    ) N'SQL Statement Executed'

    ,qp.query_plan

    FROM sys.dm_exec_query_stats AS qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

    CROSS APPLY dbo.ShowLongString(st.text) ls

    WHERE

    SUBSTRING(

    st.text,

    (qs.statement_start_offset/2) + 1,

    (

    (CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2

    ) + 1

    ) not like '%fetch%' --- and st.dbid=db_ID()

    AND total_logical_reads/execution_count>99

    ORDER BY total_logical_reads/execution_count desc, execution_count DESC;

     

     

    second time, I ran the following script:

    SELECT sm.Object_ID, Definition = ls.LongString

    FROM sys.SQL_Modules sm

    CROSS APPLY dbo.ShowLongString(sm.Definition) ls

    third time, I ran

    SELECT LongString =

    (

    SELECT REPLACE(

    CAST(

    '--' + CHAR(10) + 'select * from sys.sysprocesses'+ CHAR(10)

    AS VARCHAR(MAX))

    ,CHAR(0),'') --CHAR(0) (Null) cannot be converted to XML.

    AS [processing-instruction(LongString)]

    FOR XML PATH(''), TYPE )

  • So your saying that the following code that you posted took over 5 hours?

    SELECT sm.Object_ID, Definition = ls.LongString

    FROM sys.SQL_Modules sm

    CROSS APPLY dbo.ShowLongString(sm.Definition) ls

    I just ran that code on my prod box.  There are 1,866 rows that it returned in the grid mode.  The number characters in some of what was returned is in excess of 300 thousand character (they're auto-generated triggers for some intense auditing that we have to do).  The whole shebang executed in just over a second.  Did you have some form of blocking going on while this was running?

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden wrote:

    So your saying that the following code that you posted took over 5 hours?

    SELECT sm.Object_ID, Definition = ls.LongString FROM sys.SQL_Modules sm CROSS APPLY dbo.ShowLongString(sm.Definition) ls

    I just ran that code on my prod box.  There are 1,866 rows that it returned in the grid mode.  The number characters in some of what was returned is in excess of 300 thousand character (they're auto-generated triggers for some intense auditing that we have to do).  The whole shebang executed in just over a second.  Did you have some form of blocking going on while this was running?

    Thank you Jeff Moden!

    I checked and found it runs well on SQL Server 2012 and above edition, but it couldn't  work fine on SQL Server 2008 R2 and there is no any block in SQL Server ,the details of SQL Server 2008 R2 as below.  can you help to make it runs fine on SQL Server 2008 R2?  many thanks!

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)

    Apr 2 2010 15:48:46

    Copyright (c) Microsoft Corporation

    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

     

  • Not relevant, but why are you still developing for SQL Server 2008 RTM 11 years later ( RTM released in 2010 )?

  • 892717952 wrote:

    Jeff Moden wrote:

    So your saying that the following code that you posted took over 5 hours?

    SELECT sm.Object_ID, Definition = ls.LongString FROM sys.SQL_Modules sm CROSS APPLY dbo.ShowLongString(sm.Definition) ls

    I just ran that code on my prod box.  There are 1,866 rows that it returned in the grid mode.  The number characters in some of what was returned is in excess of 300 thousand character (they're auto-generated triggers for some intense auditing that we have to do).  The whole shebang executed in just over a second.  Did you have some form of blocking going on while this was running?

    Thank you Jeff Moden!

    I checked and found it runs well on SQL Server 2012 and above edition, but it couldn't  work fine on SQL Server 2008 R2 and there is no any block in SQL Server ,the details of SQL Server 2008 R2 as below.  can you help to make it runs fine on SQL Server 2008 R2?  many thanks!

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

    Not sure what to tell you other than check that box.  I ran the script we've been mutually working with on a very old, underpowered, 2008 box and it returned all results in less than a second. (it only has about 900 code objects instead of the more than 1800 my 2016 box has).

    As for the two other pieces of code you wrote, I'll just say I'm not responsible for what you've done there. 😉

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 14 posts - 1 through 13 (of 13 total)

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