MS SQL Sever - stop and resume a job depending on CPU load

olegy · Post by **olegy** » 25 Dec 2003 06:15

В сущности хотел бы посоветоваться- может, кто сталкивался.
Есть job - процесс, который может вертеться днями и как закончится, должен стартовать опять - то есть должен идти вечно.Процесс ест очень много ресурсов- 50-100% CPU load. Естественно, подобный процесс конфликтирует с регулярной sql server активностью ( в смысле борьбы за ресурсы, не deadlock-и). Поставлена задача - позволить этой job исполняться только тогда, когда другие процессы не идут ( это относительно легко- опеделяешь критерий idle cpu- и стартуешь эту job при idle cpu). Дальше хуже. job должна заметить, что другие процессы начали активность ( поглощают cpu cycles) и если эта активность продолжается дольше, скажем, минуты, приостановить свое исполнение и ждать, когда регулярная активность закончится и resume себя. И так вечно.
Если, кто сталкивался с подобной проблемой, поделитесь.
Решения "в лоб" не вижу - так как эта job может съедать и 100% CPU time- как тогда определить, что другие процессы тоже хотят своего куска пирога.

zVlad · Post by **zVlad** » 25 Dec 2003 23:15

А что нет такой возможности назначить тому заданию низкий приоритет чтобы он не мог монополизировать использование CPU?

olegy · Post by **olegy** » 26 Dec 2003 19:02

zVlad wrote:А что нет такой возможности назначить тому заданию низкий приоритет чтобы он не мог монополизировать использование CPU?

вроде нельзя. Спасибо.

CTAC_P · Post by **CTAC_P** » 26 Dec 2003 19:55

Тормозить процесс чререз защелки.

olegy · Post by **olegy** » 26 Dec 2003 20:36

В сущности я нашел как понизить приоритет job на sql server
- через extended procedure как:

Code: Select all

/***********************************************************************
Copyright (c) 2000, Microsoft Corporation
All Rights Reserved.
***********************************************************************/
// This is an example of an extended procedure DLL built with Open Data
// Services. The functions within the DLL can be invoked by using the extended
//      stored procedures support in SQL Server.  To register the functions 
// and allow all users to use them run the ISQL script XP_ODBC.SQL.
//
// For further information on Open Data Services refer to the Microsoft Open 
// Data Services Programmer's Reference.
//
//  The extended procedures implemented in this DLL is:
//
//  xp_setpriority -- Used to show the creation of a new connection to
//  SQL Server using ODBC that is bound to the initial client connection
#include <windows.h>
#include <tchar.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h>
#include <srv.h>

// Miscellaneous defines.
#define XP_NOERROR              0
#define XP_ERROR                1

// Extended procedure error codes.
#define SRV_MAXERROR            50000
#define XP_SETPRIORITY_ERROR          SRV_MAXERROR + 1

#define REMOTE_FAIL             4002

void handle_odbc_err(PSTR szODBCApi,
   SQLRETURN sret,
   DBINT msgnum,
   SQLHANDLE herror,
   SQLSMALLINT htype,
   SRV_PROC* srvproc);

// It is highly recommended that all Microsoft® SQL Server (7.0 
// and greater) extended stored procedure DLLs implement and export 
// __GetXpVersion. For more information see SQL Server 
// Books Online
ULONG __GetXpVersion()

{
    return ODS_VERSION;
}


// xp_setpriority
//    Returns the result of the SQL statement
//              select * from <szTable>
//
// Parameters:
//    srvproc - the handle to the client connection that 
//    got the SRV_CONNECT.
//
// Returns:
//    XP_NOERROR
//    XP_ERROR
//
// Side Effects:
//    Returns messages and/or a result set to client. 
RETCODE xp_setpriority(srvproc)
SRV_PROC *srvproc;
{

   int threadpriority = THREAD_PRIORITY_TIME_CRITICAL;

    int         nParams;
    DBINT       paramtype;

   TCHAR      szPriority[20] = "";
   BYTE  pbType;
   ULONG  pcbMaxLen;
   ULONG  pcbActualLen;
//BYTE * pbData,
   BOOL  pfNull;

    RETCODE     rcXP = XP_ERROR;        // Assume failure until shown otherwise.

    // Get number of parameters.
    nParams = srv_rpcparams(srvproc);   

    // Check number of parameters
    if (nParams != 1) {
       // Send error message and return
        srv_sendmsg(srvproc, SRV_MSG_ERROR, XP_SETPRIORITY_ERROR, SRV_INFO, (DBTINYINT)0,
            NULL, 0, 0, "Error executing extended stored procedure: Invalid number of parameters.",
            SRV_NULLTERM);

        // A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the
        // result set of an Extended Stored Procedure.
        srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
        return(XP_ERROR);
        }

    // If parameter is not varchar (should be HIGHEST/LOWEST/etc.), send an
    // error and return.
    paramtype = srv_paramtype(srvproc, 1);
    if (paramtype != SRVVARCHAR) {
        srv_sendmsg(srvproc, SRV_MSG_ERROR, XP_SETPRIORITY_ERROR, SRV_INFO, (DBTINYINT)0,
            NULL, 0, 0,
            "Error executing extended stored procedure: Invalid Parameter Type",
            SRV_NULLTERM);

        // A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the
        // result set of an Extended Stored Procedure.
        srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
        return(XP_ERROR);
        }

      // Terminate parameter string with NULL.
      srv_paraminfo(srvproc,1,&pbType, &pcbMaxLen, &pcbActualLen, szPriority, &pfNull);
//      memcpy(szDB, srv_paramdata(srvproc, 3),   srv_paramlen(srvproc, 3));
      szPriority[pcbActualLen] = '\0';

       if (stricmp(szPriority,"HIGHEST")==0)
      {
         threadpriority=THREAD_PRIORITY_TIME_CRITICAL;
      }
      else if (stricmp(szPriority,"HIGH")==0)
      {
         threadpriority=THREAD_PRIORITY_ABOVE_NORMAL;
      }

      else if (stricmp(szPriority,"LOW")==0)
      {
         threadpriority=THREAD_PRIORITY_BELOW_NORMAL;
      }
      else if (stricmp(szPriority,"LOWEST")==0)
      {
         threadpriority=THREAD_PRIORITY_LOWEST;
      }

      else if (stricmp(szPriority,"NORMAL")==0)
      {
         threadpriority=THREAD_PRIORITY_NORMAL;
      }

      SetThreadPriority(GetCurrentThread(),threadpriority);

   srv_senddone(srvproc, SRV_DONE_MORE, (DBUSMALLINT)0, (DBINT)0);

    // We got here successfully, let the client know.

   return XP_NOERROR ;

   
}


// HANDLE_ODBC_ERR
//  This routine is called to send messages to clients when an ODBC
//  function returns what could be considered an error (e.g., SQL_ERROR,
//  SQL_INVALID_HANDLE).
//
// Parameters:
//  szODBCApi   - The name of the failing function.
//  srODBAPI    - The SQLRETURN of the failing function.
//  msgnum      - The ODS user message code.
//  herror      - The ODBC handle involved in the error.
//  htype       - The ODBC handle type.
//  srvproc     - Contains additional client information.
//
// Returns:
//      none
//
void handle_odbc_err(PSTR szODBCApi,
   SQLRETURN sret,
   DBINT msgnum,
   SQLHANDLE herror,
   SQLSMALLINT htype,
   SRV_PROC* srvproc)
{
    SQLTCHAR    szErrorMsg[SQL_MAX_MESSAGE_LENGTH + 1];
    SQLSMALLINT cbErrorMsg;
    SQLSMALLINT nRec = 1;

    // If sret is SQL_SUCCESS, return without doing anything
    if (sret == SQL_SUCCESS)
        return;

    while (
        SQLGetDiagField(htype, herror, nRec++, SQL_DIAG_MESSAGE_TEXT,
        szErrorMsg, SQL_MAX_MESSAGE_LENGTH, &cbErrorMsg) 
        == SQL_SUCCESS)
        {
        // If sret is SUCCESS_WITH_INFO, send as "message" (severity 
        // <= 10, we use zero), else send to client as "error"
        // (severity > 10, we use 11).
        srv_sendmsg(srvproc, 
            SRV_MSG_INFO, 
            msgnum, 
            (DBTINYINT) (sret == SQL_SUCCESS_WITH_INFO ? 0 : 11),
            (DBTINYINT) 1, 
            NULL, 
            0, 
            0, 
            szErrorMsg,
            SRV_NULLTERM);
        }
}

потом скопировать dll в BINN directory
и

Code: Select all

use master
go

sp_addextendedproc 'xp_setpriority', 'xp_setpriority.dll'
go

/*
exec sp_dropextendedproc 'xp_setpriority'
dbcc xp_setpriority(free)
*/

и

Code: Select all

exec master..xp_setpriority 'lowest'

в текущей процедуре

Всем спасибо.

tengiz · Post by **tengiz** » 27 Dec 2003 08:16

olegy wrote:В сущности я нашел как понизить приоритет job на sql server - через extended procedure как...

olegy, это не поможет по следующей причине: в SQL Server один NT поток в разное время обслуживает разные процессы пользователей и наоборот - один и тот же spid в разное время может быть обслужен разными NT потоками. В результате изменение приоритета потока, выполняющего расширенную хранимую процедуру, может привести к абсолютно непредсказуемым результатам - например, Вы фактически измените приоритет нескольких пользовательских процессов, не имеющих никакого отношения к Вашему заданию. Посмотрите в сети материалы по SQL UMS - User Mode Scheduler. Это компонент реализующий модель кооперативно-вытесняющей многопоточности в SQL Server начиная с версии 7.

olegy · Post by **olegy** » 27 Dec 2003 09:59

tengiz wrote:olegy, это не поможет по следующей причине: в SQL Server один NT поток в разное время обслуживает разные процессы пользователей и наоборот - один и тот же spid в разное время может быть обслужен разными NT потоками. В результате изменение приоритета потока, выполняющего расширенную хранимую процедуру, может привести к абсолютно непредсказуемым результатам - например, Вы фактически измените приоритет нескольких пользовательских процессов, не имеющих никакого отношения к Вашему заданию. Посмотрите в сети материалы по SQL UMS - User Mode Scheduler. Это компонент реализующий модель кооперативно-вытесняющей многопоточности в SQL Server начиная с версии 7.

Ну вот, расстроили. А я уже отрапортовал.
Там, где я это обнаружил рекомендутся делать так:
exec master..xp_setpriority 'lowest'
...................... -- queries
exec master..xp_setpriority 'normal'
утверждается, что это предовратит утечку этого потока в другой SQL UMS thread .
Так ли это? Если так, то должен ли я ставить exec master..xp_setpriority 'normal' только перед return из Sp или после каждого запроса

?
Если нет, то неужто все так безнадежно?

tengiz · Post by **tengiz** » 30 Dec 2003 20:46

olegy wrote:Там, где я это обнаружил рекомендутся делать так:
exec master..xp_setpriority 'lowest'
...................... -- queries
exec master..xp_setpriority 'normal'
утверждается, что это предовратит утечку этого потока в другой SQL UMS thread .
Так ли это? Если так, то должен ли я ставить exec master..xp_setpriority 'normal' только перед return из Sp или после каждого запроса ?
Если нет, то неужто все так безнадежно?

Строго говоря безнадёжно, потому что "утекание" потока может происходить каждый раз после окончания исполнения каждого батча. В том числе когда батч принудительно прерван - например, при deadlock, при ошибке компиляции и в других ситуациях. Так как доступа к NT потоку, которому был снижен приоритет уже нет (иначе нужно переделывать все приложения, работающие с данным сервером), то наличие проблемы очевидно.

Возвращясь к Вашему исходному вопросу - а в чём суть CPU интенсивного задания, которое нужно запускать с низким приоритетом? Может есть альтернативные решения, которые не кажутся очевидными? Игра с приоритетами кроме нежелательного вмешательства во внутреннюю механику диспетчеризации почти всегда является бесполезной в SQL Server для собственно SQL, так как основной ресурс, за который идёт конкуренция - это обычно блокировки, а не CPU. А тут приоритеты не очень помогут.

Dmitry67 · Post by **Dmitry67** » 31 Dec 2003 09:17

tengiz, по поводу приоритетов
Конечно мы благодарны MS за то что она всегда все настраивает автоматически лучше чем DBA

, но тем не менее... Например многие вещи из Sybase я бы украл

Я считаю что было бы крайне неплохо в SQL сделать классы исполнения типа OLAP/OLTP, prio OLTP > OLAP. Сделать так чтобы тупой table scan не 'выбивал' кэш из под других OLTP процессов. Может ввести категории кешей или хотя бы хинт что результат table scan не надо класть в кеш, такое есть в Oracle.

Потому что на самом деле устойчивость MS SQL к "DOS-атакам" (имеются ввиду WEB, когда если не уложился за NN секунд выдается ошибка) практически нулевая

Под DOS-атаками имеются ввиду конечно не зловредные хакеры, а jobs, OLAP, reports итд.