Замедление работы сервера

← →
Guest10 (2006-10-09 16:47) [0]

имеется сервер Cel 2.4 RAM 512 HDD 80

такая ситуация:
при запуске ХП выполняющей длительный расчет из QA на вышеуказанном сервере (ХП содержит различные инструкции DDL и DML ) наблюдается сильное замедление работы остальных SQL процессов запущенных с клиентских машин,а также замедление работы вышеуказанной машины

что предпринять ? где искать ?

спасибо за ответ

← →
Sergey13 © (2006-10-09 16:51) [1]

> [0] Guest10 (09.10.06 16:47)

А должно было ускорение происходить?
Я с МС СКЛ не работал, но наверное тут 2 пути
1. Оптимизировать ХП
2. Наращивать мощу железа

← →
Desdechado © (2006-10-09 16:55) [2]

Celeron в качестве сервера несколько опрометчиво. А замедление - ясен пень, будет. ХП же работает, а не в потолок плюет. Оптимизация запросов, настройка СУБД, ковыряние с индексами и т.п. может помочь.

PS
DDL+DML=дурной тон

← →
Stanislav © (2006-10-09 17:10) [3]

а пользователей много подключено?
У наc умудрялись писать ХП, которые 2xXeon 2.8 ГГц 2 ГБ ОЗУ HDD 5 RAID ложили, правда когда народу было куча приконекчено и не 1 чел. выполнял эту ХП.

← →
guest10 (2006-10-09 17:22) [4]

пользователей пока меньше десятка, может быть можно управлять процессами на сервере

← →
Stanislav © (2006-10-09 17:24) [5]

А что с оперативкой в момент работы, сколько забирает сервер, сколько свободно?

← →
guest10 (2006-10-09 17:37) [6]

в момент работы забирает порядка 420М,
а может попробовать параметры вкладки memory свойств сервера

← →
Stanislav © (2006-10-09 17:39) [7]

А там по умолчанию он забирает всю свободную память.
Лучше оптимизируй запрос что ты там делаешь?

← →
Stanislav © (2006-10-09 17:42) [8]

Нужно еще проверить не создает ли эта ХП блокировок на сервере.

← →
atruhin © (2006-10-09 19:16) [9]

> в момент работы забирает порядка 420М,
> а может попробовать параметры вкладки memory свойств сервера

420M + ОС т.е. у тебя банально не хватает памяти удвой, 512 для сервера даже не смешно, при сегодняшней цене памяти.

← →
guest10 (2006-10-10 09:15) [10]

ХП выполняется в отдельной БД на сервере с которой никто не работает.

странно что промышленый сервер не справляется с такой нагрузкой

← →
Sergey13 © (2006-10-10 09:24) [11]

> [10] guest10 (10.10.06 09:15)
Даже если работать в сталелитейной промышленности, я бы не назвал
> Cel 2.4 RAM 512 HDD 80
промышленым сервером. Не говоря уж о легкой промышленности и особенно о пищевой.
8-)

← →
evvcom © (2006-10-10 09:28) [12]

> [10] guest10 (10.10.06 09:15)

Сейчас у многих персоналки на порядок круче твоего сервера. :)))

← →
Desdechado © (2006-10-10 11:15) [13]

> странно что промышленый сервер не справляется с такой нагрузкой
Серверы бывают разными, т.к. разное у них назначение. Простейший стоечный веб-сервер начального уровня может быть такой конфигурации. А сервер БД - не может.

← →
Stanislav © (2006-10-10 11:58) [14]

Под промышленным сервером ты понимаешь MSSQL 2000 Server enterprise edition?
Вообще-то это ненормально. У меня под трен. сервером стоит примерно такая-же конфа и 10 - 15 программеров на нем сидят, и все ок!

Напиши что в хранимке делаешь.
Где-то правило читал: На каждый компьютер найдется программное обеспечение способное его повесить :-).

← →
atruhin © (2006-10-10 12:06) [15]

> У меня под трен. сервером стоит примерно такая-же конфа
> и 10 - 15 программеров на нем сидят, и все ок!

Зависит не от кол-ва подключений, а от объема обрабатываемых данных в запросах. Т.е. у него происходят какие то выборки требующие большого объема данных, а памяти не хватает, начинает свопится....

← →
Stanislav © (2006-10-10 12:13) [16]

atruhin © (10.10.06 12:06) [15]
Я же и прошу у него текст хранимки.

← →
sniknik © (2006-10-10 12:39) [17]

> Я же и прошу у него текст хранимки.
на святое посягаеш? это же самая охраняемая тайна всех начинающих... - любой исходный код/реальные действия приводяшие к проблеме...
;о))

:-)

← →
guest10 (2006-10-10 16:02) [19]

> > Я же и прошу у него текст хранимки.
> на святое посягаеш? это же самая охраняемая тайна всех начинающих.
> .. - любой исходный код/реальные действия приводяшие к проблеме.
> ..

Короче говоря вот текст этой процедуры (в нескольких частях)

/*
назначение:
разузлование текущего узла состава изделий снизу вверх
проверка на наличие петель при разузловании
входные параметры: @RootNode - текущий узел
возвращаемые значения
множество цепочек входимости данного узла в другие
состояние цепочки определяется значением в поле FLG:
0- цепочка не раскрыта
1- цепочка раскрыта
2- цепочка содержит цикическую ссылку
после окончания разузлования
в таблицу TAB состава изделий в поле errcode пишется код ошибки
*/

-- вспомогательные таблицы
/*
сделаны обычными для контроля процесса
EXP_CUR_NODE - таблица входимости текущего узла

CREATE TABLE EXP_CUR_NODE

(
NID INT IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED ,
FLG INT,
LVL INT,
ROOTNODE VARCHAR(10),
NODE1 VARCHAR(10),
NODE2 VARCHAR(10),
NODE3 VARCHAR(10),
NODE4 VARCHAR(10),
NODE5 VARCHAR(10),
NODE6 VARCHAR(10),
NODE7 VARCHAR(10),
NODE8 VARCHAR(10),
NODE9 VARCHAR(10),
NODE10 VARCHAR(10),
NODE11 VARCHAR(10),
NODE12 VARCHAR(10),
NODE13 VARCHAR(10),
NODE14 VARCHAR(10),
NODE15 VARCHAR(10),
NODE16 VARCHAR(10),
NODE17 VARCHAR(10),
NODE18 VARCHAR(10),
NODE19 VARCHAR(10)
)
NXT_LEVEL - таблица следующего уровня

CREATE TABLE NXT_LEVEL
(
(
NID INT IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED ,
FLG INT,
LVL INT,
ROOTNODE VARCHAR(10),
NODE1 VARCHAR(10),
NODE2 VARCHAR(10),
NODE3 VARCHAR(10),
NODE4 VARCHAR(10),
NODE5 VARCHAR(10),
NODE6 VARCHAR(10),
NODE7 VARCHAR(10),
NODE8 VARCHAR(10),
NODE9 VARCHAR(10),
NODE10 VARCHAR(10),
NODE11 VARCHAR(10),
NODE12 VARCHAR(10),
NODE13 VARCHAR(10),
NODE14 VARCHAR(10),
NODE15 VARCHAR(10),
NODE16 VARCHAR(10),
NODE17 VARCHAR(10),
NODE18 VARCHAR(10),
NODE19 VARCHAR(10)
)
)
т. GRAPH - таблица результирущего множества

CREATE TABLE GRAPH
(
NID INT IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED ,
FLG INT,
LVL INT,
ROOTNODE VARCHAR(10),
NODE1 VARCHAR(10),
NODE2 VARCHAR(10),
NODE3 VARCHAR(10),
NODE4 VARCHAR(10),
NODE5 VARCHAR(10),
NODE6 VARCHAR(10),
NODE7 VARCHAR(10),
NODE8 VARCHAR(10),
NODE9 VARCHAR(10),
NODE10 VARCHAR(10),
NODE11 VARCHAR(10),
NODE12 VARCHAR(10),
NODE13 VARCHAR(10),
NODE14 VARCHAR(10),
NODE15 VARCHAR(10),
NODE16 VARCHAR(10),
NODE17 VARCHAR(10),
NODE18 VARCHAR(10),
NODE19 VARCHAR(10)

*/

ALTER PROCEDURE SP_EXPAND_UP_ALL
AS
SET NOCOUNT ON

CREATE TABLE #N
(
NODE VARCHAR(19)
)

CREATE TABLE #CHECK_NODES
(
CHNODE VARCHAR(19)
)

DECLARE @RootNode VARCHAR(19), -- текущий узел состава
@ChLevel INT, -- текущий уровень цепочки начиная с 0 относит. раскрываемого узла
@NID INT, -- ID узла
@NID_NXT INT, -- ID узла в табл. след.уровня
@s VARCHAR(8000), -- текст запроса на выборку род. узлов в таб. #NXT_LEVEL
@LvlCount INT, -- число уровней
@i INT,
@CNode VARCHAR(10),
@PNode VARCHAR(10),
@Node VARCHAR(10),
@NodeField VARCHAR(8),
@LastNode VARCHAR(10),
@LastNodeField VARCHAR(8),
@LastNodeChField VARCHAR(8),
@LastNodeCh VARCHAR(10),
@FirstNodeP VARCHAR(10),
@FirstNodeC VARCHAR(10)

DECLARE C_Tree CURSOR
FOR (SELECT CHIELD FROM TB)
OPEN C_Tree
FETCH NEXT FROM C_Tree INTO @RootNode
WHILE @@Fetch_Status=0
BEGIN
-- не раскрывать узлы у котрорых найдены цепочки входимости
IF EXISTS (SELECT * FROM GRAPH WHERE ROOTNODE=@ROOTNODE)
BEGIN
FETCH NEXT FROM C_Tree INTO @RootNode
CONTINUE
END

-- очистить т. EXP_CUR_NODE
DELETE FROM EXP_CUR_NODE

-- добавить в EXP_CUR_NODE данные о текущем раскрываемом узле
INSERT INTO EXP_CUR_NODE (FLG,LVL,ROOTNODE)
VALUES(0,0,@RootNode)

SET @ChLevel=0

WHILE (2+2=4)
BEGIN
--извлечение id первой попавшейся незавершенной цепочки текущего уровня
SET @NID=-1

IF (EXISTS (SELECT TOP 1 * FROM EXP_CUR_NODE WHERE LVL=@ChLevel AND FLG=0))
SELECT TOP 1 @NID=NID FROM EXP_CUR_NODE WHERE LVL=@ChLevel AND FLG=0

IF (@NID<0)
BEGIN
--есть ли вообще незавершенные цепочки
IF (EXISTS (SELECT * FROM EXP_CUR_NODE WHERE FLG=0))
-- если есть - перейти на следующий уровень
SET @ChLevel=@ChLevel+1
ELSE
-- если нет - завершение разузлования
BREAK
END
ELSE
BEGIN
-- считана очередная цепочка текущего уровня
-- очистить таблицу следующего уровня
DELETE FROM NXT_LEVEL
-- Выборка родительских узлов в таблицу след.уровня вместе с информацией текущей цепочки
-- Составление запроса на выборку из EXP_CUR_NODE и вставку в NXT_LEVEL
SET @i=1
SET @s="INSERT INTO NXT_LEVEL (FLG,LVL,ROOTNODE"
WHILE (@i<=19)
BEGIN
SET @s=@s+",NODE"+CAST(@i AS VARCHAR(2))
SET @i=@i+1
END
SET @s=@s+") SELECT FLG,E.LVL+1,ROOTNODE "

SET @i=1
WHILE (@i<=19)
BEGIN
IF (@i-1=@ChLevel)
SET @s=@s+",ST.PARENT"
ELSE
SET @s=@s+",E.NODE"+CAST(@i AS VARCHAR(2))
SET @i=@i+1
END

IF (@ChLevel=0)
SET @s=@s+" FROM EXP_CUR_NODE E, TB ST WHERE E.NID="+CAST(@NID AS VARCHAR(1024))+" AND E.ROOTNODE=ST.CHIELD"
ELSE
SET @s=@s+" FROM EXP_CUR_NODE E, TB ST WHERE E.NID="+CAST(@NID AS VARCHAR(1024))+" AND E.NODE"+CAST(@ChLevel AS VARCHAR(2))+"=ST.CHIELD"

EXEC(@s)

IF EXISTS(SELECT * FROM NXT_LEVEL)
BEGIN

← →
guest10 (2006-10-10 16:02) [20]

часть 2
-- проверка на наличие петель
DECLARE C_Loop CURSOR
FOR (SELECT NID FROM NXT_LEVEL)
OPEN C_Loop
FETCH NEXT FROM C_Loop INTO @NID_Nxt
WHILE @@FETCH_STATUS=0
BEGIN
DELETE FROM #CHECK_NODES
SELECT @CNode=ROOTNODE FROM NXT_LEVEL WHERE NID=@NID_Nxt
INSERT INTO #CHECK_NODES(CHNODE) VALUES(@CNode)

SET @i=1
WHILE @i<=19
BEGIN
DELETE FROM #N
SET @s="INSERT INTO #N(NODE) SELECT NODE"+CAST(@i AS VARCHAR(2))+
" FROM NXT_LEVEL WHERE NID="+CAST(@NID_Nxt AS VARCHAR(1024))
EXEC (@s)
SELECT @CNode=NODE FROM #N
IF (@CNode IS NULL) BREAK

IF EXISTS (SELECT * FROM #CHECK_NODES WHERE CHNODE=@CNode)
BEGIN
-- обнаружена петля
UPDATE NXT_LEVEL SET FLG=2 WHERE NID=@NID_Nxt
BREAK
END
ELSE
INSERT INTO #CHECK_NODES(CHNODE) VALUES (@CNode)
SET @i=@i+1
END
FETCH NEXT FROM C_Loop INTO @NID_Nxt
END
CLOSE C_Loop
DEALLOCATE C_Loop

-- удалить из #EXP_CUR_NODE текущую цепочку
DELETE FROM EXP_CUR_NODE WHERE NID=@NID
-- добавить записи из #NXT_LEVEL
SET @i=1
SET @s="INSERT INTO EXP_CUR_NODE(NID,FLG,LVL,ROOTNODE"
WHILE (@i<=19)
BEGIN
SET @s=@s+",NODE"+CAST(@i AS VARCHAR(2))
SET @i=@i+1
END
SET @s=@s+") SELECT * FROM NXT_LEVEL "
SET IDENTITY_INSERT EXP_CUR_NODE ON
EXEC (@s)
SET IDENTITY_INSERT EXP_CUR_NODE OFF
END
ELSE
UPDATE EXP_CUR_NODE
SET FLG=1
WHERE NID=@NID
END
END
-- добавить цепочки входимости текущего узла в т. #GRAPH
SET @i=1
SET @s="INSERT INTO GRAPH(GID,FLG,LVL,ROOTNODE"
WHILE (@i<=19)
BEGIN
SET @s=@s+",NODE"+CAST(@i AS VARCHAR(2))
SET @i=@i+1
END
SET @s=@s+") SELECT * FROM EXP_CUR_NODE"
SET IDENTITY_INSERT GRAPH ON
EXEC (@s)
SET IDENTITY_INSERT GRAPH OFF

FETCH NEXT FROM C_Tree INTO @RootNode
END

CLOSE C_Tree
DEALLOCATE C_Tree

UPDATE TB SET ErrCode=0
-- определить точки петель
DECLARE C_PointLoop CURSOR FOR
(SELECT GID FROM GRAPH WHERE FLG=2)
OPEN C_PointLoop
FETCH NEXT FROM C_PointLoop INTO @NID
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @LastNode=RootNode FROM GRAPH WHERE GID=@NID
SET @LastNodeField="ROOTNODE"
SET @LastNodeChField=""
SET @LastNodeCh=""

SET @i=1
WHILE @i<=19
BEGIN
--определить конечную точку петли
-- определить последний узел цепочки
DELETE FROM #N
SET @s="INSERT INTO #N(NODE) SELECT NODE"+CAST(@i AS VARCHAR(2))+
" FROM GRAPH WHERE GID="+CAST(@NID AS VARCHAR(1024))
EXEC(@s)
SELECT @Node=NODE FROM #N
-- определить дочерний узел для последнего узла цепочки
IF (@Node IS NULL)
BEGIN
DELETE FROM #N
SET @s="INSERT INTO #N(NODE) SELECT NODE"+CAST(@i-1 AS VARCHAR(2))+
" FROM GRAPH WHERE GID="+CAST(@NID AS VARCHAR(1024))
EXEC(@s)
SELECT @LastNode=NODE FROM #N

SET @LastNodeField="NODE"+CAST(@i-1 AS VARCHAR(2))

IF (@LastNodeField="NODE1")
SET @LastNodeChField="ROOTNODE"
ELSE
SET @LastNodeChField="NODE"+CAST(@i-2 AS VARCHAR(2))

DELETE FROM #N
SET @s="INSERT INTO #N(NODE) SELECT "+@LastNodeChField+
" FROM GRAPH WHERE GID="+CAST(@NID AS VARCHAR(1024))
EXEC(@s)
SELECT @LastNodeCh=NODE FROM #N

BREAK
END
ELSE
IF (@i=19)
BEGIN
DELETE FROM #N
SET @s="INSERT INTO #N(NODE) SELECT NODE"+CAST(@i AS VARCHAR(2))+
" FROM GRAPH WHERE GID="+CAST(@NID AS VARCHAR(1024))
EXEC(@s)
SELECT @LastNode=NODE FROM #N

DELETE FROM #N
SET @s="INSERT INTO #N(NODE) SELECT NODE"+CAST(@i-1 AS VARCHAR(2))+
" FROM GRAPH WHERE GID="+CAST(@NID AS VARCHAR(1024))
EXEC(@s)
SELECT @LastNodeCh=NODE FROM #N
SET @LastNodeField="NODE"+CAST(@i-1 AS VARCHAR(2))
BREAK
END

SET @i=@i+1
END
-- определить пару дочерний - родительский узел для каждого узла цепочки
-- и сравнить последний узел цепочки с каждым из узлов и найти начальную точку петли
IF EXISTS (SELECT * FROM GRAPH WHERE GID=@NID AND ROOTNODE=@LastNode)
BEGIN
SELECT @FirstNodeP=NODE1, @FirstNodeC=ROOTNODE FROM GRAPH WHERE GID=@NID
--установить для начала петли код 16
UPDATE TB
SET ErrCode=ErrCode|16 -- операция OR
WHERE PARENT=@FirstNodeP AND CHIELD=@FirstNodeC
--установить для конца петли код 32
UPDATE TB
SET ErrCode=ErrCode|32 -- операция OR
WHERE PARENT=@LastNode AND CHIELD=@LastNodeCh
END
ELSE
BEGIN
SET @i=1
WHILE @i<=19
BEGIN
IF ("NODE"+CAST(@i AS VARCHAR(2))=@LastNodeField)
BREAK
ELSE
BEGIN
DELETE FROM #N
SET @s="INSERT INTO #N(NODE) SELECT NODE"+CAST(@i AS VARCHAR(2))+
" FROM GRAPH WHERE GID="+CAST(@NID AS VARCHAR(1024))
EXEC(@s)
IF EXISTS(SELECT * FROM #N WHERE NODE=@LastNode)
BEGIN
DELETE FROM #N
SET @s="INSERT INTO #N(NODE) SELECT NODE"+CAST(@i+1 AS VARCHAR(2))+
" FROM GRAPH WHERE GID="+CAST(@NID AS VARCHAR(1024))
EXEC(@s)
SELECT @FirstNodeP=NODE FROM #N

--установить для начала петли код 16
UPDATE TB
SET ErrCode=ErrCode|16 -- операция OR
WHERE PARENT=@FirstNodeP AND CHIELD=@LastNode

--установить для конца петли код 32
UPDATE TB
SET ErrCode=ErrCode|32 -- операция OR
WHERE PARENT=@LastNode AND CHIELD=@LastNodeCh

BREAK
END
SET @i=@i+1
END
END
END
FETCH NEXT FROM C_PointLoop INTO @NID
END
CLOSE C_PointLoop
DEALLOCATE C_Pointloop

select * from graph where flg=2

Весь текст не осилил.
Но бросилось в глаза, зачем создавать постоянные таблицы (например, EXP_CUR_NODE) для хранения временных данных?
Да и гуляние по 19-уровневому дереву вверх-вниз явно не променад по набережной.
Кстати, почему уровни иерархии сделаны по горизонтали, а не вертикали?

Я так понял это разузловка дерева?
Она делается на много проще, но действительно нагружает сервер и блокирует таблицы.
Самое правильное решение это хранить всю разузловку в таблице (по собственному опыту).
Были надежды на 2005 сервер оператор With, но он медленее оказался.

← →
guest10 (2006-10-10 17:18) [23]

> Весь текст не осилил.
> Но бросилось в глаза, зачем создавать постоянные таблицы
> (например, EXP_CUR_NODE) для хранения временных данных?
>
> Да и гуляние по 19-уровневому дереву вверх-вниз явно не
> променад по набережной.
> Кстати, почему уровни иерархии сделаны по горизонтали, а
> не вертикали?

в коментариях сказано что таблицы сделаны постоянными для контроля выполнения ХП а на самом деле они временные, а итеративный алгоритм и горизонтальное расположение уровней в результ. таблице GRAPH позволяет легко найти зацикливания цепочек входимости так называемые "петли"
а табл. TAB имет структуру типа

ID
PARENT VARCHAR(10)
CHIELD VARCHAR(10)

Короче смотри вот функция по разворач. дерева.
Таблица SOSTZAK
поле NOMZAP - номер записи
NOZP - родитель
Здесь и реализована защита от зацикливания, работает сравнительно быстро.

CREATE FUNCTION FSOSTZAK(/*@NOMZP as int*/ )
RETURNS

@TAB TABLE (IDNOMZ INT IDENTITY (1,1) NOT NULL,
GLZAP INT,
NOMZAP INT,
NOZP INT,
UROVEN INT

primary key (IDNOMZ) ) --Создание временной таблицы

AS
BEGIN

DECLARE @MAXID INT,
@PMAXID INT,
@PRIMARYID INT, --первый максимальный ID
@I INT

--SET @NOMZP=[dbo].[FSOSTZAKGLZAP](@NOMZP)

INSERT INTO @TAB (GLZAP, NOMZAP, NOZP,UROVEN)
SELECT SOSTZAK.NOMZAP, SOSTZAK.NOMZAP,NOZP, 0
FROM SOSTZAK
-- WHERE NOMZAP=@NOMZP --Занесение во временную таблицу записи из SOSTZAK

SET @I=1 --уровень
SET @PMAXID=0--(SELECT MAX(IDNOMZ) FROM @TAB)--1 --предыдущий максимальный ID
SET @MAXID=(SELECT MAX(IDNOMZ) FROM @TAB) --максимальный ID
SET @PRIMARYID=@MAXID

WHILE @PMAXID<>@MAXID AND @I<10 --максимальный уровень (защита от зацикливания)

BEGIN

INSERT INTO @TAB (GLZAP, NOMZAP, UROVEN)
SELECT SOSTZAK.GLZAP, SZAK.NOMZAP, @I
FROM @TAB SOSTZAK INNER JOIN (SELECT NOMZAP,NOZP FROM @TAB WHERE IDNOMZ<=@PRIMARYID) SZAK ON SOSTZAK.NOMZAP=SZAK.NOZP
WHERE IDNOMZ>@PMAXID --Присоединение таблицы SOSTZAK

SET @I=@I+1
SET @PMAXID=@MAXID
SET @MAXID=@@identity--(SELECT MAX(IDNOMZ) FROM @TAB)

END

RETURN
END

Замедление работы сервера Найти похожие ветки