Рассылка закрыта
При закрытии подписчики были переданы в рассылку "Вопросы и ответы по MS SQL Server" на которую и рекомендуем вам подписаться.
Вы можете найти рассылки сходной тематики в Каталоге рассылок.
MS SQL Server - дело тонкое...
Информационный Канал Subscribe.Ru |
#212<< #213 |
СОДЕРЖАНИЕ
Reporting Services & Notification Services & Репликация Санкт-Петербург. 6 сентября 2004 года 18:00 в помещении компании DataArt состоится очередной семинар Russian SQL Server Club (Microsoft & SQL.RU). Встреча проводится при поддержке компаний DataArt и Digital Design. Программа встречи:
2. Notification Services (Антон Смольянинов, Digital Design) 3. Reporting Services (Андрей Синкин, Microsoft)
Информация о месте проведения: DataArt, Санкт-Петербург, Большой Сампсониевский пр., д. 60 А Регистрация не требуется Перемещение статистики SQL Server из одной базы данных в другую
По материалам статьи
Joe Chang:
Transferring SQL Server Statistics From One Database to Another Многие рабочие базы данных в настоящее время имеют объем в десятки и сотни гигабайт. Такой объем не является проблемой для аппаратного обеспечения компаний. Однако разработчики обычно любят работать на своих персональных компьютерах, включая ноутбуки с ограниченным дисковым пространством, поэтому база данных размером в 10-100 гигабайт уже может вызвать трудности. Кроме того, регулярная передача таких больших файлов данных через сеть может не иметь смысла из-за потерь рабочего времени. Поэтому предпочтительнее работать с меньшей по размеру базой данных, но имеющей ту же схему. Главным отличием между маленькой тестовой базой данных разработчика и полноразмерной рабочей базой данных является распределение данных статистики. Это приводит к разным оценкам затрат ресурсов и потенциально разным планам исполнения запросов. В этих условиях разработчик не сможет увидеть потенциальные проблемы с производительностью, пока план исполнения не будет проверен на полноразмерной базе данных. Решением проблемы разных планов исполнения может служить передача статистики из рабочей базы данных в меньшую базу данных разработки. В идеале такая возможность должна быть встроена в SQL Server, поэтому было бы хорошо, если бы читатели этой статьи отослали на адрес sqlwish@microsoft.com пожелание иметь такую функциональность. По слухам, такая функциональность уже есть в Sybase Adaptive Server Enterprise, т.е. над этой проблемой кто-то уже определенно задумывался. Как временное решение, предлагается альтернативный метод. Статистика SQL Server SQL Server использует оптимизацию на основе оценки затрат ресурсов. Ключевым моментом такой оптимизации является метод оценки количества строк и страниц, вовлеченных в каждый шаг плана исполнения запроса. Это является причиной того, что SQL Server генерирует и поддерживает статистику распределения. Статистика генерируется на основе ключей индексов, но также может быть сгенерирована для неиндексированных столбцов. Таблица sysindexes имеет записи для каждого индекса и каждой коллекции статистики, которая не связана с индексом. Каждая таблица имеет запись в таблице sysobjects с id, уникальным в пределах базы данных. Столбец id в таблице sysindexes - это id таблицы. Столбцы id и indid уникально определяют строку в таблице sysindexes. Столбец name в таблице sysindexes - это имя индекса или имя коллекции статистики. Любая коллекция статистики может быть отображена с помощью следующей команды: DBCC SHOW_STATISTICS ( table , target ) Target - это имя индекса или имя коллекции статистики. Ниже показан результат выполнения команды DBCC SHOW_STATISTICS для коллекции статистики на основе индекса. Первый набор данных содержит основную информацию, включая дату последнего обновления, общее количество строк, количество строк для сбора статистики (sample), и т.д. Второй набор данных содержит общее среднее значение распределения для каждого ключа в последовательности. В этом примере первым столбцом ключа является столбец eventPlannerID, а вторым (и последним) - ID. Первая строка показывает информацию об общем среднем распределении для каждого отдельного значения первого ключа, а вторая строка показывает распределение для каждого отдельного значения первого ключа, скомбинированного со вторым ключом.
Рис.1. Результат выполнения команды DBCC SHOW_STATISTICS. Процесс передачи статистики Процесс передачи статистики из одной базы данных в другую с той же схемой можно описать следующим образом: 1) Обновление статистики полноразмерной базы данных (не обязательно, но рекомендуется). 2) Создание новой базы данных на сервере с полноразмерной версией базы данных. 3) Установка AUTO_CREATE_STATISTICS и AUTO_UPDATE_STATISTICS в off. 4) Создание пользователей, типов данных, таблиц, ограничений, кластерных индексов (включая первичные ключи), и всех остальных объектов, кроме некластерных индексов. 5) Создание таблиц для сохранения связи между именами и id таблиц и пользователей исходной и новой баз данных. Заполнение таблиц связи между именами таблиц и пользователей. 6) Создание и заполнение таблицы с копией таблицы sysindexes из исходной базы данных (необязательно). 7) Выполнение sp_configure для того, чтобы разрешить обновление системных таблиц. 8) Вставка коллекций статистики, не связанных с индексами, в таблицу sysindexes новой базы данных. 9) Создание всех некластерных индексов. 10) Обновление значений, относящихся к статистике, у всех записей по индексам таблицы sysindexes. Обновление статистики Этот шаг необязателен, но если мы собираемся сделать все наилучшим образом при передаче статистики, то нам нужно передать точную информацию. Простейший метод обновления статистики - выполнение следующей команды. exec sp_updatestats Эта команда выполняет UPDATE STATISTICS по всем пользовательским таблицам в текущей базе данных. Новая статистика унаследует коэффициент сбора статистики (sampling ratio) из старой статистики. Желательно изменить коэффициент сбора статистики на full scan, для этого выполните следующую команду для генерации скрипта обновления статистики по каждой таблице с полным сканированием. Заметьте, что в SQL Server Enterprise Edition представления также могут иметь индексы. Также предполагается, что вы не собираетесь передавать статистику по таблицам, созданным SQL Server во время инсталляции. SELECT 'UPDATE STATISTICS ' + o.name + ' WITH FULLSCAN' FROM sysobjects o WHERE ( OBJECTPROPERTY(o.id, N'IsUserTable') = 1 OR OBJECTPROPERTY(o.id, N'IsUserView') = 1 ) AND OBJECTPROPERTY(o.id, N'IsMSShipped') = 0 ORDER BY o.name Также выполните DBCC UPDATEUSAGE, чтобы исправить значения столбцов rows, used, reserved и dpages таблицы sysindexes. Создание новой базы данных и отключение автоматической статистики Это пример скрипта создания новой базы данных: CREATE DATABASE [sut] ON PRIMARY (NAME = N'sut_data', FILENAME = N'C:\MSSQL\Data\sut_data.mdf' , SIZE = 32) LOG ON (NAME = N'sut_log', FILENAME = N'C:\MSSQL\Data\sut_log.ldf' , SIZE = 16) COLLATE SQL_Latin1_General_CP1_CI_AS После создания новой базы данных отключите автоматическое управление статистикой, как показано ниже. ALTER DATABASE [sut] SET AUTO_CREATE_STATISTICS OFF GO ALTER DATABASE [sut] SET AUTO_UPDATE_STATISTICS OFF GO Создание пользователей, типов данных, таблиц, кроме некластерных индексов Создайте необходимых пользователей (которые владеют объектами в исходной базе данных), типы данных, кластерные индексы, ограничения и других объекты, кроме некластерных индексов. Возможно, будет проще запустить генерацию скриптов в Enterprise Manager дважды, первый раз с индексами, но без ключей, значений по умолчанию и ограничений, и второй раз без индексов, но с ключами, значениями по умолчанию и ограничениями. Это поможет отделить некластерные индексы от значений по умолчанию. Причина того, что мы не создаем некластерные индексы на этом этапе, в том, что индексы и статистики, не связанные с индексами, могут иметь пересекающиеся значения indid. Если бы некластерные индексы были созданы сейчас, то было бы необходимо изменять связи для значений indid статистики исходной базы данных. Создание и заполнение таблицы связи Когда объекты, такие, как таблицы, создаются в новой базе данных, новый объект скорее всего имеет id, отличный от id исходного объекта. Т.к. таблица sysindexes использует столбец id для определения таблицы, необходимо создать таблицу связи для хранения id исходного и нового объектов для каждой таблицы. В скрипте столбец name не является необходимым, он включен для удобства. Это скорее всего не важно, но этот набор скриптов не тестировался на базах данных, в которых владельцами таблиц являются не dbo. CREATE TABLE [tobjects] ( name sysname , oid int , ouid int , id int , uid int Switch back to the original database, and use the following script to populate the object id mapping table. INSERT [sut]..[tobjects] (name, oid, ouid, id, uid) SELECT o.name, o.id, o.uid, n.id, n.uid FROM sysobjects o INNER JOIN [sut]..sysobjects n ON n.name = o.name WHERE ( OBJECTPROPERTY(o.id, N'IsUserTable') = 1 OR OBJECTPROPERTY(o.id, N'IsUserView') = 1 ) AND OBJECTPROPERTY(o.id, N'IsMSShipped') = 0 ORDER BY o.name Создание копии исходной таблицы sysindexes Этот шаг также необязателен, но он помогает создать постоянную копию таблицы sysindexes исходной базы данных в новой базе данных. Показанный ниже скрипт создает таблицу с теми же столбцами, что содержатся в таблице sysindexes. CREATE TABLE tindexes ( id int , status int , first binary (6) , indid smallint , root binary(6) , minlen smallint , keycnt smallint , groupid smallint , dpages int , reserved int , used int , rowcnt bigint , rowmodctr int , reserved3 tinyint , reserved4 tinyint , xmaxlen smallint , maxirow smallint , OrigFillFactor tinyint , StatVersion tinyint , reserved2 int , FirstIAM binary (6), impid smallint , lockflags smallint , pgmodctr int , keys varbinary (1088), name sysname , statblob image , maxlen int ) GO Вернитесь в исходную базу данных и заполните созданную таблицу данными из таблицы sysindexes исходной базы данных. INSERT [sut]..tindexes (id,status,first,indid,root,minlen,keycnt,groupid,dpages,reserved,used,rowcnt, rowmodctr,reserved3,reserved4,xmaxlen,maxirow,OrigFillFactor,StatVersion, reserved2,FirstIAM,impid,lockflags,pgmodctr,keys,name,statblob,maxlen,rows) SELECT i.id,status,first,indid,root,minlen,keycnt,groupid,dpages,reserved,used,rowcnt, rowmodctr,reserved3,reserved4,xmaxlen,maxirow,OrigFillFactor,StatVersion, reserved2,FirstIAM,impid,lockflags,pgmodctr,keys,i.name,statblob,maxlen,rows FROM sysindexes i INNER JOIN [sut]..[tobjects] t ON t.oid = i.id Внутреннее объединение с таблицей [tobjects] оставляет для копирования в новую таблицу только строки с верной информацией о связи. Связь между id объекта и id объекта новой базы данных пока в соответствие не ставится. Для этого нет особых причин. Изменение конфигурации, чтобы разрешить обновления системных таблиц По умолчанию, прямые обновления системных таблиц не разрешены по очевидным причинам. Следующая команда изменяет эту настройку (обычно не рекомендуется выполнять прямые обновления в системных таблицах, но это особый случай). Exec sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE GO Заметьте, что любая хранимая процедура, созданная, когда установка 'разрешить обновления' была активна, будет иметь возможность обновлять системные таблицы, даже если эта установка будет отключена. Рекомендуется немедленно отключать установку 'разрешить обновления' и не создавать хранимые процедуры в то время, когда эта установка активна. Вставка статистики Вставьте коллекции статистики, не связанные с индексами, в таблицу sysindexes новой базы данных. Столбец status с битовой маской 64 обозначает статистики, не связанные с индексами. Этот шаг должен быть выполнен до создания некластерных индексов. Когда в новой базе данных существуют только кластерные индексы, значение столбца indid в таблице sysindexes может быть равно или 0 (для неупорядоченных таблиц, куч), или 1 (для кластерных индексов). И статистики, не связанные с индексами, и некластерные индексы имеют значения столбца indid от 2 до 254. Значение indid, равное 255, обозначает данные типа text или image. Это позволяет строки таблицы sysindexes исходной базы данных просто копировать в таблицу sysindexes новой базы данных с новым id объекта и исходным indid. Заметьте, что последний столбец таблицы sysindexes, rows, не вставляется, т.к. это вычисляемый столбец.
INSERT sysindexes( Создание некластерных индексов и обновление таблицы sysindexes После вставки в таблицу sysindexes статистик, не связанных с индексами, могут быть созданы некластерные индексы. Неважно, совпадает ли значение indid некластерных индексов с исходным значением indid. Команда, показанная ниже, обновляет новую таблицу sysindexes нужными данными по статистикам из копии исходной таблицы sysindexes. UPDATE i SET dpages = s.dpages, reserved = s.reserved, used = s.used, rowcnt = s.rowcnt, rowmodctr = s.rowmodctr, statblob = s.statblob FROM sysindexes i INNER JOIN [tobjects] t ON t.id = i.id INNER JOIN [tindexes] s ON s.id = t.oid AND s.name = i.name WHERE (s.status & 64) = 0 GO Теперь выполните команду sp_configure, чтобы запретить обновления системных таблиц. Новая база данных может быть теперь сохранена (backup) или отсоединена (detach) для передачи в другую систему. Все необходимые данные для новой маленькой базы данных могут быть переданы в любое время с помощью этого процесса. Заключение В статье было представлено описание шагов передачи статистики из одной базы данных в другую. Наверное, это является наиболее полезным, когда необходимо отобразить в маленькой базе данных такой же план исполнения, который будет и в большой рабочей базе данных. Разработчики могут работать со сгенерированной скриптами базой данных и при этом видеть рабочий план исполнения, за исключением параллельно исполняемых планов, если компьютер разработчика однопроцессорный. Другие потенциальные возможности использования этого метода включают в себя возможность устранять проблемы с производительностью, используя планы исполнения, которые могут строиться только на данных статистики рабочей базы данных. Скрипты для необходимых таблиц и индексов могут быть переданы в новую базу данных вместе со статистикой. Это небольшое количество данных может быть переслано по электронной почте для удаленного устранения проблем. Статьи на русском языке
Введение в SQL - 2
Disaster in the Real World - Hurricane Самые популярные темы недели
Кто на чем пишет клиентов под SQL Server?
Установка беты Юкона.
|
http://subscribe.ru/
http://subscribe.ru/feedback/ |
Адрес подписки |
Отписаться |
В избранное | ||