I had a problem in reporting services 2014 - all the queries in a report were running fine from management studio but timing out when running the report.

here is the message from the user:

enter image description here

what to do to troubleshoot a SSRS 2014 report that gets stuck on loading?

where are the SSRS logs?

the report has 3 datasets and all of them run fine when I take the query off and run it through SSMSS.

this reporting services server, has only the service, the databases (ReportServer, etc) are on a different server but that is not the problem, it has been running for months.

this is the query of the report:

USE Product
declare @SeasonId int;set @SeasonId = 16

SELECT DISTINCT 
        EDP [EDP],
        '-' [PO],
        NULL [PO Type Initial/ Repeat],
        NULL [PO Status Open/ Close],
        s.StyleCode + c.ColourCode + sz.SizeCode [SKU] ,
        StyleDesc [Style Description] ,
        s.StyleCode [Style] ,
        c.ColourCode [Option] ,
        SizeCode [Size] ,
        NULL [MPLAN Segment Code],
        SeasonDesc [Season],
        DepartmentDesc [Department],
        CategoryDesc [Category],
        NULL [ALL Season, SPR Only, SUM Only, INTERNET Only],
        NULL [Fabric Type],
        NULL [Garment Composition],
        NULL [Country Of Origin],
        NULL [MID Code],
        NULL [HTS Code],
        0.00 [Cost to use],
        NULL [Exception Flag],
        NULL [Duty Rate]
FROM    [Product].[dbo].[Style] s
        JOIN Product.dbo.StyleDetail sd ON s.StyleID = sd.StyleID
        JOIN [Product].[dbo].[Category] ct ON s.CategoryID = ct.CategoryID
        JOIN [Product].[dbo].[Department] d ON ct.DepartmentID = d.DepartmentID
        JOIN [Product].[dbo].[Segment] sg ON d.SegmentID = sg.SegmentID --
        JOIN [Product].[dbo].[Item] i ON s.styleid = i.styleid
        JOIN [Product].[dbo].[ItemColour] ic ON i.itemid = ic.itemid
        JOIN [Product].[dbo].[Colour] c ON ic.colourid = c.colourid
        JOIN [Product].[dbo].[ItemSize] isz ON i.itemid = isz.itemid
        JOIN [Product].[dbo].[Size] sz ON isz.sizeid = sz.sizeid
        JOIN [Product].[dbo].[SeasonItem] si ON i.itemid = si.itemid
        JOIN [Product].[dbo].[Season] se ON se.seasonid = si.seasonid
        JOIN [product].[dbo].[ItemMPLANReference] pn ON pn.itemid = i.itemid
        JOIN [Product].[dbo].[Product_Staging_CategoryImportMap] ci ON ci.CategoryID = ct.CategoryID
        JOIN [SAPurchaseOrder].[dbo].[tblPO1Detail] pod ON pn.EDP = pod.strItemNo

WHERE se.seasonid IN (@SeasonId)
and EDP not in (SELECT skuEDP from USCarrier.dbo.WHDespatchSku)
ORDER BY 1

this problem has been solved. how I did it?

I had a look at the query plan and noticed a missed index.

USE [SAPurchaseOrder]
GO
CREATE NONCLUSTERED INDEX [idx_strItemNo]
ON [dbo].[tblPO1Detail] ([strItemNo])

GO

After I added the index the report started to work fine again.

What I think that really happened is: SSRS had cached the query execution plan, and there was a case of parameter sniffing. Once I created the index the plan was removed from cache and had to be recompiled.

this is very similar to this question here: SQL runs slow in SSRS, but fast in SSMS

One good thing I think useful from the question above is how to eliminate the parameter sniffing possibility:

As discussed in the comments, let's get rid of the parameters to see if your query is getting affected by parameter sniffing.

To do this we build the SQL statement from scratch. Most things in SSRS can be expressions including the SQL query so you can build it as a string. With the parameters, we'll convert them to a comma delimited list using JOIN.

questions remain:

what to do to troubleshoot a SSRS 2014 report that gets stuck on loading?

where are the SSRS 2014 logs?

  • SSRS logs:technet.microsoft.com/en-in/library/… – TheGameiswar Aug 2 '16 at 10:54
  • marcello, has anyone answered this question? Will you mark one of the answers as answer if it answered your question? – Arthur D Aug 10 '16 at 17:50
  • @ArthurD thanks for answering, I have accepted it now. I was expecting more information, but you gave me a good starting point that I was not aware of. – marcello miorelli Aug 10 '16 at 19:45
up vote 1 down vote accepted

You need to look at the Execution logs. On your Report Server database, there's a view called ExecutionLog3. You can query this view by date and find how the user was running the report.

USE [ReportServer]
GO

SELECT *
FROM dbo.ExecutionLog3 AS el
WHERE el.TimeStart >= '2016-08-02'

For SSRS reprots there are really three things to look at: IIS logs, SSRS logs, Profile the database.

The IIS logs will show you if there is an IIS issue with the site, probably not your issue but doesn't hurt to check just in case. Outside chance you are having a connection issue, so check your IIS app pool and your logs to rule this out.

As Arthur D mentions, the executionlog3 view will show you if the request has made it to the reporting database and what happened.

If both of the above still doesn't help, then Profile the database and see if your request is even making it to the database.

  • how to look at IIS logs? Where/how to look at SSRS logs? can you add screenshots? – marcello miorelli Aug 2 '16 at 14:00
  • 1
    @marcellomiorelli By SSRS logs, he means the query I posted. When he listed strategies, he listed one paragraph for IIS, one for SSRS logs, one for Profile. – Arthur D Aug 2 '16 at 14:20

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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