SQL Reporting Services - Goldmine - Reports Slow, SVCHOST and RPCSS

Hello Experts,
We've recently implemented a new Goldmine CRM and the reporting features are slow.

The setup is all on the same LAN, and involves 2 seperate servers; one for the GM application and reporting services, and another SQL 2005 Enterprise server which holds the database.

When running a report, the CPU holds at about 25%, and the application hangs for few - many minutes. The processes are SVCHOST.exe, which I drilled down to RpcSs.

When I test going to http://localhost/reports, the response is the same, which leads me to believe the problem is not really related to Goldmine, but maybe something in IIS, Reporting Services, the SQL server, or the connection between the app server and SQL server.

Any ideas on good steps to troubleshoot this further, or maybe ideas on configuration changes I can make to speed this up?
LVL 7
ottobockAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

stevengraffCommented:
Which reports are you running?

If you're running GoldMine's internal reports, that's the problem. Many of them are very inefficient when run against the current versions of GoldMine. The built-in reports were originally designed with a dBase back-end in mind.

If yes, what you need to do is to create new reports using Crystal Reports or SQL Reporting Services. You should see a marked improvement!
0
stevengraffCommented:
If, by the way, you're not running GoldMine's built-in reports (which I sort of "get" from re-reading your post), then yes, I agree with you, it has nothing to do specifically with GoldMine. Be sure to check (and double-check) your DNS settings. I can't tell you how many performance issues I've tracked back to DNS over the years. By the way, do you have tcp/ip enabled as a protocol, both for server and client?
0
ottobockAuthor Commented:
Thanks for the ideas!
The reports are coming from SQL Reporting Services ~ as you hinted to in the second comment. I can verify the "pauses" by simply going to http://servername/reports. Goldmine is using some sort of browser plugin to display the reports from within Goldmine, but also display things like Google maps...

If it's not already apparent, I'm pretty unfamiliar with troubleshooting SQL Reporting and IIS. :-( So thanks for any details you can provide me.

To try and hit on your other topics:
- DNS: hmm, could it be a DNS lookup which is pausing the reports? (I'm sort of thinking out loud) Would this show as SVCHOST-RPCSS somehow? It could be the DNS used by our internet proxy should be checked...
- TCP/IP is the only network protocol utilized in our organization.  

Thanks for your help!
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

stevengraffCommented:


The tcp/ip issue relates to what SQL uses... it can also use Named Pipes and Shared Memory. You want to be sure that the SQL Server software (and its related client software) both have tcp/ip enabled. It's possible for it to work with tcp/ip not enabled, which could be your current situation. Not that that's always automatically bad, but, in general, GoldMine recommends having tcp/ip enabled.
0
stevengraffCommented:
It sounds like the SRS report is running in the context of the GM+View tab or GM+Browser environment... Can you try running it directly through an IE browser? It should be possible to run it even without GoldMine in the picture, as SRS reports dip directly into the back-end SQL tables. You may need to feed in a parameter, like the internal GoldMine account number (contact1.accountno), but that would be about it.
0
ottobockAuthor Commented:
I have tested outside of Goldmine, sorry to be unclear. That's what I meant by saying I can navigate to http://servername/reports (I meant via IE I can go here, and see the same pauses)

TCPIP is definitely being used for all SQL communication. Named pipes is also enabled, but not utilized here.
0
stevengraffCommented:
OK; sorry, you did say that.

When you run http://servername/reports I assume you see no difference whether or not GoldMine is running? And what about seeing any difference whether you run it on one or the other server? Thinking out loud here, trying to reduce or eliminate any network artifacts.
0
ottobockAuthor Commented:
Yes, it seems to be about the same whether or not Goldmine is involved.

I may not be testing right - but I've noticed it seems a bit faster when connecting to the website from a remote PC or server... (ex: from my client to http://servername/reports)

Hmm. It's tough to tell. But another thing is when I do this from my client, the SVCHOST/RPCSS is no longer taking up CPU as it does when I try this on the local server.
0
ottobockAuthor Commented:
Verified again - on a server which has never accessed this (so I know it's nothing in IE cache).

External server connects to servername/reports and all I see on the Goldmine server is w3wp (typical IIS) appear for a second and go away, and the report is already displayed.

Sound familiar to you? For it to be so slow locally but not so bad remotely?
0
stevengraffCommented:
I hate to say it (again) but it sure sounds "dns-ish."   :)

I might have said it's something related to security, but that wouldn't make it slow... that it's just make it fail completely for one or the other.

Could it be firewall-related? Again, seems like not.

How 'bout if you try it on your iis box? This would be an interesting test because it is a) internal, and b) not, presumably, using any other network components.
0
ottobockAuthor Commented:
You may be right ~ it very well may be something quirky in DNS. I'm just checking here first before asking our domain admins to have a look at DNS.

I thought about authentication too. Doesnt seem right. I even ran a Wireshark to see if I could catch DNS requests and/or requests to the Domain Controller. Seemed OK.

Windows Freiwall is off, and there is no firewall or even a router between the GM app server and the SQL server.

In regards to trying it on the IIS box, this is actually where it is slow. The Goldmine App and SRS is all on the same server, along with IIS. Only the SQL database server is remote (but again, on the same LAN).

So to try and clarify:
On the GM/IIS/SRS server, when opening IE, and navigating to http://GMSERVER/reports, the response is slow.
On any other client or server (at least those tested), when opening IE, and navigating to http://GMSERVER/reports, the response is better.

Thanks again for helping me put some thought into this strange issue!
0
ottobockAuthor Commented:
After way too much troubleshooting, and going down every support avenue we could think of, it was finally found to be the &%#$ Kaspersky antivirus.

It was unbelievable, as during initial troubleshooting, we disabled all the AV processes and rebooted, only to have the same trouble. However, once the AV was fully uninstalled and restarted, the reports were opening fine.

(Supposedly "Critical Fix 11" for Kas AV v6 fixes this, along with scanning exemptions)

So, I'll accept this answer, along with 1 from stevengraff as it mentioned firewall, which is close. ;-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Contact Management

From novice to tech pro — start learning today.