June 2, 2021 at 2:35 am
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;
June 3, 2021 at 3:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 3, 2021 at 6:14 am
anyone help me with the said question, thanks so much!
June 3, 2021 at 3:18 pm
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;
Lowell
June 4, 2021 at 12:37 am
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!
June 4, 2021 at 2:17 pm
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
June 5, 2021 at 12:13 am
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
Change is inevitable... Change for the better is not.
June 8, 2021 at 1:20 am
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!
June 8, 2021 at 12:58 pm
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
Change is inevitable... Change for the better is not.
June 9, 2021 at 1:46 am
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 )
June 9, 2021 at 4:13 am
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
Change is inevitable... Change for the better is not.
June 9, 2021 at 7:47 am
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)
June 10, 2021 at 7:15 am
Not relevant, but why are you still developing for SQL Server 2008 RTM 11 years later ( RTM released in 2010 )?
June 10, 2021 at 1:18 pm
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
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply