Базовая информация

Во-первых, несколько простых @@Функций, которые предоставят нам базовую информацию.

Как долго ваш SQL Server работает после последнего перезапуска? Помните, что системная база данных tempdb пересоздаётся при каждом перезапуске SQL Server. Вот один из методов определения времени последнего перезапуска сервера.

 Связанные сервера

Связанные сервера – это соединения, позволяющие SQL Server’у обращаться к другим серверам с данными. Распределённые запросы могут быть запущенны на разных связанных серверах. Полезно знать – является ли ваш сервер баз данных изолированным от других, или он связан с другими серверами.

Список всех баз данных

Во-первых, получим список всех баз данных на сервере. Помните, что на любом сервере есть четыре или пять системных баз данных (master, model, msdb, tempdb и distribution, если вы пользуетесь репликацией). Вы, вероятно, захотите исключить эти базы в следующих запросах. Очень просто увидеть список баз данных в SSMS, но, эти запросы будут нашими «строительными блоками» для более сложных запросов.

Есть несколько путей для получения списка всех БД на T-SQL и ниже вы увидите некоторые из них. Каждый метод возвращает похожий результат, но с некоторыми отличиями.

Последний бэкап?

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

Будет лучше, если вы сразу узнаете путь к файлу с последним бэкапом.

Активные пользовательские соединения

Хорошо было бы понимать какие БД сейчас используются, особенно, если вы собираетесь разбираться с проблемами производительности.

Примечание переводчика: это будет работать только в SQL Server 2012 и выше, в предыдущих редакциях, в dmv sys.dm_exec_sessions отсутствовал столбец database_id. Чтобы узнать в каких БД в данный момент работают пользователи, можно воспользоваться sp_who.

— Похожая информация, может быть получена с помощью sp_who

Изучаем базы данных.

Давайте заглянем поглубже и посмотрим, как мы можем собрать информацию об объектах во всех ваших БД, используя различные представления каталога и dmv. Большинство из запросов, представленных в этом разделе, смотрят «внутрь» только одной БД, поэтому не забывайте выбирать нужную БД в SSMS или с помощью команды use database. Также помните, что вы всегда можете посмотреть в контексте какой БД будет выполнен запрос, с помощью select db_name().

Системная таблица sys.objects одна из ключевых для сбора информации об объектах, составляющих вашу модель данных.

Ниже представлен список типов объектов, информацию о которых мы можем получить (смотрите документацию на sys.objects в MSDN)

Другие представления каталога, такие как sys.tables и sys.views, обращаются к sys.objects и предоставляют информацию о конкретном типе объектов. С этими представлениями, плюс функцией OBJECTPROPERTY, мы можем получить огромное количество информации по каждому из объектов, составляющих нашу схему БД.

Расположение файлов баз данных

Физическое расположение выбранной БД, включая основной файл данных (mdf), и файл журнала транзакций (ldf), могут быть получены с помощью этих запросов.

Таблицы

Конечно, Object Explorer в SSMS показывает полный список таблиц в выбранной БД, но часть информации с помощью GUI получить сложнее, чем с помощью скриптов. Стандарт ANSI предполагает обращение к представлениям INFORMATION_SCHEMA, но они не предоставят информацию об объектах, которые не являются частью стандарта (такие как триггеры, extended procedures и т.д.), поэтому лучше использовать представления каталога SQL Server.

Количество записей в таблице

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

В SSMS мы можем нажать правой кнопкой мыши на любую таблицу, открыть свойства на вкладке Storage и увидеть количество записей в таблице.

Довольно тяжело собрать вручную эту информацию обо всех таблицах. Опять же, если мы будем писать SELECT COUNT(*) FROM TABLENAME для каждой таблицы, нам придётся очень много печатать.

Намного удобнее использовать T-SQL для генерирования скрипта. Скрипт, приведённый ниже, сгенерирует набор инструкций T-SQL для получения количества строк в каждой таблице текущей базы данных. Просто выполните его, скопируйте результат в новое окно и запустите.

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

sp_msForEachTable

Sp_msforeachtable – это недокументированная функция, которая «проходит» по всем таблицам в БД и выполняет запрос, подставляя вместо ‘?’ имя текущей таблицы. Так же существует похожая функция sp_msforeachdb, работающая на уровне баз данных.

Известно несколько проблем с этой недокументированной функцией, например, использование спецсимволов в именах объектов. Т.е. если имя таблицы или базы данных содержит знак ‘-‘, хранимая процедура, листинг которой ниже, завершится с ошибкой.

Самый быстрый способ получения количества записей – кластерный индекс

Все предыдущие метода использовали COUNT(*), который медленно отрабатывает, если в таблице больше чем 500K записей.

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

— Самый быстрый путь получения количества записей
— Hint: получайте из индекса, а не таблицы

— Похожий метод получения количества записей, но с использованием DMV

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

Работа с кучами – это как работа с плоским файлом, вместо базы данных. Если вы хотите гарантированно получать полное сканирование таблицы при выполнении любого запроса, используйте кучи. Обычно я рекомендую добавлять primary key ко всем таблицам-кучам.

— Кучи (метод 1)

— Кучи (Метод 2)

— Кучи (Метод 3) + количество записей

Разбираемся с активностью в таблице

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

Помните, что эта информация из dmv, очищается при каждом перезапуске SQL Server. Чем дольше сервер работает, тем более надёжна статистика. Я чувствую себя намного более уверенно со статистикой, собранной за 30 дней, чем со статистикой, собранной за неделю.

— Чтение/запись таблицы
— Кучи не рассматриваются, у них нет индексов
— Только те таблицы, к которым обращались после запуска SQL Server

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

— Операции чтения и записи
— Кучи пропущены, у них нет индексов
— Только таблицы, использовавшиеся после перезапуска SQL Server
— В запросе используется курсор для получения информации во всех БД
— Единый отчёт, хранится в tempdb

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

Представления

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

В SQL Server, в некоторых случаях, мы можем обновлять данные с использованием представления. Чтобы получить представление «только для чтения», можно использовать SELECT DISTINCT при его создании. Данные «через» представление можно менять только в том случае, если каждой строке представления соответствует только одна строка в «базовой» таблице. Любое представление, не отвечающее этому критерию, т.е. построенное на нескольких таблицах, или с использованием группировок, агрегатных функций и вычислений, будет доступно только для чтения.

Синонимы

Несколько раз в моей карьере я сталкивался с ситуацией, когда не мог понять к какой же таблице обращается запрос. Представьте простой запрос SELECT * FROM Client. Я ищу таблицу под именем Client, но я не могу найти её. Хорошо, думаю я, должно быть это представление, ищу представление с именем Client и всё равно не могу найти. Может быть я ошибся базой данных? В итоге выясняется, что Client – это синоним для покупателей и таблица, на самом деле, называется Customer. Отдел маркетинга хотел обращаться к этой таблице как к Client и из-за этого был создан синоним. К счастью, использование синонимов – это редкость, но разбирательства могут вызвать определённые затруднения, если вы к ним не готовы.

— дополнительная информация о синонимах

Хранимые процедуры

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

— Дополнительная информация о ХП

 

Добавить комментарий

Навигация по записям