Report hangs 4 CPU server

Forgive my lack of knowledge on SQL here...

We have a SQL server, version 7, that has 4 CPU's. Some of our staff have reports that must be run at random times before cutting checks etc. and these reports bog down the servers CPU's big time, enough so that we can not access the server via Enterprise Manager and at times not even through Query Analyzer. During this time of course other users can not do standard work of saving or accessing the database. Our Processor queue length never seems to go over 1 now. (We recently went from 2 to 4 CPU's do to a 1.4 average queue length).

I have heard that is is possible to limit the number of CPU's that certain query's or events can use. What is the proper terminology for this and can a person do this with limited knowledge? If this can be done during "working hours" so that during off hours query's can use all CPU's, even better.

Thanks,
Mike GilbertYou probably want to think HARD before you do this, but you can set the Max Degree of Parallelism (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_7h9q.asp).

-PatP|||How much ram does this machine have? Is upgrading to SQL2000 an option? What are the processor speeds?|||This is a typical situation, where everyone looks at the DBA for the solution. My gutt feeling would be to run the SQL Profilier, capture the query and do an analysis of the Query. Before thinking about changing the SQL Server HW set up, make sure that you do the following:
1. Test the Query running for the report
2. Run update Stats
3. Recompile the Query
4. Check for the Locks

Also how Big is your databases?

Report generation is consuming 100% of CPU client side.

Hi,

I have a problem while rendering a report which returns around 5000 rows. At first the server is busy to process the request, when the server is done the rendering client side takes 100% of CPU and never displays the result ("IE is not responding"). It seems that the ReportViewer has trouble to handle the server response.

Do you have any idea why ?

Sbastien.

It appeared that the problem is a paging issue. The report contains groups and by default the top level is collapsed, when we try to expand a level that contains many rows thoses rows are all displayed on a single page. That's why IE is consuming 100% of the CPU to display all the rows.

How can I change that the paging is working for the sub groups displayed ?

|||

I found the answer in BOL:

"The HTML and Excel rendering extensions are not oriented to physical pages. Furthermore, the HTML rendering extension is interactive, meaning that user actions in a report can trigger additional processing that causes a report to expand horizontally or vertically to accommodate additional content. You cannot precisely control how reports viewed through these rendering extensions will paginate if the report contains interactive features."

Report generation is consuming 100% of CPU client side.

Hi,

I have a problem while rendering a report which returns around 5000 rows. At first the server is busy to process the request, when the server is done the rendering client side takes 100% of CPU and never displays the result ("IE is not responding"). It seems that the ReportViewer has trouble to handle the server response.

Do you have any idea why ?

Sbastien.

It appeared that the problem is a paging issue. The report contains groups and by default the top level is collapsed, when we try to expand a level that contains many rows thoses rows are all displayed on a single page. That's why IE is consuming 100% of the CPU to display all the rows.

How can I change that the paging is working for the sub groups displayed ?

|||

I found the answer in BOL:

"The HTML and Excel rendering extensions are not oriented to physical pages. Furthermore, the HTML rendering extension is interactive, meaning that user actions in a report can trigger additional processing that causes a report to expand horizontally or vertically to accommodate additional content. You cannot precisely control how reports viewed through these rendering extensions will paginate if the report contains interactive features."