Limiting processing power for a specific query

Question

Limiting processing power for a specific query

J Vanderhorst

SSC Rookie

Points: 42
More actions
April 23, 2010 at 7:44 am

#219601

I have a query (within an SSRS report) that does some very complex calculations across a large amount of data. This report takes a considerable amount of time to run (around 5 minutes). The amount of time is not the problem. The problem is that the SQL Server ends up being completely bogged down while this report runs and for a while afterwards as well. The other users (100+) that are accessing the database through my application are pretty much unable to use the system because the server's CPU usage is up around 100% until the report is finished. I was wondering if someone could let me know if it is possible to put a limit on how much processing power a specific report/query/user can use so that the rest of the application is still usable. Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 1

I'm not aware of a way of restricting CPU percentage but if it's a multi-processor machine you can set the maxdop for the query.

J Vanderhorst SSC Rookie Points: 42 More actions · Answer 2

Thanks. That will probably work for around 50% of my clients. For the others that do not have multiple processors, do you have any other suggestions?

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 3

That was my only thought. Hopefully someone else comes along with another idea.

Steve Jones - SSC Editor SSC Guru Points: 736352 More actions · Answer 4

Nothing in SQL 2005. In 2008 there is the Resource governer, which can help if you setup a separate login for that report.

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 5

J Vanderhorst (4/23/2010)
Thanks. That will probably work for around 50% of my clients. For the others that do not have multiple processors, do you have any other suggestions?

Yes... let's review...

The amount of time is not the problem. The problem is that the SQL Server ends up being completely bogged down while this report runs and for a while afterwards as well.

Actually, the amount of time [font="Arial Black"]IS[/font] the problem. Poorly written queries that use a lot of resources take a lot of time. There's only one fix for this... the query must be rewritten for performance. Yes, an index here and there may help, but 90% of the time on such queries, the problem is that indexes can't actually be used by the query because they're written so poorly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)