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

Откуда:
Сообщений: 170
Добрый день!

Установлен сиквел сервер 2008 R2 Dev. Edition

Вылетело из головы, с помощью чего в managment studio

получить совет по расстановке индексов или по бесползеному использованию текущих индексов.
11 май 12, 13:19    [12535858]     Ответить | Цитировать Сообщить модератору
 Re: Рекомендуемые индексы для таблицы средствами SQL Serv Managment Studio  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
может полезен будет:

SELECT
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
  'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
  + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
  + ' ON ' + mid.statement
  + ' (' + ISNULL (mid.equality_columns,'')
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
    + ISNULL (mid.inequality_columns, '')
  + ')'
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
11 май 12, 13:26    [12535911]     Ответить | Цитировать Сообщить модератору
 Re: Рекомендуемые индексы для таблицы средствами SQL Serv Managment Studio  [new]
danton
Member

Откуда:
Сообщений: 170
andrew shalaev,

если я правильно вспомнил,

изначально включаю трейс, и потом кормлю его эдвайзеру
11 май 12, 13:28    [12535927]     Ответить | Цитировать Сообщить модератору
 Re: Рекомендуемые индексы для таблицы средствами SQL Serv Managment Studio  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
danton,

можно и так.
11 май 12, 13:31    [12535973]     Ответить | Цитировать Сообщить модератору
 Re: Рекомендуемые индексы для таблицы средствами SQL Serv Managment Studio  [new]
Александр52
Member

Откуда: Кокосовые острова ส็็็็็
Сообщений: 5133
АпАпАп!!!!!
Крайне актуально для меня)
т.е. вышеприведенным скриптом мне выдается совет по тому, каких индексов у меня не хватает? и насколько этому "совету" можно верить?
11 май 12, 13:39    [12536060]     Ответить | Цитировать Сообщить модератору
 Re: Рекомендуемые индексы для таблицы средствами SQL Serv Managment Studio  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
Александр52,

Верить вряд ли можно на 100%, можно опробовать на тестовом сервере и понять есть ли улучшение производительности. Но сразу создавать кучу новых индексов на боевом сервере НЕ рекомендуется
11 май 12, 13:50    [12536160]     Ответить | Цитировать Сообщить модератору
 Re: Рекомендуемые индексы для таблицы средствами SQL Serv Managment Studio  [new]
Александр52
Member

Откуда: Кокосовые острова ส็็็็็
Сообщений: 5133
Логично : ) пасибо))
11 май 12, 13:51    [12536170]     Ответить | Цитировать Сообщить модератору
 Re: Рекомендуемые индексы для таблицы средствами SQL Serv Managment Studio  [new]
Eagle_84
Member

Откуда: Москва
Сообщений: 1535
вот полезно будет почитать
Открытие скрытых данных для оптимизации производительности приложений
11 май 12, 14:49    [12536801]     Ответить | Цитировать Сообщить модератору
 Re: Рекомендуемые индексы для таблицы средствами SQL Serv Managment Studio  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
Eagle_84,

Просто офигительная статья!
11 май 12, 17:53    [12538305]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Рекомендуемые индексы для таблицы средствами SQL Serv Managment Studio  [new]
Снежинка
Member

Откуда:
Сообщений: 18
andrew shalaev
Извините за "чайник"-овский вопрос, но можно попросить Вас более доступным языком (грубо говоря, как для дебилов) пояснить как работает Ваш запрос? :)
Заранее, большое спасибо.
3 янв 15, 19:59    [17083045]     Ответить | Цитировать Сообщить модератору
 Re: Рекомендуемые индексы для таблицы средствами SQL Serv Managment Studio  [new]
Jovanny
Member

Откуда:
Сообщений: 1195
Снежинка,

Он генерирует скрипты для формирования индексов, которые, по мнению SQL Server, следует добавить для улучшения производительности.
5 янв 15, 10:17    [17086195]     Ответить | Цитировать Сообщить модератору
 Re: Рекомендуемые индексы для таблицы средствами SQL Serv Managment Studio  [new]
Снежинка
Member

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

А что это за столбец improvement_measure? Я вижу по запросу, что это migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans), но более доступным языком можно объяснить, что это за количество чего? :)
5 янв 15, 22:20    [17087735]     Ответить | Цитировать Сообщить модератору
 Re: Рекомендуемые индексы для таблицы средствами SQL Serv Managment Studio  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37056
Снежинка
Jovanny

А что это за столбец improvement_measure? Я вижу по запросу, что это migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans), но более доступным языком можно объяснить, что это за количество чего? :)
Прочитайте в хелпе, что означает каждая из колонок, участвующая в выражении. Если не понятно, ждите, пока (если) появится автор и скажет вам что-то вразумительное.
5 янв 15, 22:25    [17087751]     Ответить | Цитировать Сообщить модератору
 Re: Рекомендуемые индексы для таблицы средствами SQL Serv Managment Studio  [new]
Снежинка
Member

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

Если я правильно понимаю, то SQL каким-то образом учитывает при построении запроса использование индексов, даже несуществующих? И накапливает статистику об этом. А этот запрос работает именно с этой статистикой?
5 янв 15, 23:21    [17087901]     Ответить | Цитировать Сообщить модератору
 Re: Рекомендуемые индексы для таблицы средствами SQL Serv Managment Studio  [new]
Glory
Member

Откуда:
Сообщений: 104760
Снежинка
то SQL каким-то образом учитывает при построении запроса использование индексов, даже несуществующих?

Да, в хелпе прямо так и написано
Information returned by sys.dm_db_missing_index_groups is updated when a query is optimized by the query optimizer, and is not persisted. Missing index information is kept only until SQL Server is restarted. Database administrators should periodically make backup copies of the missing index information if they want to keep it after server recycling

Снежинка
А этот запрос работает именно с этой статистикой?

В хелпе так же есть описание каждого из системных представлений, участвующих в данном запросе
6 янв 15, 09:35    [17088507]     Ответить | Цитировать Сообщить модератору
 Re: Рекомендуемые индексы для таблицы средствами SQL Serv Managment Studio  [new]
Crimean
Member

Откуда:
Сообщений: 13148
я советую внимательно смотреть на существующие индексы, особенно кластерные, прежде чем "соглашаться" с тем, что предлагает этот скриптик. иногда он предлагает сделать "почти такие же", как уже есть, разница в паре включаемых полей. часто включает поля кластерного индекса и т.д.
в общем это скорее повод для подумать, чем готовый сценарий к действию
6 янв 15, 10:17    [17088565]     Ответить | Цитировать Сообщить модератору
 Re: Рекомендуемые индексы для таблицы средствами SQL Serv Managment Studio  [new]
Jovanny
Member

Откуда:
Сообщений: 1195
Снежинка,

Эта ссылка может оказаться полезной: SQL Server 2005, 2008: Создание недостающих индексов.
В 12535911 использует для определения полезности индекса (improvement_measure) как поиск, так и сканирование по индексу(migs.user_seeks + migs.user_scans).
В курсе "SQL Server 2005 Реализация и обслуживание" предлагается использовать только поиск.
Хотя я проверил все свои базы, везде, как ни странно user_scans равно 0.

В общем, советую использовать рекомендации из курса Microsoft:
Значение выше 5000 в промышленных системах означает, что следует рассмотреть возможность создания этих индексов.
Если же значение превышает 10000, это обычно означает, что индекс может обеспечить значительное повышение производительности для операций чтения.
6 янв 15, 10:46    [17088607]     Ответить | Цитировать Сообщить модератору
 Re: Рекомендуемые индексы для таблицы средствами SQL Serv Managment Studio  [new]
Jovanny
Member

Откуда:
Сообщений: 1195
И конечно же,
Crimean
это скорее повод для подумать, чем готовый сценарий к действию
6 янв 15, 10:50    [17088611]     Ответить | Цитировать Сообщить модератору
 Re: Рекомендуемые индексы для таблицы средствами SQL Serv Managment Studio  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Crimean
иногда он предлагает сделать "почти такие же", как уже есть, разница в паре включаемых полей. часто включает поля кластерного индекса и т.д.
И что из этого следует? Включать или не включать поля кластерного в новый индекс, все равно разницы никакой не будет.

Jovanny
В курсе "SQL Server 2005 Реализация и обслуживание" предлагается использовать только поиск.
Хотя я проверил все свои базы, везде, как ни странно user_scans равно 0.
А кто нибудь видел чтобы user_scans было больше 0? Я лично, ни разу.

Jovanny
В общем, советую использовать рекомендации из курса Microsoft:
Значение выше 5000 в промышленных системах означает, что следует рассмотреть возможность создания этих индексов.
Если же значение превышает 10000, это обычно означает, что индекс может обеспечить значительное повышение производительности для операций чтения.
Очень странная рекомендация. Я бы даже сказал - нелепая.
Какие то, непонятно откуда взявшиеся, пороговые значения (5К и 10К) в абсолютных величинах, учитывая что значение само по себе кумулятивное, да и вдобавок avg_total_user_cost вообще измеряется в условных единицах. Так что, мое мнение - абсолютное значение improvement_measure вообще никакого смысла в себе не несет и нужно лишь для сравнения с другими индексами, чтобы определить на что смотреть в первую очередь.
6 янв 15, 20:30    [17090479]     Ответить | Цитировать Сообщить модератору
 Re: Рекомендуемые индексы для таблицы средствами SQL Serv Managment Studio  [new]
Снежинка
Member

Откуда:
Сообщений: 18
Спасибо за ответы! Более-менее понятно. Я понимаю, что слепо нельзя верить этому запросу, но можно, так сказать, практический пример? Вот итоги конкретной базы данных (выборка данных где improvement_measure более 400):
improvement_measure	user_seeks	avg_total_user_cost	avg_user_impact

1356947622,57	          1409,00	     963057,22	             100,00
   2120116,29	         55380,00	         74,92	              51,10
   2014064,38	         55362,00	         74,92	              48,56
   1938806,93	         55380,00	         74,92	              46,73
    183774,12	         13427,00	         15,42	              88,75
    134543,78        	  5788,00	         23,29	              99,79
     97305,57	        511625,00	          0,20	              96,18
     97092,15	        509425,00	          0,20	              96,35
     63270,50	        267083,00	          0,38	              61,79
     62049,83	          5130,00	         12,26	              98,66
     61503,80	          6756,00	          9,80	              92,90
     53901,37	          1680,00	         34,06	              94,21
     43478,96	          1886,00	         23,07	              99,95
     42657,25	          4727,00	          9,03	              99,96
     37860,79	          1698,00	         22,61	              98,60
     37363,36	           930,00	         40,75	              98,59
     36324,89	          1048,00	         62,39	              55,56
     33577,13	           859,00	         39,73	              98,38
     32203,74	           649,00	         70,99	              69,90
     30689,74	          1344,00	         55,38	              41,23
     26342,16	           718,00	         36,72	              99,90
     24687,87	           665,00	         46,23	              80,31
     24188,01	          2548,00	          9,95	              95,41
     23805,32	           890,00	         28,98	              92,31
     21655,21	           704,00	         32,37	              95,04
     19311,49	           732,00	         26,45	              99,74
     17859,74	        266326,00	          0,11	              62,49
     17786,54	           768,00	         55,18	              41,97
     17225,30	           470,00	         38,49	              95,22
     15634,73	           467,00	         33,48	              99,99
     14629,01	           549,00	         32,60	              81,73
     14098,55	           895,00	         15,82	              99,57
     13384,14	           845,00	         16,26	              97,41
     13300,13	           333,00	         45,12	              88,53
     12779,47	           349,00	         43,10	              84,96
     12656,75	          1463,00	          8,66	              99,92
     12328,81	          1364,00	          9,04	              99,96
     10140,83	          1098,00	          9,25	              99,89
     10137,75	           825,00	         12,29	              99,97
      9387,42	           251,00	         37,40	              99,99
      8939,92	           264,00	         33,88	              99,95
      6201,65	           676,00	          9,24	              99,27
      5138,04	           135,00	         65,19	              58,38
      5092,44	         13140,00	          0,39	              99,01
      5009,63	         13140,00	          0,39	              97,40
      4906,93	           552,00	          8,90	              99,92
      4885,88	           549,00	          8,90	              99,96
      4826,68	           296,00	         41,02	              39,75
      4348,08	           460,00	          9,46	              99,96
      4100,31	           452,00	          9,08	              99,96
      3949,59	           309,00	         12,79	              99,97
      3887,83	         61137,00	          0,07	              88,87
      3715,10	            95,00	         67,90	              57,59
      3289,83	           126,00	         26,14	              99,88
      3281,85	           351,00	         96,59	               9,68
      3182,39	           873,00	          4,34	              83,91
      2987,41	            81,00	        135,79	              27,16
      2501,99	         42072,00	          0,07	              88,14
      2080,62	           164,00	         12,69	              99,97
      2059,30	            52,00	         40,25	              98,40
      1813,75	            45,00	         78,45	              51,38
      1733,41	           136,00	         12,75	              99,97
      1729,82	            47,00	         36,91	              99,72
      1658,35	            45,00	         37,46	              98,38
      1625,95	            45,00	         78,45	              46,06
      1590,54	            43,00	         39,29	              94,15
      1329,20	            35,00	         38,02	              99,89
      1277,35	            34,00	         38,02	              98,82
      1181,60	            45,00	         63,59	              41,29
       997,59	            45,00	         63,59	              34,86
       870,45	            70,00	         12,44	              99,97
       784,84	            90,00	          8,73	              99,90
       755,92	            28,00	        102,38	              26,37
       707,11	            45,00	         53,54	              29,35
       656,54	            18,00	         36,48	              99,98
       632,57	            17,00	         76,41	              48,70
       621,00	          4942,00	          0,14	              88,71
       618,44	          3867,00	          0,17	              94,25
       584,79	           722,00	          1,08	              74,84
       559,58	          3206,00	          0,18	              97,71
       527,78	            14,00	         41,85	              90,08
       521,12	            45,00	         53,54	              21,63
       511,82	            14,00	         36,57	              99,97
       485,17	            11,00	        378,92	              11,64
       473,02	            13,00	         55,26	              65,84
       460,79	            11,00	         79,49	              52,70
       400,49	            46,00	          9,01	              96,66

Явно напрашивается применить индекс из первой строки. Честно говоря, я его уже добавила, завтра посмотрим сказалось ли это на производительности.
Правильно ли, вообще, я сделала? И что можно сказать по поводу остальных значений? На что нужно обратить внимание?
Большое спасибо всем за терпение и помощь!

P.S. С Рождеством!
7 янв 15, 23:40    [17093128]     Ответить | Цитировать Сообщить модератору
 Re: Рекомендуемые индексы для таблицы средствами SQL Serv Managment Studio  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Снежинка
Спасибо за ответы! Более-менее понятно. Я понимаю, что слепо нельзя верить этому запросу, но можно, так сказать, практический пример?
Явно напрашивается применить индекс из первой строки. Честно говоря, я его уже добавила, завтра посмотрим сказалось ли это на производительности.
Ну теперь можете посмотреть, используется ли этот индекс или нет.

+ Например вот таким запросом.
DECLARE @tableName VARCHAR(128)
SET @tableName = 'Имя вашей таблицы'

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET LOCK_TIMEOUT 10000

SELECT 
  ISNULL(i.name, '<<<HEAP>>>') AS IndexName,
  i.is_primary_key AS PK,
  i.is_unique_constraint AS UQ,
  CAST(CASE WHEN i.index_id = 1 THEN 1 ELSE 0 END AS BIT) AS Clust,
  CAST(SizeMB AS DECIMAL(20, 2)) AS SizeMB,
  user_seeks,
  user_scans,
  user_lookups,
  user_seeks + user_scans + user_lookups AS total_user_reads,
  CAST(user_scans*SizeMB/1024. AS DECIMAL(30, 2)) AS TotalScanGB,
  user_updates, 
  last_user_seek,
  last_user_scan,
  last_user_lookup,
  CASE WHEN ISNULL(s.last_user_seek, '1900-01-01') > ISNULL(s.last_user_scan, '1900-01-01') 
  THEN 
    CASE WHEN ISNULL(s.last_user_seek, '1900-01-01') > ISNULL(s.last_user_lookup, '1900-01-01') 
    THEN 
      s.last_user_seek
    ELSE
      s.last_user_lookup
    END
  ELSE 
    CASE WHEN ISNULL(s.last_user_scan, '1900-01-01') > ISNULL(s.last_user_lookup, '1900-01-01') 
    THEN 
      s.last_user_scan
    ELSE
      s.last_user_lookup
    END          
  END AS last_user_read,
  last_user_update
FROM sys.indexes AS i
LEFT JOIN 
    (
      select object_id, index_id, SUM(used_page_count)/128. AS SizeMB
      from sys.dm_db_partition_stats
      group by object_id, index_id
    ) p ON p.object_id = i.object_id AND p.index_id = i.index_id
LEFT JOIN sys.dm_db_index_usage_stats AS s
  ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
  AND s.database_id = DB_ID()
WHERE i.[object_id] = OBJECT_ID(@tableName) 
OPTION(MAXDOP 1)

А вообще судя по зашкаливающему значению avg_total_user_cost, скорее всего этот индекс нужен какому то очень тяжелому запросу. Неплохо было бы его найти, потому что не факт, что создание индекса исправит ситуацию на все 100.
8 янв 15, 00:29    [17093194]     Ответить | Цитировать Сообщить модератору
 Re: Рекомендуемые индексы для таблицы средствами SQL Serv Managment Studio  [new]
NickAlex66
Member

Откуда:
Сообщений: 319
Снежинка,

По большому счету Вам нужно переделывать всю карту индексов в проблемной базе. А один, два индекса картину в целом не изменят.
8 янв 15, 01:54    [17093320]     Ответить | Цитировать Сообщить модератору
 Re: Рекомендуемые индексы для таблицы средствами SQL Serv Managment Studio  [new]
Jovanny
Member

Откуда:
Сообщений: 1195
Mind
Так что, мое мнение - абсолютное значение improvement_measure вообще никакого смысла в себе не несет и нужно лишь для сравнения с другими индексами, чтобы определить на что смотреть в первую очередь.

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

Но никто не скажет, что анализ плана запроса бесполезный.
8 янв 15, 10:01    [17093597]     Ответить | Цитировать Сообщить модератору
 Re: Рекомендуемые индексы для таблицы средствами SQL Serv Managment Studio  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Jovanny
Mind
Так что, мое мнение - абсолютное значение improvement_measure вообще никакого смысла в себе не несет и нужно лишь для сравнения с другими индексами, чтобы определить на что смотреть в первую очередь.

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

Но никто не скажет, что анализ плана запроса бесполезный.
Не понял вашу мысль. Я говорил лишь о том, что значения improvement_measure относительные, а не абсолютные.
8 янв 15, 19:59    [17095086]     Ответить | Цитировать Сообщить модератору
 Re: Рекомендуемые индексы для таблицы средствами SQL Serv Managment Studio  [new]
Снежинка
Member

Откуда:
Сообщений: 18
NickAlex66
По большому счету Вам нужно переделывать всю карту индексов в проблемной базе.

Весь момент в том, что индексов в базе то почти и нет.
Это наше новое ПО. Исходники нам должны отдать в марте, но и сейчас видно, что база в общем-то тормозит. Я поэтому и решила заняться грамотной расстановкой индексов. Купила и попыталась почитать умные книги типа "SQL Server 2008 руководство администратора" Брайана Найта и других, но там на тему индексов весьма скудно написано Поэтому решила попросить помощи здесь.
Mind
Ну теперь можете посмотреть, используется ли этот индекс или нет.

Отработали рабочий день. Запустила. Получила такую картину:
PK     UK     Clust     SizeMB     user_seeks     user_scans     user_lookups     total_user_reads     TotalScanGB     user_updates           last_user_seek            last_user_scan            last_user_lookup           last_user_read            last_user_update
0       0       0       26.11         14528           74               0              14602               1.89           5636            2015-01-08 19:46:15.637     2015-01-08 16:50:19.907            NULL               2015-01-08 19:46:15.637   2015-01-08 19:39:10.223

Это говорит о том, что как-то индекс помог, но все-таки недостаточно эффективно. Правильно ли я интерпретирую результат выполнения запроса? Спасибо.
8 янв 15, 21:07    [17095212]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить