Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SSRS report hangs system and pushed cpu to 100%

Posted on 2012-03-30
19
Medium Priority
?
2,709 Views
Last Modified: 2013-11-19
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
Comment
Question by:cindyfiller
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 2
  • +2
19 Comments
 
LVL 16

Expert Comment

by:DcpKing
ID: 37788996
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
 

Author Comment

by:cindyfiller
ID: 37789051
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
 

Author Comment

by:cindyfiller
ID: 37825322
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
2017 Webroot Threat Report

MSPs: Get the facts you need to protect your clients.
The 2017 Webroot Threat Report provides a uniquely insightful global view into the analysis and discoveries made by the Webroot® Threat Intelligence Platform to provide insights on key trends and risks as seen by our users.

 
LVL 20

Accepted Solution

by:
Marten Rune earned 2000 total points
ID: 37826572
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
 

Author Comment

by:cindyfiller
ID: 37827362
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
 

Author Comment

by:cindyfiller
ID: 37827859
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
 

Author Comment

by:cindyfiller
ID: 37827896
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
 
LVL 20

Expert Comment

by:Marten Rune
ID: 37828221
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
 

Author Comment

by:cindyfiller
ID: 37828267
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
 
LVL 20

Expert Comment

by:Marten Rune
ID: 37828506
Glad to see its resolved.

//Marten
0
 

Expert Comment

by:JeffreyVP
ID: 39656603
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
 

Author Comment

by:cindyfiller
ID: 39657017
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
 
LVL 16

Expert Comment

by:DcpKing
ID: 39657236
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
 
LVL 35

Expert Comment

by:James0628
ID: 39657661
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
 

Author Comment

by:cindyfiller
ID: 39657782
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
 

Expert Comment

by:JeffreyVP
ID: 39658569
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
 
LVL 35

Expert Comment

by:James0628
ID: 39659030
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

Cyber Threats to Small Businesses (Part 1)

This past May, Webroot surveyed more than 600 IT decision-makers at medium-sized companies to see how these small businesses perceived new threats facing their organizations.  Read what Webroot CISO, Gary Hayslip, has to say about the survey in part 1 of this 2-part blog series.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question