asked on

SSRS report hangs system and pushed cpu to 100%

I have a series of reports that were originally designed in SSRS 2005. Months ago we upgraded to 2008 and most reports converted without any issues, including the one I'm referencing today. Suddenly this report won't run. It won't run from the web site, from BIDS and I can't get it to run from the query designer. It just spins and spins. A week after time we upgraded to SSRS2008 we moved our servers to a virtual environment. When I try and run this report from any of the 3 areas listed above, the CPU usage goes to 100% and I get alarms about the high CPU usage. However, I can copy the code into a query in management studio, put static info for the 3 paramaters and it runs in seconds. I've tried copying the report thinking maybe there was some kind of issue with the report itself, but the copy does the same thing. Any ideas?

I've copied the code for the report below:

SELECT BeginBalQuery.ProjectBalance AS BeginBal, View_Project_with_Attributes.ProjectDescription, View_Project_with_Attributes.ProjCollege,
View_Project_with_Attributes.ProjDept, View_Project_with_Attributes.ProjPurpose, View_Project_with_Attributes.ProjClass, View_Project_with_Attributes.ProjectDimID,
View_Project_with_Attributes.ProjectID, SUM(CurrentTransactions.TransAmt) AS Activity, ExpendableAmt.ExpendAmt
FROM (SELECT ProjectDimID,
SUM(CASE ExpendQuery.AccountCategorySystemID WHEN '4' THEN ExpendQuery.NaturalAmount WHEN '5' THEN - ExpendQuery.NaturalAmount ELSE 0
END) AS ExpendAmt
FROM FACT_GLTransactionDistribution AS ExpendQuery
WHERE (AccountCode = '4250') AND (PostDate >= @BeginDate) AND (PostDate <= @ReportDate) AND (AccountCategorySystemID IN ('4', '5')) AND
(SUBSTRING(CONVERT(varchar, CASE WHEN Month(@ReportDate) < 7 THEN @ReportDate ELSE DateAdd(yyyy, 1, @ReportDate) END, 12), 1, 2)
= SUBSTRING(LTRIM(RTRIM(JournalReference)), 3, 2))
GROUP BY ProjectDimID) AS ExpendableAmt RIGHT OUTER JOIN
View_Project_with_Attributes ON ExpendableAmt.ProjectDimID = View_Project_with_Attributes.ProjectDimID LEFT OUTER JOIN
(SELECT ProjectDimID,
CASE CurrQuery.AccountCategorySystemID WHEN '4' THEN CurrQuery.NaturalAmount WHEN '5' THEN - CurrQuery.NaturalAmount ELSE 0 END AS TransAmt
FROM FACT_GLTransactionDistribution AS CurrQuery
WHERE (PostDate >= @BeginDate) AND (PostDate <= @ReportDate) AND (AccountCategorySystemID IN ('4', '5'))) AS CurrentTransactions ON
View_Project_with_Attributes.ProjectDimID = CurrentTransactions.ProjectDimID LEFT OUTER JOIN
(SELECT ProjectDimID,
SUM(CASE FACT_GLTransactionDistribution.AccountCategorySystemID WHEN '1' THEN FACT_GLTransactionDistribution.NaturalAmount WHEN '2' THEN
- FACT_GLTransactionDistribution.NaturalAmount ELSE 0 END) AS ProjectBalance
FROM FACT_GLTransactionDistribution
WHERE (PostDate < @BeginDate)
GROUP BY ProjectDimID) AS BeginBalQuery ON View_Project_with_Attributes.ProjectDimID = BeginBalQuery.ProjectDimID
WHERE (View_Project_with_Attributes.ProjectActive = 'Active')
GROUP BY BeginBalQuery.ProjectBalance, View_Project_with_Attributes.ProjectDescription, View_Project_with_Attributes.ProjCollege, View_Project_with_Attributes.ProjDept,
View_Project_with_Attributes.ProjPurpose, View_Project_with_Attributes.ProjClass, View_Project_with_Attributes.ProjectDimID,
View_Project_with_Attributes.ProjectID, ExpendableAmt.ExpendAmt
HAVING (View_Project_with_Attributes.ProjClass IN ('Endowed - I&E', 'Quasi Endowed - I&E', 'Restricted', 'Endowed - I&E Testamentary')) AND
(View_Project_with_Attributes.ProjCollege IN (@College))

DcpKing

If something in the report has become corrupted then copying will only copy thecorruption too.

Try creating a new report - say in ReportBuilder - to show the 9 fields and see what happens then. If something has become corrupted and you don't have a good copy (in your Source Code Version Control system ?) then you'll most likely want to re-create it.

BTW, the .rdl files are just text XML - you might be able to see a blatent problem just scanning through them. However, usually visibly obvious errors are usually bad enough that the report doesn't even load.

cindyfiller

ASKER

Good thought - didn't work. I started with a brand new report and copied the sql code into it. I didn't create the groups or totals - just ran it with detail lines. It did the same thing - it hun and my cpu is now over 100%

cindyfiller

ASKER

I did ask that several other zones be added and can provide more info on this issue. Before moving to VMware SSRS ran on a 7 or 8 year old server. It did have 2 cpu's and was a decent machine - but it was old. I actually moved all vm's off this one host but the server that houses SSRS and tried running the report. I still had the same issue - it ate all of the cpu up. This host has 12 cpu's in it, so something just doesn't seem right. I don't know if certain SQL apps don't run well in VMware? I have another 7 or 8 vm's with SQL on them and am only having issues with this one. I did also copy the code into a totally different report and tried to run it - and it still gobbled up all of the cpu.

ASKER CERTIFIED SOLUTION

Marten Rune

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cindyfiller

ASKER

I have tried running this 4 different ways - it hangs if it is run using SSRS in any fashion but works fine if I run it from SSMS. I have tried running the code from query designer, clicking preview in bids and running it from the report server - all 3 of those hang even if I have static info instead of paramaters. When I run it from SSMS it runs in a matter of seconds. I don't know of anything I can run in BIDS that might do the same as profiler?

cindyfiller

ASKER

FYI, if I run the query from query designer I do get a Time out expired message - time out period elapsed prior to completion of the operation or the server is not responding. I also did test replacing each parameter used with a static value. It timed on until I had replaced all values. Once that is done, it runs in query designer - just as it runs in SSMS.

cindyfiller

ASKER

Hmm - another update. The SSRS report also runs when all of the values are static. I'm not quite sure which parameter is causing teh issue so I'll keep checking.

Marten Rune

Now you've found it.

Can it be that the timeout is actually ok, i e your database have grown so that the query is not executed fast enough for the RS timeout.
http://blogs.msdn.com/b/emilianb/archive/2009/05/28/timeouts-and-microsoft-sql-server-2005-reporting-services.aspx

//Marten

cindyfiller

ASKER

I got the report working! I started testing the parameters in reverse order and found that the last parameter was the one causing the issue. That particular parm can be pulled with the long or short description. I changed it to pull based on the short description and the report ran in seconds. I'm not quite sure why it made such a difference, but it works. Thanks!

Marten Rune

Glad to see its resolved.

//Marten

JeffreyVP

Hi I seem to have the same big issue.
My query is :
SELECT
            PWSL.[Shipment Date] AS Leverdatum,
            PWSL.[Destination No_] AS Klantencode,
            SH.[Bill-to Name] AS Klantennaam,
            STA.Name AS Leveringsnaam,
            STA.Address AS [Leveradres 1],
            STA.[Post Code] AS Postcode,
STA.City AS Gemeente,
            PWSL.[Item No_] AS Artikelcode,
            PWSL.Description AS Artikelomschrijving,
PWSL.Quantity AS Aantal,
            PWSL.[Source No_] AS Ordernummer,
            SL.[Unit Price] AS 'Verkoopprijs uit salesLine',
            PWSL.Quantity * SL.[Unit Price] AS Verkoopbedrag,
            SL.[Unit of Measure] AS Eenheid,
PWSH.[Shipping Agent Code] AS Transporteur,
            SL.[Budget Code],
            SH.[TradePoint Order],
            Co.[Name]AS Besteld_door
FROM [OPS Sepia$Posted Whse_ Shipment Line] AS PWSL INNER JOIN
[OPS Sepia$Posted Whse_ Shipment Header] AS PWSH on PWSL.No_ = PWSH.No_ LEFT OUTER JOIN
[OPS Sepia$Ship-to Address] AS STA ON PWSL.[Destination No_] = STA.[Customer No_] AND PWSL.[Ship-to Code] = STA.Code inner join
[OPS Sepia$Sales Line]AS SL ON PWSL.[Source No_] = SL.[Document No_] and PWSL.[Source Line No_] = SL.[Line No_] INNER JOIN
                   [OPS Sepia$Sales Header] as SH ON PWSL.[Source No_] = SH.[No_]      left outer JOIN
                   [OPS Sepia$Contact] as Co ON SH.[Tradepoint User] = Co.[No_]      AND
PWSL.[Source Line No_] = SL.[Line No_]
WHERE (PWSH.[Responsibility Center] = 'BDCO') AND (PWSL.[Shipment Date] BETWEEN @dtStart AND @dtEnd)

union all

SELECT
            PWSL.[Posting Date] AS Leverdatum,
            PWSL.[Destination No_] AS Klantencode,
            SH.[Bill-to Name] AS Klantennaam,

            STA.Name AS Leveringsnaam,
            STA.Address AS [Leveradres 1],

            STA.[Post Code] AS Postcode,
STA.City AS Gemeente,
            PWSL.[Item No_] AS Artikelcode,
            PWSL.Description AS Artikelomschrijving,

PWSL.Quantity AS Aantal,
            PWSL.[Source No_] AS Ordernummer,

            SL.[Unit Price] AS 'Verkoopprijs uit salesLine',
            PWSL.Quantity * SL.[Unit Price] AS Verkoopbedrag,


            SL.[Unit of Measure] AS Eenheid,

'Retour' AS Transporteur,
            SL.[Budget Code],
            SH.[TradePoint Order],
            Co. [Name]AS Besteld_door
FROM
[OPS Sepia$Posted Whse_ Receipt Line] AS PWSL LEFT OUTER JOIN
[OPS Sepia$Ship-to Address] AS STA ON PWSL.[Destination No_] = STA.[Customer No_] AND PWSL.[Ship-to Code] = STA.Code INNER JOIN

[OPS Sepia$Sales Line] AS SL ON PWSL.[Source No_] = SL.[Document No_] and PWSL.[Source Line No_] = SL.[Line No_] INNER JOIN
                   [OPS Sepia$Sales Header] as SH ON PWSL.[Source No_] = SH.[No_]      left outer JOIN
                   [OPS Sepia$Contact] as Co ON SH.[Tradepoint User] = Co.[No_]
                  AND
PWSL.[Source Line No_] = SL.[Line No_]
WHERE (SL.[Responsibility Center] = 'BDCO') AND (PWSL.[Posting Date] BETWEEN @dtStart AND @dtEnd)

ORDER BY Leverdatum, Klantencode

Running the report makes the CPU going upto 100%.
Running the same query management studio : 3 seconds.

What do u mean with parameters in reverse order ?
Many thanks !
Jeffrey

cindyfiller

ASKER

I was removing parameters and running the report without one of the parameters just so I could see which one was causing the issue. I did it in reverse order of how they showed on the screen - no reason why!

DcpKing

So, Cindy, the answer wasn't that you (i.e. the front end) was doing anything "wrong" - but rather that too much info was being pushed back for SSRS to handle? JeffreyVP might note this too: I've seen this too - there seems to be a limit to the amount of info you can dump onto SSRS before it just gives up.

James0628

cindyfiller,

FWIW, if you changed your query so that it compared the parameter with a different column (changed from using "long description" to "short description"), then maybe the new column was indexed and the old column was not?

James

cindyfiller

ASKER

No - neither column was indexed. But we have a short table and a long table for the college and as soon as I changed it to the short table (5 characters vs 60 or so), it worked fine. It's odd because I use the long table on other reports - this one must have been a bit more complicated.

JeffreyVP

don't get it why there can be a difference in that way in running it in the management studio or in the query functionality in dataset.

obdc issue ?

James0628

cindyfiller,

Oh well. It was worth a shot. :-)
Just to clarify, in your last post you said "long table" and "short table". Did you mean column, or was it actually a different table?

JeffreyVP,

FWIW, if you really want to pursue this, you should probably start a new question (if you haven't already). It would probably be the "proper" thing to do, but, aside from that, the only people that are likely to see this old question are the people that were involved in it, or were monitoring it (like myself). If you start a new question, a lot more people will probably see it.

James