Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2778
  • Last Modified:

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))
0
cindyfiller
Asked:
cindyfiller
  • 8
  • 3
  • 2
  • +2
1 Solution
 
DcpKingCommented:
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.
0
 
cindyfillerAuthor Commented:
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%
0
 
cindyfillerAuthor Commented:
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.
0
Cyber Threats to Small Businesses (Part 2)

The evolving cybersecurity landscape presents SMBs with a host of new threats to their clients, their data, and their bottom line. In part 2 of this blog series, learn three quick processes Webroot’s CISO, Gary Hayslip, recommends to help small businesses beat modern threats.

 
Marten RuneCommented:
Im thinking you need to capture information from this query.
Run profiler to capture the query, now run this captured query in SSMS against the database, does it behave the same, i e is it 'hanging'. You want to be able to repeat the problem in order to solve it.

Can you do a profiler, and see whats going on.
Can you run the report with parameters lika a month ago (when it worked), does it work then
Can you put static values into your newly designed report (that behaves the same) and se if it works

regards Marten
0
 
cindyfillerAuthor Commented:
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?
0
 
cindyfillerAuthor Commented:
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.
0
 
cindyfillerAuthor Commented:
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.
0
 
Marten RuneCommented:
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
0
 
cindyfillerAuthor Commented:
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!
0
 
Marten RuneCommented:
Glad to see its resolved.

//Marten
0
 
JeffreyVPCommented:
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
0
 
cindyfillerAuthor Commented:
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!
0
 
DcpKingCommented:
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.
0
 
James0628Commented:
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
0
 
cindyfillerAuthor Commented:
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.
0
 
JeffreyVPCommented:
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 ?
0
 
James0628Commented:
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
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now