Доступ к счетчикам Perfmon из SQL Server. Способы 1 и 2.

Article
07/12/2009

Способ 1.

Посл-ть д-й.

1. Все определенные на машине каунтеры экспортятся в текстовый файл при помощи typeperf.

2. Текстовый файл редактируется, остаются только интересующие каунтеры.

3. Собираются значения каунтеров на интервале при помощи logman.

4. Файл с рез-тами замеров грузится на SQL Server через создание ODBC DSN и команду relog.

5. На SQL Server появляются таблички DisplayToID, CounterDetails, CounterData, содержащие информацию по каунтерам и их кардиограммы.

Описан здесь - https://www.mssqltips.com/tip.asp?tip=1722.

Это оч.хор. способ, и нет сомнения, что администраторы будут его пользовать. Но разработчики обладают более независимым и свободным суждением. Разработчика угнетают все эти непонятные typeperf, logman, relog и пр. Что это такое вообще? Очевидно, что эти тулы тоже писаны разработчиками, которых когда-то давно достали админы своими унылыми хотелками видеть счетчики в базе. Настоящий разработчег не будет пользоваться приблудой, написанной другим разработчиком. Вместо этого он потратит час или два или сколько не жалко, но напишет свою собственную приблуду, хотя бы ему пришлось к ней прибегнуть всего один раз. Она, по определению, будет лучше всех остальных аналогичных приблуд хотя бы потому, что писана лично им. Настоящий разработчик не оформляет свою приблуду в виде exeшника, потому что тогда ей смогут пользоваться администраторы. Администраторы подпишут у начальства инструкцию, по которой все на предприятии должны будут пользоваться этой тулой вместо того, чтобы развивать творческую мысль, изобретая новый невиданный велосипед. Настоящий разработчик распространяет приблуду в виде исходников. Тогда другие разработчики, вдоволь раскритиковав и поглумившись над собратом, возьмут исходник за основу и примутся развивать его дальше. И так до тех пор, пока какой-нибудь ушлый администратор не настоит на exeшнике, что остановит процесс эволюции, приведет со временем к утрате исходника, нагнетет революционную ситуацию и вызовет очередной виток диалектической спирали. Go to начало абзаца.

Способ 2.

Йа нопесал оч.хор.приблуду, кот.делает сабж. Сначала думал UDF, потом решил UDT, чтобы не передавать всякий раз имя счетчика, когда один и тот же счетчик дергается много раз. А UDF туда вошла как статик метод.

Счетчег задается в формате Машина\Категория(Инстанс)\Имя_счетчика, например, localhost\Processor(_Total)\% Processor Time, как они значатся в Perfmone:

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

Примеры вызовов:

declare @x as PerfCounter

set @x = 'localhost\Processor(_Total)\% Processor Time'

select @x.ToString(), @x.Value

declare @y as PerfCounter

set @y = 'Memory\Available bytes'

select @y.ToString(), @y.Value

declare @z as PerfCounter

set @z = ''

select @z.ToString(), @z.Value

select PerfCounter::GetValue('.\SQLServer:Plan Cache(SQL Plans)\Cache Hit Ratio')

Так это работает:

По-моему, афегительная вещь. Я даже где-то молодец. Отдаю задаром. Берите, пользуйтесь, дорабатывайте. Единственный пункт лицензионного соглашения – ссылка на источник (https://blogs.msdn.com/alexejs/).

Code skipped, т.к. далее исправленная и дополненная версия

--------------------------------------------------------------------------------------------------------------------------

Роскошная вещь, мне нравится (сам себя не похвалишь ...:)). Нет, в самом деле, смотрите, как с ней изящно и непринужденно делается логгирование перфмоновских счетчиков в SQL Server.

Создаем две таблички. В первой будут сидеть нужные нам каунтеры:

create table my.Counters (id int identity primary key, counter PerfCounter)

insert my.Counters (counter) values ('localhost\Processor(_Total)\% Processor Time')

insert my.Counters (counter) values ('Memory\Available bytes')

insert my.Counters (counter) values ('.\SQLServer:Plan Cache(SQL Plans)\Cache Hit Ratio')

Во второй результаты сбора:

create table my.PerfLog (zamer char(8), counter_id int references my.Counters(id), value float, primary key (zamer, counter_id))

Процесс сбора происходит например, так. Делаем 5 замеров всех счетчиков из Counters каждую секунду

truncate table my.PerfLog

declare @i int

declare @j int, @pc PerfCounter

set @i = 1

while (@i <= 5) begin

select @i

select @j = min(id) - 1 from my.Counters

while (1 = 1) begin

select top 1 @j = id, @pc = counter from my.Counters where id > @j

if @@rowcount = 0 break

select '---', @j

insert my.PerfLog values (convert(char(8), getdate(), 108), @j, @pc.Value)

end

waitfor delay '00:00:01'

set @i = @i + 1

end

В журнале собралась история замеров выбранных счетчиков

select * from my.PerfLog

Давайте ее распайвотим для наглядности:

select * from my.PerfLog pivot (min(value) for counter_id in ([1], [2], [3])) t

Все хорошо, но хотелось бы иметь осмысленные названия счетчиков

select PerfLog.zamer, Counters.counter.ToString() cntName, PerfLog.value

from my.PerfLog join my.Counters on PerfLog.counter_id = Counters.id

и, соответственно, так:

select * from (

select PerfLog.zamer, Counters.counter.ToString() cntName, PerfLog.value

from my.PerfLog join my.Counters on PerfLog.counter_id = Counters.id ) t1

pivot

(min(value) for cntName in ([localhost\Processor(_Total)\% Processor Time], [.\Memory\Available bytes], [.\SQLServer:Plan Cache(SQL Plans)\Cache Hit Ratio])) t2

Супер! Одно неудобно – перечислять руками список счетчиков в pivot-списке. Поправим это дело.

declare @s nvarchar(max)

set @s = 'select * from (' +

' select PerfLog.zamer, Counters.counter.ToString() cntName, PerfLog.value ' +

' from my.PerfLog join my.Counters on PerfLog.counter_id = Counters.id ) t1 ' +

'pivot ' +

'(min(value) for cntName in (['

select @s = @s + counter.ToString() + '], [' from my.Counters

set @s = left(@s, len(@s) - 3) + ')) t2'

--select @s

exec sp_executesql @s

Все. Пользуйтесь.

--------------------------------------------------------------------------------------------------------------------------

Счетчики позволяют не только пассивно считывать показания предопределенных датчиков перфмона, но и создать свои, значения которых мы вольны изменять в зависимости от обстоятельств.

В состав SQL Serverных счетчиков входит счетчик Query, относящийся к категории SQLServer:User Settable, экземпляры которой выглядят, как User counter , где может принимать значения от 1 до 10.Управление значениями экземпляров осуществляется при помощи процедур sp_user_counter n, где то же, а n – целочисленное значение, в которое устанавливается счетчик. Значение данных счетчиков сбрасывается в 0 при рестарте SQL Server.

Пример

declare @x PerfCounter

set @x = 'SQLServer:User Settable(User counter 5)\Query'

select @x.Value

exec sp_user_counter5 50

select @x.Value

------------------------

Так это выглядит на графике Performance Monitor. Более подробную информацию про хранимые процедуры sp_user_counter можно почерпнуть здесь: https://msdn2.microsoft.com/en-us/library/ms187480(SQL.100).aspx.

На тот случай, если вдруг 10-ти пользовательских счетчиков покажется мало, можно воспользоваться обычным Server Explorer в Visual Studio для создания / удаления собственных перфмоновских категорий и каунтеров внутри них.

Кроме того, я расширил CLRный тип PerfCounter (см. выше), с помощью которого теперь можно создавать кастомные категории / счетчики. Пример:

select PerfCounter::CreateCategory('MyCategory', 'Моя категория')

select PerfCounter::AddCounterToCategory('MyCategory', 'MyCounter1', 'Мой счетчик 1')

select PerfCounter::AddCounterToCategory('MyCategory', 'MyCounter2', 'Мой счетчик 2')

Вот они появились:

Меняем значения

declare @x PerfCounter

set @x = 'MyCategory\MyCounter1'

select @x.ToString(), @x.Value

--Для customных счетчиков можно менять значения

set @x.Value = 50

select @x.ToString(), @x.Value

waitfor delay '00:00:20'

set @x.IncrBy(10) --Обратите внимание на вызов void мутатора

select @x.ToString(), @x.Value

waitfor delay '00:00:20'

set @x.IncrBy(-30) --аналогично

select @x.ToString(), @x.Value

Вот, что при этом происходит:

--Для неcustomных счетчиков попытки изменить значение ничего не дают (в нашей имплементации)

declare @y PerfCounter

set @y = 'SQLServer:User Settable(User counter 5)\Query'

select @y.ToString(), @y.Value

set @y.Value = 10

select @y.ToString(), @y.Value

set @y.IncrBy(1)

select @y.ToString(), @y.Value

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

select PerfCounter::DeleteCategory('MyCategory')

Удалить по ошибке некастомную категорию не удастся, т.к. при этом выкидывается InvalidOperationException, что Cannot delete Performance Category because this category is not registered or is a system category.

Более подробную инфу по созданию кастомных каунтеров в перфмоне – см. https://msdn2.microsoft.com/en-us/library/system.diagnostics.performancecounter(VS.80).aspx

Приложение. Модифицированный код UDT PerfCounter. Посл.модификация от 18.01.2009:

/* Эта сборка unsafe, поск. в ней исп-ся доступ к Perf Counters. */

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Diagnostics;

using System.Text;

using System.IO;

[Serializable]

//Поскольку в нашем пользовательском типе будет внутренняя переменная не Value типа, Native сериализация не проходит.

//Пользовательская сериализация означает, что нам еще придется имплементировать интерфейс IBinarySerialize,

//состоящий из двух методов: Write - это бинарный эквивалент ToString() и Read() - соответственно,

//бинарный эквивалент Parse().

//MaxByteSize выставляем из соображений, что тип предполагается не только для SQL Server 2008,

//но и для 2005, где размер пользовательского типа не может превышать 8000 байт.

[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, MaxByteSize = 8000)]

public struct PerfCounter : INullable, IBinarySerialize

{

private PerformanceCounter pc;

private bool isNull;

/// <returns>Название счетчика в формате Машина\Категория(Экземпляр)\Счетчик</returns>

public override string ToString()

{

if (isNull) return "Null";

StringBuilder sb = new StringBuilder(pc.MachineName);

sb.Append('\\'); sb.Append(pc.CategoryName);

if (!String.IsNullOrEmpty(pc.InstanceName))

{

sb.Append('('); sb.Append(pc.InstanceName); sb.Append(')');

}

sb.Append('\\'); sb.Append(pc.CounterName);

return sb.ToString();

}

public bool IsNull

{

get

{

return isNull;

}

public static PerfCounter Null

{

get

{

PerfCounter h = new PerfCounter();

h.isNull = true;

return h;

}

/// <param name="counter">Строка с полным именем счетчика в формате Машина\Категория(Экземпляр)\Счетчик.

/// Машина может быть пустой - полагается локальная тачка.

/// Экземпляр тоже может быть пустой. Круглые скобки при этом можно не ставить.

/// Если экземпляр пустой, а счетчик по природе многоэкземплярный, берется _Total.</param>

/// <returns></returns>

public static PerfCounter Parse(SqlString counter)

{

if (counter.IsNull) return Null;

PerfCounter u = new PerfCounter();

try

{

string s = counter.ToString();

string[] cntItems = counter.ToString().Trim().Split(new char[] { '\\' }, StringSplitOptions.None);

string cntMachine, cntCategory, cntInstance, cntName;

cntName = cntItems[cntItems.Length - 1].Trim();

cntCategory = cntItems[cntItems.Length - 2].Trim();

if (cntItems.Length < 3) //Если разделителей \ меньше 2-х, предполагаем, что это опущена машина, т.е. она предполагается локальной

cntMachine = ".";

else

{

cntMachine = cntItems[cntItems.Length - 3].Trim();

if (String.IsNullOrEmpty(cntMachine)) cntMachine = "."; //Если разделитель после машины был, но машина пустая, она тоже предполагается локальной

}

//Сейчас в cntCategory категория лежит вместе с экземпляром, напр., SQLServer:Wait Statistics(Average Wait Time (ms))

//Разделяем категорию и экземпляр.

int firstOpeningBracket = cntCategory.IndexOf('('); //Если экземпляр есть, cntCategory заканчивается на закрывающую скобку, поэтому заводить lastClosingBracket бессмысленно.

if (firstOpeningBracket == -1)

cntInstance = String.Empty; //Нет экземпляра

else

{

cntInstance = cntCategory.Substring(firstOpeningBracket + 1, cntCategory.Length - firstOpeningBracket - 2);

cntCategory = cntCategory.Substring(0, firstOpeningBracket);

}

//Итак, мы распарсили строку с именем счетчика в cntMachine, cntCategory, cntInstance, cntName.

//Из них сейчас пустым может быть только cntInstance. Если он пустой

if (String.IsNullOrEmpty(cntInstance))

{

//А категория счетчика предполагает несколько экземпляров

PerformanceCounterCategory pcc = new PerformanceCounterCategory(cntCategory, cntMachine);

if (pcc.CategoryType == PerformanceCounterCategoryType.MultiInstance)

//Экземпляр выставляем в _Total

cntInstance = "_Total";

}

u.pc = CreatePerfCounterFromItems(cntMachine, cntCategory, cntInstance, cntName);

}

catch (Exception)

{

return Null;

}

return u;

}

private static PerformanceCounter CreatePerfCounterFromItems(string cntMachine, string cntCategory, string cntInstance, string cntName)

{

PerformanceCounter pc;

//Если экземпляр счетчика пустой

if (String.IsNullOrEmpty(cntInstance))

{

//Значит, он вообще не предусмотрен для данного счетчика. Создаем без него.

pc = new PerformanceCounter(cntCategory, cntName);

pc.MachineName = cntMachine;

}

else

//Иначе создаем обычным порядком

pc = new PerformanceCounter(cntCategory, cntName, cntInstance, cntMachine);

try

{

pc.ReadOnly = false; // Само по себе присвоение свойства не вызывает ошибки. Надо еще чего-нибудь сделать.

pc.NextValue();

}

catch (InvalidOperationException)

{

pc.ReadOnly = true;

}

return pc;

}

public void Write(BinaryWriter w)

{

w.Write(isNull);

if (! isNull)

{

w.Write(pc.MachineName); w.Write(pc.CategoryName); w.Write(pc.InstanceName); w.Write(pc.CounterName);

}

public void Read(BinaryReader r)

{

isNull = r.ReadBoolean(); if (isNull) return;

string cntMachine, cntCategory, cntInstance, cntName;

cntMachine = r.ReadString(); cntCategory = r.ReadString(); cntInstance = r.ReadString(); cntName = r.ReadString();

pc = CreatePerfCounterFromItems(cntMachine, cntCategory, cntInstance, cntName);

}

/// <summary>

/// Создает кастомную категорию каунтеров. Для простоты она создается одноинстансовой.

/// </summary>

/// <param name="categoryName">В случае Null заводится категория Null</param>

/// <param name="categoryDescription"></param>

/// Приходится возвращать что-то, потому что я не представляю, как из Т-SQL вызвать

/// static void метод</returns>

public static SqlByte CreateCategory(SqlString categoryName, SqlString categoryDescription)

{

string m_categoryName = categoryName.ToString();

string m_categoryDescription = categoryDescription.IsNull ? String.Empty : categoryDescription.ToString();

if (PerformanceCounterCategory.Exists(m_categoryName))

throw new Exception("Ничего не создано, потому что такая категория уже существует. " +

"Используйте метод DeleteCategory, чтобы удалить ее.");

PerformanceCounterCategory pcc = PerformanceCounterCategory.Create(m_categoryName, m_categoryDescription,

PerformanceCounterCategoryType.SingleInstance, new CounterCreationDataCollection());

return 0;

}

/// <summary>

/// Удалить некастомную категорию не даст, выкинув InvalidOperationException, что

/// Cannot delete Performance Category because this category is not registered or is a system category.

/// </summary>

/// <param name="categoryName"></param>

/// <returns></returns>

public static SqlByte DeleteCategory(SqlString categoryName)

{

string m_categoryName = categoryName.ToString();

if (PerformanceCounterCategory.Exists(m_categoryName)) PerformanceCounterCategory.Delete(m_categoryName);

return 0;

}

/// <summary>

/// Создает кастомный каунтер с простейшим способом подсчета (PerformanceCounterType.NumberOfItems32).

/// Аналогичную штуку можно написать, чтобы их ремувить

/// (завести новую CounterCreationDataCollection, перебрать в цикле GetCounters(),

/// добавив в коллекцию все за исключением того, у которого имя совпадает с параметром),

/// но я не стал париться, решив, что удалять будем всю категорию целиком.

/// </summary>

/// <param name="categoryName">В случае Null заводится каунтер Null</param>

/// <param name="categoryDescription"></param>

/// Приходится возвращать что-то, потому что я не представляю, как из Т-SQL вызвать

/// static void метод</returns>

public static SqlByte AddCounterToCategory(SqlString categoryName, SqlString counterName, SqlString counterDescription)

{

string m_categoryName = categoryName.ToString();

string m_counterName = counterName.ToString();

string m_counterDescription = counterDescription.IsNull ? String.Empty : counterDescription.ToString();

PerformanceCounterCategory pcc = new PerformanceCounterCategory(m_categoryName);

if (pcc.CounterExists(m_counterName))

throw new Exception("Счетчик не был добавлен, т.к. счетчик с таким именем в данной категории уже существует. " +

"Используйте метод DeleteCategory, чтобы удалить всю категорию.");

CounterCreationData ccd;

CounterCreationDataCollection ccdc = new CounterCreationDataCollection();

string m_categoryDescription = pcc.CategoryHelp;

foreach (PerformanceCounter pc in pcc.GetCounters())

{

ccd = new CounterCreationData();

ccd.CounterName = pc.CounterName; ccd.CounterHelp = pc.CounterHelp;

ccd.CounterType = pc.CounterType; ccdc.Add(ccd);

}

ccd = new CounterCreationData();

ccd.CounterName = m_counterName; ccd.CounterHelp = m_counterDescription;

ccd.CounterType = PerformanceCounterType.NumberOfItems32; ccdc.Add(ccd);

PerformanceCounterCategory.Delete(m_categoryName);

PerformanceCounterCategory.Create(m_categoryName, m_categoryDescription, PerformanceCounterCategoryType.SingleInstance, ccdc);

return 0;

}

public SqlDouble Value

{

get

{

pc.NextValue(); System.Threading.Thread.Sleep(10); return pc.NextValue();

}

set

{

if (!pc.ReadOnly) pc.RawValue = (long)value;

}

[SqlMethod(IsMutator=true)]

public void IncrBy(SqlDouble delta)

{

if (!pc.ReadOnly) pc.IncrementBy((long)delta);

}

/// <summary>

/// Статический аналог св-ва Value (на чтение)

/// </summary>

/// <param name="counter">Название счетчика, напр., localhost\Processor(_Total)\% Processor Time</param>

/// <returns>Текущее значение</returns>

public static SqlDouble GetValue(SqlString counter)

{

PerfCounter u = PerfCounter.Parse(counter);

return u.Value;

}

public SqlString Description

{

get

{

return pc.CounterHelp;

}

/// <summary>

/// Метод - статический аналог св-ва Description

/// </summary>

/// <param name="counter">Название счетчика, напр., .\SQLServer:Plan Cache(SQL Plans)\Cache Hit Ratio</param>

/// <returns>Описание</returns>

public static SqlString GetDescription(SqlString counter)

{

return PerfCounter.Parse(counter).Description;

}

--------------------------------------------------------------------------------------------------------------------------

alexejs Ср фев 27, 2008 08:26

Комментарий к комментарию

Код: Выделить всё

...
/// <returns>Приходится возвращать что-то, потому что я не представляю, как из Т-SQL вызвать
/// static void метод типа
...

Как выяснилось, неспроста я это не представляю. Оно в принципе невозможно. Мужики ответили так: "You cannot call static void methods directly on a type. Instead, you must create them as user defined procedures. This should work:
Create proc CreateCategory @categoryName nvarchar(4000), @categoryDescription @nvarchar(4000) as external name <Assembly>.PerfCounter.CreateCategory"

Ну это что в лоб, что по лбу. Можно сделать его не void, как я, можно завернуть в процедуру, как советуют - один хрен извращаться приходится.

Доступ к счетчикам Perfmon из SQL Server. Способы 1 и 2.

Additional resources