Saturday, 10 June 2017

SQL Query Time Execute Check

  1. SELECT    
  2.     qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,  
  3.     qs.total_elapsed_time / 1000000.0 AS total_seconds,  
  4.     qs.execution_count,  
  5.     SUBSTRING (qt.text,qs.statement_start_offset/2,   
  6.          (CASE WHEN qs.statement_end_offset = -1   
  7.             THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2   
  8.           ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,  
  9.     o.name AS object_name,  
  10.     DB_NAME(qt.dbid) AS database_name  
  11. FROM sys.dm_exec_query_stats qs  
  12.     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt  
  13.     LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id  
  14. WHERE qt.dbid = DB_ID()  
  15. ORDER BY average_seconds DESC;  

No comments:

Post a Comment

Resetting IDENTITY Seed in Table Variable

For a "normal" table, you would use:  DBCC  CHECKIDENT( ' [table_name]' , RESEED, [new_reseed_value]). you cannot reseed...