title: How to identify a long-running query in the SQL Server database?
body: <p>We are facing a long-running query in our production database. We tried to run a query to identify the long-running sessions. We also tried to run a query to identify which stored procedure runs long. Afterwards, the result that was return has no record. Meaning, there is no such stored procedure that runs long.</p>
  
  <p>Query to identify the long-running query session:</p>
  
  <pre><code>SELECT 
      sqltext.TEXT,
      req.session_id,
      req.status,
      req.command,
      req.cpu_time,
      req.total_elapsed_time
  FROM 
      sys.dm_exec_requests req
  CROSS APPLY 
      sys.dm_exec_sql_text(sql_handle) AS sqltext
  WHERE
      sqltext.[Text] NOT LIKE '--''check%'
  ORDER BY 
      req.cpu_time DESC
  </code></pre>
  
  <p>This is the snippet of the long-running session:</p>
  
  <pre><code>(@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),@p__linq__2 int)SELECT TOP (1) 
      [Extent1].[Id] AS [Id], 
      [Extent1].[LogType] AS [LogType], 
      [Extent1].[UserId] AS [UserId], 
      [Extent1].[Description] AS [Description], 
      [Extent1].[CreatedDate] AS [CreatedDate]
      FROM [dbo].[Log] AS [Extent1]
      WHERE (7 =  CAST( [Extent1].[LogType] AS int)) AND ( CAST( [Extent1].[LogType] AS int) IS NOT NULL) AND ([Extent1].[CreatedDate] &gt;= @p__linq__0) AND ([Extent1].[CreatedDate] &lt;= @p__linq__1) AND ((([Extent1].[UserId] = @p__linq__2) AND ( NOT ([Extent1].[UserId] IS NULL OR @p__linq__2 IS NULL))) OR (([Extent1].[UserId] IS NULL) AND (@p__linq__2 IS NULL)))
  </code></pre>
  
  <p>We tried this query to identify the long-running stored procedure:</p>
  
  <pre><code>SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
  FROM INFORMATION_SCHEMA.ROUTINES 
  WHERE ROUTINE_DEFINITION LIKE '%FROM \[dbo\].\[Logs\]%' ESCAPE '\'
  </code></pre>
  
  <p>We need some help on how to identify a long running query instead of checking the stored procedure.</p>