Querying SSRS Execution Log

Question

Post reply

Querying SSRS Execution Log

Neil Burton

SSC-Insane

Points: 23009
More actions
March 13, 2020 at 10:48 am

#3733982
I've come across an odd situation when querying the ExecutionLog3 view in the SSRS database. The TOP 1000 rows query below essentially times out because as soon as I see the user hasn't run something I stop it. This takes a couple of seconds but the query keeps running for considerably longer and completes in four minutes or so.
```
SELECT TOP (1000)  [InstanceName]
      ,[ItemPath]
      ,[UserName]
      ,[ExecutionId]
      ,[RequestType]
      ,[Format]
      ,[Parameters]
      ,[ItemAction]
      ,[TimeStart]
      ,[TimeEnd]
      ,[TimeDataRetrieval]
      ,[TimeProcessing]
      ,[TimeRendering]
      ,[Source]
      ,[Status]
      ,[ByteCount]
      ,[RowCount]
  FROM [dbo].[ExecutionLog3]
  WHERE 
ItemPath LIKE '/Test%'
ORDER BY
TimeStart DESC;
```
When I run the query below, it takes 56 seconds for the results to come back but the query has completed at that point.
```
SELECT TOP (100) PERCENT [InstanceName]
      ,[ItemPath]
      ,[UserName]
      ,[ExecutionId]
      ,[RequestType]
      ,[Format]
      ,[Parameters]
      ,[ItemAction]
      ,[TimeStart]
      ,[TimeEnd]
      ,[TimeDataRetrieval]
      ,[TimeProcessing]
      ,[TimeRendering]
      ,[Source]
      ,[Status]
      ,[ByteCount]
      ,[RowCount]
  FROM [dbo].[ExecutionLog3]
  WHERE 
ItemPath LIKE '/Test%'
ORDER BY
TimeStart DESC;
```
I've had a look at the execution plans and the principal difference I can see is that the TOP (100) PERCENT plan goes parallel. I'm not sure why this would be though. Can anybody shed any light on this?
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 2 posts - 1 through 1 (of 1 total)

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

Site Owners SSC Guru Points: 80325 More actions · Answer 1

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

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