web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Management Reporter - ...
Microsoft Dynamics GP (Archived)

Management Reporter - High CPU on SQL Server when running large reports

(0) ShareShare
ReportReport
Posted on by Pat Pawks 90

Hi,

Does anyone else have this problem? When we run a large report in Management Reporter 2012 (latest October 2015 release), which usually takes about 10 minutes to request the data (45 min. total to generate report start to finish), the CPU on the SQL Server stays near 100% until the query finishes. This causes the rest of the databases on the server to perform at a turtle pace because MR is taking up all the resources on the SQL Server.

Has anyone else noticed this huge performance hit when running large reports within MR?

Some of you might say it's maybe the hardware, but we meet/exceed all recommended requirements for the Management Reporter Data Mart specifications.

We have also changed the GL Transaction to Fact trigger to run every 10 minutes.

We have also tried changing the MAXDOP setting in MR to 0 and 1, with no luck.

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    babubaskaran@outlook.com Profile Picture
    babubaskaran@outloo... 12 on at

    Hi Pat,

    Please have a look at a very good post about  SQL Server performance boosting settings by Mariano and see whether that make any difference to running the report on MR.

  • Pat Pawks Profile Picture
    Pat Pawks 90 on at

    Thanks Babu. I'll try playing around with these settings to see if they make a difference. I'll also try to identify the query that is causing the huge spike in CPU, hopefully that will shed light on this issue as well. I'll report my findings hopefully within the next week or so.

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at

    Hi Pat,

    What is the server load in idle time when you don't run the report generation ? Have you tried to generate the same report by using the Legacy connector rather then the DataMart ?

  • Pat Pawks Profile Picture
    Pat Pawks 90 on at

    SQL Server load in idle time is 5% total processor time or less, with the occasional spike (which is normal because of other integrations). I was trying to stay away from the Legacy connector because I read they will be removing it in a future release. But can't hurt to try it right now to see if it will make a difference. I will try that as well.

    Here's a picture of PerfMon for the duration of the report. The highlighted line is the CPU on the SQL Server. It spikes during the "Request data" stage when running the report in MR.

     PerfMon.JPG

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at

    What is the transaction with high load to 100% for about 4-5 minute (blue line) ? could you identify this ?  And this other one (green) that takes 50% of the CPU for another 4-5 min.. ?

    Is your MR 2012 server Database installed on the same server as your GP DB's ? How large are the MR2012 DB and the DM DB ?

  • Pat Pawks Profile Picture
    Pat Pawks 90 on at

    This is the query that bogs down the server:

    SELECT filters.Row, dc.Id , dc.Dimension2Id, dc.Dimension1Id, dc.Dimension3Id, dc.Dimension4Id, dc.Dimension5Id, dc.Dimension6Id

    into #dimComboRow  

    from [Datamart].DimensionCombination dc

    INNER JOIN [tag:AccountFilter] filters on ((filters.Dimension1Id is null OR (isNull(dc.Dimension1Id, -1) = filters.Dimension1Id) OR (filters.Dimension1Id = -2 AND dc.Dimension1Id is not null))  AND (filters.Dimension2Id is null OR (isNull(dc.Dimension2Id, -1) = filters.Dimension2Id) OR (filters.Dimension2Id = -2 AND dc.Dimension2Id is not null)) )

    where OrganizationId = 1

    The DimensionCombination table has 111861 rows. I'm still analyzing how the [tag:AccountFilter] table gets populated.

    Green line is the # of packets/sec. sent through the network card. My analysis of that is once the CPU goes down (query completes), it sends the results to MR.

    Yes, all MR databases are on the same SQL Server instance as GP DBs.

    MR2012: 16GB, DM roughly 1.5GB

  • Verified answer
    Pat Pawks Profile Picture
    Pat Pawks 90 on at

    Turns out CU14 (which came out November 25th 2015) actually corrected my issue. Instead of spiking at 100% CPU for the same report, the SQL spiked at 90% for 1 second, and then it was cruising from there. A report that took 10 minutes to request the data took less than 10 seconds with CU14. Obviously they tweaked the code. There are alot of performance fixes in this update. Thanks everyone for your input!

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at

    Thank you Pat,

    Didn't noticed that CU14 was released... too busy with post-upgrade tasks in GP.

    Will spend a look at it and report my findings too.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
Community Member Profile Picture

Community Member 2

#2
Victoria Yudin Profile Picture

Victoria Yudin 1

#2
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans