Solved

SSRS report hangs system and pushed cpu to 100%

Posted on 2012-03-30
19
2,415 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
  • 8
  • 3
  • 2
  • +2
19 Comments
 
LVL 16

Expert Comment

by:DcpKing
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 20

Accepted Solution

by:
Marten Rune earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:cindyfiller
Comment Utility
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
Comment Utility
Glad to see its resolved.

//Marten
0
 

Expert Comment

by:JeffreyVP
Comment Utility
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
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:James0628
Comment Utility
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
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:James0628
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Veeam Backup & Replication has added a new integration – Veeam Backup for Microsoft Office 365.  In this blog, we will discuss how you can benefit from Office 365 email backup with the Veeam’s new product and try to shed some light on the needs and …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now