November 2007

You are currently browsing the monthly archive for November 2007.

Помню, в SQL Server 2000 часто возникала задача определить, какое на данный момент сложилось соотношение распределения страниц данных, индексов и блобов для всех таблиц и индексированных представлений. Это соотношение, иногда, давало очень неожиданные результаты, которые заставляли задуматься. Давно хотел это сделать, и вот выкладываю простой сценарий, который не работает правильно в последующих версиях…

SELECT * FROM ( SELECT OBJECT_NAME(id) AS [Имя объекта] ,(SELECT 8 * SUM([sidx].[dpages])/1024 FROM sysindexes AS [sidx] WHERE [sidx].[indid] < 2 AND [sidx].[id] = [sob].[id]) AS [Занято данными (МБ)] ,(SELECT 8 * SUM(ISNULL([sidx].[used],0) - ISNULL([sidx].[dpages],0))/1024 FROM dbo.sysindexes AS [sidx] WHERE [sidx].[indid] < 2 AND [sidx].[id] = [sob].[id]) AS [Занято индексами (МБ)] ,ISNULL((SELECT 8 * SUM(ISNULL([sidx].[used],0))/1024 FROM sysindexes AS [sidx] WHERE [sidx].[indid] = 255 AND [sidx].[id] = [sob].[id]),0) AS [Занято под BLOB (МБ)] ,(SELECT 8 * SUM(ISNULL([sidx].[reserved],0))/1024 FROM sysindexes AS [sidx] WHERE [sidx].[indid] <= 255 AND [sidx].[id] = [sob].[id]) AS [Зарезервировано (МБ)] FROM sysobjects AS [sob] WHERE ( [sob].[type] = 'U' OR [sob].[type] = 'S' ) AND OBJECTPROPERTY([sob].[id], 'IsSystemTable') = 0 UNION ALL -- Объединяем с данными о материализованных представлениях SELECT OBJECT_NAME(id) AS [Имя объекта] ,0 AS [Занято данными (МБ)] ,(SELECT 8 * SUM(ISNULL([sidx].[used],0))/1024 FROM sysindexes AS [sidx] WHERE [sidx].[indid] < 2 AND [sidx].[id] = [sob].[id]) AS [Занято индексами (МБ)] ,0 AS [Занято под BLOB (МБ)] ,(SELECT 8 * SUM(ISNULL([sidx].[reserved],0))/1024 FROM sysindexes AS [sidx] WHERE [sidx].[indid] <= 255 AND [sidx].[id] = [sob].[id]) AS [Зарезервировано (МБ)] FROM sysobjects AS [sob] WHERE ( OBJECTPROPERTY([sob].[id], 'IsView') = 1 AND OBJECTPROPERTY([sob].[id], 'IsIndexed') = 1 ) AND OBJECTPROPERTY([sob].[id], 'IsSystemTable') = 0 ) AS [sub] ORDER BY [Зарезервировано (МБ)] DESC

Не за горами время выхода SQL Server 2008. На днях должна появиться очередная предварительная версия – CTP5, которая, ещё в более предварительном виде, уже доступна SQL Server MVP. Как все прекрасно понимают, предварительные версии отличаются от окончательного выпуска существенной нестабильностью в работе, многие компоненты могут работать не надёжно, или вообще быть отключены. Кроме того, само бетатестирование является методом испытания на прочность тестируемого программного обеспечения и может привести к потере работоспособности последнего. Одним из проявления частичной потери работоспособности SQL Server, с которым мне очень часто приходилось сталкиваться во время участия в программах бетатестирования SQL Server 2005 и его локализованной версии, является невозможность корректного удаления предварительной версии SQL Server. Причин для проявления такой проблемы может быть множество, в реальных условиях это может быть, к примеру, не корректное исправление в системном реестре или сбои дисковой подсистемы.
В этом году мне уже неоднократно приходилось устанавливать на испытательный полигон предварительные версии SQL Server 2008, и поскольку вероятность возникновения необходимости переустановки или удаления не поддающихся правильным способам программ резко возросла, я решил оставить тут (в основном для себя и для тех, кто занимается аналогичным тестированием) небольшую шпаргалку – путеводитель, по тем шагам, которые необходимо предпринять, для обмана программы установки SQL Server, чтобы она осуществляла установку так, как будто на компьютере никогда не был установлен SQL Server .
Сразу хочу предостеречь читателя от применения изложенных ниже методов на «боевой» системе. НИКОГДА ЭТОГО НЕ ДЕЛАЙТЕ!!! Внесение изменений в системный реестр операционной системы Windows чревато непредсказуемым поведением самой системы или установленного на этой системе программного обеспечения. То, что ниже рекомендуется, можно применять только на выделенном для тестовых целей компьютере, потеря информации которого или отказы в работе не могут нанести вред Вам и вашей организации. Эти меры можно рассматривать только как крайние меры, когда другие средства не помогли решению проблемы, а полная переустановка системы не желательна. Также, эти методики могут оказаться непригодными, если у Вас на компьютере установлено несколько экземпляров SQL Server, одной или разных версий.
Предлагаемый метод удаления или замены SQL Server 2005 не затрагивает вопросы сохранности обслуживаемых этим сервером баз данных и любых иных данных и настроек. Об этом следует позаботиться заранее, до того как вы преступите к перечисленным ниже шагам:


  1. Первым шагом попробуйте удалить все возможные компоненты SQL Server из оснастки установки удаления программ панели управления операционной системы. Чем больше Вам удастся удалить таким путём, тем больше шансов на успешное выздоровление. Очень Вам рекомендую удалить на время дальнейших манипуляций .NET Framework v2.0, при установке SQL Server 2005 он будет заново проинсталлирован, т.ч. потеря не велика, зато глючащие компоненты, которые это используют, Вам не помешают.
  2. Если какие-то компоненты нормальным способом удалить не удалось, попробуйте их удалить, запуская программу установки из командной строки, как это описано в поставляемом с дистрибутивом файле подсказки по установке. У меня дистрибутив валяется на флешке, вот где в нём этот файл:

      D:\SQLDEV2K5rus\Server\Setup\help\1049\ setupsql9.chm

    Поищите там статью с названием: «Как установить SQL Server 2005 из командной строки». В качестве примера, можно рассмотреть предложенные ниже варианты, хотя в документации есть много примеров на разные случаи жизни (кластерные конфигурации или удаление экзотики, например, полнотекстового поиска).
    Для удаления компонента ядра экземпляра по умолчанию можно использовать такую команду:

      start /wait E:\SQLDEV2K5rus\Server\setup.exe /qb INSTANCENAME=MSSQLSERVER REMOVE=SQL_Engine

    Для удаления всего экземпляра по умолчанию можно использовать такую команду:

      start /wait E:\SQLDEV2K5rus\Server\setup.exe /qb INSTANCENAME=MSSQLSERVER REMOVE=ALL

    Для переустановки компонент ядра и замене системных баз применим такой вызов программы установки:

      E:\SQLDEV2K5rus\ Server\Setup.exe /qb INSTANCENAME=MSSQLSERVER ADDLOCAL=SQL_Engine REINSTALLMODE=OMUS SKUUPGRADE=1 REBUILDDATABASE=1

  3. Если Вы читаете эту строку, значит предыдущий «рецепт» тоже не помог :( Тогда давайте выполним некоторые подготовительные шаги, которые могут оказаться не обязательными, но иногда из-за этих мелочей могут возникнуть проблемы совсем в неожиданных местах. Если в профиле пользователя, от имени которого Вы осуществляете манипуляции по удалению следов SQL Server, каталоги временных директорий TEMP и TMP были переназначены на отличающиеся от предлагаемых по умолчанию мест, лучше верните всё так, как оно было по умолчанию. Кроме того, иногда бывает полезно почистить временные директории. Запустите оснастку служб и остановите там все службы, которые могут относиться к SQL Server, и не забудьте закрыть потом эту оснастку и все подобные, основанные на консоли mmc оснастки.
  4. Если предыдущие шаги не помогли, придётся использовать запрещённый приём :( Засучите рукава, нам придётся лезть руками в системный реестр Windows, для чего стоит запустить программу regedit. ВАЖНО!: сделайте резервную копию системы и реестра! Перед внесением изменений в ключи реестра, делайте копии изменяемых веток! (надеюсь, это всё вам потом не понадобиться :). В оснастке установки и удаления программ Вы, возможно, ещё видите следы того, что ещё не удалось удалить. Чтобы убедиться, что это Вам не кажется, периодически нажимайте в окне этой оснастки клавишу F5, которая повторяет запрос к системному реестру, откуда и черпает информацию об установленном ПО и о том, где искать деиснтоллятор каждой из представленных в системе программ. Для этого оснастка пользуется двумя ветками реестра, которые также проверяются при установке нового ПО, чтобы убедиться в отсутствии более «свежих» версий или конфликтов. Нас тоже будут интересовать эти ветки реестра. Начнём, пожалуй, с той, которая непосредственно видна в оснастке установки и удаления программ панели управления.
    Нам нужно будет найти и открыть раздел реестра HKEY_LOCAL_MACHINE. В этом разделе хранятся настройки всех установленных на компьютере программ. Нужно перейти к папке Uninstall, относящейся к операционной системе. Расположена она по этому пути:

      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\

    Тут подлежит удалению всё то, что относится к установленным ранее компонентам SQL Server. Для этого нужно просмотреть все имеющиеся там ключи. Проще всего, воспользоваться поиском по реестру начиная от корня этой папки, и искать, к примеру, слово «SQL». Однако, никто не знает, какие нас ждут сюрпризы в новых версиях, поэтому стоить полагаться только на свои глаза… В качестве примера подлежащего удалению ключа можно привести самый очевидный ключ:

      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\Microsoft SQL Server 2005

    Аналогичную работу нужно провести и в этой ветке:

      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Products

    Вторым разделом реестра, который нам понадобиться, будет HKEY_CLASSES_ROOT. Это слабо структурированный раздел, поэтому нам придётся напрячься, и перейти к папке Installer, в которой сосредоточена информация об установленных программах, компонентах этих программ и обновлениях/заплатках для них. Нужно терпеливо и внимательно просмотреть все подветви в разделах этой папки: Features, Patches, и Products. Ищем и удаляем все ключи, которые относятся к SQL Server. Если Вы ещё не устанавливали обновлений и заплат, то искомые ключи будут скорее всего только в папке Products. Вот примеры удалённых мной когда то ключей:

      HKEY_CLASSES_ROOT\Installer\Patches\A339FCF7E60313B4A82FE3B9A8C43AB8
      HKEY_CLASSES_ROOT\Installer\Features\3B0D31EF391510B46AA17252235CFFCA

    (На этом месте я обычно уже, сгорая от нетерпения, пробую установить SQL Server поверх замаскированного удалениями в реестре экземпляра, но, увы, часто это не проходит)
    После успешного удаления всех упоминаний из перечисленных выше разделов и веток реестра, в оснастке установки и удаления программ Вы не должны больше видеть ничего, что относиться к SQL Server и его обновлениям. Также, не должно быть ничего относящегося к .NET Framework v2.0.
    Для верности, стоит почистить ещё ключи в разделах реестра, относящихся к настройкам и службам, вот перечень основных:

      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server
      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS
      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSFTESQLInstMap
      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSOSOAP
      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer
      HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER
      HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\msftesql
      HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\msftesqlFD
      HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT

    После основательной чистки реестра, есть смысл перезагрузить операционную систему, чтобы изменения стали актуальны, а вычищенные службы не остались запущенными. Если Вам нужно экономить дисковое пространство, можете удалить каталоги, относящиеся к SQL Server и созданные во время установки. По умолчанию это:
      C:\Program Files\Microsoft SQL Server.
    Закончив с этим, можно приступить к установке SQL Server, как будто его никогда на компьютере установлено не было :)
    Если установка не прошла, проанализируйте журналы установки, которые можно обнаружить в двух местах, это некая папка в корне одного из присутствующих у Вас дисков, которая напоминает GUID, и в ней только один файл лога. Или это папка журналов установки, которая по умолчанию создаётся тут:
      C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG
    Если журналы ничего не подсказывают, вернитесь в реестр и посмотрите, возможно Вы что-то пропустили (я всегда что-то пропускаю, там ведь так много всего). Если ничего не нашли, можно попробовать почистить Авгиевы конюшни зарегистрированных компонент, но, практика показывает, что переустановка системы занимает гораздо меньше времени. Вот эти подразделы:

      HKEY_CLASSES_ROOT\AppID
      HKEY_CLASSES_ROOT\Applications
      HKEY_CLASSES_ROOT\CLSID
      HKEY_CLASSES_ROOT\IntegrationServices.Configuration.90
      HKEY_CLASSES_ROOT\Microsoft SQL Replication Distributor 9.0
      HKEY_CLASSES_ROOT\Microsoft.AnalysisServices.Account

    и т.д.
  5. Надеюсь, опасные игры с реестром привели Вас к победе над проблемой :) Если это так и SQL Server установлен поверх неудаляемого до этого экземпляра, то нужно закрепить успех. Не удивляйтесь, но я предложу Вам теперь удалить все следы SQL Server 2005, воспользовавшись ПРАВИЛЬНЫМ инструментом, а именно, оснасткой установки и удаления программ панели управления Windows. Зачем это нужно? Это нужно для того, чтобы те огрехи и обманы, которые присущи предложенному выше методу НЕ правильного удаления не помешали в дальнейшем Вам наслаждаться таким замечательным продуктом, как SQL Server 2005. Если удаление и установка пройдут успешно, есть большая вероятность того, что предлагаемы в этой статье действия не принесут в дальнейшем никакого вреда.
  6. И последний шаг, напишите мне письмо и опишите в нём что сработало, а что нет, чтобы я мог внести в статью исправления или дополнения, могущие оказаться полезными тем, кто будет вынужден воспользоваться этим способом после Вас :)

В тему:


Вы, наверное, уже заметили, какую полезную информацию об индексах и о статистике использования существующих и, как это не удивительно, не существующих индексов можно получить в SQL Server 2005 из системных динамических административных представлений. Особенно впечатляет эта информация, когда её видишь в виде пользовательского отчёта приборной панели производительности SQL Server, которую в качестве бесплатного дополнения к SQL Server Management Studio 2005 предлагает использовать Майкрософт.
Ниже представлен сценарий, который позволяет получить рекомендации по индексам, которых в обозримом прошлом недоставало базе данных, в контексте которой этот сценарий исполняется. Учтите, что сценарий в целях наглядности был упрощён, поэтому результирующие команды по созданию индексов, наверняка, будут содержать ошибки, т.к. тут не учитывается возможность отсутствия не относящихся к предикатам эквивалентности колонок, а также колонок, которые в индексе будут присоединёнными. Впрочем, эти ошибки легко устранимы, просто поищите лишние запятые перед запуском сценария, а также не забудьте удалить пустые инструкции INCLUDE.


    -- Не забудьте поправить сценарий создания индексов, -- убрав лишние запятые или пустую инструкцию INCLUDE SELECT [Рекомендуемый индекс]= '-- CREATE INDEX [IX_' + OBJECT_NAME(mid.object_id) + '_' + CAST(mid.index_handle AS nvarchar) + '] ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns,'') + ', ' + ISNULL(mid.inequality_columns,'') + ') INCLUDE (' + ISNULL(mid.included_columns,'') + ');', [Число компиляций] = migs.unique_compiles, [Количество операций поиска] = migs.user_seeks, [Количество операций просмотра] = migs.user_scans, [Средняя стоимость ] = CAST(migs.avg_total_user_cost AS int), [Средний процент выигрыша] = CAST(migs.avg_user_impact AS int) FROM sys.dm_db_missing_index_groups mig JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID()



10 правил здравого смысла для индексов


  1. Создавайте только те индексы, которые будут использоваться. Удалите неиспользуемые индексы.
  2. Создавайте индексы по колонкам, которые в запросах используются в предложениях: WHERE, ORDER BY, GROUP BY или DISTINCT.
  3. Располагайте в ключе индекса вначале самые избирательные колонки, предикаты которых вводят самые большие ограничения выборки.
  4. Некластеризованный индекс максимально полезен, если его ключ высокоселективен.
  5. Создавайте индекс для всех внешних ключей.
  6. Используйте покрывающие индексы для часто используемых ресурсоёмких запросов.
  7. Используйте минимально-возможную длину ключей кластеризованных индексов.
  8. Кластеризованные индексы хороши для выборки диапазонов.
  9. Предпочтительнее много коротких, чем один очень длинный индекс.
  10. На периодической основе используйте программу «Помощник по настройке ядра СУБД».

В тему:






Учебник Майкрософт: Проектирование и оптимизация доступа к базам данных Microsoft SQL Server 2005. Учебный курс Microsoft (+ CD-ROM) рекомендует при разработке индексов соблюдать следующие правила:


  • В первую очередь всегда изучайте план запроса. Он покажет оптимальный с точки зрения подсистемы обработки запросов текущий план выполнения. Найдите самую дорогую часть плана выполнения и займитесь её оптимизацией. Перед этим удо-стоверьтесь, что статистика по всем таблицам в вашем запросе актуальна, запустив команду обновления статистики для всех таблиц в вашем запросе.
  • Если вы видите просмотр строк таблицы, выполните оптимизацию. Просмотр таб¬лицы – это самый медленный способ выполнения. Просмотр таблицы означает не только то, что не используется никакой индекс, но и то, что эта таблица вообще не имеет кластеризованного индекса. Даже если вы можете только заменить просмотр строк таблицы просмотром кластеризованного индекса, это уже стоит сделать.
  • Если вы видите просмотр кластеризованного индекса, выясните, может ли он быть заменен поиском в индексе. Для этого найдите условия, примененные к этой табли¬це. Как правило, существуют условия для двух или трех полей таблицы. Найдите самое селективное условие (т.е. условие, применение которого привело бы к полу¬чению наименьшего количества записей) и посмотрите, существует ли индекс на этом поле. Будет рассмотрен любой индекс, который содержит это поле. Если тако¬го индекса нет, создайте его и посмотрите, возьмет ли его в обработку подсистема обработки запросов.
  • Если подсистема обработки запросов не отрабатывает существующий индекс (т.е. если все еще выполняется просмотр кластеризованного индекса), проверьте список выходных данных. Возможно, поиск по вашему индексу может выполняться быст¬рее, чем просмотр кластеризованного индекса, но вызывает поиск закладок, что де¬лает общие издержки больше, чем использование кластеризованного индекса. Опе¬рации на кластеризованном индексе (сканирование или поиск) никогда не требуют поиска закладок, потому что кластеризованный индекс уже содержит все данные. Если список выходных данных не очень большой, добавьте эти поля в индекс и по¬смотрите, подхватила ли его подсистема обработки запросов. Помните, что общий размер важнее, чем количество полей. Добавление трех целочисленных полей в ин¬декс менее дорого, чем добавление одного поля типа varchar со средней длинной данных, равной 20.
  • Если вы видите поиск закладок, это означает, что ваш индекс не является покры¬вающим. Попытайтесь сделать его покрывающим, если это имеет смысл (см. пре¬дыдущий пункт списка). План выполнения, выбранный подсистемой обработки за¬просов, может оказаться не самым лучшим. Подсистема обработки запросов делает определенные допущения о стоимости дисковой подсистемы и центрального про¬цессора против стоимости ввода/вывода. Эти допущения иногда могут быть непра¬вильными. Если вы не верите, что выбор подсистемы обработки запросов является лучшим, запустите запрос на выполнение в цикле в течение 10-15 минут с планом, выбранным оптимизатором. Потом измените запрос, чтобы он использовал ваш индекс (вы должны будете использовать для этого подсказку индекса), и затем снова запустите запрос на выполнение в течение 10-15 минут. Сравните результаты, чтобы увидеть, какой запрос работает лучше.
  • Избегайте любых вычислений или операций на столбцах, где это возможно. Неко¬торые операции не допускают использования индекса на этом поле, даже если он существует, например, применение функций LTRIM или RTRIM на строковых дан¬ных серьезно влияет на производительность. Например, вместо приведения условий (DateField as varchar (20)) = @dateString, попробуйте сначала преобразовать @dateString в выражение типа datetime и затем сравнить со значением DateField. Если невозможно обойтись без функций или вычислений на столбце, используйте индекс, построенный на этом выражении. Это можно сделать двумя способами:
     создайте вычисляемое поле на базе вашего выражения;
     создайте представление и постройте на нем индекс.