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;
Результат у меня (правда старт сервера по умолчанию без учёта часового пояса):
Запрос текущих исполняемых запросов (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;
Если я просто запущу этот запрос, то получу следующий результат:
Фактически он сам себя и показал.
Но так не интересно, поэтому я решил паралленьно запустить другой запрос, на добавление миллионов записей (выбрал первый попавшияся запрос, создал для неё таблицу)
Ниже приведён текст вспомогательного запроса 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
Запускаю запрос на добавление млн. записей, и запускаю запрос на получение статистики. Получаю результат:
Таким образом мы можем в любой момент посмотреть выполняемые запросы на сервере.
А если мы интенсивно будем повторять запрос статуса, то сможем получить и ещё некоторую информацию. Например:
Здесь указано, что есть ожидание LOGBUFFER (Ожидание записи в лог)
Ну или ещё у меня промелькнуло:
Задержка PREEMPTIVE_OS_WRITEFILEGATHER (ожидание автоувеличения файлов базы и логов)
Короче, все эти параметры и что они означают и когда они могут проявляться нужно искать в мануалах.
Запрос текущих блокировок (sys.dm_os_waiting_tasks)
Здесь я буду использовать вспомогательный запрос 1 (добавление миллионов записей), приведённый выше, а также простой запрос на выборку из той таблицы, куда будем добавлять.
SELECT * FROM TestSpeed
Теперь наша главная задача попасть этим запросом в тот момент, когда запрос на вставку данных будет непосредственно копировать данные из временной таблицы в таблицу TestSpeed. Это должно заблокировать TestSpeed.
Но это не так то и просто сделать. Мне же ещё нужно успеть выдать запрос на получение блокировок.Поэтому я буду вставлять 10 млн записей. На моём компьютере и с этим алгоритмом вставки временная таблица заполняется за 45 секунд. Т.е. выборку нет смысла начинать раньше 45 секунд (повторюсь на моём компьютере).
Теперь текст запроса на получение блокировок:
Итак последовательность примера такая:
- запускаем вставку на 10 млн.
- ждём 50 секунд, когда начнётся копирования в таблицу
- запускаем запрос на выборку SELECT
- запускаем запрос на получение блокировок (если нет блокировок, то повторяем п.3-4
Вот что получилось у меня:
Нахождение не использованных индексов (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). И я получил следующий результат:
теперь каким нибудь индексом воспользоваться. Например выполним следующий запрос:
Select * FROM HR.Employees AS e ORDER BY e.lastname
Мы обратились к индексу idx_nc_lastname, который в таблице под номером 6. И теперь ожидаем, что в новой таблице упоминания этого индекса не будет.
Как мы видим индекс отсутствует, проверка прошла успешна.
Также информация представлена здесь:
https://msdn.microsoft.com/library/azure/ms188754.aspx
И информации там полно.
Writen by Igor Motskin
Если вас интересует удаленная работа программиста, мы предлагаем удаленную работу MS SQL на базе нашей платформы.
P.S. Если у вас совсем мало опыта, но вы готовы обучаться технологиям SQL и HTML, мы предлагаем обучение, стажировка для начинающего программиста