Is there a way to throttle or limit the amount of sql requests either by IP address or username?

Question

Is there a way to throttle or limit the amount of sql requests either by IP address or username?

ghurty

SSC Veteran

Points: 241
More actions
January 11, 2015 at 10:10 am

#313618

Is there a way to throttle or limit the amount of sql requests either by IP address or username?
Thank you

Viewing 9 posts - 1 through 9 (of 9 total)

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

Grant Fritchey SSC Guru Points: 399299 More actions · Answer 1

You should take a look at the Resource Governor.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004650 More actions · Answer 2

ghurty (1/11/2015)
Is there a way to throttle or limit the amount of sql requests either by IP address or username?
Thank you

I have found that such throttling is to control resource usage from queries that are simply using too many resources for what they're supposed to do. Using things like Resource Governor will only serve to make them run longer because they've been "throttled down".

My recommendation would be to first look at the queries for tuning opportunities. Many such queries can be made to use literally thousands of time fewer resources and the equivalent reduction in duration. If users are designing and running their own queries, show them how to fix them or provide them with stored procedures that work better.

--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)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

ghurty (1/11/2015)
Is there a way to throttle or limit the amount of sql requests either by IP address or username?
Thank you

Depending on what they are doing you could perhaps store them in a queue of some flavor (SQL Service Broker, MSMQ, others) and then pop them off and execute in a controlled manner.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004650 More actions · Answer 4

Or just make them run better. 😉 Then there won't be a problem that you have to throttle or manage.

--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)

Grant Fritchey SSC Guru Points: 399299 More actions · Answer 5

Yeah, tuning the queries so that they run correctly is always a good option.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Jeff Moden (1/11/2015)
Or just make them run better. 😉 Then there won't be a problem that you have to throttle or manage.

Not true Jeff. You can absolutely receive too many perfectly-tuned queries (even trivial ones) and overwhelm a SQL Server. And bigger hardware (the normal solution for that scenario may not be viable at the moment for some reason.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004650 More actions · Answer 7

TheSQLGuru (1/12/2015)
Jeff Moden (1/11/2015)
Or just make them run better. 😉 Then there won't be a problem that you have to throttle or manage.
Not true Jeff. You can absolutely receive too many perfectly-tuned queries (even trivial ones) and overwhelm a SQL Server. And bigger hardware (the normal solution for that scenario may not be viable at the moment for some reason.

At best then, throttling such queries should be a very temporary solution because such throttling will make people wait. Ostensibly, the reason why so many queries are being fired is because they need to be.

--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)

Eric M Russell SSC Guru Points: 125600 More actions · Answer 8

The "query governor cost limit" server option (and session override setting) has been around since at least v2000. You basically specify the maximum number of seconds, and then SQL Server compares that to the estimated time required to complete a query. It will actually abort a submitted query, if it's estimate exceeds the limit.

But it's not commonly used. Has anyone here ever made good use of this server setting?

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho