SQL Server постоянно контролирует себя и собирает информацию, полезную для мониторинга состояния экземпляра, находит проблемы, такие как отсутствующие индексы, и оптимизирует запросы. SQL Server предоставляет эту информацию посредством динамических административных объектов (dynamic management objects, DMO).

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

С помощью динамических административных объектов большое количество нужной вам информации уже собрано. Все, что вам нужно сделать, — это запросить соответствующие динамические административные объекты с помощью стандартных запросов T-SQL и извлечь полезную информацию.

Запрос параметров системы (sys.dm_os_sys_info)

SELECT cpu_count AS логических_ядер,
cpu_count / hyperthread_ratio AS процессоров,
CAST(physical_memory_kb / 1024. AS int) AS ОЗУ_MБайт,
sqlserver_start_time AS старт_сервера
FROM sys.dm_os_sys_info;

Результат у меня (правда старт сервера по умолчанию без учёта часового пояса):

sql101

Запрос текущих исполняемых запросов (sys.dm_exec_requests)

Также мы получим  текст этого запроса (sys.dm_exec_sql_text) и если есть, то и ожидание.

SELECT S.login_name, S.host_name, S.program_name,
R.command, T.text,
R.wait_type, R.wait_time, R.blocking_session_id
FROM sys.dm_exec_requests AS R
INNER JOIN sys.dm_exec_sessions AS S
ON R.session_id = S.session_id
OUTER APPLY sys.dm_exec_sql_text(R.sql_handle) AS T
WHERE S.is_user_process = 1;

Если я просто запущу этот запрос, то получу следующий результат:

sql102

Фактически он сам себя и показал.

Но так не интересно, поэтому я решил паралленьно запустить другой запрос, на добавление миллионов записей (выбрал первый попавшияся запрос, создал для неё таблицу)

Ниже приведён текст вспомогательного запроса 1 (он мне ещё в другом примере понадобится, поэтому нумерую его):

SET NOCOUNT ON
 
TRUNCATE TABLE TestSpeed;
GO
 
DECLARE @TOTAL AS int = 3000000;
DECLARE @N AS int = 0;
DECLARE @i AS int = 0;
 
DECLARE @T TABLE
(  
	random float
);
 
WHILE @N<@TOTAL
BEGIN
   INSERT INTO @T(random) VALUES(rand(@N));
   SET @N += 1;
END
--WAITFOR DELAY '00:01:00'
 
WHILE @i<10
BEGIN
	INSERT INTO TestSpeed(random) SELECT random FROM @T;
	SET @i += 1;
END

Запускаю запрос на добавление млн. записей, и запускаю запрос на получение статистики. Получаю результат:

sql103

Таким образом мы можем в любой момент посмотреть выполняемые запросы на сервере.

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

sql104

Здесь указано, что есть ожидание LOGBUFFER (Ожидание записи в лог)

Ну или ещё у меня промелькнуло:

sql106

Задержка PREEMPTIVE_OS_WRITEFILEGATHER (ожидание автоувеличения файлов базы и логов)

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

Запрос текущих блокировок (sys.dm_os_waiting_tasks)

Здесь я буду использовать вспомогательный запрос 1 (добавление миллионов записей), приведённый выше, а также простой запрос на выборку из той таблицы, куда будем добавлять.

SELECT * FROM TestSpeed

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

Но это не так то и просто сделать. Мне же ещё нужно успеть выдать запрос на получение блокировок.Поэтому я буду вставлять 10 млн записей. На моём компьютере и с этим алгоритмом вставки временная таблица заполняется за 45 секунд.  Т.е. выборку нет смысла начинать раньше 45 секунд (повторюсь на моём компьютере).

Теперь текст запроса на получение блокировок:

Итак последовательность примера такая:

  1. запускаем вставку на 10 млн.
  2. ждём 50 секунд, когда начнётся копирования в таблицу
  3. запускаем запрос на выборку SELECT
  4. запускаем запрос на получение блокировок (если нет блокировок, то повторяем п.3-4

Вот что получилось у меня:

sql106

Нахождение не использованных индексов (sys.indexes)

Следующий запрос отобразит некластеризованные индексы, которые не использовались с момента последнего запуска экземпляра сервера:

SELECT OBJECT_NAME(I.object_id) AS objectname,
I.name AS indexname, I.index_id AS indexid
FROM sys.indexes AS I
INNER JOIN sys.objects AS O
ON O.object_id = I.object_id
WHERE I.object_id > 100
AND I.type_desc = 'NONCLUSTERED' AND I.index_id NOT IN
(SELECT S.index_id
FROM sys.dm_db_index_usage_stats AS S
WHERE S.object_id=I.object_id
AND I.index_id=S.index_id
AND database_id = DB_ID('TSQL2012'))
ORDER BY objectname, indexname;

У меня есть учебная база данных TSQL2012 (именно её в запросе я и указал). После включения компьютере (запуска сервера) я не обращался к этой БД (за исключением таблицы TestSpeed). И я получил следующий результат:

sql107

теперь каким нибудь индексом воспользоваться. Например выполним следующий запрос:

Select *
FROM HR.Employees AS e
ORDER BY e.lastname

Мы обратились к индексу idx_nc_lastname, который в таблице под номером 6. И теперь ожидаем, что в новой таблице упоминания этого индекса не будет.

sql108

Как мы видим индекс отсутствует, проверка прошла успешна.

Также информация представлена здесь:

https://msdn.microsoft.com/library/azure/ms188754.aspx

И информации там полно.

Writen by Igor Motskin

Если вас интересует удаленная работа программиста, мы предлагаем удаленную работу MS SQL на базе нашей платформы.

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

Посмотреть также: