Central site Reporting Point slowness/Timeout

myITforum.com Community Forum

Home Forums Blogs Live Support chat Search Articles Wiki FAQ Email Lists Register Login My Profile Inbox Address Book My Subscription My Forums

Photo Gallery Member List Search Calendars FAQ Ticket List Log Out

All Forums RSS Feed Subscription:

Central site Reporting Point slowness/Timeout

View related threads: (in this forum | in all forums)

Logged in as: Guest

Printable Version

All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003 >> Central site Reporting Point slowness/Timeout

Page: [1]

Message

<< Older Topic Newer Topic >>

Central site Reporting Point slowness/Timeout - 10/29/2008 2:50:25 PM

drake21

Posts: 22
Score: 0
Joined: 9/6/2006
Status: offline

We have admins attempting to run SMS reports on the Central site(with approx 100k clients) and the reports are either timing out or generally taking a long time to complete.(DB+SMS+Reporting Point on the same box) We have performed all the recommendations to tweak IIS settings in the Registry. What is everyone doing to optomize performance on the Central site Reporting Point ?...How can I get to the bottom of where the bottleneck is? We can't have many users attempting to run these Reports and have it timeout.

Thanks

Post #: 1

RE: Central site Reporting Point slowness/Timeout - 10/29/2008 3:14:58 PM

jnelson993

Posts: 959
Score: 132
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

There are so many things that could be causing this. Could be an overworked server (100K clients means a lot of file I/O, plus a lot of SQL I/O to get all those status messages and inventory files into the DB), could be that someone's got a poorly written query that's running and locking it for others. Could be that the reports they're trying to run aren't well designed (in my experience, this is actually the most likely), could be that the server is out of memory, could be that the server has Virus Scan turned on without exceptions on the SMS folders, the list goes on.

Now, the first thing I'd say is that the reporting point that everyone uses shouldn't be on the central site, unless by central you mean a site above your central administration site who's only purpose is to roll all data up for reports...it would technically be a central server with SMS and SQL on it, but you don't actually push packages from it or do day to day administration with it. Otherwise, if you're doing day to day admin and all the clients are reporting up to it and all the admins are doing stuff on it, it's going to be plenty busy that it's not going to like doing reports too. Another server above it or a replicated database on a different server would be the best place for general ad-hoc reporting...that's just my opinion. When you get to 100K+, you really have a lot of processing going on and you have to do things a little differently than someone with 5K

Now, to determine what the actual problem is, you've gotta find out where the bottleneck is...is it memory? Disk? SMS? SQL? I have a post out there on what perfmon counters I use to check out a system
http://myitforum.com/cs2/blogs/jnelson/archive/2008/03/26/114267.aspx

Look to perfmon first, then look at the SQL Activity monitor to see if you have anyone connected with ODBC or something that may be doing stuff you didn't know about.

Let us know how it goes.

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to drake21)

Post #: 2

RE: Central site Reporting Point slowness/Timeout - 10/30/2008 7:59:18 AM

drake21

Posts: 22
Score: 0
Joined: 9/6/2006
Status: offline

The slowness/timeout seems to be related to IIS because we can run WQL queries and queries directly in SQL and have the queries return. I ran perfmon and the bottleneck seems to be mainly Disk IO(CPU+Memory mid to low) What about setting up a System Center Reporting Manager or offloading the Reporting Point off of the Central site? We have Primary Child sites reporting up to a single Central site(for central administration and Reporting). SQL 2005 and SMS on same box. /PAE switch enabled with 8gb memory. I thought only the Management Point data could be replicated?(Reporting Point would still go back to the SMS Box)

(in reply to drake21)

Post #: 3

RE: Central site Reporting Point slowness/Timeout - 10/30/2008 10:58:55 PM

jnelson993

Posts: 959
Score: 132
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

Well if the queries return results fairly quickly then I wonder if it's not because there's just a lot of rows that need to spool to the client. Our reporting server can handle some pretty big queries (let's say there's 10MB worth of data that comes back) and serve them up pretty fast using SQL Management Studio, but when you go to serve that up using IIS, it has to wrap each column in each row with HTML and it might need to spool 100MB or 500MB and THAT's where the problem lies. I had a 32K row report once that included some long columns like the full OU which has up to 512 characters in that one column alone. It was taking a long time and when I looked at Task Manager, I noticed it was using over 700MB and growing. I stopped it after 1GB was consumed because it was tipping over my desktop.

So it could be something that simple. If your report has a lot of rows and each row has a lot of columns or columns with a lot of characters, it's just going to take a long time to spool all that stuff. If that's the case, then you might have to make summary reports that drill-down into detail reports and perhaps group them by OU or subnet or something to limit the number of detail rows that come back. Cuz honestly, if you hand someone a list of 100K machines, do you honestly think they're going to be able to do something with that? They're going to have to manipulate and massage the data into manageable chunks anyway and then delegate the work to various people, etc. However, if you give them a URL that allows them to put in an OU or a subnet or some other means to filter the results into manageable chunks, they can give that URL to their people so they can work on stuff. Make sense?

Now, if it's NOT that and it's really just the disk throughput issue having to first spool up the data using SQL, and then buffer it all in IIS and push it to a web browser, then having a separate reporting point or using reporting services from a different box should help.

Yes, only management point data replication is the only supported configuration, but that doesn't mean it's a bad thing to do if you know what you're doing.

Perhaps the easiest thing to try would be to have the reporting point be on a different server so it can offload the IIS memory and traffic (as long as you have a pretty quick network connection between it and the primary). If that doesn't fix anything, then you might need to look at your reports. If small reports are still slow, then we need to get cr@zy.

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to drake21)

Post #: 4