Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Непонятные тормоза на ровном месте  [new]
Xevus
Member

Откуда:
Сообщений: 18
Здраствуйте. Помогите, пожалуйста, что делать с такой проблемой. Есть некое приложение (кто-то возможно даже догадается какое), которое в целом работает нормально, но в одном месте у нас неожиданно образовался затык. Есть запрос

SELECT
     T1.CONFLICT_ID,
     T1.DB_LAST_UPD_SRC,
     CONVERT (VARCHAR (10),T1.DB_LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T1.DB_LAST_UPD, 8),
     CONVERT (VARCHAR (10),T1.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T1.LAST_UPD, 8),
     CONVERT (VARCHAR (10),T1.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T1.CREATED, 8),
     T1.LAST_UPD_BY,
     T1.CREATED_BY,
     T1.MODIFICATION_NUM,
     T1.ROW_ID,
     T1.AUDIT_LOG,
     T1.AUDIT_SOURCE_CD,
     T1.BC_BASE_TBL,
     T1.BUSCOMP_NAME,
     T1.CHILD_BC_BASE_TBL,
     T1.CHILD_RECORD_ID,
     T1.CHILD_BC_NAME,
     CONVERT (VARCHAR (10),T1.OPERATION_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.OPERATION_DT, 8),
     CONVERT (VARCHAR (10),T1.SYNC_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.SYNC_DT, 8),
     T1.USER_ID,
     T2.LOGIN,
     T1.FIELD_NAME,
     T1.GROUP_NUM,
     T1.ITEM_IDEN_NUM,
     T1.LINK_NAME,
     CONVERT(NVARCHAR(2000),SUBSTRING(T1.NEW_VAL, 1, 4000)),
     T1.NODE_NAME,
     CONVERT(NVARCHAR(2000),SUBSTRING(T1.OLD_VAL, 1, 4000)),
     T1.OPERATION_CD,
     T1.RECORD_ID,
     T1.TBL_NAME,
     T1.TBL_RECORD_ID,
     T2.ROW_ID,
     T2.PAR_ROW_ID,
     T2.MODIFICATION_NUM,
     CONVERT (VARCHAR (10),T2.DB_LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T2.DB_LAST_UPD, 8)
  FROM
      dbo.S_AUDIT_ITEM T1
         LEFT OUTER JOIN dbo.S_USER T2 ON T1.USER_ID = T2.PAR_ROW_ID
  WHERE
     (T1.RECORD_ID='1-ADKMN')
  ORDER BY
     T1.OPERATION_DT DESC
В таблице S_AUDIT_ITEM 15 миллионов записей и следующие индексы

CREATE NONCLUSTERED INDEX [S_AUDIT_ITEM_M1] ON [dbo].[S_AUDIT_ITEM] 
(
	[BUSCOMP_NAME] ASC,
	[OPERATION_DT] DESC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [S_AUDIT_ITEM_M2] ON [dbo].[S_AUDIT_ITEM] 
(
	[USER_ID] ASC,
	[OPERATION_DT] DESC,
	[BUSCOMP_NAME] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [S_AUDIT_ITEM_M3] ON [dbo].[S_AUDIT_ITEM] 
(
	[RECORD_ID] ASC,
	[BUSCOMP_NAME] DESC,
	[OPERATION_DT] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [S_AUDIT_ITEM_M4] ON [dbo].[S_AUDIT_ITEM] 
(
	[OPERATION_DT] DESC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE CLUSTERED INDEX [S_AUDIT_ITEM_P1] ON [dbo].[S_AUDIT_ITEM] 
(
	[ROW_ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

При выполнении запроса через Mgmt Studio запрос отрабатывает мгновенно. При работе-же приложения происходит ужас. Сначала оно делает sp_prepexec с этим запросом, а потом делает sp_cursorfetch. И этот курсор отрабатывает 20 МИНУТ. Я искал информацию в Гугле, везде пишут что серверный курсор это плохо и надо от него избавится. К сожалению, переделать приложение мы не можем. Что еще можно сделать для нормализации ситуации ?

Сообщение было отредактировано: 31 окт 11, 23:29
31 окт 11, 23:15    [11529905]     Ответить | Цитировать Сообщить модератору
 Re: Непонятные тормоза на ровном месте  [new]
Glory
Member

Откуда:
Сообщений: 104751
Xevus
Сначала оно делает sp_prepexec с этим запросом, а потом делает sp_cursorfetch. И этот курсор отрабатывает 20 МИНУТ.

"этот курсор отрабатывает" - это вы про sp_prepexec или про sp_cursorfetch ?
1 ноя 11, 11:39    [11531377]     Ответить | Цитировать Сообщить модератору
 Re: Непонятные тормоза на ровном месте  [new]
Xevus
Member

Откуда:
Сообщений: 18
SP_CURSORPREPEXEC выполняется мгновенно, первый SP_CURSORFETCH - 15-20 минут.
1 ноя 11, 12:03    [11531564]     Ответить | Цитировать Сообщить модератору
 Re: Непонятные тормоза на ровном месте  [new]
Xevus
Member

Откуда:
Сообщений: 18
Может мне еще какие-то данные надо предоставить ? Очень нужна помощь, сами бьемся вторую неделю и не можешь ничего понять :(
1 ноя 11, 17:51    [11534922]     Ответить | Цитировать Сообщить модератору
 Re: Непонятные тормоза на ровном месте  [new]
sp_cursorprepexec
Guest
Xevus,

sp_cursorprepexec c какими параметрами выполняется?
1 ноя 11, 18:03    [11535018]     Ответить | Цитировать Сообщить модератору
 Re: Непонятные тормоза на ровном месте  [new]
Xevus
Member

Откуда:
Сообщений: 18
Копирую из трейса.

declare @p1 int
set @p1=1073741910
declare @p2 int
set @p2=180150173
declare @p5 int
set @p5=2
declare @p6 int
set @p6=1
declare @p7 int
set @p7=-1
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 nvarchar(15)',N'SELECT
      (SELECT порезан, он такой-же как выше)
   WHERE 
      (T1.RECORD_ID = @P1)
   ORDER BY
      T1.OPERATION_DT DESC
 OPTION (FAST 40)',@p5 output,@p6 output,@p7 output,N'1-ADKMN'
select @p1, @p2, @p5, @p6, @p7
1 ноя 11, 18:06    [11535051]     Ответить | Цитировать Сообщить модератору
 Re: Непонятные тормоза на ровном месте  [new]
Glory
Member

Откуда:
Сообщений: 104751
T1.RECORD_ID какой тип имеет ?
1 ноя 11, 18:09    [11535066]     Ответить | Цитировать Сообщить модератору
 Re: Непонятные тормоза на ровном месте  [new]
Xevus
Member

Откуда:
Сообщений: 18
Картинка с другого сайта.
1 ноя 11, 18:12    [11535092]     Ответить | Цитировать Сообщить модератору
 Re: Непонятные тормоза на ровном месте  [new]
Glory
Member

Откуда:
Сообщений: 104751
DECLARE @P1 nvarchar(15)
SET @P1 = N'1-ADKMN'
SELECT
     T1.CONFLICT_ID,
     T1.DB_LAST_UPD_SRC,
     CONVERT (VARCHAR (10),T1.DB_LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T1.DB_LAST_UPD, 8),
     CONVERT (VARCHAR (10),T1.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T1.LAST_UPD, 8),
     CONVERT (VARCHAR (10),T1.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T1.CREATED, 8),
     T1.LAST_UPD_BY,
     T1.CREATED_BY,
     T1.MODIFICATION_NUM,
     T1.ROW_ID,
     T1.AUDIT_LOG,
     T1.AUDIT_SOURCE_CD,
     T1.BC_BASE_TBL,
     T1.BUSCOMP_NAME,
     T1.CHILD_BC_BASE_TBL,
     T1.CHILD_RECORD_ID,
     T1.CHILD_BC_NAME,
     CONVERT (VARCHAR (10),T1.OPERATION_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.OPERATION_DT, 8),
     CONVERT (VARCHAR (10),T1.SYNC_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.SYNC_DT, 8),
     T1.USER_ID,
     T2.LOGIN,
     T1.FIELD_NAME,
     T1.GROUP_NUM,
     T1.ITEM_IDEN_NUM,
     T1.LINK_NAME,
     CONVERT(NVARCHAR(2000),SUBSTRING(T1.NEW_VAL, 1, 4000)),
     T1.NODE_NAME,
     CONVERT(NVARCHAR(2000),SUBSTRING(T1.OLD_VAL, 1, 4000)),
     T1.OPERATION_CD,
     T1.RECORD_ID,
     T1.TBL_NAME,
     T1.TBL_RECORD_ID,
     T2.ROW_ID,
     T2.PAR_ROW_ID,
     T2.MODIFICATION_NUM,
     CONVERT (VARCHAR (10),T2.DB_LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T2.DB_LAST_UPD, 8)
  FROM
      dbo.S_AUDIT_ITEM T1
         LEFT OUTER JOIN dbo.S_USER T2 ON T1.USER_ID = T2.PAR_ROW_ID
  WHERE
     (T1.RECORD_ID= @P1)
  ORDER BY
     T1.OPERATION_DT DESC
Это в Mgmt Studio за сколько отрабатывает ?

Сообщение было отредактировано: 1 ноя 11, 18:16
1 ноя 11, 18:16    [11535121]     Ответить | Цитировать Сообщить модератору
 Re: Непонятные тормоза на ровном месте  [new]
Xevus
Member

Откуда:
Сообщений: 18
В Mgmt Studio отрабатывает менее чем за секунду, возвращает 107 записей.
1 ноя 11, 18:17    [11535136]     Ответить | Цитировать Сообщить модератору
 Re: Непонятные тормоза на ровном месте  [new]
keyset
Guest
Xevus,

у вас курсор открывается как keyset

BOL
keyset - Specifies that the membership and order of rows in the cursor are fixed when the cursor is opened. The set of keys that uniquely identify the rows is built into a table in tempdb known as the keyset.


если возможность выставить FAST_FORWARD для типа курсора?
1 ноя 11, 18:18    [11535143]     Ответить | Цитировать Сообщить модератору
 Re: Непонятные тормоза на ровном месте  [new]
Xevus
Member

Откуда:
Сообщений: 18
Если тип курсора нельзя форсировать через какие-то тонкие настройки SQL Native Client, то боюсь что нет.
1 ноя 11, 18:32    [11535275]     Ответить | Цитировать Сообщить модератору
 Re: Непонятные тормоза на ровном месте  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
Xevus
В Mgmt Studio отрабатывает менее чем за секунду, возвращает 107 записей.

а если в SSMS сначала выполнить SET ARITHABORT OFF, а затем Ваш запрос, то за сколько он отработает?
1 ноя 11, 19:22    [11535489]     Ответить | Цитировать Сообщить модератору
 Re: Непонятные тормоза на ровном месте  [new]
Xevus
Member

Откуда:
Сообщений: 18
andrey odegov,

Тоже мгновенно.
1 ноя 11, 19:23    [11535494]     Ответить | Цитировать Сообщить модератору
 Re: Непонятные тормоза на ровном месте  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
Xevus, а версия sql server какая?
1 ноя 11, 19:50    [11535615]     Ответить | Цитировать Сообщить модератору
 Re: Непонятные тормоза на ровном месте  [new]
Xevus
Member

Откуда:
Сообщений: 18
andrey odegov,

Исходно был 2005 SP2, потом мы его пропатчили до SP4 в надежде что такое поведение уйдет, но не помогло.
1 ноя 11, 20:28    [11535748]     Ответить | Цитировать Сообщить модератору
 Re: Непонятные тормоза на ровном месте  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
Xevus,
выполните в ssms
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
запустите в приложении проблемный код,
дождитесь его завершения
затем в ssms выполните следующий запрос и покажите его резульат
WITH Waits AS
(
  SELECT
    wait_type,
    wait_time_ms / 1000. AS wait_time_s,
    100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
    ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
  FROM sys.dm_os_wait_stats
  WHERE wait_type NOT LIKE '%SLEEP%'
  -- filter out additional irrelevant waits
)
SELECT
  W1.wait_type,
  CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
  CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
  CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
  JOIN Waits AS W2
    ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 80 -- percentage threshold
OR W1.rn <= 5
ORDER BY W1.rn;
1 ноя 11, 21:17    [11535901]     Ответить | Цитировать Сообщить модератору
 Re: Непонятные тормоза на ровном месте  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
м.б. в начале еще имеет смысл выполнить
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
1 ноя 11, 21:19    [11535905]     Ответить | Цитировать Сообщить модератору
 Re: Непонятные тормоза на ровном месте  [new]
Xevus
Member

Откуда:
Сообщений: 18
andrey odegov,

Спасибо, завтра это проделаем и выложим результаты.
1 ноя 11, 21:35    [11535940]     Ответить | Цитировать Сообщить модератору
 Re: Непонятные тормоза на ровном месте  [new]
Xevus
Member

Откуда:
Сообщений: 18
Вот такие результаты.

wait_type wait_time pct running_pct
SQLTRACE_BUFFER_FLUSH 1620.30 41.36 41.36
PAGEIOLATCH_SH 1452.77 37.09 78.45
ASYNC_NETWORK_IO 797.63 20.36 98.82
SOS_SCHEDULER_YIELD 33.42 0.85 99.67
PAGEIOLATCH_EX 11.59 0.30 99.96


Трейс потому что у нас на всех серверах включен трейс по умолчанию, он мониторит только события SQLTRACE_LOCK.
10 ноя 11, 13:26    [11574451]     Ответить | Цитировать Сообщить модератору
 Re: Непонятные тормоза на ровном месте  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 532
Xevus,

ПОпробуйте насильно хинтом задать индекс который дает план запроса в SSMS. Похоже у вас очень неровная гистограмма распределения по полю (видимо) T1.RECORD_ID. Очень похоже что сервер делает автопараметризацию и решает full table scan предпочтительнее чем index seek.

Поищите так же какой план запроса строит сервер когда запрос выполняется из приложения. Кажется на 2005 уже такая возможность была
10 ноя 11, 16:25    [11576441]     Ответить | Цитировать Сообщить модератору
 Re: Непонятные тормоза на ровном месте  [new]
Xevus
Member

Откуда:
Сообщений: 18
К сожалению, управлять запросом от приложения мы не можем вообще никак, так что хинт добавить вряд ли получится. План запроса внутри курсора сделаем.
10 ноя 11, 16:56    [11576782]     Ответить | Цитировать Сообщить модератору
 Re: Непонятные тормоза на ровном месте  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
Попробовать поуправлять запросом Вы можете через руководство плана (plan guide).
10 ноя 11, 22:36    [11578387]     Ответить | Цитировать Сообщить модератору
 Re: Непонятные тормоза на ровном месте  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
Использование подсказки USE PLAN в запросах с курсорами
10 ноя 11, 22:39    [11578395]     Ответить | Цитировать Сообщить модератору
 Re: Непонятные тормоза на ровном месте  [new]
выдает чего
Guest
Xevus,

dbcc show_statistics('dbo.S_AUDIT_ITEM', [S_AUDIT_ITEM_M3])
чего выдает?
10 ноя 11, 23:01    [11578458]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить