1

I'm reviewing some execution logs for long running queries in SSRS.
A common thread I'm seeing is reports that seem to take a few hours to process, with a rowcount of 0.

I'm using the following query to get my results:

SELECT e.instancename, 
   e.username, 
   e.timestart, 
   e.timeend, 
   Datediff(mi, e.timestart, e.timeend) AS 'Minutes', 
   e.timedataretrieval, 
   e.timeprocessing, 
   e.timerendering, 
   e.[RowCount],
   catalog.PATH, 
   catalog.name
FROM   executionlogstorage e  
   INNER JOIN catalog 
     ON e.reportid = catalog.itemid 
   INNER JOIN users 
     ON catalog.modifiedbyid = users.userid 
   INNER JOIN users AS users_1 
     ON catalog.createdbyid = users_1.userid 
WHERE  Datediff(mi, e.timestart, e.timeend) > 15
   AND catalog.name <> '' 
   and TimeStart > 01-01-2014
   -- Filtering out Oracle reports--
   and (TimeDataRetrieval + TimeProcessing + TimeRendering + [RowCount]) <> 0
ORDER  BY 'Minutes' DESC 

And one of the results I'm not sure how to translate is:


|Minutes | timedataretrieval | timeprocessing | timerendering | RowCount |
|243 | 3 | 14453530 | 0 | 0 |

How is it possible to have a report processing for 4 hours, and return 0 rows, with a dataretrieval time of 3 miliseconds?
If this were a lone case I'd ignore it, but I've got a hundred similar ones. (and a few thousand ones that do make sense)

2

This has happened to me before when the InteractiveHeight was set to 0. The query only returned a few thousand rows, but since there was no paging in the report, SSRS was trying to put everything on a single page. Eventually I would hit the SSRS timeout before it reached the rendering phase, and have 0 rows returned.

If that doesn't apply to your situation, please check out this link for some additional troubleshooting ideas: http://technet.microsoft.com/en-us/library/bb522806(v=sql.105).aspx#Process

  • I came across this possible explanation in my research, however it mentioned that the issue would cause "TimeRendering" to be exceptionally high, not TimeProcessing. So I don't think it's applicable here, however I'll make sure to look into it as well. – Reaces Jan 5 '15 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.