Метод оценки временных затрат на выполнение набора задач по историческим данным

В этой статье я расскажу о ситуации, когда необходимо дать оценку для scope задач, при условии, что есть оценка для каждой из них и есть данные, по которым понятно, как предыдущие оценки соотносятся с реально затраченным временем.

Примеры, когда это может пригодиться:

- Задачи оценены в story point-ах, а требуется оценить сколько это будет в часах

- Есть оценки в задач часах, но предыдущие данные показывают, что такие оценки расходятся с реально потраченным временем и хотелось бы получить более точную оценку


Теперь сам метод. Итак, пусть у нас есть:

1) Исторические данные по задачам, где для каждой задачи есть:

     he_i - её оценка
     ht_i - реально затраченное время

     Оценка и затраченное время могут быть как в разных единицах (например story point-ы и часы), так и в одинаковых (часы)

2) Некоторое множество задач, каждая из которых оценена в тех же единицах, что и в предыдущем пункте.


Мы попробуем получить интервальную оценку затраченного времени (интервал, в который с заданной вероятностью попадет суммарное время на выполнение этих задач)

Шаг 1. Вначале сгруппируем и упорядочим исторические данные по значению оценки. Т.е получится несколько групп задач:

задачи с оценкой he₁,
задачи с оценкой he₂,
...
задачи с оценкой he_K

he_i < he_i+1, 1 <= i < K

Шаг 2. Если есть группы, в которых меньше двух задач, объединим их со следующими в списке группами.

В результате получится такая структура групп задач:

G₁ - задачи у которых оценка лежит в пределах от a₁ до b₁
G₂ - задачи у которых оценка лежит в пределах от a₁ до b₂
...
G_M - задачи у которых оценка лежит в пределах от a_M до b_M

b_i < a_i+1, 1 <= i < M

Шаг 3. Для каждой группы по множеству значений реально потраченного времени посчитаем 3 показателя:

Количество: m_i
Среднее: X_i
Несмещенную выборочную дисперсию (экселевская функция VAR.S): S_i²


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

Оценка	Реальное значение
2	2.5
3	3.5
2	1.5
5	6
8	8
6	5
4	3
6	7
3	2.5
10	14
2	3.5

Группируем  строки по значению оценки:

Оценка	Количество	Реальные значения
2	3	2.5, 1.5, 3.5
3	2	3.5, 2.5
4	1	3
5	1	6
6	2	5, 7
8	1	8
10	1	14

Есть 4 строки, в которых количество значений меньше 2 (строки с оценкой 4, 5, 8 и 10). Начиная с начала таблицы, объединим такие строки со следующими в списке, чтобы это исправить:

Оценка	Количество	Реальные значения
2	3	2.5, 1.5, 3.5
3	2	3.5, 2.5
4-5	2	3, 6
6	2	5, 7
8-10	2	8, 14

Теперь посчитаем среднее и несмещенную дисперсию:

Оценка	Количество	Среднее	Несмещенная дисперсия
2	3	(2.5+1.5+3.5)/3 = 2.5	((2.5-2.5)2+(1.5-2.5)2+(3.5-2.5)2)/(3-1) = 1
3	2	(3.5+2.5)/2 = 3	((3.5-3)2+(2.5-3)2)/(2-1) = 0.5
4-5	2	(3+6)/2 = 4.5	((3-4.5)2+(6-4.5)2)/(2-1) = 4.5
6	2	(5+7)/2 = 6	((5-6)2+(7-6)2)/(2-1) = 2
8-10	2	(8+14)/2 = 11	((8-11)2+(14-11)2)/(2-1) = 18

Вернемся к теоретической части.

Шаг 4. Сгруппируем оценки новых задач по значению самой оценки. Т.е. получим данные такого типа:

k₁ задач с оценкой e₁
k₂ задач с оценкой e₂
...
k_L задач с оценкой e_L

e_i < e_i+1, 1 <= i < K

Шаг 5. Чтобы получить итоговую оценку соотнесем оценки новых задач с историческими данными. Для каждой группы новых задач из предыдущего шага находим группу из исторических данных (из шага 2), в которую попадает ее оценка. Если такой группы нет, то берем самую первую группу, у которой оценка больше (лучше переоценить задачу, чем недооценить). Если оценка выше, чем самая высокая оценка в таблице, отнесем ее к последней группе. В примере выше задачу с оценкой 3 отнесем ко второй группе, задачу с оценкой 3.5 - к третьей, задачу с оценкой 13 - к последней.

В результате этой процедуры получим N групп задач, которые нам нужно оценить, соотнесенное с историческими данными:

n₁ задач, относящихся к группе из m₁ исторических задач, в которой среднее X₁ и дисперсия S₁²
n₂ задач, относящихся к группе из m₂ исторических задач, в которой среднее X₂ и дисперсия S₂²
...
n_N задач, относящихся к группе из m_N исторических задач, в которой среднее X_N и дисперсия S_N²

тогда распределение суммарного времени E для этих задач будет приближенно описываться таким законом:

(1)

T_i(m_i-1) - независимые случайные величины, имеющие распределение Стьюдента с m_i-1 степенями свободы.

Недостаток этой формулы в том, что сложно дать интервальную оценку времени (т.е оценку типа "с вероятностью 90% на эти задачи уйдет от 100 до 130 часов"). Нужны некоторые навыки программирования, чтобы смоделировать это распределение. Формула может быть полезна программистам, разрабатывающим софт для управления проектами, но для быстрого практического применения ее придется упростить.

Здесь нам помогут 2 фактора:

1) Сумма независимых распределений хорошо приближается нормальным распределением
2) Распределение Стьюдента при увеличении числа степеней свободы сходится к нормальному

Поэтому для приближения заменим каждое из распределений Стьюдента в формуле (X) на нормальное распределение с той же дисперсией. В итоге получим, что распределение оценки приближается нормальным распределением с математическим ожиданием:

(2)

и дисперсией:

(3)

где

S²_{n, i} - выборочная дисперсия (экселевская функция VAR.P) i-ой соответствующей группы исторических данных. Т.е в упрощенной формуле на шаге 3 будет считаться именно выборочная дисперсия.

Здесь стоит заметить, что распределение Стьюдента с n степенями свободы имеет дисперсию только при n >= 3. Поэтому для работы приближенной формулы необходимо, чтобы количество элементов в каждой группе исторических данных было не менее 4. В этом случае шаг 2 меняется следующим образом:

Если группа содержит меньше 4 элементов, объединяем ее с последующей. Если последняя группа содержит меньше 4 элементов, обїединяем ее с предпоследней. Повторяем до тех пор, пока каждая группа не будет содержать не менее 4 элементов. Очевидно, что эта процедура может быть выполнена, если в исторических данных есть не менее 4 оценок.


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

Если например они находятся в таблице HistoricalData со столбцами с именами Estimate и Time,
то предварительные данные для расчета среднего и дисперсии можно получить таким запросом:

SELECT [Estimate], COUNT(1) AS [Count], SUM([Time]) AS [Sum], SUM([Time]* [Time]) AS [SumOfSquares] FROM [HistoricalData] GROUP BY [Estimate] ORDER BY [Estimate]

(4)

При объединении групп (шаг 2) суммируются количества (столбец Count) и суммы (Sum и SumOfSquares).

После этого среднее и дисперсии в i-ой строке таблицы считаются по таким формулам:

Xi = Sumi/Counti

(5)

Sn,i2 = (SumOfSquaresi - Sumi2)/Counti

(6)

Si2 = (SumOfSquaresi - Sumi2)/(Counti-1)

(7)

Пример расчета оценки с пошаговым разбором

Пусть есть такой набор исторических данных (левая таблица) и набор оценок задач (список справа):

Оценка Реальное значение 16 28 1 1 3 3 1 1 8 4 8 11 10 5 2 2 6 14 3 0.5 1 4 1 1 5 5 5 5 12 12 5 4 1 0.5 8 10 2 0.5 8 13 16 22 4 6 1 2.5 1 1.5 4 7 4 12 8 8.5 6 6 12 12 4 4 12 11 1 1 6 3.5 5 6 8 7 12 11 5 36 8 13.5 1 2 12 12 2 0.5 3 6.5 6 4 3 5.5 1 1 4 3.5 3 3 3 5 3 1 10 24.5 3 9 3 3 1 5 3 4 3 2 8 9.5 1 2.5 4 5.5 5 5 2 5 8 8 4 8.5 1 1 1 5 2 2 3 1 5 1.5 1 1 2 4 2 2 1 1 4 5 2 6 4 1 4 4 1 1 12 14 12 12 2 2 16 16 3 3 3 4.5 5 5 6 7 6 4 4 5 8 6 12 11 3 3 6 5 3 2 5 3 4 4 2 2 3 1 2 13 6 3 8 20.5 6 0.5 4 4 6 6 4 4 12 9.5 5 11 4 4 6 2 8 8.5 2 2 6 6.5 8 8 6 6 2 2 4 3 6 26.5 10 27 8 2 4 5.5 8 8 8 8 3 16 3 2 3 2.5 1 1 2 2 2 2 6 8 5 5 2 2 5 6.5 5 13.5 16 19 18 18 6 6 8 26 8 15 6 6 6 11 3 3 16 17 10 16 13 20 2 2 1 2 7 2 11 17.5 4 11 4 2 8 8 13 12 4 1 2 0.5 3 4 8 24 8 8 13 13 2 2 3 3 4 4 8 6 6 4 3 4 1 1 3 3

4, 4, 3, 5, 4, 5, 15, 5, 12, 3, 4, 2, 3, 1, 4, 4, 8, 4, 3, 1, 2, 3, 8, 8, 3, 8, 8, 4, 8, 1, 1, 16, 5, 3, 5, 6, 2

Дадим интервальную оценку времени, которое будет затрачено на выполнение задач справа

Шаг 1. Группируем исторические данные, считая количество сумму и сумму квадратов реальных значений (я здесь воспользовался SQL-запросом (4))

Оценка	Количество	Сумма	Сумма квадратов
1	20	36	100
2	19	53.5	294.75
3	25	94.5	596.25
4	21	104	672
5	13	106.5	1829.75
6	19	129	1453
7	1	2	4
8	22	232.5	3229.25
10	4	72.5	1610.25
11	1	17.5	306.25
12	9	104.5	1225.25
13	3	45	713
16	5	102	2174
18	1	18	324

Шаг 2. Идем по таблице сверху вниз и объединяем каждую строку, в которой количество меньше 4 со следующей, при этом суммируя значения в столбцах Количество, Сумма и Сумма квадратов.

Оценка	Количество	Сумма	Сумма квадратов
1	20	36	100
2	19	53.5	294.75
3	25	94.5	596.25
4	21	104	672
5	13	106.5	1829.75
6	19	129	1453
7	1	2	4
8	22	232.5	3229.25
10	4	72.5	1610.25
11	1	17.5	306.25
12	9	104.5	1225.25
13	3	45	713
16	5	102	2174
18	1	18	324

Строку с оценкой 7 объединим со строкой с оценкой 8

Оценка	Количество	Сумма	Сумма квадратов
1	20	36	100
2	19	53.5	294.75
3	25	94.5	596.25
4	21	104	672
5	13	106.5	1829.75
6	19	129	1453
7 - 8	23	234.5	3233.25
10	4	72.5	1610.25
11	1	17.5	306.25
12	9	104.5	1225.25
13	3	45	713
16	5	102	2174
18	1	18	324

Строку с оценкой 11 объединим со строкой с оценкой 12

Оценка	Количество	Сумма	Сумма квадратов
1	20	36	100
2	19	53.5	294.75
3	25	94.5	596.25
4	21	104	672
5	13	106.5	1829.75
6	19	129	1453
7 - 8	23	234.5	3233.25
10	4	72.5	1610.25
11 - 12	10	122	1531.5
13	3	45	713
16	5	102	2174
18	1	18	324

Строку с оценкой 13 объединим со строкой с оценкой 16

Оценка	Количество	Сумма	Сумма квадратов
1	20	36	100
2	19	53.5	294.75
3	25	94.5	596.25
4	21	104	672
5	13	106.5	1829.75
6	19	129	1453
7 - 8	23	234.5	3233.25
10	4	72.5	1610.25
11 - 12	10	122	1531.5
13 - 16	8	147	2887
18	1	18	324

В последней строке с оценкой 18 количество тоже меньше 4, но поскольку для нее нет следующей строки, объединим ее с предыдущей:

Оценка	Количество	Сумма	Сумма квадратов
1	20	36	100
2	19	53.5	294.75
3	25	94.5	596.25
4	21	104	672
5	13	106.5	1829.75
6	19	129	1453
7 - 8	23	234.5	3233.25
10	4	72.5	1610.25
11 - 12	10	122	1531.5
13 - 18	9	165	3211

Шаг 3. Считаем среднее и выборочную дисперсию в каждой группе:

Оценка	Количество	Сумма	Сумма квадратов	Среднее	Выборочная дисперсия
1	20	36	100	36/20 = 1.8	100/20 - 1.82 = 1.76
2	19	53.5	294.75	53.5/19 = 2.815789474	294.75/19 - 2.8157894742 = 7.584487535
3	25	94.5	596.25	94.5/25 = 3.78	596.25/25 - 3.782 = 9.5616
4	21	104	672	104/21 = 4.952380952	672/21 - 4.9523809522 = 7.473922902
5	13	106.5	1829.75	106.5/13 = 8.192307692	1829.75/13 - 8.1923076922 = 73.63609467
6	19	129	1453	129/19 = 6.789473684	1453/19 - 6.7894736842 = 30.3767313
7 - 8	23	234.5	3233.25	234.5/23 = 10.19565217	3233.25/23 - 10.195652172 = 36.62476371
10	4	72.5	1610.25	72.5/4 = 18.125	1610.25/4 - 18.1252 = 74.046875
11 - 12	10	122	1531.5	122/10 = 12.2	1531.5/10 - 12.22 = 4.31
13 - 18	9	165	3211	165/9 = 18.33333333	3211/9 - 18.333333332 = 20.66666667

Шаг 4. Группируем оценки новых задач. Напомню список:

4, 4, 3, 5, 4, 5, 15, 5, 12, 3, 4, 2, 3, 1, 4, 4, 8, 4, 3, 1, 2, 3, 8, 8,  3, 8, 8, 4, 8, 1, 1, 16, 5, 3, 5, 6, 2

Если сгруппировать, получится такая таблица:

Оценка	Количество
1	4
2	3
3	7
4	8
5	5
6	1
8	6
12	1
13	1
16	1

Шаг 5. Сопоставляем группы исторических данных (слева) и новых задач (справа):

Оценка Количество Среднее Выборочная дисперсия 1 20 1.8 1.76 2 19 2.815789474 7.584487535 3 25 3.78 9.5616 4 21 4.952380952 7.473922902 5 13 8.192307692 73.63609467 6 19 6.789473684 30.3767313 7 - 8 23 10.19565217 36.62476371 10 4 18.125 74.046875 11 - 12 10 12.2 4.31 13 - 18 9 18.33333333 20.66666667

Оценка Количество 1 4 2 3 3 7 4 8 5 5 6 1 8 6 12 1 13 1 16 1

Соответствующие группы выделены одинаковыми цветами

Теперь объединяем соответствующие строки 2 таблиц, группируя строки правой, если нужно (например последние две):

Оценка	Количество (mi)	Среднее (Xi)	Выборочная дисперсия (S2n, i)	Количество (ni)
1	20	1.8	1.76	4
2	19	2.815789474	7.584487535	3
3	25	3.78	9.5616	7
4	21	4.952380952	7.473922902	8
5	13	8.192307692	73.63609467	5
6	19	6.789473684	30.3767313	1
7 - 8	23	10.19565217	36.62476371	6
11 - 12	10	12.2	4.31	1
13 - 18	9	18.33333333	20.66666667	2

Далее, по формулам (2) и (3) получаем:

M[E] = 239.5180079

σ² = 1336.791715

т.е

σ = 36.56216233

Чтобы получить интервальную оценку, используем таблицу квантилей нормального распределения. С 90% вероятностью значение нормально распределенной случайной величины E лежит в пределах M[E]±1,645σ

Таким образом с вероятностью 90% можно утверждать, что время потраченное на планируемые задачи лежит в пределах от  239.5180079-1,645*36.56216233 ≈ 179.37 до 239.5180079+1,645*36.56216233 ≈ 299.66 часов.

Практические выводы из расчетов:

Вывод 1. Формула (3) подтверждает практический совет: при планировании задач ставить буфер в конце цикла, а не локально в каждую задачу/требование.

Вывод 2. Исторические данные естественно лучше обновлять по мере работы над проектом. А именно, сделанные задачи пополняют множество исторических данных. И вот тут полезно использовать таблицу, полученную после первичной группировки на шаге 1. Сделанные задачи обновляют строки этой таблицы (увеличивая количество, сумму или сумму квадратов) или вставляют новые. Таким образом исторические данные могут считаются "кумулятивно", что позволяет не выполнять полную обработку всей истории каждый раз, когда нужно оценить новый набор задач. Достаточно один раз получить небольшую таблицу и обновлять ее по мере накопления исторических данных.

Вывод 3. Задачи с большими оценками вносят значительный вклад в погрешность общей оценки. Из формулы (3) видны 2 причины этого:

1. Большое значение S²_{n, i}. Это понятно, так как при оценке больших задач и погрешность оценки больше. 
2. Небольшое количество таких задач m_i в исторических данных

Поэтому при оценках больших задач лучше разбивать их на задачи поменьше. Сама процедура разбиения помогает понять, что же нужно сделать в задаче и уточнить оценку просто за счет того, что уменьшается вероятность упустить какую-то часть задачи, на которую не обратили внимание при более поверхностной оценке.

Оценка времени выполнения составной задачи по оценкам ее подзадач

С Новым Годом, дорогие друзья! Сегодня я расскажу о методе для оценки времени выполнения сложной задачи (состоящей из нескольких подзадач). Его можно использовать для предварительной оценки проектных задач или при планировании спринтов в SCRUM.

Итак, пусть задача T разбита на подзадачи T₁, T₂, ... T_n и требуется получить интервал, в который с заданной вероятностью α попадет общее время, затраченное на выполнение задачи T (т.е. сформулировать оценку вида: на выполнение этой задачи уйдет от 85 до 110 часов с вероятностью 95%). Пусть каждую задачу оценивают несколько (для данного метода требуется не менее 2) экспертов (вспомним, например planning poker в SCRUM). Чтобы получить итоговую оценку, делаем следующее:

1) Для каждой задачи T_k подсчитываем среднее значение:

E_k = (e_k1 + e_k2 + ... + e_km)/m_k

и несмещенную дисперсию:

s_k = ((e_k1 - E_k)² + (e_k2 - E_k)² + ... + (e_km - E_k)²)/(m_k - 1)

Где e_k1, e_k2, ... e_km - оценки подзадачи T_k, полученные от разных экспертов, а m_k - количество экспертов, оценивших эту подзадачу

2) Для итоговой задачи считаем:

cреднее значение: E = E₁ + E₂ + ... + E_n

дисперсию: s = s₁ + s₂ + ... + s_n

и стандартное отклонение: σ = s^1/2

3) Согласно центральной предельной теореме распределение времени выполнения задачи при больших n приближается нормальным распределением со средним E и стандартным отклонением σ.

Поэтому в качестве оценки для времени выполнения задачи T с вероятностью α можно взять интервал (E - U_(1+α)/2*σ, E + U_(1+α)/2*σ), где U_(1+α)/2 это (1+α)/2 - квантиль стандартного нормального распределения. В частности для 95% вероятности, т.е α = 0.95: U_0.975 ≈ 1.959963985.

Значения квантилей нормального распределения можно опредедить по таблицам, часто прилагающимся к учебникам по теории вероятностей и математической статистике или воспользоваться Excel-функцией NORM.INV, т.е U_(1+α)/2 в Excel записывается как =NORM.INV((1+α)/2,0,1)


Пример: Требуется оценить объем работ в человеко-часах сложной задачи, которая состоит из 14 подзадач. Оценить подзадачи дали 4 программистам и получили следующие оценки в часах:

№ подзадачи	Программист 1	Программист 2	Программист 3	Программист 4
1	2	3	5	2
2	3		4	3
3	5	8	4	6
4	5	3	4	6
5	5	3	3	6
6	8	12		16
7	4	2	2	3
8	3		4	6
9	6	8	6	12
10	5		3	6
11	3	6		5
12	2	3	5	4
13	6	4	5
14	2	3	2	4

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

Расчеты:

Для первой подзадачи:
среднее: E₁ = (2 + 3 + 5 + 2)/4 = 3
дисперсия: s₁ = ((2 - 3)² + (3 - 3)² + (5 - 3)² + (2 - 3)²)/(4 - 1) = 2

Для второй подзадачи:
среднее: E₂ = (3 + 4 + 3)/3 = 3.333333333
дисперсия: s₂ = ((3 - 3.333333333)² + (4 - 3.333333333)² + (3 - 3.333333333)²)/(3 - 1) = 0.333333333

Аналогично для последующих подзадач. Итоговая таблица со значениями средних и дисперсий для каждой подзадачи, а также средним и дисперсией для всей задачи:

№ подзадачи	Программист 1	Программист 2	Программист 3	Программист 4	Среднее	Дисперсия
1	2	3	5	2	3	2
2	3		4	3	3.333333333	0.333333333
3	5	8	4	6	5.75	2.916666667
4	5	3	4	6	4.5	1.666666667
5	5	3	3	6	4.25	2.25
6	8	12		16	12	16
7	4	2	2	3	2.75	0.916666667
8	3		4	6	4.333333333	2.333333333
9	6	8	6	12	8	8
10	5		3	6	4.666666667	2.333333333
11	3	6		5	4.666666667	2.333333333
12	2	3	5	4	3.5	1.666666667
13	6	4	5		5	1
14	2	3	2	4	2.75	0.916666667
Итого:					68.5	44.66666667

Среднеквадратичное отклонение для всей задачи: σ = 44.66666667^1/2 = 6.683312552
Интервал для объема работ:
от E - U_(1+α)/2*σ = 68.5 - 6.683312552*1.959963985 = 55.4009481 до E + U_(1+α)/2*σ = 68.5 + 6.683312552*1.959963985 = 81.5990519

Таким образом, можно с 95% вероятностью утверждать, что затраты времени на задачу составляют от 55.4 до 81.6 человеко-часов.

P.S. Для удобства конечно лучше автоматизировать подобные вычисления. Поэтому я создал простой Excel-документ, в котором проводил все эти расчеты. Можно скачать отсюда. Поддерживается до 10 оценок каждой подзадачи (столбцы от F до K, которые я скрыл для удобства демонстрации примера выше) и до 30 подзадач (просто копируем формулы, если нужно больше). Можно изменять степень доверия (столбец Confidence). Границы интервала автоматически пересчитываются при добавлении задач и оценок.

Split по списку разделителей на T-SQL

Обычно T-SQL не часто используется для синтаксического анализа текста (например разбивки предложений на слова). Для этого больше подходят языки с поддержкой регулярных выражений. Но иногда все же возникает необходимость в разборе текста именно на стороне SQL-сервера. Скажем, при импорте текстовых файлов в БД с некоторым процессингом, когда недостачно функциональности bcp, BULK INSERT и SSIS-пакетов. Сегодня я поделюсь своей реализацией функции разбивки текста на фрагменты (split) по заданному списку разделителей. Работает в SQL Server 2008 и 2012.

Поскольку в T-SQL нет массивов или списков, вначале создадим табличный тип для хранения разделителей:

CREATE TYPE [dbo].[StringArray] AS TABLE(
 [String] nvarchar(max) NULL
)

Теперь функция:

CREATE FUNCTION [dbo].[SplitOn](
 @String nvarchar(max),
 @Separators StringArray readonly
)
RETURNS @Fragments TABLE (
 FragmentNumber int,
 Fragment nvarchar(max)
)
AS
BEGIN
 IF @String IS NULL RETURN
 
 IF NOT EXISTS (SELECT 1 FROM @Separators)
 BEGIN
  INSERT @Fragments SELECT 1, @String
  RETURN
 END
 
 DECLARE @SepLengths TABLE (
  Separator nvarchar(max), [Length] int
 )
 
 INSERT @SepLengths 
 SELECT [String], DATALENGTH([String]) / 2 FROM @Separators
 
 DECLARE @CurrentPosition int = 1, @OldPosition int = 1,
 @FragmentLength int = 0, @FragmentNumber int = 1,
 @Length int = DATALENGTH(@String) / 2, @Fragment nvarchar(max)
 
 WHILE @CurrentPosition IS NOT NULL
 BEGIN
  SELECT @CurrentPosition = NULL
  
  SELECT TOP 1 @CurrentPosition = [Position],
  @FragmentLength = [Length]
  FROM 
   (
    SELECT 
    [Position] = 
    CHARINDEX(Separator, @String, @OldPosition),
    [Length] FROM @SepLengths
   ) p
  WHERE [Position] > 0 ORDER BY [Position], [Length] DESC
  
  IF @CurrentPosition IS NULL
  BEGIN
   SELECT @Fragment = 
   SUBSTRING(@String, @OldPosition, 
   @Length - @OldPosition + 1)
   
   INSERT @Fragments
   SELECT @FragmentNumber, @Fragment
  END
  ELSE
  BEGIN
   SELECT @Fragment = 
   SUBSTRING(@String, @OldPosition,
   @CurrentPosition - @OldPosition)
   
   INSERT @Fragments SELECT @FragmentNumber, @Fragment
   
   SELECT @FragmentNumber = @FragmentNumber + 1,
   @OldPosition = @CurrentPosition + @FragmentLength
  END
 END
 
 RETURN
END

И пример использования:

DECLARE @String nvarchar(max) = N'Воры! Пожар! Убивают! Ничего подобного не случалось с тех самых пор, как он обосновался под Горой. Ярость дракона не поддается описанию. Разве что можно ее сравнить с бешенством какого-нибудь толстосума, привыкшего к своим богатствам и нежданно утратившего что-то, чем он почти не пользовался или чего вообще не трогал в течение многих лет. Смог выпустил из пасти струю пламени; пещера наполнилась дымом, Гора сотряслась от макушки до основания. Дракон попытался просунуть голову в отверстие. Убедившись, что оно слишком маленькое, Смог взревел — под землей словно прогремел гром — и ринулся по подземным коридорам к Парадным Вратам.'

DECLARE @Separators StringArray
INSERT @Separators VALUES ('.'), (','), ('!'), (';'), ('—')

SELECT * FROM dbo.SplitOn(@String, @Separators)

Результат выполнения:

Небольшое пояснение к алгоритму разбиения. По мере продвижения по строке вправо функция находит тот разделитель из списка, который встречается раньше других. Если есть вхождение двух разделителей с одной и той же позиции (такое возможно, когда один из разделителей входит с начала в другой), то выбирается самый длинный разделитель. Это демонстрируется следующим примером:

DECLARE @String nvarchar(max) = N'1,2,,3'

DECLARE @Separators1 StringArray, @Separators2 StringArray
INSERT @Separators1 VALUES (',')
INSERT @Separators2 VALUES (','), (',,')

SELECT * FROM dbo.SplitOn(@String, @Separators1)
SELECT * FROM dbo.SplitOn(@String, @Separators2)

Подробная статистика использования индексов в MS SQL Server 2008/2012

Одна из задач, которую разработчикам и DBA приходится решать для улучшения производительности базы данных  это удаление лишних индексов, которые не используются при выборках, а только занимают место в базе и замедляют изменение данных. В таких случаях очень полезной может оказаться замечательная статья Грега Робиду "How to get index usage information in SQL Server".
Следующий запрос, взятый из этой статьи позволяет получить статистику всех операций, произведенных над индексом с момента старта SQL сервера:

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
         I.[NAME] AS [INDEX NAME], 
         USER_SEEKS, 
         USER_SCANS, 
         USER_LOOKUPS, 
         USER_UPDATES 
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S 
         INNER JOIN SYS.INDEXES AS I 
           ON I.[OBJECT_ID] = S.[OBJECT_ID] 
              AND I.INDEX_ID = S.INDEX_ID 
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 

Однако часто самого факта, что индекс используется может оказаться недостаточно и хотелось бы знать, какие именно запросы этот индекс используют. Когда мы имеем дело с одним запросом, то легко увидеть, какие индексы он использует, проанализировав план его выполнения. Будем использовать тот же подход и в случае множества запросов. Вначале получим статистику о выполненных запросах из кэша планов сервера:

SELECT qs.query_hash, qs.query_plan_hash, st.[text], qp.query_plan, qs.creation_time, qs.last_execution_time, qs.execution_count
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

Возвращаемые столбцы:

query_hash – хэш SQL-предложения (часть пакета)
query_plan_hash – хэш плана всего запроса
text – текст всего пакета или процедуры
query_plan – план запроса в виде xml (можно посмотреть его в SQL Server Management Studio, если сохранить его этот xml с расширением .sqlplan)
creation_time – когда план был скомпилирован
last_execution_time – когда запрос был выполнен по этому плану в последний раз
execution_count – сколько раз запрос был выполнен по этому плану

Затем из xml-плана каждого запроса с помощью методов типа данных xml получим информацию о том, какие индексы были задействованы и какие операции над ними выполнялись. Это достаточно несложно сделать, т.к xml плана запроса подчиняется схеме:
http://schemas.microsoft.com/sqlserver/2004/07/showplan

Например, для запроса (база данных master):

SELECT TOP 10 * FROM sys.objects ORDER BY [name]

На моем SQL Server 2008 генерируется такой план:

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.50.2550.0">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="SELECT TOP 10 * FROM sys.objects ORDER BY [name]" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.0240863" StatementEstRows="10" StatementOptmLevel="FULL" QueryHash="0xFBA5FED7D0BBA861" QueryPlanHash="0x2E11BC9EAFF10957" StatementOptmEarlyAbortReason="GoodEnoughPlanFound">
          <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
          <QueryPlan CachedPlanSize="56" CompileTime="7" CompileCPU="7" CompileMemory="864">
            <RelOp NodeId="0" PhysicalOp="Top" LogicalOp="Top" EstimateRows="10" EstimateIO="0" EstimateCPU="1e-006" AvgRowSize="134" EstimatedTotalSubtreeCost="0.0240863" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
              <OutputList>
                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" />
                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" />
                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" />
                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" />
                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" />
                <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="indepid" />
                <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="name" />
                <ColumnReference Column="Expr1006" />
                <ColumnReference Column="Expr1009" />
                <ColumnReference Column="Expr1010" />
              </OutputList>
              <Top RowCount="0" IsPercent="0" WithTies="0">
                <TopExpression>
                  <ScalarOperator ScalarString="(10)">
                    <Const ConstValue="(10)" />
                  </ScalarOperator>
                </TopExpression>
                <RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="10" EstimateIO="0" EstimateCPU="0.00039292" AvgRowSize="134" EstimatedTotalSubtreeCost="0.0240853" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                  <OutputList>
                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" />
                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" />
                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" />
                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" />
                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" />
                    <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="indepid" />
                    <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="name" />
                    <ColumnReference Column="Expr1006" />
                    <ColumnReference Column="Expr1009" />
                    <ColumnReference Column="Expr1010" />
                  </OutputList>
                  <NestedLoops Optimized="0">
                    <OuterReferences>
                      <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
                    </OuterReferences>
                    <RelOp NodeId="2" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="10" EstimateIO="0" EstimateCPU="0.00039292" AvgRowSize="72" EstimatedTotalSubtreeCost="0.0191962" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                      <OutputList>
                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" />
                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" />
                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" />
                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" />
                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" />
                        <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="indepid" />
                        <ColumnReference Column="Expr1006" />
                        <ColumnReference Column="Expr1009" />
                        <ColumnReference Column="Expr1010" />
                      </OutputList>
                      <NestedLoops Optimized="0">
                        <OuterReferences>
                          <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                        </OuterReferences>
                        <RelOp NodeId="3" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="10" EstimateIO="0" EstimateCPU="0.00014852" AvgRowSize="68" EstimatedTotalSubtreeCost="0.0143071" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                          <OutputList>
                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" />
                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" />
                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" />
                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" />
                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" />
                            <ColumnReference Column="Expr1006" />
                            <ColumnReference Column="Expr1009" />
                            <ColumnReference Column="Expr1010" />
                          </OutputList>
                          <Filter StartupExpression="0">
                            <RelOp NodeId="4" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="10" EstimateIO="0" EstimateCPU="9.4e-006" AvgRowSize="70" EstimatedTotalSubtreeCost="0.0142913" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                              <OutputList>
                                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" />
                                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" />
                                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsclass" />
                                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
                                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" />
                                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pclass" />
                                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" />
                                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" />
                                <ColumnReference Column="Expr1006" />
                                <ColumnReference Column="Expr1009" />
                                <ColumnReference Column="Expr1010" />
                              </OutputList>
                              <ComputeScalar>
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Column="Expr1006" />
                                    <ScalarOperator ScalarString="CONVERT(bit,[master].[sys].[sysschobjs].[status] as [o].[status]&amp;(1),0)">
                                      <Convert DataType="bit" Style="0" Implicit="0">
                                        <ScalarOperator>
                                          <Arithmetic Operation="BIT_AND">
                                            <ScalarOperator>
                                              <Identifier>
                                                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status" />
                                              </Identifier>
                                            </ScalarOperator>
                                            <ScalarOperator>
                                              <Const ConstValue="(1)" />
                                            </ScalarOperator>
                                          </Arithmetic>
                                        </ScalarOperator>
                                      </Convert>
                                    </ScalarOperator>
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Column="Expr1009" />
                                    <ScalarOperator ScalarString="CONVERT(bit,[master].[sys].[sysschobjs].[status] as [o].[status]&amp;(16),0)">
                                      <Convert DataType="bit" Style="0" Implicit="0">
                                        <ScalarOperator>
                                          <Arithmetic Operation="BIT_AND">
                                            <ScalarOperator>
                                              <Identifier>
                                                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status" />
                                              </Identifier>
                                            </ScalarOperator>
                                            <ScalarOperator>
                                              <Const ConstValue="(16)" />
                                            </ScalarOperator>
                                          </Arithmetic>
                                        </ScalarOperator>
                                      </Convert>
                                    </ScalarOperator>
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Column="Expr1010" />
                                    <ScalarOperator ScalarString="CONVERT(bit,[master].[sys].[sysschobjs].[status] as [o].[status]&amp;(64),0)">
                                      <Convert DataType="bit" Style="0" Implicit="0">
                                        <ScalarOperator>
                                          <Arithmetic Operation="BIT_AND">
                                            <ScalarOperator>
                                              <Identifier>
                                                <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status" />
                                              </Identifier>
                                            </ScalarOperator>
                                            <ScalarOperator>
                                              <Const ConstValue="(64)" />
                                            </ScalarOperator>
                                          </Arithmetic>
                                        </ScalarOperator>
                                      </Convert>
                                    </ScalarOperator>
                                  </DefinedValue>
                                </DefinedValues>
                                <RelOp NodeId="5" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="10" EstimateIO="0" EstimateCPU="0.00039292" AvgRowSize="73" EstimatedTotalSubtreeCost="0.0142903" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                                  <OutputList>
                                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" />
                                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" />
                                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsclass" />
                                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status" />
                                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
                                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" />
                                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pclass" />
                                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" />
                                    <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" />
                                  </OutputList>
                                  <NestedLoops Optimized="0">
                                    <OuterReferences>
                                      <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                                    </OuterReferences>
                                    <RelOp NodeId="6" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="10" EstimateIO="0.003125" EstimateCPU="0.0002604" AvgRowSize="45" EstimatedTotalSubtreeCost="0.003293" TableCardinality="94" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                                      <OutputList>
                                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" />
                                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" />
                                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsclass" />
                                      </OutputList>
                                      <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0">
                                        <DefinedValues>
                                          <DefinedValue>
                                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsclass" />
                                          </DefinedValue>
                                        </DefinedValues>
                                        <Object Database="[master]" Schema="[sys]" Table="[sysschobjs]" Index="[nc2]" Alias="[o]" IndexKind="NonClustered" />
                                      </IndexScan>
                                    </RelOp>
                                    <RelOp NodeId="8" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="34" EstimatedTotalSubtreeCost="0.0109555" TableCardinality="94" Parallel="0" EstimateRebinds="9.89362" EstimateRewinds="0">
                                      <OutputList>
                                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status" />
                                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
                                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" />
                                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pclass" />
                                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" />
                                        <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" />
                                      </OutputList>
                                      <IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0">
                                        <DefinedValues>
                                          <DefinedValue>
                                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pclass" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" />
                                          </DefinedValue>
                                        </DefinedValues>
                                        <Object Database="[master]" Schema="[sys]" Table="[sysschobjs]" Index="[clst]" Alias="[o]" TableReferenceId="-1" IndexKind="Clustered" />
                                        <SeekPredicates>
                                          <SeekPredicateNew>
                                            <SeekKeys>
                                              <Prefix ScanType="EQ">
                                                <RangeColumns>
                                                  <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                                                </RangeColumns>
                                                <RangeExpressions>
                                                  <ScalarOperator ScalarString="[master].[sys].[sysschobjs].[id] as [o].[id]">
                                                    <Identifier>
                                                      <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                </RangeExpressions>
                                              </Prefix>
                                            </SeekKeys>
                                          </SeekPredicateNew>
                                        </SeekPredicates>
                                      </IndexScan>
                                    </RelOp>
                                  </NestedLoops>
                                </RelOp>
                              </ComputeScalar>
                            </RelOp>
                            <Predicate>
                              <ScalarOperator ScalarString="has_access('CO',[master].[sys].[sysschobjs].[id] as [o].[id])=(1) AND [master].[sys].[sysschobjs].[nsclass] as [o].[nsclass]=(0) AND [master].[sys].[sysschobjs].[pclass] as [o].[pclass]=(1)">
                                <Logical Operation="AND">
                                  <ScalarOperator>
                                    <Compare CompareOp="EQ">
                                      <ScalarOperator>
                                        <Intrinsic FunctionName="has_access">
                                          <ScalarOperator>
                                            <Const ConstValue="'CO'" />
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                                            </Identifier>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Const ConstValue="" />
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Const ConstValue="" />
                                          </ScalarOperator>
                                        </Intrinsic>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Const ConstValue="(1)" />
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Compare CompareOp="EQ">
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsclass" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Const ConstValue="(0)" />
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Compare CompareOp="EQ">
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pclass" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Const ConstValue="(1)" />
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                </Logical>
                              </ScalarOperator>
                            </Predicate>
                          </Filter>
                        </RelOp>
                        <RelOp NodeId="30" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00484728" TableCardinality="167" Parallel="0" EstimateRebinds="9.89362" EstimateRewinds="0">
                          <OutputList>
                            <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="indepid" />
                          </OutputList>
                          <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="indepid" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Index="[clst]" Alias="[r]" IndexKind="Clustered" />
                            <SeekPredicates>
                              <SeekPredicateNew>
                                <SeekKeys>
                                  <Prefix ScanType="EQ">
                                    <RangeColumns>
                                      <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="depid" />
                                      <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="class" />
                                      <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="depsubid" />
                                    </RangeColumns>
                                    <RangeExpressions>
                                      <ScalarOperator ScalarString="[master].[sys].[sysschobjs].[id] as [o].[id]">
                                        <Identifier>
                                          <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator ScalarString="(97)">
                                        <Const ConstValue="(97)" />
                                      </ScalarOperator>
                                      <ScalarOperator ScalarString="(0)">
                                        <Const ConstValue="(0)" />
                                      </ScalarOperator>
                                    </RangeExpressions>
                                  </Prefix>
                                </SeekKeys>
                              </SeekPredicateNew>
                            </SeekPredicates>
                          </IndexScan>
                        </RelOp>
                      </NestedLoops>
                    </RelOp>
                    <RelOp NodeId="31" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="71" EstimatedTotalSubtreeCost="0.0048641" TableCardinality="127" Parallel="0" EstimateRebinds="6.70213" EstimateRewinds="3.29787">
                      <OutputList>
                        <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="name" />
                      </OutputList>
                      <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="name" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Index="[cl]" Alias="[n]" IndexKind="Clustered" />
                        <SeekPredicates>
                          <SeekPredicateNew>
                            <SeekKeys>
                              <Prefix ScanType="EQ">
                                <RangeColumns>
                                  <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="class" />
                                  <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="value" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="'OBTY'">
                                    <Const ConstValue="'OBTY'" />
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="[master].[sys].[sysschobjs].[type] as [o].[type]">
                                    <Identifier>
                                      <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
                                    </Identifier>
                                  </ScalarOperator>
                                </RangeExpressions>
                              </Prefix>
                            </SeekKeys>
                          </SeekPredicateNew>
                        </SeekPredicates>
                      </IndexScan>
                    </RelOp>
                  </NestedLoops>
                </RelOp>
              </Top>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Для упрощения запросов к такому xml первым делом удалим namespace (в данном случае строку xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan). Это можно сделать например такой функцией:

USE [master]

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RemoveNamespaceString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[RemoveNamespaceString]

GO

CREATE FUNCTION [dbo].[RemoveNamespaceString](@xml xml, @namespaceString nvarchar(max))
RETURNS xml
AS
BEGIN
 DECLARE @xc nvarchar(max) = CAST(@xml as nvarchar(max))
 DECLARE @ci int = CHARINDEX(@namespaceString, @xc)
 IF @ci = 0 RETURN @xml
 RETURN CAST(STUFF(@xc, @ci, LEN(@namespaceString), '') as xml)
END

На входе – xml с namespace-ом и сам namespace. На выходе – xml без namespace-а. Функция не отличается изяществом и просто удаляет первое вхождение строки @namespaceString.
Теперь функция, возвращающая табличное представление информации об индексах:

USE [master]

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IndexesUsedByPlan]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[IndexesUsedByPlan]

GO


CREATE FUNCTION [dbo].[IndexesUsedByPlan](@xml xml)
RETURNS TABLE
AS
RETURN
(
 SELECT * FROM
 (
  SELECT
  statement_xml = s.query('.'),
  operation_xml = op.query('.'),
  object_xml = obj.query('.'),
  statement_id = s.value('.[1]/@StatementId', 'nvarchar(max)'),
  statement_text = s.value('.[1]/@StatementText', 'nvarchar(max)'),
  query_hash = convert(binary(8), s.value('.[1]/@QueryHash', 'varchar(max)'), 1),
  query_plan_hash = convert(binary(8), s.value('.[1]/@QueryPlanHash', 'nvarchar(max)'), 1),
  operation = op.value('.[1]/@PhysicalOp', 'nvarchar(max)'),
  index_database = obj.value('.[1]/@Database', 'nvarchar(max)'),
  index_schema = obj.value('.[1]/@Schema', 'nvarchar(max)'),
  index_table = obj.value('.[1]/@Table', 'nvarchar(max)'),
  [index] = obj.value('.[1]/@Index', 'nvarchar(max)'),
  index_kind = obj.value('.[1]/@IndexKind', 'nvarchar(max)')
  FROM @xml.nodes('//StmtSimple') T(s)
  CROSS APPLY s.nodes('.//RelOp') S(op)
  CROSS APPLY op.nodes('.//Object') Ro(obj)
 ) p 
 WHERE operation LIKE '%Index%' and [index] IS NOT NULL 

)

@xml – xml плана (без namespace-а)

Возвращаемые столбцы:

statement_xml – часть плана, содержащая данные о том SQL-предложении (часть batch-а или процедуры), которое задействовало индекс
operation_xml – часть плана, содержащая данные об операции с индексом (такой, как Index Seek или Index Scan)
object_xml – часть плана, содержащая данные об объекте, (в данном случае - индексе), над которым производилась операция
statement_id – порядковый номер SQL-предложения в batch-е или процедуре
statement_text – текст SQL-предложения
query_hash – хэш SQL-предложения
query_plan_hash – хэш плана запроса
operation – операция, которая производится с индексом (например "Index Scan")
index_database – база данных, в которой находится индекс
index_schema – схема таблицы, которой принадлежит индекс
index_table – имя таблицы, которой принадлежит индекс. Дополнительное
index – имя индекса
index_kind – тип индекса (Clustered/NonClustered)

И наконец запрос, выбирающий все нужные данные:

SELECT 
pind.statement_text, query_text = st.[text],
pind.statement_id, qs.statement_start_offset, qs.statement_end_offset,
qs.creation_time, qs.last_execution_time, qs.execution_count,
pind.index_database, pind.index_schema, pind.index_table, [index], pind.index_kind,
pind.query_hash, pind.query_plan_hash,
qp.query_plan, pind.statement_xml, pind.operation_xml, pind.object_xml
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
CROSS APPLY (
 SELECT p.* FROM
 [master].dbo.IndexesUsedByPlan( 
  [master].dbo.RemoveNamespaceString(qp.query_plan,
  'xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"')) p
 JOIN
 (
  SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.tables WHERE
  table_type='BASE TABLE'
 ) ut
 ON p.index_schema COLLATE DATABASE_DEFAULT = '[' + ut.TABLE_SCHEMA + ']'
 AND p.index_table COLLATE DATABASE_DEFAULT = '[' + ut.TABLE_NAME + ']'
 WHERE p.index_database = '[' + DB_NAME() + ']'
) pind
WHERE
qp.query_plan IS NOT NULL
AND qs.query_hash = pind.query_hash
AND qs.query_plan_hash = pind.query_plan_hash
ORDER BY pind.[index_table], pind.[index]

JOIN с выборкой из представления information_schema.tables нужен, чтобы ограничить возвращаемые индексы только текущей базой данных и только пользовательскими таблицами.