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?
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?
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?
ASKER
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!
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!
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.
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.
ASKER
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.
TCPIP is definitely being used for all SQL communication. Named pipes is also enabled, but not utilized here.
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.
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.
ASKER
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.
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.
ASKER
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!