Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gmaxwel
Starting Member
1 Post |
 Posted - 2002-03-15 : 11:25:49
|
| Question??I have several queries that can take 4-5 min's to complete, however everytime I execute the queries the server's 4 processors peg to around 95%. After about a min or two of this the client (VB) applications begin to time out with communications (ADO with 60 sec timeout). Is there any way to limit the amount of system resources that a query can utilize?Thanks,Greg Maxwell |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-15 : 11:52:14
|
| There is a setting in Enterprise Manager, if you right-click on the server registration and choose Properties, then go to the Server Settings tab.There's a check box and spinner that lets you throttle the server (I love that term! felt like doing it a few times... :) It's actually called the query governer. You can try settings that will restrict how many CPU resources are thrown at the query...unfortunately this will also (probably) take longer to execute.Also, on the Processor tab is the setting for "minimum query plan threshold" for parallel execution. You may need to tweak both of these settings to find a balance that fixes the problem.You really need to read up on these settings in Books Online, I have only the most basic knowledge of them and never used them in a situation like yours.I do have some questions though: does your query use cursors by any chance? Does it manipulate a lot of rows? How about the memory usage? If you could post your query it might help us figure out a way to alleviate the problem.HTH |
 |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-03-15 : 12:22:30
|
| There is also a Max degree of parallelism option in sp_configure. This will allow you to set the max # of processors to use in parallel.You can also use the MAXDOP option on the individual query.If a parallel plan is selected, SQL Server pegs the processors until the query completes. This is why a parallel plan is never selected (Should never be anyway) unless the user load is lower than the number of procs.-Chad |
|
|
|
jfuex
Starting Member
29 Posts |
Posted - 2007-06-08 : 12:30:10
|
| I have a similiar issue. We run an web application against a SQL database, but also need data anlayts to perform some data analysis directly in the back end of the application. Every so often a query form a DA will start causing major timeout issues in the web app which is a very bad thing. I'd love to be able to say that the web app gets top priority even if it makes the back-end queries run much more slowly. Throttling the whole server doesn't seem to help because I don't want to completely kill those back end queries, just make them slow down and stop using so many darn resources if the server is really busy yielding to customer activity in the DB.Ideas?ooo Oooo O |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2007-06-10 : 23:36:19
|
| This is more likely caused by problems in the code... do you have "triangular joins", cursors, while loops, improperly constructed updates, explicit long winded trans actions, recursion, etc, etc?--Jeff Moden |
|
|
|
|
|
|
|
|
Query Pegs Server Processor's