Long running queries with low priority

When you run a process on you Windows machine, you can set the process
priority to 'low', if you don't want the process to take resources away form
other processes running in the same timeframe.
Does anyone know of a similar technique for long running queries on Sql
server 2000 or 2005?
I have a series on long running analytical queries, where the query
execution time is not important, and I donâ't want them to take any resources
away form other queries running in the same timeframe.
I would like a way to tell the Sql server â' â'run this, but if anything else
happens, give that the first priority!â'
Any suggestions?pehuan
I'm afraid you cannot manage it. Perhaps it's time to start an optimization
of the long running queries.
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don?t want them to take any
resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server ? ?run this, but if anything
else
> happens, give that the first priority!?
> Any suggestions?
>
>|||Sorry, but i don´t think there is a way to do this.
Jens SUessmeyer.
"pehuan" <pehuan@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
> form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don't want them to take any
> resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server - "run this, but if anything
> else
> happens, give that the first priority!"
> Any suggestions?
>
>|||I know that DB2 has something called the â'Query Patrollerâ' that can be useed
to proactively and dynamically control the flow of queries against your DB2
database in the following key ways:
a. Define separate query classes for queries of different sizes to better
share system resources among queries and to prevent smaller queries from
getting stuck behind larger ones
b. Give queries submitted by certain users high priority so that these
queries run sooner
c. Automatically put large queries on hold so that they can be cancelled or
scheduled to run during off-peak hours
d. Track and cancel runaway queries
The purpose of this is to allow you to regulate your database's query
workload so that small queries and high-priority queries can run promptly and
your system resources are used efficiently.
I donâ't thing these thing are unreasonable requirements for a database
server in the enterprise. Iâ'm surprised the this seems so hard to do on Sql
server.
Have anyone looked into the features on the Sql server 2005? Any
improvements there?
Are there any of you guys that has been struggling with the same problems â'
and found a solution or a workaround?
"pehuan" wrote:
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I donâ't want them to take any resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server â' â'run this, but if anything else
> happens, give that the first priority!â'
> Any suggestions?
>
>|||Read up this article
http://www.databasejournal.com/features/mssql/article.php/3427581
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:66FFBDA0-98CA-44F3-A4ED-A3B43958C546@.microsoft.com...
> I know that DB2 has something called the ?Query Patroller? that can be
useed
> to proactively and dynamically control the flow of queries against your
DB2
> database in the following key ways:
> a. Define separate query classes for queries of different sizes to better
> share system resources among queries and to prevent smaller queries from
> getting stuck behind larger ones
> b. Give queries submitted by certain users high priority so that these
> queries run sooner
> c. Automatically put large queries on hold so that they can be cancelled
or
> scheduled to run during off-peak hours
> d. Track and cancel runaway queries
> The purpose of this is to allow you to regulate your database's query
> workload so that small queries and high-priority queries can run promptly
and
> your system resources are used efficiently.
> I don?t thing these thing are unreasonable requirements for a database
> server in the enterprise. I?m surprised the this seems so hard to do on
Sql
> server.
> Have anyone looked into the features on the Sql server 2005? Any
> improvements there?
> Are there any of you guys that has been struggling with the same
problems ?
> and found a solution or a workaround?
>
> "pehuan" wrote:
> > When you run a process on you Windows machine, you can set the process
> > priority to 'low', if you don't want the process to take resources away
form
> > other processes running in the same timeframe.
> >
> > Does anyone know of a similar technique for long running queries on Sql
> > server 2000 or 2005?
> >
> > I have a series on long running analytical queries, where the query
> > execution time is not important, and I don?t want them to take any
resources
> > away form other queries running in the same timeframe.
> >
> > I would like a way to tell the Sql server ? ?run this, but if anything
else
> > happens, give that the first priority!?
> >
> > Any suggestions?
> >
> >
> >|||Hi Uri,
Thanks for the article â' however there question here is not whether spâ's or
queries can be executed asynchronously from the application, but rather how
the machine resource consumption of particular queries can be controlled so
the wonâ't affect the execution time of other queries or spâ's running in
parallel.
Cheers
pehuan
"Uri Dimant" wrote:
> Read up this article
> http://www.databasejournal.com/features/mssql/article.php/3427581
> "pehuan" <pehuan@.discussions.microsoft.com> wrote in message
> news:66FFBDA0-98CA-44F3-A4ED-A3B43958C546@.microsoft.com...
> > I know that DB2 has something called the â'Query Patrollerâ' that can be
> useed
> > to proactively and dynamically control the flow of queries against your
> DB2
> > database in the following key ways:
> >
> > a. Define separate query classes for queries of different sizes to better
> > share system resources among queries and to prevent smaller queries from
> > getting stuck behind larger ones
> >
> > b. Give queries submitted by certain users high priority so that these
> > queries run sooner
> >
> > c. Automatically put large queries on hold so that they can be cancelled
> or
> > scheduled to run during off-peak hours
> >
> > d. Track and cancel runaway queries
> >
> > The purpose of this is to allow you to regulate your database's query
> > workload so that small queries and high-priority queries can run promptly
> and
> > your system resources are used efficiently.
> >
> > I donâ't thing these thing are unreasonable requirements for a database
> > server in the enterprise. Iâ'm surprised the this seems so hard to do on
> Sql
> > server.
> >
> > Have anyone looked into the features on the Sql server 2005? Any
> > improvements there?
> >
> > Are there any of you guys that has been struggling with the same
> problems â'
> > and found a solution or a workaround?
> >
> >
> > "pehuan" wrote:
> >
> > > When you run a process on you Windows machine, you can set the process
> > > priority to 'low', if you don't want the process to take resources away
> form
> > > other processes running in the same timeframe.
> > >
> > > Does anyone know of a similar technique for long running queries on Sql
> > > server 2000 or 2005?
> > >
> > > I have a series on long running analytical queries, where the query
> > > execution time is not important, and I donâ't want them to take any
> resources
> > > away form other queries running in the same timeframe.
> > >
> > > I would like a way to tell the Sql server â' â'run this, but if anything
> else
> > > happens, give that the first priority!â'
> > >
> > > Any suggestions?
> > >
> > >
> > >
>
>|||On a multi-processor box, you can use OPTION(MAXDOP 1) with the query, so
that it only uses 1 processor, but that is as far as you can get.
--
Jacco Schalkwijk
SQL Server MVP
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
> form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don't want them to take any
> resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server - "run this, but if anything
> else
> happens, give that the first priority!"
> Any suggestions?
>
>