Connection Throttle

I have an application that where some of the queries hit the server hard (I
cannot rewrite the app). Some of the queries take 5 or more minutes to run
and during this time the CPU is pegged and SQL no longer services other
queries until the the hog query is complete. I there a way I can throttle a
connection - like not allow a connection to use more than 25% of the CPU?
Thanks,
TroyTry setting the MAXDOP to something less than the total number of
processors. But it sounds like you probably need to just tune your query.
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>I have an application that where some of the queries hit the server hard (I
>cannot rewrite the app). Some of the queries take 5 or more minutes to run
>and during this time the CPU is pegged and SQL no longer services other
>queries until the the hog query is complete. I there a way I can throttle
>a connection - like not allow a connection to use more than 25% of the CPU?
> Thanks,
> Troy
>|||Troy Murphy wrote:
> I have an application that where some of the queries hit the server
> hard (I cannot rewrite the app). Some of the queries take 5 or more
> minutes to run and during this time the CPU is pegged and SQL no
> longer services other queries until the the hog query is complete. I
> there a way I can throttle a connection - like not allow a connection
> to use more than 25% of the CPU?
> Thanks,
> Troy
Maybe you could add appropriate indexes to the database to help the
query run more efficiently without worrying about touching the
application.
David Gugick
Imceda Software
www.imceda.com|||Thanks, I will look into that. The queries are fairly optimized, but we
have 10's of millions of records. Even a simple select sum() takes several
minutes. However the main culprit is a morning update against a few hundred
thousand records that keeps the software unusable during that time.
Troy
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uezVitfsEHA.3572@.tk2msftngp13.phx.gbl...
> Try setting the MAXDOP to something less than the total number of
> processors. But it sounds like you probably need to just tune your
> query.
> --
> Andrew J. Kelly SQL MVP
>
> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
> news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>|||Several minutes for a simple SUM() is pretty slow. I suspect you lack
enough memory and have the wrong disk configurations. Any chance you can
create a covering index for that SUM()? As for the updates are you trying
to do all of them in one transaction? If so you may want to break them up
into smaller batches. It's pretty easy to lop and update say 10,000 rows at
a time and will usually be faster and certainly less intrusive.
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:Onn12wisEHA.2780@.TK2MSFTNGP09.phx.gbl...
> Thanks, I will look into that. The queries are fairly optimized, but we
> have 10's of millions of records. Even a simple select sum() takes
> several minutes. However the main culprit is a morning update against a
> few hundred thousand records that keeps the software unusable during that
> time.
> Troy
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uezVitfsEHA.3572@.tk2msftngp13.phx.gbl...
>|||On Thu, 14 Oct 2004 07:12:17 -0700, "Troy Murphy"
<thm92630@.hotmail.com> wrote:
>I have an application that where some of the queries hit the server hard (I
>cannot rewrite the app). Some of the queries take 5 or more minutes to run
>and during this time the CPU is pegged and SQL no longer services other
>queries until the the hog query is complete. I there a way I can throttle
a
>connection - like not allow a connection to use more than 25% of the CPU?
No.
Better would be a way to assign a lower priority to the offending
connection/query.
But the thinking is that this kind of thing is very dangerous, as the
throttled thread may be holding resources that block other users.
Unfortunately, you can get conditions where the range of the heavy
query is logically limited, it just soaks up all the database
capability.
If your server is otherwise large enough memory-wise and your disk
usage is appropriate (log files on separate disk from data files) and
you have two or more processors, then short of recoding, which you say
you can't do, that's it.
J.|||Thanks to all for the feedback. The SUM() is just an example and the
application is a 3rd party solution so changing the source would be
difficult to say the least.
Still, the behavior seems to be that only a single query will run at a time
and queries from other connections are on hold while the query is running.
I played with the MAXDOP as well as the CURSOR THRESHOLD setting with out
any changes in behavior (reboot of server after each change).
I can reproduce this behavior using query analyzer from 2 workstations where
WS1 will run a long query and while that is running WS2 will run a very
quick query. While WS1 is running WS2 has the world turning, but as soon as
WS1 completes, the result set from WS2 is served.
The server configuration is a dual processor with hyper-threading and 2 gb
RAM. Unfortunately the drive is un-striped IDE. Windows 2003 and SQL
Server 2000 Enterprise Edition. Running Performance Monitor on Processor
Time and SqlServer Cache Pages does not indicate an overload.
Troy
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O9GpoIksEHA.2320@.TK2MSFTNGP12.phx.gbl...
> Several minutes for a simple SUM() is pretty slow. I suspect you lack
> enough memory and have the wrong disk configurations. Any chance you can
> create a covering index for that SUM()? As for the updates are you trying
> to do all of them in one transaction? If so you may want to break them up
> into smaller batches. It's pretty easy to lop and update say 10,000 rows
> at a time and will usually be faster and certainly less intrusive.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
> news:Onn12wisEHA.2780@.TK2MSFTNGP09.phx.gbl...
>|||Troy,
What you are describing sounds more like a blocking issue and not so much
CPU. If you set MAXDOP to 1 and the query were to use all of the first
processor the others would still be available. Have you looked at sp_who2
to see if any blocking is going on while this is happening? It also still
can be that the tables are poorly indexed. Even though the app is 3rd party
you can most likely add indexes.
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:ufFY8ttsEHA.3556@.TK2MSFTNGP10.phx.gbl...
> Thanks to all for the feedback. The SUM() is just an example and the
> application is a 3rd party solution so changing the source would be
> difficult to say the least.
> Still, the behavior seems to be that only a single query will run at a
> time and queries from other connections are on hold while the query is
> running. I played with the MAXDOP as well as the CURSOR THRESHOLD setting
> with out any changes in behavior (reboot of server after each change).
> I can reproduce this behavior using query analyzer from 2 workstations
> where WS1 will run a long query and while that is running WS2 will run a
> very quick query. While WS1 is running WS2 has the world turning, but as
> soon as WS1 completes, the result set from WS2 is served.
> The server configuration is a dual processor with hyper-threading and 2 gb
> RAM. Unfortunately the drive is un-striped IDE. Windows 2003 and SQL
> Server 2000 Enterprise Edition. Running Performance Monitor on Processor
> Time and SqlServer Cache Pages does not indicate an overload.
> Troy
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9GpoIksEHA.2320@.TK2MSFTNGP12.phx.gbl...
>|||Ok, I reproduced the scenerio described below and ran SP_WHO (I was actually
expecting this to wait for the query to complete, but it ran as expected).
Anyway, the user who was running the long query showed a status of 'Ready to
Run' while executing and the process that was waiting in limbo showed as
'Sleeping'. All values in the BLK field were 0 and each had a unique SPID.
I had reset all the Configuration settings back to default. We exported the
database to another server (single processor, 1 gb Ram, SCSI) and that
server did not seem to have this problem in that the small queries ran as
expected while the large query was still running. HELP!!!!
Troy
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uPahbQusEHA.2072@.tk2msftngp13.phx.gbl...
> Troy,
> What you are describing sounds more like a blocking issue and not so much
> CPU. If you set MAXDOP to 1 and the query were to use all of the first
> processor the others would still be available. Have you looked at sp_who2
> to see if any blocking is going on while this is happening? It also still
> can be that the tables are poorly indexed. Even though the app is 3rd
> party you can most likely add indexes.
> --
> Andrew J. Kelly SQL MVP
>
> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
> news:ufFY8ttsEHA.3556@.TK2MSFTNGP10.phx.gbl...
>|||What about disk queues? Is the first process creating a massive disk queue?
sp_who would not be affected as much by that. What about wait types? Try
running DBCC SQLPERF(Waitstats) or select * from sysprocesses and see what
the wait types and times are for the 2 spids.
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:efIBI7usEHA.3788@.TK2MSFTNGP15.phx.gbl...
> Ok, I reproduced the scenerio described below and ran SP_WHO (I was
> actually expecting this to wait for the query to complete, but it ran as
> expected). Anyway, the user who was running the long query showed a status
> of 'Ready to Run' while executing and the process that was waiting in
> limbo showed as 'Sleeping'. All values in the BLK field were 0 and each
> had a unique SPID. I had reset all the Configuration settings back to
> default. We exported the database to another server (single processor, 1
> gb Ram, SCSI) and that server did not seem to have this problem in that
> the small queries ran as expected while the large query was still running.
> HELP!!!!
> Troy
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uPahbQusEHA.2072@.tk2msftngp13.phx.gbl...
>