SQL query result truncated

Asked Oct 09 '15 at 08:55

Active Oct 09 '15 at 08:55

Viewed 253 times

I have the following query to find bad performing queries

SELECT TOP 10
    total_worker_time/execution_count AS Avg_CPU_Time
        ,execution_count
        ,total_elapsed_time/execution_count as AVG_Run_Time
        ,(SELECT
              SUBSTRING(text,statement_start_offset/2,(CASE
                     WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 
                     ELSE statement_end_offset 
                               END -statement_start_offset)/2
                       ) FROM sys.dm_exec_sql_text(sql_handle)
         ) AS query_text 
FROM sys.dm_exec_query_stats 

--pick your criteria

ORDER BY Avg_CPU_Time DESC
--ORDER BY AVG_Run_Time DESC
--ORDER BY execution_count DESC

Found here: How to find the worst performing queries in SQL Server 2008?

However, when executed in Management Studio, the query_text is truncated and I cannot see the full query. The queries found are very long, is there a way to still see the full query?

Thanks in advance.

edited May 23 '17 at 12:29

Community

asked Oct 09 '15 at 08:55

mario.schlipf

1,257
2
13
29

http://www.johnsansom.com/how-to-identify-the-most-costly-sql-server-queries-using-dmvs/ – wiretext Oct 09 '15 at 09:11
You can increase the query_text length specifying nvarchar(4000) instead of nvarchar(max), but you can't go beyond that value. – Sergio Internicola Oct 09 '15 at 09:19
Thank you both! The link to johnsamson.com helped me – mario.schlipf Oct 09 '15 at 09:38

SQL query result truncated

0 Answers0