September 2007

You are currently browsing the monthly archive for September 2007.


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

 

SELECT   OBJECT_NAME(i.object_id) AS [Table Name],
         i.name AS [Not Used Index Name],
         s.last_user_update AS [Last Update Time],
         s.user_updates AS [Updates]
FROM     sys.dm_db_index_usage_stats AS s
JOIN     sys.indexes AS i
ON       i.object_id = s.object_id
AND      i.index_id = s.index_id
JOIN     sys.objects AS o
ON       o.object_id = s.object_id
WHERE    s.database_id = DB_ID()
AND      (    user_scans   = 0
          AND user_seeks   = 0
          AND user_lookups = 0
          AND last_user_scan   IS NULL
          AND last_user_seek   IS NULL
          AND last_user_lookup IS NULL 
         )
AND      OBJECTPROPERTY(i.[object_id],         ‘IsSystemTable’   ) = 0
AND      INDEXPROPERTY (i.[object_id], i.name, ‘IsAutoStatistics’) = 0
AND      INDEXPROPERTY (i.[object_id], i.name, ‘IsHypothetical’  ) = 0
AND      INDEXPROPERTY (i.[object_id], i.name, ‘IsStatistics’    ) = 0
AND      INDEXPROPERTY (i.[object_id], i.name, ‘IsFulltextKey’   ) = 0
AND      (i.index_id between 2 AND 250 OR (i.index_id=1 AND OBJECTPROPERTY(i.[object_id],‘IsView’)=1))
AND      o.type <> ‘IT’
ORDER BY OBJECT_NAME(i.object_id)

Подробнее о правилах использования этого сценария можно прочитать в описании динамического административного представления sys.dm_db_index_usage_stats.

Полезная статья: Script to identify indexes with heavy writes and low reads

Практика показывает, что использование гаджетов (мини-приложений для боковой панели Windows Vista) для задач мониторинга производительности и работоспособности SQL Server оказывается очень удобным инструментом. Вся прелесть подобных мини-приложений в том, что они постоянно на виду или часто попадаются на глаза. Кроме того, чрезвычайная простота их разработки и установки делает их особенно привлекательными как для профессиональных разработчиков, так и для тех, кто не разрабатывает настольные приложения своими руками.
В интернете можно найти уже готовые мини-приложения, которые “заточены” под конкретные задачи и позволяют отслеживать только те ключевые показатели производительности или жизнедеятельности СУБД, которые были заложены в них на этапе разработки. К таким относится Conchango SQL Server Monitor Gadget v1.0.001 for Windows Vista Sidebar

Заслуживают внимания решения для мини-приложений, на подобие того, которое предлагает PowerGadgets. Эта компания разработала инструментарий для создания пользовательских гаджетов, причём, не нужно для этого владеть языками программирования высокого уровня, достаточно знать T-SQL.
Например, таким образом можно отслеживать счётчик производительности “Buffer cache hit ratio” и представлять его значения в виде стрелочного индикатора.
Запрос может иметь следующий вид:

    SELECT [Buffer cache hit ratio] = (SELECT CAST(cntr_value * 100 AS money) FROM master.dbo.sysperfinfo where object_name = 'SQLServer:Buffer Manager' AND counter_name = 'Buffer cache hit ratio') /(SELECT CAST(cntr_value AS money) FROM master.dbo.sysperfinfo where object_name = 'SQLServer:Buffer Manager' AND counter_name = 'Buffer cache hit ratio base')

Индикатор удобно выбрать такой:

Недавно, мне понадобилось отслеживать производительность одной пользовательской хранимой процедуры, которая является основным звеном технологической цепочки приложения и весьма чувствительна к изменениям среды её окружения и утилизации ресурсов. Для наблюдения тоже оказалось очень удобным использовать мини-приложение, которое я сделал “на коленках” очень быстро, и помог мне как раз инструментарий PowerGadgets.
Для отслеживания статистики работы процедуры я использовал запрос к административным динамическим представлениям, вот он:

    SELECT CAST(SUM(sub.avg_elapsed_time_sec) AS money) AS avg_elapsed_time_sec FROM (SELECT TOP 100 PERCENT (st.total_elapsed_time * 1.0 /100000)/st.execution_count AS avg_elapsed_time_sec FROM master.sys.dm_exec_cached_plans AS c CROSS APPLY master.sys.dm_exec_query_plan (c.plan_handle) AS q INNER JOIN master.sys.dm_exec_query_stats AS st ON c.plan_handle = st.plan_handle CROSS APPLY master.sys.dm_exec_sql_text(sql_handle) AS s WHERE c.cacheobjtype = 'Compiled Plan' AND c.objtype = 'Proc' AND q.dbid = DB_ID('ИМЯВАШЕЙБАЗЫ') AND OBJECT_NAME(s.objectid,s.dbid) = 'ИМЯПРОЦЕДУРЫ' ORDER BY SUBSTRING (s.text, st.statement_start_offset/2, (CASE WHEN st.statement_end_offset = -1 THEN LEN(CAST(s.text AS nvarchar(MAX))) * 2 ELSE st.statement_end_offset END - st.statement_start_offset )/2 ) ) sub

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

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

Portqry.exe – это запускаемая из командной строки или посредством графического пользовательского интерфейса программа, которую можно использовать при устранении неполадок с подключениями к SQL Server по протоколу TCP/IP. Средство Portqry.exe работает на компьютерах с операционной системой Windows 2000, Windows XP, Windows Vista или Windows Server 2003. Программа сообщает о состоянии портов ТСР и UDP на указанном компьютере.

Подробное описание назначения программы и ссылки для её скачивания можно найти в статье базы знаний Майкрософт: Описание запускаемого из командной строки средства Portqry.exe Вот небольшая выдержка из этой статьи:

Программа Portqry.exe сообщает о состоянии порта TCP/IP следующим образом.

  • Listening – Какой-либо процесс прослушивает выбранный порт на выбранном компьютере. Программа Portqry.exe получила ответ от порта.
  • Not Listening – Выбранный порт указанного компьютера не прослушивается ни одним процессом. Программа Portqry.exe получила сообщение протокола ICMP “Destination Unreachable – Port Unreachable” от проверяемого UDP-порта. Если же проверяется TCP-порт, программа получила пакет подтверждения TCP с установленным флагом Reset.
  • Filtered – Проверяемый порт на выбранном компьютере фильтруется. Программа Portqry.exe не получила ответа от этого порта. Поэтому неизвестно, имеется ли на нем прослушивающий процесс. По умолчанию TCP-порт опрашивается три раза, а UDP-порт – один раз, после чего программа сообщает, что порт фильтруется.

Средство Portqry.exe может опрашивать один порт, заданный список портов или последовательный диапазон портов.

В этой статье я опишу порядок установки Reporting Services из комплекта SQL Server 2005 Developer Edition на компьютер с установленной операционной системой Windows Vista Ultimate. За основу мной взята пошаговая инструкция, опубликованная в статье базы знаний Майкрософт: http://support.microsoft.com/kb/934164/ru-ru
Во время установки SQL Server 2005 Developer Edition среди предлагаемых к установке компонент Reporting Services отсутствовал. Поэтому вначале была выполнена установка SQL Server 2005, потом установлен Server 2005 Service Pack 2 (SP2) и последний из доступных кумулятивных пакетов заплат.
Перед установкой Reporting Services нужно установить недостающие компоненты для ASP.NET и IIS. Для этого нужно в оснастке “Панель Управления” (Control Panel) перейти в раздел “Программы” (Programs), а затем дважды щелкнуть ссылку “Включение и отключение компонентов Windows” (Programs and Features). Далее, необходимо включить совместимость IIS 6 WMI. Сделать это можно пометив чекбокс, раскрыв следующую цепочку узлов дерева функций: “Службы IIS” / “Средства управления веб-узлом” / “Совместимость управления IIS 6″ / “Совместимость WMI IIS 6″ и “Совместимости конфигурации метабазы IIS и IIS 6″.

После этого нужно добавить функционал службам интернета (World Wide Web Services), для чего пометьте чекбоксы следующих компонент разработки приложений: ASP.NET, ISAPI Extensions и ISAPI Filters.

Следующим шагом идёт настройка основных функций HTTP (Common HTTP Features), где нужно чтобы были помечены все чекбоксы: Default Document, Directory Browsing, HTTP Errors, HTTP Redirection и Static Content.

Следующим шагом является настройка функций безопасности, где нужно пометить чекбокс для Windows Authentication.

Когда Вы пометили все чекбоксы, которые необходимы для нормальной работы веб-служб отчётности, нажмите кнопку OK окна включения или отключения компонентов Windows. Будет выполнена настройка компонентов, что длится несколько минут.
Обратите внимание, что если какая-нибудь из перечисленных выше функций не будет включена, компоненты Reporting Services не будут доступны для выбора при запуске программы установки SQL Server. Наличие диска с дистрибутивом не требуется.
После установки компонент, которые необходимы для веб-публикации отчётов, нужно запустить саму службу веб-публикации (World Wide Web Publishing Service) и настроить её автоматический запуск. Это нужно для того, чтобы можно было выбрать установку конфигурации Reporting Services по умолчанию.
После того, как публикация в веб на вашем компьютере с Windows Vista заработает и служба SQL Server тоже будет успешно запущена, можно приступить к установке недостающих компонент Reporting Services для SQL Server 2005 или SQL Server 2005 Express Edition with Advanced Services SP2.
В ходе установки нужно убедиться, что компонент Reporting Services выбран.

Вы можете выбрать любую из предлагаемых в окне (Report Server Installation Options) опций установки, заданную по умолчанию конфигурацию или отказаться от настройки службы отчётности на этапе установки компонент.
После успешной установки компонент Report Server нужно их сразу обновить до последнего SP и, если таковые имеются, установить последний после SP кумулятивный пакет заплат. В нашем случае, это был SP2 и кумулятивный пакет из статьи KB936252.

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

C:\ >IISRESET Попытка остановки… Службы Интернета успешно остановлены Попытка запуска… Службы Интернета успешно перезапущены

Следующим шагом является настройка Report Server, которая документирована в BOL и выполняется средствами оснастки: “Настройка служб Reporting Services” (RSConfigTool.exe). Важно, чтобы Reporting Services работал как унаследованное с точки зрения IIS 7.0 приложение, т.е. прикладной пул для виртуального каталога Report Server должен быть классический: Classic .NET AppPool.

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

Дополнительную информацию можно найти в статьях: