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

Откуда: Днепропетровск
Сообщений: 4236
Всем добрый день.
Есть таблица с данными по филиалам за несколько лет. В рамках года данных порядка 50 млн записей. Кластерный индекс в ней построен по PK, который IDENTITY + дополнительные индексы по разным комбинациям полей.
Есть хранимая процедура, которая считает по этой таблице сумму по месячно для каждого филиала(упрощенно) и выполняется порядка 30 секунд

Прочитав о различных способах оптимизации БД сделал копию базы из бекапа и перестроил кластерный индекс году+месяцу+филиалу, оставил PK на том-же поле IDENTITY и добавил ещё несколько индексов.
После перестройки базы эта-же хранимая процедура стала выполнятся немного быстрее - 27 секунд.

Промониторил оба запроса в SQL Server Profiler получил следующую картину:
TextDataCPUReadsWritesDuration
Новая база - EXEC sp_get_view_close_report 2013, 1, 10262629237788016428548
Старяя база - EXEC sp_get_view_close_report 2013, 1, 1019615144823616631372


Если оба запроса запустить в SSMS так:
EXEC new_base.dbo.sp_get_view_close_report
     @work_year = 2013,
     @start_month = 1,
     @end_month = 10
GO
	
EXEC old_base.dbo.sp_get_view_close_report
     @work_year = 2013,
     @start_month = 1,
     @end_month = 10
GO

и включить показ плана выполнения, то первый запрос относительно всего пакета занимает 32%
а второй - 68%

Вопрос в следующем:
Насколько я вижу по данным профайлера на переделанной базе запрос больше грузит процессор и на порядок больше чтений, при этом запрос выполняется быстрее и SSMS показывает стоимость в два раза меньше чем изначально и я не совсем понимаю почему при большей нагрузке запрос считается более дешёвым.

Спасибо

P.S. Новая база на том-же инстансе SQLServer'а. После перезапуска сервера запрос в новой базе выполняется немного дольше, чем запрос на старой базе, но повторный запуск показывает результаты аналогичные приведенным в таблице.
select @@version

SSMS
Microsoft SQL Server 2005 - 9.00.5057.00 (X64) Mar 25 2011 13:33:31 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)
16 дек 13, 12:34    [15298023]     Ответить | Цитировать Сообщить модератору
 Re: Как интерпретировать данные профайлера MSSQL2005  [new]
NickAlex66
Member

Откуда:
Сообщений: 319
Игорь Горбонос,

Для вас 30 сек много?
16 дек 13, 13:26    [15298437]     Ответить | Цитировать Сообщить модератору
 Re: Как интерпретировать данные профайлера MSSQL2005  [new]
Игорь Горбонос
Member

Откуда: Днепропетровск
Сообщений: 4236
NickAlex66,

У меня Front End - приложение на FoxPro которое общается с базой через вебсервис. Учитывая что я ограничен одним потоком, то каждый долгий запрос к базе затормаживает программу. Поэтому хочется лучшего :)
В принципе 30 секунд допустимо.
16 дек 13, 13:38    [15298552]     Ответить | Цитировать Сообщить модератору
 Re: Как интерпретировать данные профайлера MSSQL2005  [new]
it17
Member

Откуда:
Сообщений: 108
Игорь Горбонос,

Вы используете в запросе табличные переменные?
автор
Особенно часто считается, что приблизительная стоимость выполнения является хорошим показателем того, сколько времени займет выполнение запроса и что эта оценка позволяет отличить хорошие планы от плохих. Это неверно

http://technet.microsoft.com/ru-ru/magazine/2007.11.sqlquery.aspx
16 дек 13, 13:40    [15298569]     Ответить | Цитировать Сообщить модератору
 Re: Как интерпретировать данные профайлера MSSQL2005  [new]
Игорь Горбонос
Member

Откуда: Днепропетровск
Сообщений: 4236
it17,

Чисто табличных нет, внутри хранимки CTE по основной таблице из которого получаю список филиалов с суммами помесячно с признаком готовности и "добиваю" строчкой с итого
16 дек 13, 13:44    [15298605]     Ответить | Цитировать Сообщить модератору
 Re: Как интерпретировать данные профайлера MSSQL2005  [new]
Glory
Member

Откуда:
Сообщений: 104760
Игорь Горбонос
Насколько я вижу по данным профайлера на переделанной базе запрос больше грузит процессор и на порядок больше чтений, при этом запрос выполняется быстрее и SSMS показывает стоимость в два раза меньше чем изначально и я не совсем понимаю почему при большей нагрузке запрос считается более дешёвым.

Это же относительно всего пакета. Кто же знает, что у вас там еще в процедуре делается
16 дек 13, 13:47    [15298632]     Ответить | Цитировать Сообщить модератору
 Re: Как интерпретировать данные профайлера MSSQL2005  [new]
NickAlex66
Member

Откуда:
Сообщений: 319
Игорь Горбонос,

Повышение до Enterprise не рассматриваете?
16 дек 13, 13:51    [15298670]     Ответить | Цитировать Сообщить модератору
 Re: Как интерпретировать данные профайлера MSSQL2005  [new]
Игорь Горбонос
Member

Откуда: Днепропетровск
Сообщений: 4236
Glory
Это же относительно всего пакета. Кто же знает, что у вас там еще в процедуре делается

+
	DECLARE @work_year1       INT,
	        @start_month1     INT,
	        @end_month1       INT
	
	SELECT @work_year1 = @work_year,
	       @start_month1     = @start_month,
	       @end_month1       = @end_month
	
	DECLARE @ttt INT
	IF @start_month1 > @end_month1
	BEGIN
	    SET @ttt = @end_month1
	    SET @end_month1 = @start_month1
	    SET @start_month1 = @ttt
	END
	
	IF @end_month1 = 0
	BEGIN
	    SET @end_month1 = @start_month1
	END;
	
	
	WITH rep_branch(
	    name_branch_end,
	    sort_for_report,
	    code,
	    closed_fact,
	    closed_report,
	    summ_rep,
	    summ_prep,
	    Jan,
	    Feb,
	    Mar,
	    Apr,
	    May,
	    Jun,
	    Jul,
	    Aug,
	    Sep,
	    Oct,
	    Nov,
	    [Dec],
	    Jan_close,
	    Feb_close,
	    Mar_close,
	    Apr_close,
	    May_close,
	    Jun_close,
	    Jul_close,
	    Aug_close,
	    Sep_close,
	    Oct_close,
	    Nov_close,
	    Dec_close
	) AS (
	    SELECT name_branch_end,
	           sort_for_report,
	           d2.code,
	           closed_fact,
	           closed_report,
	           SUM(summ_rep)    AS summ_rep,
	           SUM(summ_prep)   AS summ_prep,
	           SUM([1])         AS Jan,
	           SUM([2])         AS Feb,
	           SUM([3])         AS Mar,
	           SUM([4])         AS Apr,
	           SUM([5])         AS May,
	           SUM([6])         AS Jun,
	           SUM([7])         AS Jul,
	           SUM([8])         AS Aug,
	           SUM([9])         AS Sep,
	           SUM([10])        AS Oct,
	           SUM([11])        AS Nov,
	           SUM([12])        AS [Dec],
	           MIN([1_close])   AS Jan_close,
	           MIN([2_close])   AS Feb_close,
	           MIN([3_close])   AS Mar_close,
	           MIN([4_close])   AS Apr_close,
	           MIN([5_close])   AS May_close,
	           MIN([6_close])   AS Jun_close,
	           MIN([7_close])   AS Jul_close,
	           MIN([8_close])   AS Aug_close,
	           MIN([9_close])   AS Sep_close,
	           MIN([10_close])  AS Oct_close,
	           MIN([11_close])  AS Nov_close,
	           MIN([12_close])  AS Dec_close
	    FROM   (
	               SELECT name_branch_end,
	                      sort_for_report,
	                      code,
	                      MIN(closed_fact) AS closed_fact,
	                      MIN(closed_report) AS closed_report,
	                      SUM(
	                          COALESCE([1], 0.00) +
	                          COALESCE([2], 0.00) +
	                          COALESCE([3], 0.00) +
	                          COALESCE([4], 0.00) +
	                          COALESCE([5], 0.00) +
	                          COALESCE([6], 0.00) +
	                          COALESCE([7], 0.00) +
	                          COALESCE([8], 0.00) +
	                          COALESCE([9], 0.00) +
	                          COALESCE([10], 0.00) +
	                          COALESCE([11], 0.00) +
	                          COALESCE([12], 0.00)
	                      )               AS summ_rep,
	                      SUM(summ_prep)  AS summ_prep,
	                      SUM([1])        AS [1],
	                      SUM([2])        AS [2],
	                      SUM([3])        AS [3],
	                      SUM([4])        AS [4],
	                      SUM([5])        AS [5],
	                      SUM([6])        AS [6],
	                      SUM([7])        AS [7],
	                      SUM([8])        AS [8],
	                      SUM([9])        AS [9],
	                      SUM([10])       AS [10],
	                      SUM([11])       AS [11],
	                      SUM([12])       AS [12]
	               FROM   (
	                          SELECT bd.code,
	                                 bd.name_branch_end,
	                                 SUM(CASE WHEN rf.is_fact = 2 THEN rf.summa ELSE 0.00 END) AS summ_rep,
	                                 SUM(CASE WHEN rf.is_fact = 1 THEN rf.summa ELSE 0.00 END) AS summ_prep,
	                                 rf.month_report,
	                                 b.sort_for_report,
	                                 MIN(CAST(rf.closed_fact AS INT)) AS closed_fact,
	                                 MIN(CAST(rf.closed_report AS INT)) AS closed_report
	                          FROM   branch b WITH(NOLOCK)
	                                 INNER JOIN branch_detail bd WITH(NOLOCK)
	                                      ON  bd.code_branch = b.code
	                                 INNER JOIN branch_detail_relation_end bdre
	                                      ON  bdre.code_branch_detail = bd.code
	                                 INNER JOIN branch_end be WITH(NOLOCK)
	                                      ON  be.code = bdre.code_branch_end
	                                 INNER  JOIN raw_fact rf WITH(NOLOCK)
	                                      ON  (
	                                              be.code = rf.code_branch_end_edge
	                                              --AND rf.is_fact = 2
	                                          AND rf.work_year = @work_year1
	                                          AND (rf.month_report BETWEEN @start_month1 AND @end_month1)
	                                          )
	                          WHERE  NOT b.sort_for_report IS NULL
	                          AND    bd.code <> 62 /*Днепр(общий)*/
	                                 --AND    bd.code IN ( 7, 6)
	                          GROUP BY
	                                 bd.code,
	                                 bd.name_branch_end,
	                                 rf.month_report,
	                                 b.sort_for_report
	                      )
	                      d PIVOT(
	                          SUM(d.summ_rep) FOR d.month_report IN ([1],
	                                                                 [2],
	                                                                 [3],
	                                                                 [4],
	                                                                 [5],
	                                                                 [6],
	                                                                 [7],
	                                                                 [8],
	                                                                 [9],
	                                                                 [10],
	                                                                 [11],
	                                                                 [12])
	                      )                  pvt
	               GROUP BY
	                      name_branch_end,
	                      sort_for_report,
	                      code
	           ) d2
	           INNER JOIN (
	                    SELECT code,
	                           [1]   AS [1_close],
	                           [2]   AS [2_close],
	                           [3]   AS [3_close],
	                           [4]   AS [4_close],
	                           [5]   AS [5_close],
	                           [6]   AS [6_close],
	                           [7]   AS [7_close],
	                           [8]   AS [8_close],
	                           [9]   AS [9_close],
	                           [10]  AS [10_close],
	                           [11]  AS [11_close],
	                           [12]  AS [12_close]
	                    FROM   (
	                               SELECT bd.code,
	                                      bd.name_branch_end,
	                                      rf.month_report,
	                                      b.sort_for_report,
	                                      MIN(CAST(rf.closed_fact AS INT)) AS closed_fact
	                               FROM   branch b WITH(NOLOCK)
	                                      INNER JOIN branch_detail bd WITH(NOLOCK)
	                                           ON  bd.code_branch = b.code
	                                      INNER JOIN branch_detail_relation_end bdre
	                                           ON  bdre.code_branch_detail = bd.code
	                                      INNER JOIN branch_end be WITH(NOLOCK)
	                                           ON  be.code = bdre.code_branch_end
	                                      INNER  JOIN raw_fact rf WITH(NOLOCK)
	                                           ON  (
	                                                   be.code = rf.code_branch_end_edge
	                                               AND rf.is_fact = 2
	                                               AND rf.work_year = @work_year1
	                                               AND rf.month_report BETWEEN @start_month1
	                                                   AND @end_month1
	                                               )
	                               WHERE  NOT b.sort_for_report IS NULL
	                               AND    bd.code <> 62 /*Днепр(общий)*/
	                                      --AND    bd.code IN ( 7, 6)
	                                      --GROUP BY
	                                      --       bd.name_branch_end,
	                                      --       b.sort_for_report
	                               GROUP BY
	                                      bd.code,
	                                      bd.name_branch_end,
	                                      rf.month_report,
	                                      b.sort_for_report
	                           )
	                           d PIVOT(
	                               MIN(d.closed_fact) FOR d.month_report IN ([1],
	                                                                         [2],
	                                                                         [3],
	                                                                         [4],
	                                                                         [5],
	                                                                         [6],
	                                                                         [7],
	                                                                         [8],
	                                                                         [9],
	                                                                         [10],
	                                                                         [11],
	                                                                         [12])
	                           )        pvt
	                ) d1
	                ON  d2.code = d1.code
	    GROUP BY
	           name_branch_end,
	           sort_for_report,
	           d2.code,
	           closed_fact,
	           closed_report
	)
	--SELECT *
	--FROM   rep_branch rb
	--ORDER BY
	--       sort_for_report,
	--       name_branch_end
	--Доделать итоговый запрос на показ данных с суммами и флагами показывающий закрытие периодов
	
	
	SELECT rb.name_branch_end,
	       SUM(rb.summ_rep)   AS summ_rep,
	       SUM(rb.summ_prep)  AS summ_prep,
	       CAST(
	           MIN(
	               CASE 
	                    WHEN (@end_month1 -(@start_month1 - 1)) = (
	                             COALESCE(Jan_close, 0) +
	                             COALESCE(Feb_close, 0) +
	                             COALESCE(Mar_close, 0) +
	                             COALESCE(Apr_close, 0) +
	                             COALESCE(May_close, 0) +
	                             COALESCE(Jun_close, 0) +
	                             COALESCE(Jul_close, 0) +
	                             COALESCE(Aug_close, 0) +
	                             COALESCE(Sep_close, 0) +
	                             COALESCE(Oct_close, 0) +
	                             COALESCE(Nov_close, 0) +
	                             COALESCE(Dec_close, 0)
	                         ) THEN 1
	                    ELSE 0
	               END
	           ) AS BIT
	       )                  AS closed_fact,
	       CAST(MIN(rb.closed_report) AS BIT) AS closed_report,
	       SUM(rb.Jan)        AS Jan,
	       SUM(rb.Feb)        AS Feb,
	       SUM(rb.Mar)        AS Mar,
	       SUM(rb.Apr)        AS Apr,
	       SUM(rb.May)        AS May,
	       SUM(rb.Jun)        AS Jun,
	       SUM(rb.Jul)        AS Jul,
	       SUM(rb.Aug)        AS Aug,
	       SUM(rb.Sep)        AS Sep,
	       SUM(rb.Oct)        AS Oct,
	       SUM(rb.Nov)        AS Nov,
	       SUM(rb.[Dec])      AS [Dec],
	       SUM(
	           COALESCE(rb.Jan, 0.00) +
	           COALESCE(rb.Feb, 0.00) +
	           COALESCE(rb.Mar, 0.00) +
	           COALESCE(rb.Apr, 0.00) +
	           COALESCE(rb.May, 0.00) +
	           COALESCE(rb.Jun, 0.00) +
	           COALESCE(rb.Jul, 0.00) +
	           COALESCE(rb.Aug, 0.00) +
	           COALESCE(rb.Sep, 0.00) +
	           COALESCE(rb.Oct, 0.00) +
	           COALESCE(rb.Nov, 0.00) +
	           COALESCE(rb.[Dec], 0.00)
	       )                  AS itogo,
	       rb.sort_for_report,
	       rb.code,
	       CAST(MAX(rb.Jan_close) AS BIT) AS Jan_close,
	       CAST(MAX(rb.Feb_close) AS BIT) AS Feb_close,
	       CAST(MAX(rb.Mar_close) AS BIT) AS Mar_close,
	       CAST(MAX(rb.Apr_close) AS BIT) AS Apr_close,
	       CAST(MAX(rb.May_close) AS BIT) AS May_close,
	       CAST(MAX(rb.Jun_close) AS BIT) AS Jun_close,
	       CAST(MAX(rb.Jul_close) AS BIT) AS Jul_close,
	       CAST(MAX(rb.Aug_close) AS BIT) AS Aug_close,
	       CAST(MAX(rb.Sep_close) AS BIT) AS Sep_close,
	       CAST(MAX(rb.Oct_close) AS BIT) AS Oct_close,
	       CAST(MAX(rb.Nov_close) AS BIT) AS Nov_close,
	       CAST(MAX(rb.Dec_close) AS BIT) AS Dec_close,
	       1                  AS for_srt
	FROM   rep_branch            rb
	GROUP BY
	       rb.name_branch_end,
	       rb.sort_for_report,
	       rb.code
	UNION ALL
	SELECT CAST(' Итого ' AS VARCHAR(50)) AS name_branch_end,
	       SUM(rb.summ_rep)   AS summ_rep,
	       SUM(rb.summ_prep)  AS summ_prep,
	       CAST(MIN(rb.closed_fact) AS BIT) AS closed_fact,
	       CAST(MIN(rb.closed_report) AS BIT) AS closed_report,
	       SUM(rb.Jan)        AS Jan,
	       SUM(rb.Feb)        AS Feb,
	       SUM(rb.Mar)        AS Mar,
	       SUM(rb.Apr)        AS Apr,
	       SUM(rb.May)        AS May,
	       SUM(rb.Jun)        AS Jun,
	       SUM(rb.Jul)        AS Jul,
	       SUM(rb.Aug)        AS Aug,
	       SUM(rb.Sep)        AS Sep,
	       SUM(rb.Oct)        AS Oct,
	       SUM(rb.Nov)        AS Nov,
	       SUM(rb.[Dec])      AS [Dec],
	       SUM(
	           COALESCE(rb.Jan, 0.00) +
	           COALESCE(rb.Feb, 0.00) +
	           COALESCE(rb.Mar, 0.00) +
	           COALESCE(rb.Apr, 0.00) +
	           COALESCE(rb.May, 0.00) +
	           COALESCE(rb.Jun, 0.00) +
	           COALESCE(rb.Jul, 0.00) +
	           COALESCE(rb.Aug, 0.00) +
	           COALESCE(rb.Sep, 0.00) +
	           COALESCE(rb.Oct, 0.00) +
	           COALESCE(rb.Nov, 0.00) +
	           COALESCE(rb.[Dec], 0.00)
	       )                  AS itogo,
	       10000              AS sort_for_report,
	       CAST(NULL AS INT)  AS code,
	       CAST(MIN(rb.Jan_close) AS BIT) AS Jan_close,
	       CAST(MIN(rb.Feb_close) AS BIT) AS Feb_close,
	       CAST(MIN(rb.Mar_close) AS BIT) AS Mar_close,
	       CAST(MIN(rb.Apr_close) AS BIT) AS Apr_close,
	       CAST(MIN(rb.May_close) AS BIT) AS May_close,
	       CAST(MIN(rb.Jun_close) AS BIT) AS Jun_close,
	       CAST(MIN(rb.Jul_close) AS BIT) AS Jul_close,
	       CAST(MIN(rb.Aug_close) AS BIT) AS Aug_close,
	       CAST(MIN(rb.Sep_close) AS BIT) AS Sep_close,
	       CAST(MIN(rb.Oct_close) AS BIT) AS Oct_close,
	       CAST(MIN(rb.Nov_close) AS BIT) AS Nov_close,
	       CAST(MIN(rb.Dec_close) AS BIT) AS Dec_close,
	       2                  AS for_srt
	FROM   rep_branch            rb
	ORDER BY
	       rb.sort_for_report,
	       1,	--rb.name_branch_end,
	       4,	--rb.closed_fact,
	       5 --rb.closed_report;
	;
16 дек 13, 13:56    [15298709]     Ответить | Цитировать Сообщить модератору
 Re: Как интерпретировать данные профайлера MSSQL2005  [new]
Игорь Горбонос
Member

Откуда: Днепропетровск
Сообщений: 4236
NickAlex66
Повышение до Enterprise не рассматриваете?

Пока нет, потому что, не видно преимуществ.
Или я не туда смотрю?
16 дек 13, 13:58    [15298720]     Ответить | Цитировать Сообщить модератору
 Re: Как интерпретировать данные профайлера MSSQL2005  [new]
Glory
Member

Откуда:
Сообщений: 104760
И что нужно понять из этого кода ?
Нужно самому распределить стоимость выполнения и затраченные ресурсы между командами ?
16 дек 13, 14:00    [15298737]     Ответить | Цитировать Сообщить модератору
 Re: Как интерпретировать данные профайлера MSSQL2005  [new]
Игорь Горбонос
Member

Откуда: Днепропетровск
Сообщений: 4236
Glory,

А куда смотреть и что распределять? Я бы попробовал сам это сделать.

Вообще я хотел понять по каким критериям можно оценить стало лучше или хуже или осталось как было.
16 дек 13, 14:11    [15298828]     Ответить | Цитировать Сообщить модератору
 Re: Как интерпретировать данные профайлера MSSQL2005  [new]
Glory
Member

Откуда:
Сообщений: 104760
Игорь Горбонос
А куда смотреть и что распределять?

Наверное надо в профайлере трассировать все команды процедуры ?

Игорь Горбонос
Вообще я хотел понять по каким критериям можно оценить стало лучше или хуже или осталось как было.

Стало лучше или хуже что ?
Какие проблемы были до оптимизации ? Что именно вы пытались оптимизировать и почему ?
16 дек 13, 14:14    [15298851]     Ответить | Цитировать Сообщить модератору
 Re: Как интерпретировать данные профайлера MSSQL2005  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Игорь Горбонос,

время выполнения (duration) изменилось незначительно, а вот нагрузка на диск (reads) увеличилась на порядок. итого - новый вариант хуже старого
к "стоимостям" запросов стоит относиться аккуратно, она показывает не совсем то, что вы от нее поначалу ждете. скажем так, более быстрый вариант запроса почти всегда стоит дороже. логично же, если подумать?
16 дек 13, 14:40    [15299047]     Ответить | Цитировать Сообщить модератору
 Re: Как интерпретировать данные профайлера MSSQL2005  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
Crimean
Игорь Горбонос,

время выполнения (duration) изменилось незначительно, а вот нагрузка на диск (reads) увеличилась на порядок. итого - новый вариант хуже старого
к "стоимостям" запросов стоит относиться аккуратно, она показывает не совсем то, что вы от нее поначалу ждете. скажем так, более быстрый вариант запроса почти всегда стоит дороже. логично же, если подумать?
Почему логично?
16 дек 13, 15:08    [15299308]     Ответить | Цитировать Сообщить модератору
 Re: Как интерпретировать данные профайлера MSSQL2005  [new]
Игорь Горбонос
Member

Откуда: Днепропетровск
Сообщений: 4236
Glory,

Оптимизировать пытаюсь запросы на получение информации из этой таблицы. Не только этого, но и других.
Оптимизировать пытаюсь потому, что из-за трёхзвенки ограничен по времени выполнения и раньше, время выполнения запросов укладывалось в 2 минуты, но в сентябре запросы стали выполнятся до 10-15 минут. Для тех, кому нужны результаты "долгоиграющих" запросов я переделал на двухзвенку и они вынужденны ждать. Сейчас появилась возможность провести различные тесты и опробовать варианты оптимизаций. Что я и делаю. И в связи с этим появился вопрос: а правильно ли я интерпретирую ту информацию, которую мне выдает тот-же SSMS или профайлер.


Crimean,

я не совсем уверен что Reads это нагрузка на диск, если выполнить так:
SET STATISTICS IO ON
GO
EXEC corp.dbo.sp_get_view_close_report
     @work_year = 2013,
     @start_month = 1,
     @end_month = 10
GO
	
EXEC corporation.dbo.sp_get_view_close_report
     @work_year = 2013,
     @start_month = 1,
     @end_month = 10
GO
SET STATISTICS io OFF
GO

Речь идет о таблице raw_fact
то
+ по новой базе
Таблица "branch_detail". Число просмотров 8, логических чтений 16, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "branch_end". Число просмотров 634, логических чтений 1932, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "branch_detail_relation_end". Число просмотров 64, логических чтений 560, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "branch". Число просмотров 2, логических чтений 752, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "Worktable". Число просмотров 0, логических чтений 0, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "raw_fact". Число просмотров 300, логических чтений 2356439, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "Worktable". Число просмотров 0, логических чтений 0, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.


+ по старой базе
Таблица "branch_detail". Число просмотров 8, логических чтений 16, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "raw_fact". Число просмотров 532, логических чтений 426250, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "branch_end". Число просмотров 532, логических чтений 1680, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "branch". Число просмотров 0, логических чтений 1472, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "branch_detail_relation_end". Число просмотров 64, логических чтений 560, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "Worktable". Число просмотров 0, логических чтений 0, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "Worktable". Число просмотров 0, логических чтений 0, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
и если я правильно понимаю чтений именно с диска нет(все данные в кеше). Есть меньшее число просмотров и большее число чтений. Меньшее число просмотров я связываю с тем, что перестроен кластерный индекс и данные в рамках года+месяца+филиала лежат "рядом" т.е. нужно меньше времени на поиск, но не понятно почему на порядок больше логических чтений.
16 дек 13, 16:19    [15299834]     Ответить | Цитировать Сообщить модератору
 Re: Как интерпретировать данные профайлера MSSQL2005  [new]
Glory
Member

Откуда:
Сообщений: 104760
Игорь Горбонос
Glory,

Оптимизировать пытаюсь запросы на получение информации из этой таблицы.

Мда. Что именно то ?

Игорь Горбонос
Меньшее число просмотров я связываю с тем, что перестроен кластерный индекс и данные в рамках года+месяца+филиала лежат "рядом" т.е. нужно меньше времени на поиск, но не понятно почему на порядок больше логических чтений.

Число просмотров - это сколько раз читалась та или линая таблица. А не сколько времени занял поиск.
Если сканировать всю таблицу, например, то число обращений будет 1, а число чтений - размер таблицы
16 дек 13, 16:27    [15299896]     Ответить | Цитировать Сообщить модератору
 Re: Как интерпретировать данные профайлера MSSQL2005  [new]
Игорь Горбонос
Member

Откуда: Днепропетровск
Сообщений: 4236
Glory
Мда. Что именно то ?

Я не понимаю
я пытаюсь оптимизировать время выполнения некоторых запросов

Glory
Число просмотров - это сколько раз читалась та или иная таблица. А не сколько времени занял поиск.
Если сканировать всю таблицу, например, то число обращений будет 1, а число чтений - размер таблицы
Понятно
16 дек 13, 16:35    [15299971]     Ответить | Цитировать Сообщить модератору
 Re: Как интерпретировать данные профайлера MSSQL2005  [new]
Glory
Member

Откуда:
Сообщений: 104760
Игорь Горбонос
я пытаюсь оптимизировать время выполнения некоторых запросов

Для этого для начала нужно понять, на что тратится время выполнения текущего запроса.
16 дек 13, 16:39    [15300004]     Ответить | Цитировать Сообщить модератору
 Re: Как интерпретировать данные профайлера MSSQL2005  [new]
Игорь Горбонос
Member

Откуда: Днепропетровск
Сообщений: 4236
Glory,

Вы имеете в виду взять план выполнения запроса и посмотреть что, сколько раз и почему выполняется? И эти моменты пытаться оптимизировать?

И ещё один вопрос возник: в плане? в одной из строчек касаемо большой таблицы показывается EstimateRows = 2367,634, а в столбце Rows = 24243176
+ строчка из плана
RowsExecutesStmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions
24243176133 | | |--Index Seek(OBJECT:([corp].[dbo].[raw_fact].[_dta_index_raw_fact_11_1017106714__K14_K28_K8_K29_17_34_35] AS [rf]), SEEK:([rf].[code_branch_end_edge]=[corp].[dbo].[branch_end].[code] as [be].[code] AND [rf].[work_year]=[@work_year1] AND [rf].[month_report] >= [@start_month1] AND [rf].[month_report] <= [@end_month1]) ORDERED FORWARD)14632Index SeekIndex SeekOBJECT:([corp].[dbo].[raw_fact].[_dta_index_raw_fact_11_1017106714__K14_K28_K8_K29_17_34_35] AS [rf]), SEEK:([rf].[code_branch_end_edge]=[corp].[dbo].[branch_end].[code] as [be].[code] AND [rf].[work_year]=[@work_year1] AND [rf].[month_report] >= [@start_month1] AND [rf].[month_report] <= [@end_month1]) ORDERED FORWARD[rf].[month_report], [rf].[summa], [rf].[is_fact], [rf].[closed_fact], [rf].[closed_report]2367,6340,011273150,002761397241,215794[rf].[month_report], [rf].[summa], [rf].[is_fact], [rf].[closed_fact], [rf].[closed_report]NULLPLAN_ROW195,6085

почему ожидается меньшее число строк, чем получается в результате. Статистика только что созданная.
16 дек 13, 17:10    [15300263]     Ответить | Цитировать Сообщить модератору
 Re: Как интерпретировать данные профайлера MSSQL2005  [new]
Glory
Member

Откуда:
Сообщений: 104760
Игорь Горбонос
Вы имеете в виду взять план выполнения запроса и посмотреть что, сколько раз и почему выполняется? И эти моменты пытаться оптимизировать?

Да

Игорь Горбонос
И ещё один вопрос возник: в плане? в одной из строчек касаемо большой таблицы показывается EstimateRows = 2367,634, а в столбце Rows = 24243176

Лучше показать весь план
16 дек 13, 17:17    [15300309]     Ответить | Цитировать Сообщить модератору
 Re: Как интерпретировать данные профайлера MSSQL2005  [new]
aleks2
Guest
Игорь Горбонос
Есть таблица с данными по филиалам за несколько лет. В рамках года данных порядка 50 млн записей. Кластерный индекс в ней построен по PK, который IDENTITY + дополнительные индексы по разным комбинациям полей.
Есть хранимая процедура, которая считает по этой таблице сумму по месячно для каждого филиала(упрощенно) и выполняется порядка 30 секунд

Прочитав о различных способах оптимизации БД сделал копию базы из бекапа и перестроил кластерный индекс году+месяцу+филиалу, оставил PK на том-же поле IDENTITY и добавил ещё несколько индексов.
После перестройки базы эта-же хранимая процедура стала выполнятся немного быстрее - 27 секунд.


Может открыть страдальцу таинство Indexed View?

ЗЫ. Для Web-сервисов надо иметь не 30 сек, а 30 миллисекунд.
16 дек 13, 17:25    [15300361]     Ответить | Цитировать Сообщить модератору
 Re: Как интерпретировать данные профайлера MSSQL2005  [new]
Glory
Member

Откуда:
Сообщений: 104760
aleks2
Может открыть страдальцу таинство Indexed View?

У него в запросе UNION и PIVOT
16 дек 13, 17:34    [15300417]     Ответить | Цитировать Сообщить модератору
 Re: Как интерпретировать данные профайлера MSSQL2005  [new]
Игорь Горбонос
Member

Откуда: Днепропетровск
Сообщений: 4236
Glory
Лучше показать весь план

вот

К сообщению приложен файл (1.zip - 77Kb) cкачать
16 дек 13, 17:57    [15300568]     Ответить | Цитировать Сообщить модератору
 Re: Как интерпретировать данные профайлера MSSQL2005  [new]
Glory
Member

Откуда:
Сообщений: 104760
Для Nested Loops столбец EstimateRows - это для одной итерации, а Rows - соответственно для всех итераций

Повторяющиеся части в вашем плане запроса говорят о том, что его вообще надо переписывать
17 дек 13, 10:47    [15303135]     Ответить | Цитировать Сообщить модератору
 Re: Как интерпретировать данные профайлера MSSQL2005  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Игорь Горбонос,

Оставьте в покое эти logical_reads и сколько раз там чего выполняется. Явно же видно что проблема не в этом. Лучше посмотрите на то столько данных ожидается на входе в Hash Match оператор и сколько реально приходит. Лично я вижу 9мб против 1272мб. Вот вам и spill в tempdb и наверняка 95% времени выполнения запроса. Чтобы убедится в этом выполните пару раз вот этот запрос, в то время как выполняется ваш монстр:

select 
    tsu.session_id, tsu.request_id, 
    SUM(tsu.internal_objects_alloc_page_count- tsu.internal_objects_dealloc_page_count)/128. AS tempdb_internal_current_mb
from sys.dm_db_task_space_usage tsu 
where  tsu.internal_objects_alloc_page_count > 0
group by tsu.session_id, tsu.request_id 


Потом помножим это на 4. Именно столько раз у вас зачем то сканируется и самое главное группируется осовная таблица - raw_fact. Для начала перепишите запрос чтобы серверу не приходилось делать одну и ту же работу 4 раза.

Из-за чего происходит такая ошибка в оценках?
Во-первых, сервер не знает значения ваших переменных в момент компиляции, следовательно берет усредненные значения, которые могут быть далеки от реальных. Попробуйте для начала заменить все переменные на константы и посмотрите что получится.
Во-вторых, сервер легко может ошибится при оценке когда фильтр накладывается сразу по 4-м колонкам: code_branch_end_edge, is_fact, work_year, month_report. Может помочь создание дополнительных многоколоночных статистик, но не гарантирую. Нормального решения этой проблеме нет, ввиду несовершенства модели, можно только попытаться минимизировать эти ошибки.

Попробуйте переписать запрос так, чтобы во-первых минимизировать обращение в raw_fact, и во-вторых следите за тем чтобы если сервер выберет Hash Match для группировки, то количество реальных строк не сильно расходится с ожидаемым. Ну или еще лучше заставить его делать Stream Aggregate. При этом можно забить на неселективные фильтры. Например если фильтр по филиалу отфильтровывает только 1% значений, то code_branch_end_edge можно попробовать убрать из индексируемых колонок и запихать в INCLUDE.

Вот самая главная и тяжелая часть вашего запроса, начните с неё. Посмотрите планы, оценки и время выполнения, с переменными и с константами.

 SELECT 
        rf.code_branch_end_edge,
        rf.month_report,
        SUM(CASE WHEN rf.is_fact = 2 THEN rf.summa ELSE 0.00 END) AS summ_rep,
	    SUM(CASE WHEN rf.is_fact = 1 THEN rf.summa ELSE 0.00 END) AS summ_prep,
	    MIN(CAST(rf.closed_fact AS INT)) AS closed_fact,
	    MIN(CAST(rf.closed_report AS INT)) AS closed_report
FROM raw_fact rf WITH(NOLOCK)
WHERE rf.work_year = @work_year1
    AND (rf.month_report BETWEEN @start_month1 AND @end_month1)
GROUP BY
	    rf.code_branch_end_edge,
	    rf.month_report


И еще, параллелизм. Сомневаюсь что этот запрос сильно выигрывает от того что выполняется в 16 потоках одновременно, учитывая тот факт что некоторые потоки получают в 3 раза больше данных чем другие. Попробуйте уменьшить MAXDOP до 8, 4 или 1 и посмотрите на скорость выполнения. Да и для всего сервера можно было бы уменьшить хотя бы до 8 если у вас там 16 логических CPU.
18 дек 13, 02:35    [15308291]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить