М Е Т О Д И Ч Н І В К А З І В К И. Інші з Бази даних. Робота № 516159

Перехід до торгівельного партнера Binance

М Е Т О Д И Ч Н І В К А З І В К И

Інформація про навчальний заклад

ВУЗ:

Національний університет Львівська політехніка

Інститут:

Не вказано

Факультет:

Не вказано

Кафедра:

Програмного забезпечення

Інформація про роботу

Рік:

2009

Тип роботи:

Інші

Предмет:

Бази даних

Група:

Завантажити

Частина тексту файла (без зображень, графіків і формул):

МІНІСТЕРСТВО ОСВІТИ І НАУКИ УКРАЇНИ НАЦІОНАЛЬНИЙ УНІВЕРСИТЕТ “ЛЬВІВСЬКА ПОЛІТЕХНІКА” Підвищення продуктивності запитів в SQL Server 2008 М Е Т О Д И Ч Н І В К А З І В К И до виконання лабораторних робіт з дисципліни “Бази даних” для студентів базового напрямку "Програмна інженерія" Затверджено на засіданні кафедри програмного забезпечення. Протокол № ___ від ______ 2009 р. Львів-2009 Підвищення продуктивності запитів в SQL Server 2008: Методичні вказівки до виконання лабораторних робіт з дисципліни “Бази даних” для студентів базового напрямку “Програмна інженерія” / Укл.: Н.Я. Павич, Р. Б. Тушницький. – Львів: Видавництво Національного університету “Львівська політехніка”, 2009. – ____ с. Укладачі Павич Н.Я., канд. техн. наук, доц., Тушницький Р.Б., асист. Відповідальний за випуск Левус Є.В., канд. техн. наук, доц. Рецензенти Мельник Р.А., д-р. техн. наук, проф., Іванців Р.Д., канд. техн. наук, доц. Вступ Методичні вказівки містять теоретичні відомості стосовно основ Transact-SQL, його основних елементів, базових функцій, процедур та представлень для управління базами даних та запитами до Microsoft SQL Server, контрольні запитання та завдання до лабораторних занять. Метою виконання лабораторних завдань студентами є освоєння нової мови запитів Transact-SQL, в той же час поглиблення знань з фундаментальних понять баз даних, керування ними та їх адміністрування. Методичні вказівки забезпечують вивчення вступного курсу до управління серверами баз даних і рекомендуються для початківців у цій галузі. Назви службових слів для наглядного сприйняття текстів виділено жирним шрифтом. Лабораторна робота № 1 Тема. Ознайомлення із мовою Transact-SQL. Мета. Навчитися використовувати T-SQL, DML та транзакції для зміни даних та управління запитами в SQL Server 2008. Теоретичні відомості. Microsoft SQL Server − комерційна система керування базами даних, що розповсюджується корпорацією Microsoft. Мова, що використовується для запитів − Transact-SQL (T-SQL), створена спільно Microsoft та Sybase. Transact-SQL є реалізацією стандарту ANSI/ISO щодо структурованої мови запитів (SQL) із розширеннями. Використовується як для невеликих і середніх за розміром баз даних, так і для великих баз даних масштабу підприємства. Багато років вдало конкурує з іншими системами керування базами даних. 1. Забезпечення цілісності даних. Планування та створення таблиць вимагає вказівки допустимих значень для стовпців і визначення способів примусового забезпечення цілісності даних в них. SQL Server надає наступні механізми для примусового забезпечення цілісності даних у стовпці: Обмеження PRIMARY KEY Обмеження FOREIGN KEY Обмеження UNIQUE Обмеження CHECK Визначення DEFAULT Дозвіл значень NULL 2. Індекси. Подібно до змісту у книзі, індекс у базі даних дозволяє швидко шукати певні відомості в таблиці або індексованого представлення. Індекс містить ключі, побудовані з одного або декількох стовпців таблиці або представлення, і покажчики, які зіставляються з місцем зберігання заданих даних. Створення добре спроектованих індексів для підтримки запитів забезпечує значне поліпшення продуктивності запитів до бази даних і застосувань. Індекси скорочують обсяг даних, які необхідно прочитати, щоб повернути результуючий набір. Індекси також забезпечують унікальність рядків таблиці, гарантуючи цілісність даних таблиці. Індекс є структурою на диску, яка пов’язана з таблицею чи представленням і прискорює отримання рядків з таблиці або представлення. Індекс містить ключі, побудовані з одного або декількох стовпців у таблиці або представлення. Ці ключі зберігаються у вигляді структури збалансованого дерева, яка підтримує швидкий пошук рядків по їх ключовим значенням в SQL Server. Таблиця або представлення може містити такі типи індексів. Кластерний Кластерні індекси сортують і зберігають рядки даних у таблицях або представленнях на основі їх ключових значень. Цими значеннями є стовпці, включені у визначення індексу. Існує тільки один кластерний індекс для кожної таблиці, тому що рядки даних можуть бути відсортовані тільки в єдиному порядку. Рядки даних в таблиці зберігаються в порядку сортування тільки в тому випадку, якщо таблиця містить кластерний індекс. Якщо у таблиці є кластерний індекс, то таблиця називається кластеризованою. Якщо у таблиці немає кластерного індексу, то рядки даних зберігаються у невпорядкованій структурі, яка називається купою. Некластерний Некластерні індекси мають структуру, окрему від рядків даних. У некластерному індексі містяться значення ключа некластерного індексу, і кожен запис значення ключа містить вказівник на рядок даних, який містить значення ключа. Вказівник з рядка індексу в некластерному індексі, який вказує на рядок даних, називається вказівником рядка. Структура вказівника рядка залежить від того, чи зберігаються сторінки даних в купі або в кластеризованій таблиці. Для купи вказівник рядка є вказівником на рядок. Для кластеризованої таблиці вказівник рядка даних є ключем кластерного індексу. Можна додати неключові стовпці на кінцевий рівень некластерного індексу і обійти існуюче обмеження на ключі індексів (900 байт і 16 ключових стовпців) і виконувати повністю індексовані запити. 3. Оптимізація запитів. Кожна SQL-операція має так званий "коефіцієнт корисності" - рівень ефективності даної операції. Чим більший бал, тим "корисніша" операція, а значить, SQL-запит виконується швидше. Практично будь-яка умова складається з двох операндів і знака операції між ними. Щоб краще зрозуміти таблиці, розглянемо приклад розрахунку рейтингу запиту. ... WHERE smallint_column = 12345 5 балів за поле зліва (smallint_column), 2 бали за точний цифровий операнд (smallint_column), 10 балів за операцію порівняння (=) і 10 балів за значення справа (12345). Разом отримали 27 балів. Тепер розглянемо більш складний приклад: ... WHERE char_column> = varchar_column | | ‘x’ 5 балів за поле зліва (char_column), 0 балів за символьний операнд (char_column), 5 балів за операцію більше або дорівнює (>=), 3 бали за логічне вираження (varchar_column | | ‘x’), 0 балів за символьний операнд ( varchar_column). У результаті отримаємо 13 балів. Природно, такі розрахунки не обов’язково проводити для кожного запиту. Але коли постане питання про швидкість умов того чи іншого запиту, його можна буде з’ясувати за допомогою цих двох таблиць. На швидкість запиту також впливає кількість обираних даних і додаткові директиви, які розглянемо нижче. Також май на увазі, що розрахунок "коефіцієнта корисності" не є таким собі універсальним способом оптимізації. Все залежить від конкретної ситуації. Основний закон при оптимізації запитів - закон перетворення. Неважливо, як ми представляємо умову, головне щоб результат залишився тим самим. І знову розглянемо приклад. Є запит: ... WHERE column1 < column2 AND column2 = column3 AND column1 = 5. Використовуючи перестановку, отримуєш запит: ... WHERE 5 <column2 AND column2 = column3 AND column1 = 5. Результат запиту буде один і той же, а продуктивність різною, тому що використання точного значення (5) впливає на продуктивність. Вислів x = 1+1-1-1 під час компіляції у С/С++ стане x = 0. Дивно, що лише деякі БД здатні виконувати такі операції. При виконанні запиту БД буде виконувати операції додавання і віднімання та витрачати дорогоцінний час. Тому завжди краще відразу розраховувати такі вирази там, де це можливо. Не ... WHERE a - 3 = 5, а ... WHERE a = 8. Ще одна можливість оптимізувати запит - дотримуватися загальної ідеї складання умов в SQL. Іншими словами, умова повинна мати вигляд: <колонка> <операція> <вираз>. Наприклад, запит "... WHERE column1 - 3 =-column2" краще привести до вигляду: ... WHERE column1 =-column2 + 3. І ці прийоми оптимізації працюють практично завжди і скрізь. 3.1. Оптимізація умов. Більшість запитів використовують директиву SQL WHERE, тому, оптимізуючи умови, можна добитися значної продуктивності запитів. При цьому чомусь лише невелика частина додатків для БД використовують оптимізацію умов. AND Очевидно, що в серії з декількох операторів AND умови повинні розташовуватися в порядку зростання ймовірності істинності цієї умови. Це робиться для того, щоб при перевірці умов БД не перевіряла іншу частину умови. Ці рекомендації не відноситься до БД Oracle, де умови починають перевірятися з кінця. Відповідно, їх порядок повинен бути зворотнім - за зменшенням ймовірності істинності. OR Ситуація з даним оператором прямо протилежна ситуації з AND. Умови повинні розташовуватися в порядку зменшення ймовірності істинності. Фірма Microsoft наполегливо рекомендує використовувати даний метод при побудові запитів, хоча багато хто навіть не знають про це або, принаймні, не звертають на нього увагу. Але знову ж таки це не відноситься до БД Oracle, де умови повинні розташовуватися за зростанням ймовірності істинності. Ще однією умовою для оптимізації можна вважати той факт, що якщо однакові колонки розташовуються поруч, запит виконується швидше. Наприклад, запит ".. WHERE column1 = 1 OR column2 = 3 OR column1 = 2" буде виконуватися повільніше, ніж запит "WHERE column1 = 1 OR column1 = 2 OR column2 = 3". Навіть якщо ймовірність істинності умови column2 = 3 вище, ніж column1 = 2. AND + OR Розподільний закон говорить, що A AND (B OR C) - те ж саме, що й (A AND B) OR (A AND C). Встановлено, що запит виду "... WHERE column1 = 1 AND (column2 = ‘A’ OR column2 = ‘B’)" виконується трохи швидше, ніж "... WHERE (column1 = 1 AND column2 = ‘A ‘) OR (column1 = 1 AND column2 =‘ B ‘)". Деякі БД самі вміють оптимізувати запити такого типу, але краще перестрахуватися. NOT Цю операцію завжди слід приводити до більш "читабельність" виду (в розумних межах, звичайно). Так, запит "... WHERE NOT (column1> 5)" перетворюється на "... WHERE column1 <= 5". Більш складні умови можна перетворити використовуючи правила де Моргана. Згідно з цим правилом NOT (A AND B) = (NOT A) OR (NOT B) і NOT (A OR B) = (NOT A) AND (NOT B). Наприклад, умова "... WHERE NOT (column1> 5 OR column2 = 7)" перетворюється в більш просту форму: ... WHERE column1 <= 5 AND column2 <> 7. IN Багато хто наївно вважають, що запит "... WHERE column1 = 5 OR column1 = 6" рівносильний запитом "... WHERE column1 IN (5, 6)". Насправді це не так. Операція IN працює набагато швидше, ніж серія OR. Тому завжди слід заміняти OR на IN, де це можливо, не дивлячись на те, що деякі БД самі виробляють цю оптимізацію. Там, де використовується серія послідовних чисел, IN слід поміняти на BETWEEN. Наприклад, "... WHERE column1 IN (1, 3, 4, 5)" оптимізується до виду: ... WHERE column1 BETWEEN 1 AND 5 AND column1 <> 2. І цей запит дійсно швидше. LIKE Цю операцію слід використовувати лише за крайньої необхідності, тому що краще і швидше використовувати пошук, заснований на full-text індексах. CASE Сама ця функція може використовуватися для підвищення швидкості роботи запиту, коли в ньому є більше одного виклику повільної функції в умові. Наприклад, щоб уникнути повторного виклику slow_function () в запиті "... WHERE slow_function (column1) = 3 OR slow_function (column1) = 5", потрібно використовувати CASE: ... WHERE 1 = CASE slow_function (column1) WHEN 3 THEN 1 WHEN 5 THEN 1 END Сортування ORDER BY використовується для сортування, яке, як відомо, займає час. Чим більший обсяг даних, тим більше часу займе сортування, тому треба обов’язково його оптимізувати. На швидкість сортування в запитах впливає три фактори: 1. кількість вибраних записів; 2. кількість колонок після оператора ORDER BY; 3. довжина і тип колонок, зазначених після оператора ORDER BY. Найбільш ресурсномістким сортуванням є сортування рядків. Незважаючи на те, що текстові поля мають фіксовану довжину, довжина вмісту цих полів може бути різною (в межах розміру поля). Тому не дивно, що сортування колонки VARCHAR (100) буде повільніше, ніж сортування колонки VARCHAR (10) (навіть якщо дані будуть однакові). А відбувається це через те, що під час сортування сама база даних виділяє пам’ять для своїх операцій відповідно до максимального розміру поля незалежно від вмісту. Тому при оголошенні полів завжди слід використовувати розмір, який потрібен, і не виділяти зайві байти про запас. На комп’ютерах з ОС Windows поля типу INTEGER займають 32 біта, а поля типу SMALLINT - 16 біт. Логічно припустити, що сортування полів типу SMALLINT повинне відбуватися швидше. Насправді сортування INTEGER відбувається швидше, ніж SMALLINT. Також сортування INTEGER відбувається швидше, ніж CHAR. Оптимізації сортування проводиться для конкретної ситуації, тому що універсальних рекомендацій ніхто дати не може. Групування Операція GROUP BY використовується для визначення підмножини в результаті запиту, а також для застосування до цього підмножині агрегатних функцій. Розглянемо кілька найбільш ефективних методів оптимізації операції групування. Перше, що варто пам’ятати, - потрібно використовувати якомога менше колонок для угруповання. Також слід уникати зайвих умов. Наприклад, у запиті SELECT secondary_key_column, primary_key_column, COUNT (*) FROM Table1 GROUP BY secondary_key_column, primary_key_column колонка secondary_key_column абсолютно не потрібна. Причина проста: secondary_key_column є унікальним полем, воно може не мати значень NULL, а отже, деякі дані можуть просто загубитися. Але якщо прибрати secondary_key_column з секції GROUP BY, деякі БД можуть видати помилку про те, що неможливо вказувати це поле, якщо воно не оголошено в секції GROUP BY. Для вирішення цієї проблеми можна написати запит в такому вигляді: SELECT MIN (secondary_key_column), primary_key_column, COUNT (*) FROM Table1 GROUP BY primary_key_column. Цей запит швидше і "правильніше" з точки зору конструювання запитів. У більшості БД операції WHERE і HAVING не рівноцінні і виконуються не однаково. Це означає, що наступні два запити логічно однакові, але виконуються з різною швидкістю: SELECT column1 FROM Table1 WHERE column2 = 5 GROUP BY column1 HAVING column1> 6 SELECT column1 FROM Table1 WHERE column2 = 5 AND column1> 6 GROUP BY column1 Другий запит працює швидше, ніж перший. HAVING слід використовувати в тих рідкісних випадках, коли умова (у прикладі column1> 6) складно висловити без шкоди продуктивності. Якщо потрібно групування, але без використання агрегатних функцій (COUNT (), MIN (), MAX і т.д.), розумно використовувати DISTINCT. Так, замість SELECT column1 FROM Table1 GROUP BY column1 краще використовувати SELECT DISTINCT column1 FROM Table1. При використанні MIN () або MAX () враховуємо, що ці функції краще працюють окремо. Це означає, що їх краще використовувати в роздільних запитах або у запитах з використанням UNION. При використанні функції SUM () більшої продуктивності можна добитися використовуючи SUM (x + y), а не SUM (x) + SUM (y). Для вирахування краще протилежне: SUM (x) - SUM (y) швидше, ніж SUM (x - y). З’єднання таблиць (JOINS) Ось де складно щось сказати про оптимізацію, так це при використанні JOIN. Справа в тому, що швидкість виконання таких операцій багато в чому залежить від організації самої таблиці: використання foreign-key, primary-key, кількість вкладених з’єднань і т.д. Іноді кращої продуктивності можна добитися використовуючи вкладені цикли безпосередньо в програмі. Іноді швидше працюють JOINs. Однозначної ради по тому, як використовувати різні способи з’єднання таблиць, не існує. Все залежить від конкретного випадку та архітектури БД. Підзапит (SUBQUERIES) Раніше далеко не всі БД могли похвалитися підтримкою підзапитів, а зараз практично будь-яка сучасна БД це вміє. Навіть MySQL, яка кілька років втілювала підзапит в життя, нарешті обросла їх підтримкою. Основна проблема при оптимізації підзапитів - не оптимізація безпосередньо самого коду запиту, а вибір правильного способу для реалізації запиту. Завдання, для яких використовуються підзапит, також можуть вирішуватися за допомогою вкладених циклів або JOIN’ов. Коли використовуєш JOIN, даєш змогу БД вибрати механізм, яким буде здійснюватися підключення таблиць. Якщо ж використовуєш підзапит, то явно вказуєш на використання вкладених циклів. Що вибрати? Нижче аргументи на користь того чи іншого способу. Переваги JOIN: * Якщо запит містить умову WHERE, вбудований оптимізатор БД буде оптимізувати запит в цілому, в той час як у випадку використання підзапитів запити будуть оптимізуватися окремо. * Деякі БД більш ефективно працюють з JOINs, ніж з підзапит (наприклад, Oracle). * Після JOIN’а інформація виявиться в загальному "списку", що не можна сказати про підзапит. Переваги SUBQUERIES: * Підзапит допускають більш вільні умови. * Підзапит можуть містити GROUP BY, HAVING, що набагато складніше реалізувати в JOIN’ах. * Підзапит можуть використовуватися при UPDATE, що неможливо при використанні JOIN’ов. * Останнім часом оптимізація підзапитів самими БД (їх вбудованим оптимізатором) помітно покращилася. Основна перевага JOIN’ов в тому, що не треба вказувати БД те, яким саме способом робити операцію. А основна перевага підзапитів в тому, що цикл підзапит може мати кілька ітерацій (повторень), що, у свою чергу, може істотно збільшити продуктивність. 4. Використання SQL Query Аnalyzer і SQL Profiler. Используя Query Analyzer, вы можете видеть план исполнения, выбранный для оператора T-SQL оптимизатором запросов SQL Server. Оптимизатор запросов – это внутренний модуль, который ищет наилучший план исполнения для каждого оператора T-SQL. Оптимизатор запросов анализирует каждый оператор T-SQL, просматривает ряд возможных планов исполнения и выполняет оценку "стоимости" каждого плана с точки зрения требуемых ресурсов и времени обработки. Выбирается план с наименьшей стоимостью. Стоимость каждого плана определяется на основе имеющейся статистики, которая собрана системой и может оказаться устаревшей. Поскольку вы можете знать больше о вашей базе данных и ваших данных, чем оптимизатор запросов, то, возможно, вам удастся создать план, который окажется лучше, чем у оптимизатора запросов. Используя информацию, которую выдает Query Analyzer, вы можете определить, будет ли эффективным план оптимизатора запросов для определенного оператора, и если нет, то вы можете попытаться оптимизировать данный оператор, модифицируя его или используя подсказку SQL. В этой лекции вы узнаете, как оптимизировать операторы T-SQL, что будет дополнением к изучению использования Query Analyzer. Используя Profiler, вы можете анализировать операции внутри вашей системы SQL Server, чтобы определять, какие операторы SQL и хранимые процедуры используют излишние системные ресурсы. Обладая этой информацией, вы можете сосредоточить свои усилия по настройке в первую очередь на этих операторах и хранимых процедурах. Кроме описания того, как использовать Profiler, в этой лекции также показано, как наиболее эффективно использовать информацию, получаемую с помощью Profiler. Использование SQL Query Аnalyzer Утилита Query Analyzer поставляется вместе с Microsoft SQL Server 2000 взамен Interactive SQL for Windows (ISQL/W) как графический пользовательский интерфейс (GUI) SQL, но вы, возможно, обратили внимание, что утилита Query Analyzer представлена как isqlw.exe в диспетчере задач. Вы можете использовать Query Analyzer для обработки операторов T-SQL и просмотра результатов этих операторов. Query Analyzer можно также использовать как средство отладки для оценки плана исполнения, который генерируется оптимизатором запросов для вашего оператора T-SQL. Выполнение операторов T-SQL Выполнение операторов T-SQL и вывод результатов этих операторов являются основными возможностями Query Analyzer. Чтобы использовать Query Analyzer для выполнения оператора T-SQL, выполните следующие шаги. Щелкните на кнопке Start (Пуск), укажите Programs, укажите Microsoft SQL Server и затем выберите Query Analyzer. Появится диалоговое окно Connect to SQL Server (Подсоединение к SQL Server) (рис. 35.1). Это диалоговое окно используется для соединения с системой SQL Server. Рис. 35.1. Диалоговое окно Connect to SQL Server (Подсоединение к SQL Server) Введите имя сервера в комбинированном поле с раскрывающимся списком. Это может быть имя локального сервера или удаленного сервера. На рис. 35.1 в этом поле введена точка (.). Ввод точки указывает, что вы хотите подсоединиться к локальному серверу. Установка флажка непосредственно под полем SQL Server указывает, что вы хотите запустить SQL Server, если он еще не запущен. В секции Connect using (Подсоединяться с использованием) выберите метод аутентификации, который хотите использовать для подсоединения к SQL Server. Если выбрать вариант Windows NT authentication (Аутентификация Windows), то вам не нужно указывать имя пользователя или пароль, поскольку для аутентификации доступа к SQL Server будет использоваться учетная запись Microsoft Windows 2000. Если выбрать вариант SQL Server authentication (Аутентификация в SQL Server), то для доступа к SQL Server нужно указать имя пользователя SQL Server (Login name) и пароль (Password). Щелкните на кнопке OK для подсоединения к указанному серверу SQL и для запуска Query Analyzer. При первоначальном появлении окна Query Analyzer видны только панель Query и панели навигации, но этот вид изменяется, как только вы начинаете запускать операторы T-SQL. Разверните панель Query для заполнения всей правой стороны окна Query Analyzer (рис. 35.2). В раскрывающемся списке панели инструментов выберите базу данных, в которой хотите запускать запросы. На рис. 35.2 выбрана база данных master. Для нашего примера щелкните на направленной вниз стрелке и выберите Northwind. После выбора базы данных введите в правой панели оператор T-SQL – в данном случае – SELECT * FROM Customers. Теперь у вас появляется несколько возможностей. Вы можете проверить синтаксис данного оператора T-SQL, щелкнув на кнопке Parse Query (Синтаксическая проверка запроса) в панели инструментов (синяя пометка ["галочка"]), или можете запустить оператор, щелкнув на кнопке Execute Query (Выполнить запрос) (зеленый треугольник, указывающий вправо). Вы можете остановить выполнение запроса, щелкнув на кнопке Cancel Executing Query (Отменить выполнение запроса) (квадрат). На рис. 35.3 показан выполненный запрос по таблице Customers базы данных Northwind. После запуска оператора T-SQL утилита Query Analyzer создает панель с возможностью вертикальной и горизонтальной прокрутки для просмотра результатов, как это показано на рис. 35.3. Query Analyzer можно также использовать как средство, помогающее вам в настройке ваших операторов T-SQL, как мы увидим в разделе "Оптимизация операторов T-SQL" ниже в этой лекции. Рис. 35.2. Окно Query Analyzer Рис. 35.3. Выполненный запрос в панели Query Analyzer Просмотр планов исполнения и модифицирование операторов T-SQL Как уже говорилось, вы можете также использовать Query Analyzer для просмотра плана исполнения, выбранного оптимизатором запросов для оператора T-SQL. Это средство позволяет определить, насколько эффективен ваш оператор T-SQL и какие пути выбраны для исполнения и доступа к данным. Вы можете затем внести изменения в этот оператор T-SQL и схему базы данных и затем определить, как это влияет на производительность. Чтобы использовать Query Analyzer для просмотра оценочного плана исполнения, выполните следующие шаги. В окне Query Analyzer введите оператор T-SQL, чтобы выполнить его оценку с помощью Query Analyzer, как это описано выше, и затем щелкните на кнопке Display Estimated Execution Plan (Отобразить оценочный план исполнения) (кнопка справа от раскрывающегося списка выбора базы данных) или нажмите клавиши Ctrl+L. Появится панель Estimated Execution Plan (рис. 35.4). В этой панели запрос представлен в графическом виде; показана также "стоимость" каждой операции. Здесь также показан метод доступа к данным. В панели (рис. 35.4), появляется имя индекса Customers.PK_Customers, а это означает, что для доступа к данным используется кластеризованный индекс Customers.PK_Customers. Рис. 35.4. Панель Estimated Execution Plan Панель Estimated Execution Plan предоставляет доступ к дополнительным данным об операциях, показанных в этой панели. Чтобы увидеть эти дополнительные данные для любой операции, задержите указатель мыши на значке этой операции. Появится всплывающее окно, содержащее дополнительные данные (рис. 35.5). Рис. 35.5. Просмотр дополнительных данных об операции Это всплывающее окно содержит следующую информацию: Physical operation/Logical operation (Физическая операция/Логическая операция). Операции, выполняемые данным запросом, такие как индексное сканирование, связывание (join), агрегирование и т.д. Если физический оператор представлен красным цветом, это означает, что оптимизатор запросов выдал предупреждение и вы должны внести исправления в ваш оператор T-SQL. Estimated row count (Оценка количества строк). Количество строк, которое (по оценке Query Optimizer) будет выбрано данной операцией. Estimated Row Size (Оценка размера строк). Оценка размера считываемых строк в байтах. Estimated I/O cost/Estimated CPU cost (Оценка стоимости ввода-вывода/Оценка стоимости ЦП). Оценка ресурсов ввода-вывода и времени процессора, которые будут использоваться этой операцией. Меньшее значение соответствует большей эффективности оператора T-SQL Estimated number of executes (Оценка количества выполнений). Приблизительное количество выполнений данной операции во время выполнения данного оператора T-SQL. Estimated cost (Оценка стоимости). Стоимость операции по оценке оптимизатора запросов. Эта стоимость показана в процентах от полной стоимости данного оператора T-SQL. Estimated subtree Cost (Оценка стоимости поддеревьев). Оценка стоимости выполнения предыдущих частей и данной части оператора T-SQL. Если имеется несколько поддеревьев, то это средство позволяет просматривать стоимость выполнения каждого поддерева. Argument (Параметры). Параметры, используемые данным оператором T-SQL. Примечание. План исполнения описывает, как оптимизатор запросов будет исполнять оператор T-SQL. Этот план показывает типы операций, которые будут использоваться, и порядок, в котором они будут выполняться. Метод доступа к данным описывает, как будет осуществляться доступ к объектам базы данных (таблицам, индексам и т.д.). План и метод доступа связаны друг с другом: метод доступа к данным иногда рассматривается как часть плана исполнения, но его можно также рассматривать отдельно. Далее мы рассмотрим некоторые более сложные примеры использования Query Analyzer. Эти примеры также показывают влияние неэффективных операторов T-SQL на снижение производительности за счет увеличения времени отклика и использования системных ресурсов, которые могли бы использоваться другими процессами. Сначала мы рассмотрим пример использования Query Analyzer для просмотра и модифицирования плана исполнения оператора T-SQL. Как уже говорилось, за счет модифицирования ваших операторов T-SQL вам, возможно, удастся получить для них более высокую производительность. Во многих случаях вы можете создать более эффективный и при этом функционально эквивалентный оператор T-SQL. Затем мы будем рассматривать все более сложные оценочные планы исполнения для нескольких типов операторов T-SQL. В примерах остальной части этого раздела используется таблица Orders базы данных Northwind. Посмотрим, как организована эта таблица. Когда мы будем рассматривать примеры, эта информация поможет нам определить, насколько приемлемым является план исполнения, выбранный оптимизатором запросов. Таблица Orders имеет кластеризованный индекс с именем PK_Orders по колонке OrderID и восемь других индексов, что показано в диалоговом окне Manage Indexes (Управление индексами) (рис. 35.6). Рис. 35.6. Диалоговое окно Manage Indexes (Управление индексами) Для доступа к этому окну с помощью Enterprise Manager раскройте группу серверов, раскройте сервер, раскройте папку Databases, раскройте базу данных Northwind и щелкните на папке Tables. Щелкните правой кнопкой мыши на таблице Orders в правой панели, укажите в контекстном меню All Tasks (Все задачи) и затем выберите Manage Indexes. Или просто выберите пункт Manage Indexes из меню Tools окна Query Analyzer и затем выберите из раскрывающегося меню таблицу Orders. Просмотр плана для оператора SELECT и модифицирование этого оператора В этом разделе мы рассмотрим запрос информации по заказам (orders), помещенным сотрудником (employee), идентификационный номер которого (employee ID) равен 4. Вот этот запрос: SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders WHERE EmployeeID = 4 В данной организации каждый сотрудник обрабатывает небольшую часть от всех заказов, поэтому вы, вероятно, предполагаете, что SQL Server будет использовать при обработке этого запроса индекс EmployeeID. Вместо этого Query Analyzer информирует вас, что SQL Server будет использовать доступ с помощью кластеризованного индекса PK_Orders (рис. 35.7). Рис. 35.7. Панель Estimated Execution Plan, где показано, что будет использоваться кластеризованный индекс PK_Orders Чтобы оптимизатор запросов использовал вместо этого индекс EmployeeID, вы можете использовать подсказку в операторе SELECT, как показано в следующем операторе. (См. раздел "Использование подсказок" далее.) SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders WITH (INDEX(EmployeeID)) WHERE EmployeeID = 5 Примечание. В Microsoft SQL Server 7 предпочтительной подсказкой по индексам было INDEX=имя_индекса. С появлением SQL Server 2000 предпочтительной подсказкой по индексам стало INDEX(имя_индекса). Включая в команду эту дополнительную информацию, вы указываете оптимизатору запросов, что нужно использовать нужный вам план исполнения, а не тот, что был выбран для вас оптимизатором. На рис. 35.8 показана панель Estimated Execution Plan с измененным планом. Как видно из представленного в панели метода доступа, индекс EmployeeID будет использоваться в качестве входного параметра для процесса поиска по закладкам (bookmark lookup), который выполнит затем выборку данных из базы данных. (Процесс поиска по закладкам ищет внутренний идентификатор для строки данных.) Рис. 35.8. Панель Estimated Execution Plan с измененным планом Оптимизатор запросов является эффективным инструментом, который постоянно обновляет статистику, чтобы выбрать наилучший план исполнения. Но поскольку вы хорошо представляете себе вашу организацию и ваши данные, то в некоторых случаях вы осведомлены лучше, чем оптимизатор запросов, чтобы выбрать лучший план исполнения. Внимание. Используя подсказку, переопределяющую план оптимизатора запросов, вы делаете это на свой страх и риск. Хотя здесь нет или почти нет опасности потери или порчи данных, вы можете ухудшить производительность системы. Просмотр плана для операции связывания Выполнение какой-либо операции связывания (join) включает в себя намного больше процессов, чем будет показано в панели Estimated Execution Plan ниже в этой лекции. Операция связывания выполняет доступ к нескольким таблицам, сопровождаемый связыванием (объединением) считываемых данных. (Операции связывания рассматриваются в лекции 14.) Вот пример оператора с операцией связывания: SELECT OrderID, CustomerID, Employees.EmployeeID, FirstName, LastName, OrderDate FROM Orders JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID Сюда включен оператор SQL-92 JOIN. Использование этого оператора является рекомендованным способом связывания в SQL Server 2000. В следующем операторе используется более традиционный синтаксис связывания, который по-прежнему поддерживается в SQL Server: SELECT OrderID, CustomerID, Employees.EmployeeID, FirstName, LastName, OrderDate FROM Orders, Employees WHERE Orders.EmployeeID = Employees.EmployeeID Любой из этих операторов T-SQL связывает таблицы Orders и Employees по колонке EmployeeID. Результирующий оценочный план исполнения показан на рис. 35.9. Рис. 35.9. Операция связывания, показанная в панели Estimated Execution Plan В этой панели видно, какое из двух поддеревьев имеет более высокую стоимость. Вы также видите тип планируемой операции связывания. SQL Server поддерживает несколько операций связывания, включая хеш-связывание, связывание вложенных цепочек и связывание слиянием. При использовании комплексной операции связывания план исполнения может оказаться очень сложным. (Query Analyzer регулирует размер панели Estimated Execution Plan, чтобы вместить нужно количество ветвей.) Поскольку нашей целью является сокращение времени ЦП и количества операций ввода-вывода, вам нужно попытаться определить, можно ли выбрать лучший план исполнения. В некоторых случаях вы можете применить подсказку, указывающую использование определенного индекса, сокращая тем самым время использования ЦП и интенсивность операций ввода-вывода. Вы можете также использовать подсказки в операциях связывания таблиц. Для запроса (рис. 35.9), возможно, выбран наилучший план исполнения, поскольку в предложении FROM связывание является единственной операцией. Просмотр плана для операции агрегирования Показанный здесь оператор T-SQL выполняет не только операцию связывания, но также операцию агрегирования: SET QUOTED_ IDENTIFIER ON GO SELECT CustomerID, SUM("Order Details".UnitPrice) FROM Orders JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID GROUP BY CustomerID Панель Estimated Execution Plan для этой комплексной операции показана на рис. 35.10. Рис. 35.10. Операция агрегирования, представленная в панели Estimated Execution Plan Примечание. Поскольку имя таблицы Order Details содержит ключевое слово и пробел, здесь должен использоваться параметр SET QUOTED_IDENTIFIER ON. Это позволяет указывать имя таблицы Order Details в кавычках. Для получения более подробной информации по этому параметру найдите "SET QUOTED_IDENTIFIER" Books Online. Просмотр плана для хранимой процедуры Для просмотра плана исполнения хранимой процедуры нужно просто вызвать эту хранимую процедуру из окна Query Analyzer. В панели Query Analyzer будет выведен оценочный план вызванной вами хранимой процедуры. На рис. 35.11 показан план для процедуры sp_who. (Отметим, что план исполнения этой широко используемой хранимой процедуры очень сложен.) Вы можете просматривать план исполнения хранимой процедуры, не зная, какие операторы T-SQL образуют эту процедуру. Рис. 35.11. План исполнения хранимой процедуры, показанный в панели Estimated Execution Plan Использование браузера объектов Браузер объектов (Object Browser) – это расширение в Query Analyzer, включенное в SQL Server 2000. Запустив Query Analyzer, вы увидите браузер объектов в левой части этого окна. Браузер объектов разбит на две секции: секция объектов баз данных и секция общих объектов (Common objects). В секции объектов баз данных вы можете выполнять перемещение по объектам, таким как таблицы и представления. В секции общих объектов обеспечивается удобный доступ к системным объектам и функциям. Вам нужно выполнить просмотр в браузере объектов, чтобы выяснить, какую информацию он содержит, и затем определить, что вы можете использовать. Объекты базы данных Верхняя секция браузера объектов содержит объекты баз данных. Вы сразу видите базы данных по умолчанию и любые созданные вами базы данных под обозначением системы SQL Server, которой они принадлежат. Чтобы увидеть информацию, которая содержится в браузере объектов, нужно просто раскрыть объекты. Раскроем базу данных Northwind и затем раскроем папку User Tables (Пользовательские таблицы). Вы увидите таблицы базы данных Northwind (рис. 35.12). Рис. 35.12. Просмотр таблиц в браузере объектов Затем вы можете раскрывать определенные пользователем таблицы и затем раскрывать папки, содержащие информацию о колонках, индексах, ограничениях, зависимостях и триггерах. На рис. 35.13 раскрыта таблица Orders. Вы можете также раскрывать соответствующие папки для просмотра информации о системных таблицах, представлениях, хранимых процедурах, функциях и определенных пользователем типах данных. Доступ к информации базы данных внутри Query Analyzer – очень удобное средство, поскольку это позволяет создавать операторы SQL и хранимые процедуры без необходимости поиска информации об объектах вне Query Analyzer. Вы можете не только просматривать информацию в браузере объектов, но также редактировать объекты, перемещать объекты методом "drag and drop" и даже формировать сценарии создания и модифицирования объектов. Это еще более расширяет функциональные возможности Query Analyzer. Секция Common Objects Нижняя часть браузера объектов – это папка с именем Common Objects (Общие объекты) (рис. 35.14). Рис. 35.13. Раскрытие таблицы в браузере объектов Рис. 35.14. Раскрытие папки в секции Common objects (Общие объекты) браузера объектов Внутри этой папки находятся папки, содержащие информацию о таких объектах, как функции конфигурирования, функции курсоров, функции даты и времени, а также математические функции. Тем самым вы получаете простой доступ к функциям без необходимости поиска их синтаксиса. Если раскрыть какую-либо папку в этой секции, то вы увидите соответствующие функции этой папки (функции конфигурирования на рис. 35.14). Вы можете перетаскивать эти функции в панель запросов или, поместив курсор мыши поверх функции, вы можете видеть ее краткое описание. Это удобно для обработки эпизодических запросов. Кроме доступа к глобальным переменным, вы имеете доступ к вызовам других полезных функций, таких как математические и строковые функции. Продолжая раскрывать объекты, вы можете получать, например, информацию о параметрах этих функций. На рис. 35.15 показана папка Parameters (Параметры) раскрытой математической функции. Рис. 35.15. Просмотр параметров в браузере объектов Использование SQL Profiler В дополнение к использованию Query Analyzer для поиска неэффективных операторов T-SQL вы можете также использовать утилиту SQL Server Profiler. Profiler позволяет наблюдать за всеми операторами T-SQL, которые выполняются в системе, с графическим отображением информации об этих операторах. Profiler также предоставляет возможности сортировки и фильтрации, которые можно использовать для выявления операторов T-SQL, использующих основную часть ресурсов ЦП и ввода-вывода. Обладая этой информацией, вы можете определять, каким операторам T-SQL уделить основное внимание для их настройки. Операторы T-SQL, которые вызываются из приложения, можно просматривать в Profiler; при этом вам не требуется доступ исходному коду самого приложения. Утилита Profiler в SQL Server 2000 действует аналогично утилите Profiler в SQL Server 7, но она содержит некоторые улучшения. Одним из полезных дополнений является шаблон трассировки (trace template), который можно использовать для создания файлов трассировки. (Трассировку нужно по-прежнему создавать до того, как вы сможете использовать ее для мониторинга операций SQL Server.) В SQL Server трассировки должны были создаваться вручную. Для вызова утилиты Profiler и запуска трассировки выполните следующие шаги. Щелкните на кнопке Start, укажите пункт Programs, укажите Microsoft SQL Server и затем выберите Profiler. При первоначальном открытии окна Profiler оно будет пустым. Не будет открыто ни одной панели, и не будет выполняться никакого профилирования в SQL Server. Чтобы начать создание профилирование, вы должны выбрать для выполнения существующий шаблон трассировки или создать новый шаблон трассировки для выполнения. (Процесс запуска описан на шаге 4.) SQL Server 2000 Profiler предоставляет для выбора целый ряд шаблонов трассировки. Использование этих шаблонов трассировки может сэкономить вам много времен, поскольку вам не нужно создавать трассировку с самого начала. Чтобы увидеть список шаблонов трассировки, щелкните на меню File (Файл), укажите команду Open (Открыть) и выберите пункт Trace Templates (Шаблоны трассировки), чтобы появилось диалоговое окно Open (рис. 35.16). Рис. 35.16. Диалоговое окно Open со списком шаблонов трассировки Имеются следующие шаблоны трассировки, поставляемые вместе с SQL Server. SQLServerProfilerSP_Counts.tdf. Подсчитывает количество запущенных хранимых процедур. Результаты группируются по именам хранимых процедур и содержат...

Інші Бази даних

31.12.2011 01:12-

Коментарі

Ви не можете залишити коментар. Для цього, будь ласка, або зареєструйтесь.

Ділись своїми роботами та отримуй миттєві бонуси!

Маєш корисні навчальні матеріали, які припадають пилом на твоєму комп'ютері? Розрахункові, лабораторні, практичні чи контрольні роботи — завантажуй їх прямо зараз і одразу отримуй бали на свій рахунок! Заархівуй всі файли в один .zip (до 100 МБ) або завантажуй кожен файл окремо. Внесок у спільноту – це легкий спосіб допомогти іншим та отримати додаткові можливості на сайті. Твої старі роботи можуть приносити тобі нові нагороди!

поділитись