You’re offline. This is a read only version of the page.
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
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.
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!
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
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 ?
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.
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 ?
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.
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.
#1
André Arnaud de Cal...
291,969
Super User 2025 Season 1
#2
Martin Dráb
230,842
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
