assigning process priority to transactions?

I am using ADO 2.8 and SQL server 2000.
Is there any way to assign process priorities to multiple DB connections?
For example, consider that there is a long-time taking transaction and many
short-time transactions running on a server machine. I want to assign low
priority to the long-time transaction to prevent from starvation of
short-time transactions. Of couse, let's assume that there is no locking
influence between them.
Please reply. Thanks in advance.
Regards,
Hyun-jik BaeHi
I am affraid you cannot do that. Perhaps you want to look at SET
LOCK_TIMEOUT command.
"Bae,Hyun-jik" <imays@.NOSPAM.paran.com> wrote in message
news:upjA1nJbFHA.228@.TK2MSFTNGP12.phx.gbl...
> I am using ADO 2.8 and SQL server 2000.
> Is there any way to assign process priorities to multiple DB connections?
> For example, consider that there is a long-time taking transaction and
many
> short-time transactions running on a server machine. I want to assign low
> priority to the long-time transaction to prevent from starvation of
> short-time transactions. Of couse, let's assume that there is no locking
> influence between them.
> Please reply. Thanks in advance.
> Regards,
> Hyun-jik Bae
>

Assigning priority to users or logins

Is there a way in Sql ( 7 and 2000) where I can assign low
priorities to certain users? That would mean that they
would have the last access to resources. If a higher
priority user came along, that would get priority for
resources.
I havent seen anything but was wondering if there is
anything like that.
TIA,
Hack
Jack,
There's no such option in SQL Server, nor can you set priority for a connection.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
> Is there a way in Sql ( 7 and 2000) where I can assign low
> priorities to certain users? That would mean that they
> would have the last access to resources. If a higher
> priority user came along, that would get priority for
> resources.
> I havent seen anything but was wondering if there is
> anything like that.
> TIA,
> Hack
|||Agree with Tibor, but just to add, Ken Henderson presented an extended
stored procedure called xp_setpriority, in his book, "The Guru's Guide to
SQL Server Stored Procedures, XML, and HTML". This xp can do what you are
after.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
Is there a way in Sql ( 7 and 2000) where I can assign low
priorities to certain users? That would mean that they
would have the last access to resources. If a higher
priority user came along, that would get priority for
resources.
I havent seen anything but was wondering if there is
anything like that.
TIA,
Hack
|||Tibor, is your time off, or am I missing something :-) Sure, I had a late
night last night!
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e5t$1e4MEHA.3988@.TK2MSFTNGP09.phx.gbl...
Jack,
There's no such option in SQL Server, nor can you set priority for a
connection.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
> Is there a way in Sql ( 7 and 2000) where I can assign low
> priorities to certain users? That would mean that they
> would have the last access to resources. If a higher
> priority user came along, that would get priority for
> resources.
> I havent seen anything but was wondering if there is
> anything like that.
> TIA,
> Hack
|||Thats a shame. I hope they make that an enhancment in
later versions. I worked with Sybase and that is a really
useful feature. It prevents non technical (reporting)
users from bringing the machine down to its kness.

>--Original Message--
>Jack,
>There's no such option in SQL Server, nor can you set
priority for a connection.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Jack A" <anonymous@.discussions.microsoft.com> wrote in
message news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...[vbcol=seagreen]
low[vbcol=seagreen]
is
>
>.
>

Assigning priority to users or logins

Is there a way in Sql ( 7 and 2000) where I can assign low
priorities to certain users? That would mean that they
would have the last access to resources. If a higher
priority user came along, that would get priority for
resources.
I havent seen anything but was wondering if there is
anything like that.
TIA,
HackJack,
There's no such option in SQL Server, nor can you set priority for a connection.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
> Is there a way in Sql ( 7 and 2000) where I can assign low
> priorities to certain users? That would mean that they
> would have the last access to resources. If a higher
> priority user came along, that would get priority for
> resources.
> I havent seen anything but was wondering if there is
> anything like that.
> TIA,
> Hack|||Agree with Tibor, but just to add, Ken Henderson presented an extended
stored procedure called xp_setpriority, in his book, "The Guru's Guide to
SQL Server Stored Procedures, XML, and HTML". This xp can do what you are
after.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
Is there a way in Sql ( 7 and 2000) where I can assign low
priorities to certain users? That would mean that they
would have the last access to resources. If a higher
priority user came along, that would get priority for
resources.
I havent seen anything but was wondering if there is
anything like that.
TIA,
Hack|||Tibor, is your time off, or am I missing something :-) Sure, I had a late
night last night!
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e5t$1e4MEHA.3988@.TK2MSFTNGP09.phx.gbl...
Jack,
There's no such option in SQL Server, nor can you set priority for a
connection.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
> Is there a way in Sql ( 7 and 2000) where I can assign low
> priorities to certain users? That would mean that they
> would have the last access to resources. If a higher
> priority user came along, that would get priority for
> resources.
> I havent seen anything but was wondering if there is
> anything like that.
> TIA,
> Hack|||Thats a shame. I hope they make that an enhancment in
later versions. I worked with Sybase and that is a really
useful feature. It prevents non technical (reporting)
users from bringing the machine down to its kness.
>--Original Message--
>Jack,
>There's no such option in SQL Server, nor can you set
priority for a connection.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Jack A" <anonymous@.discussions.microsoft.com> wrote in
message news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
>> Is there a way in Sql ( 7 and 2000) where I can assign
low
>> priorities to certain users? That would mean that they
>> would have the last access to resources. If a higher
>> priority user came along, that would get priority for
>> resources.
>> I havent seen anything but was wondering if there
is
>> anything like that.
>> TIA,
>> Hack
>
>.
>

Assigning priority to users or logins

Is there a way in Sql ( 7 and 2000) where I can assign low
priorities to certain users? That would mean that they
would have the last access to resources. If a higher
priority user came along, that would get priority for
resources.
I havent seen anything but was wondering if there is
anything like that.
TIA,
HackJack,
There's no such option in SQL Server, nor can you set priority for a connect
ion.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message news:967a01c43386$8d1a45c0$a
001280a@.phx.gbl...
> Is there a way in Sql ( 7 and 2000) where I can assign low
> priorities to certain users? That would mean that they
> would have the last access to resources. If a higher
> priority user came along, that would get priority for
> resources.
> I havent seen anything but was wondering if there is
> anything like that.
> TIA,
> Hack|||Agree with Tibor, but just to add, Ken Henderson presented an extended
stored procedure called xp_setpriority, in his book, "The Guru's Guide to
SQL Server Stored Procedures, XML, and HTML". This xp can do what you are
after.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
Is there a way in Sql ( 7 and 2000) where I can assign low
priorities to certain users? That would mean that they
would have the last access to resources. If a higher
priority user came along, that would get priority for
resources.
I havent seen anything but was wondering if there is
anything like that.
TIA,
Hack|||Tibor, is your time off, or am I missing something :-) Sure, I had a late
night last night!
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e5t$1e4MEHA.3988@.TK2MSFTNGP09.phx.gbl...
Jack,
There's no such option in SQL Server, nor can you set priority for a
connection.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
> Is there a way in Sql ( 7 and 2000) where I can assign low
> priorities to certain users? That would mean that they
> would have the last access to resources. If a higher
> priority user came along, that would get priority for
> resources.
> I havent seen anything but was wondering if there is
> anything like that.
> TIA,
> Hack|||Thats a shame. I hope they make that an enhancment in
later versions. I worked with Sybase and that is a really
useful feature. It prevents non technical (reporting)
users from bringing the machine down to its kness.

>--Original Message--
>Jack,
>There's no such option in SQL Server, nor can you set
priority for a connection.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Jack A" <anonymous@.discussions.microsoft.com> wrote in
message news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
low[vbcol=seagreen]
is[vbcol=seagreen]
>
>.
>

Assigning a lower priority to some users in SQL Server.

I have a production database used by a web site, and at the same time a group of read-only users who can query the database directly “without the web site”

When one of the users runs a complex query, it slows down the server, and affects the web site.

Is it possible to change the SQL User account or SQL User Group’s priority to low?

You know, the same like in the Task Manager and Windows, I can change a process to low, so it will not affect the important processes, can I do this in SQL Server, and is there any workaround.

in my humble opinion

its the query that needs to be changed

or if your using 2005 you can implement HA feature

such as database snapshot, mirroring etc.

|||Besides the guessing, does anyone have a real solution? Are there sql execution priorities available in SQL Server? Or they are just in the deal databases, like Oracle?|||

if you are so convince that thats the best query you can write and there is no

room for improvement then you can schedule your heavy process to run

on offpeak times.

if that heavy process cross the line of tolerable performance your only option

is to kill that process.

the solution to your problem are

1. send readonly report users to a database snapshot if you are using 2k5

2. schedule the process to run on offpeak times

3. use page caching, fragment caching and most importanctly database caching in asp.net or on your website so you dont rely much on your database

By the way, what does this complex query do? if you would not mind.

how complex is it?