Is there a way to tell MS SQL that a query is not too important and that it can (and should) take its time?
是否有一种方法可以告诉MS SQL查询不太重要，并且它可以(而且应该)慢慢来?
Likewise is there a way to tell MS SQL that it should give higher priority to a query?
同样，是否有一种方法可以告诉MS SQL它应该给查询赋予更高的优先级?
3 个解决方案
#1
18
Not in versions below SQL 2008. In SQL Server 2008 there's the resource governor. Using that you can assign logins to groups based on properties of the login (login name, application name, etc). The groups can then be assigned to resource pools and limitations or restrictions i.t.o. resources can be applied to those resource pools
不是SQL 2008下面的版本。在SQL Server 2008中有资源调控器。使用它，您可以根据登录的属性(登录名、应用程序名等)为组分配登录。然后可以将组分配给资源池，并将限制或限制i.t.o资源应用于这些资源池
#2
2
SQL Server does not have any form of resource governor yet. There is a SET option called QUERY_GOVERNOR_COST_LIMIT but it's not quite what you're looking for. And it prevents queries from executing based on the cost rather than controlling resources.
SQL Server还没有任何形式的资源调控器。有一个名为query_总督_cost_limit的SET选项，但它不是您要寻找的。它阻止基于成本而不是控制资源的查询执行。
#3
1
I'm not sure if this is what you're asking, but I had a situation where a single UI click added 10,000 records to an email queue (lots of data in the body). The email went out over the next several days so it didn't need to be a high priority, in fact it would bog the server every time it happened.
我不确定这是不是你想问的，但我曾经遇到过这样的情况:一个UI单击就会向一个电子邮件队列添加10,000条记录(主体中有很多数据)。这封邮件在接下来的几天里发出了，所以它不需要成为一个高度优先级，事实上它每次发生时都会使服务器崩溃。
I split the procedure into 10,000 individual calls, ran the process on the UI in a different thread (set to low priority) and set it to sleep for a second after running the procedure. It took a while, but I had very granular control over exactly what it was doing.
我将过程分割为10,000个单独的调用，在不同的线程(设置为低优先级)上运行UI上的进程，并在运行过程之后将其设置为休眠一秒钟。这花了一段时间，但我对它所做的事情有非常细致的控制。
btw, this was NOT spam, so don't flame me thinking it was.
顺便说一句，这不是垃圾邮件，所以不要让我以为是垃圾邮件。
#1
18
Not in versions below SQL 2008. In SQL Server 2008 there's the resource governor. Using that you can assign logins to groups based on properties of the login (login name, application name, etc). The groups can then be assigned to resource pools and limitations or restrictions i.t.o. resources can be applied to those resource pools
不是SQL 2008下面的版本。在SQL Server 2008中有资源调控器。使用它，您可以根据登录的属性(登录名、应用程序名等)为组分配登录。然后可以将组分配给资源池，并将限制或限制i.t.o资源应用于这些资源池
#2
2
SQL Server does not have any form of resource governor yet. There is a SET option called QUERY_GOVERNOR_COST_LIMIT but it's not quite what you're looking for. And it prevents queries from executing based on the cost rather than controlling resources.
SQL Server还没有任何形式的资源调控器。有一个名为query_总督_cost_limit的SET选项，但它不是您要寻找的。它阻止基于成本而不是控制资源的查询执行。
#3
1
I'm not sure if this is what you're asking, but I had a situation where a single UI click added 10,000 records to an email queue (lots of data in the body). The email went out over the next several days so it didn't need to be a high priority, in fact it would bog the server every time it happened.
我不确定这是不是你想问的，但我曾经遇到过这样的情况:一个UI单击就会向一个电子邮件队列添加10,000条记录(主体中有很多数据)。这封邮件在接下来的几天里发出了，所以它不需要成为一个高度优先级，事实上它每次发生时都会使服务器崩溃。
I split the procedure into 10,000 individual calls, ran the process on the UI in a different thread (set to low priority) and set it to sleep for a second after running the procedure. It took a while, but I had very granular control over exactly what it was doing.
我将过程分割为10,000个单独的调用，在不同的线程(设置为低优先级)上运行UI上的进程，并在运行过程之后将其设置为休眠一秒钟。这花了一段时间，但我对它所做的事情有非常细致的控制。
btw, this was NOT spam, so don't flame me thinking it was.
顺便说一句，这不是垃圾邮件，所以不要让我以为是垃圾邮件。