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)