Tuning SQL query performance презентация

Test questions
1. What functions does the query optimizer perform?
2. What is the purpose of the indexes?
3. Compare the Estimated execution plan with Actual execution plan .
ru:
1. Какие функции выполняет оптимизатор запросов?
2. Каково назначение индексов?
3.Сравните предполагаемый
план выполнения с действитель-
ным планом выполнения.
en:

Contents
1. Query Processing
2. Database Indexes
3. Query Analysis Tools
4. Query tuning practice

1. Query Processing

1.1. End User Interaction with DBMS
End users interact with the DBMS through the use of queries to generate information,
using the following sequence:
1. The end-user application generates a query.
2. The query is sent to the DBMS.
3. The DBMS executes the query.
4. The DBMS sends the resulting data set to the end-user application.
The goal of database performance is to execute queries as fast as possible

Query Processing includes translations on high level Queries into low level expressions that can be used at physical level of file system, query optimization and actual execution of query to get the actual result.
1.2. Query Processing

Importance: The goal of query optimization is to reduce the system resources required to fulfill a query, and ultimately provide the user with the correct result set faster.
1. It provides the user with faster results, which makes the application seem faster to the user.
2. It allows the system to service more queries in the same amount of time, because each request takes less time than unoptimized queries.
3. Query optimization ultimately reduces the amount of wear on the hardware (e.g. disk drives), and allows the server to run more efficiently (e.g. lower power consumption, less memory usage).
1.3. Query Optimization

A single query can be executed through different algorithms or re-written in different forms and structures. Hence, the question of query optimization comes into the picture – Which of these forms or pathways is the most optimal? The query optimizer attempts to determine the most efficient way to execute a given query by considering the possible query plans.
The process of searching and evaluating various options (that is, different candidate execution plans) for fulfilling the query occurs at the optimization phase using the Query Optimizer.
It selects the best plan for the next phase. The actual execution plan is a single tree with physical operators.
1.4. Query Optimizer

Query Optimizer is often a cost-based optimizer. It assigns a number called cost to each possible plan. A higher cost means a more complex plan, and a more complex plan means a slower query.
Query Optimizer calculates the cost of an operation by determining the algorithm used by a physical operator and by estimating the number of rows that have to be processed. The estimation of the number of rows is also called cardinality estimation. The cost expresses usage of physical resources such as the amount of disk I/O, CPU time, and memory needed for execution.
For calculating the cost, the Query Optimizer needs some information for the estimation of the number of rows processed by each physical operator. The Query Optimizer gets this information from optimizer statistics. DBMS maintains statistics about the total number of rows and distribution of the number of rows over key values of an index for each index.
After the Query Optimizer gets the cost for all operators in a plan, it can calculate the cost of the whole plan.
1.5. Cost of Execution Plan

Since database structures are complex, in most cases, and especially for not-very-simple queries, the needed data for a query can be collected from a database by accessing it in different ways, through different data-structures, and in different orders.
Each different way typically requires different processing time. Processing times of the same query may have large variance, from a fraction of a second to hours, depending on the way selected.
The purpose of query optimization, which is an automated process, is to find the way to process a given query in minimum time. The large possible variance in time justifies performing query optimization, though finding the exact optimal way to execute a query, among all possibilities, is typically very complex, time consuming by itself, may be too costly, and often practically impossible.
Because the number of possible plans grows in a factorial way with query complexity, it is impossible to generate and check all possible plans for complex queries. The Query Optimizer balances between plan quality and time needed for the optimization. Therefore, the Query Optimizer cannot guarantee that the best possible plan is always selected.
Thus query optimization typically tries to approximate the optimum by comparing several common-sense alternatives to provide in a reasonable time a "good enough" plan which typically does not deviate much from the best possible result.
1.6. Query Optimization Issues

2. Database Indexes

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure.
Tables in the database can have a large number of rows that are stored in random order, and it can take a lot of time to search them according to a specified criterion by sequentially viewing the table row by row.
The index is formed from the values ​​of one or more columns of the table and pointers to the corresponding rows of the table and, thus, allows you to search for rows that meet the search criteria.
Acceleration of work using indexes is achieved primarily due to the fact that the index has a structure optimized for search - for example, a balanced tree.
2.1. Database Index Concept

Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition.
There can be only one clustered index per table, because the data rows themselves can be stored in only one order.
The only time the data rows in a table are stored in sorted order is when the table contains a clustered index.
When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.
2.2. Types of Indexes
Clustered indexes

Nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.
When you create a table with a UNIQUE constraint, Database Engine automatically creates a nonclustered index.
When you try to enforce a PRIMARY KEY constraint on an existing table and a clustered index already exists on that table, SQL Server enforces the primary key using an nonclustered index.
2.2. Types of Indexes
Nonclustered indexes

When you create a table with a Primary Key, SQL Server automatically creates a corresponding clustered index based on columns included in the primary key.
In case a table does not have a primary key, which is very rare, you can use the CREATE CLUSTERED INDEX statement to define a clustered index for the table.
Example. For the PriceList (Name, Price) table :
CREATE CLUSTERED INDEX IX_PriceList_Name
ON PriceList (Name);
2.3. Create Indexes
Clustered indexes
CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, ...)
In Visual Studio 2017:

Example. For the Products table :
CREATE INDEX IX_Products_Name
ON Products (Name);
2.3. Create Indexes
Nonclustered indexes
CREATE [ UNIQUE ] [NONCLUSTERED ] INDEX index_name
ON
( column_name [ ASC | DESC ] [ ,...n ] )
In SSMS 2017:
Слайд 17
Example. For the Products table :
DROP INDEX Products.IX_Products_Name;
2.4. Drop Index
DROP INDEX table_name.index_name;
Note. Indexes
that are created as the result of creating PRIMARY KEY or UNIQUE constraints cannot be dropped by using DROP INDEX. They are dropped using
the ALTER TABLE DROP CONSTRAINT statement.
Слайд 18
2.5. Looking for indexes
sp_helpindex is a system stored procedure which lists
the information of all the indexes on a table or view. sp_helpindex returns the name of the index, description of the index and the name of the column on which the index was created.
Example.
EXEC sp_helpindex '[[[SCHEMA-NAME.TABLE-NAME]]]'
Слайд 19
2.5. Looking for indexes
sp_helpindex is a system stored procedure which lists
the information of all the indexes on a table or view. sp_helpindex returns the name of the index, description of the index and the name of the column on which the index was created.
EXEC sp_helpindex '[[[SCHEMA-NAME.TABLE-NAME]]]'
Example.
Слайд 20
3. Query Analysis Tools
Слайд 21
STATISTICS IO will tell you the cost of the query in
terms of the actual number of physical reads from disk, logical reads from memory on query and read-ahead reads as number of pages placed into the cache for the query by SQL Servers ‘Read-ahead’ mechanism.
3.1. STATISTICS IO
SET STATISTICS IO { ON | OFF }
Table 'Sales'. Scan count 1, logical reads 87, physical reads 1, read-ahead reads 85, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Products'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Message:
DBCC DROPCLEANBUFFERS; -- Clear cache data
SET STATISTICS IO ON
SELECT Sale_date, Name, Quantity
FROM Sales JOIN Products ON Sales.ProductId = Products.ProductId
SET STATISTICS IO OFF
Example.
Слайд 22
Displays the number of milliseconds required to parse, compile, and execute
each statement.
3.2. STATISTICS TIME
SET STATISTICS TIME { ON | OFF }
SQL Server Execution Times:
CPU time = 62 ms, elapsed time = 490 ms.
Message:
DBCC DROPCLEANBUFFERS; -- Clear cache data
SET STATISTICS TIME ON
SELECT Sale_date, Name, Quantity
FROM Sales JOIN Products ON Sales.ProductId = Products.ProductId
SET STATISTICS TIME OFF
Example.
Слайд 23
Execution plans can tell you how a query will be executed,
or how a query was executed.
Estimated execution plan is the plan that represents the output from the optimizer.
The operators, or steps, within the plan will be labelled as logical, because they’re representative of the optimizer’s view of the plan.
Actual execution plan is represents the output from the actual query execution.
It shows what actually happened when the query executed.
The main cause of a difference between the plans is differences between the statistics and the actual data. This generally occurs over time as data is added and deleted. This causes the key values that define the index to change, or their distribution (how many of what type) to change. This means that, over time, the statistics become a less-and-less accurate reflection of the actual data.
3.3. Types of Execution Plans
Слайд 24
In the Query Editor window, click the Display Estimated Execution Plan
icon on the tool bar.
3.4. Estimated Execution Plans
Слайд 25
1. In the Query Editor window, click the Include Actual Execution
Plan icon on the tool bar.
2. Click the Execute icon
3.5. Estimated Execution Plans
111
211
Слайд 26
Usually, you read a graphical execution plan from right to left
and top to bottom.
The arrows represent the data transmitted between the operators in the form of icons.
The thickness of the arrow reflects the amount of data being passed, thicker meaning more rows.
3.6. Reading the Execution Plan
If you hover over these arrows, it will show the number of rows that it represents.
Below each icon is displayed a number as a percentage. It represents the relative cost to the query for that operator (the estimated execution time).
Слайд 27
Execution plans can tell you how a query will be executed,
or how a query was executed.
Estimated execution plan is the plan that represents the output from the optimizer.
The operators, or steps, within the plan will be labelled as logical, because they’re representative of the optimizer’s view of the plan.
Actual execution plan is represents the output from the actual query execution.
It shows what actually happened when the query executed.
The main cause of a difference between the plans is differences between the statistics and the actual data. This generally occurs over time as data is added and deleted. This causes the key values that define the index to change, or their distribution (how many of what type) to change. This means that, over time, the statistics become a less-and-less accurate reflection of the actual data.
3.7. Types of Execution Plans
Слайд 28
3.8. Operator Descriptions
https://docs.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference?view=sql-server-ver15
Слайд 29
4. Query tuning practice
Слайд 30
Identify relevant stakeholders. (All involved parties + DBA)
Focus on
business outcomes. Be sure the query has a definite and unique purpose.
Prepare a discussion for good requirements. Define the function and scope of the report, specifying the intended audience. This will focus the query on tables with the right level of detail.
Develop good requirements by asking great questions. Those questions typically follow the 5 W’s – Who? What? Where? When? Why?
Write very specific requirements and confirm them with stakeholders. The performance of the production database is too critical to have unclear or ambiguous requirements.
4.1. Define business requirements before starting
Слайд 31
DBMS should scan column names and replace * with actual table
columns.
4.2. Avoid SELECT * in Your Queries
Instead of:
use:
Слайд 32
DBMS should scan column names and replace * with actual table
columns.
4.2. Avoid SELECT * in Your Queries
Instead of:
use:
Слайд 33
SELECT DISTINCT is a handy way to remove duplicates from a query.
SELECT
DISTINCT works by GROUPing all fields in the query to create distinct results.
4.3. Avoid DISTINCT in SQL Queries
Instead of:
use:
Слайд 34
In some databases, this type of queries are inefficient as it
first creates temp data with all possible options (most probably CROSS JOIN) and then it applies WHERE conditions.
4.4. Create Joins with INNER JOIN Rather than WHERE
Instead of:
use:
In SQL Server, they are equivalent
Слайд 35
Practice to create clustered and non-clustered index since indexes helps in
to access data fastly.
But be careful, more indexes on a table will slow the INSERT, UPDATE, DELETE operations.
Hence try to keep small no of indexes on a table.
4.5. Create Clustered and Non-Clustered Indexes
Example. Optimize performance of the query
SELECT
SalesId, ProductId, Quantity
FROM Sales
WHERE ProductId = 1;
Steps:
Check indexes on the Sales table
Simplified query without non-clustered indexes
Add non-clustered index on ProductId
Add new Quantity field in SELECT
Include columns
Слайд 36
EXEC sp_helpindex 'Sales'
Check indexes on the Sales table
Example
Слайд 37
2. Simplified query without non-clustered indexes
Example
SELECT SalesId, ProductId
FROM Sales
WHERE ProductId =
1;
Слайд 38
3. Add non-clustered index on ProductId
Example
CREATE INDEX IX_Sales_ProductID
ON Sales(ProductID);
EXEC sp_helpindex
'Sales'
SELECT SaleId, ProductId
FROM Sales
WHERE ProductId = 1;
Слайд 39
4. Add new Quantity field in SELECT
Example
SELECT SaleId, ProductId, Quantity
FROM
Sales
WHERE ProductId = 1;
Right click – Missing Index Details
Слайд 40
5. Include columns
Example
DROP INDEX IX_Sales_ProductID
ON Sales(ProductID);
CREATE INDEX IX_Sales_ProductID_Inc
ON Sales (ProductId)
INCLUDE
(SaleId,Quantity)
SELECT SaleId, ProductId, Quantity
FROM Sales
WHERE ProductId = 1;
Слайд 41
6. Add column and condition
Example (extension)
CREATE INDEX IX_Sales_Sale_date
ON Sales (Sale_date)
SELECT SaleId,
Sale_date, ProductId, Quantity
FROM Sales
WHERE ProductId = 1 AND Sale_date='01.01.2018';
Слайд 42
6. Add column and condition
Example (extension)
CREATE INDEX IX_Sales_Sale_date
ON Sales (Sale_date)
SELECT SaleId,
Sale_date, ProductId, Quantity
FROM Sales
WHERE ProductId = 1 AND Sale_date='01.01.2018';
Слайд 43
6. Add column and condition
Example (extension- from tips)
DROP INDEX IX_Sales_Sale_date
ON
Sales;
SELECT SaleId, Sale_date, ProductId, Quantity
FROM Sales
WHERE ProductId = 1 AND Sale_date='01.01.2018';
CREATE INDEX IX_Sales_Sale_date_ProductId
ON Sales ([Sale_date],[ProductId])
Имя файла: Tuning-SQL-query-performance.pptx
Количество просмотров: 102
Количество скачиваний: 0
Скачать
- Предыдущая
Состав, строение и происхождение Солнечной системы
Следующая -
Викторина Этот загадочный космос
Похожие презентации
Информация и её свойства. Для заочников
Основные web-технологии
Условный оператор
Презентация по информатике Печать документов
Технология Умный дом
Сравнительный анализ WEB-дизайна сайтов
Основы операционных систем
Строки. Лекция 4
Cloud Computing. Новый способ предоставления вычислительных ресурсов
Операционные системы, среды и оболочки. Архитектура операционной системы (Лекция 2)
Компьютерные презентации. Этапы создания и планирование. (Лекция 6)
Шифрование с открытым ключом. Алгоритм RSA
Урок в 6 классе по теме: Создание новогоднего поздравления - открытки в текстовом процессоре Word.
Тип даних множина
Связывание таблиц базы данных
Текстурный анализ изображений
Решение логических задач с помощью нескольких таблиц
Реферативная база данных
Программирование разветвляющихся алгоритмов. Начала программирования. Информатика. 8 класс
Поколения ЭВМ
New CCC user guide 2017.5. Overseas Service Planning Team
Подготовка к выполнению курсовой работы
Фреймы в HTML
Архитектура интеллектуальных информационных систем
Информация и информационные процессы
Алгоритмы и структуры данных. Лекция 6. Двоичные (бинарные) деревья поиска
Клавиатура. Основные позиции пальцев на клавиатуре
Губка Боб Квадратные Штаны
Обратная связь
Email:
Нажмите что бы посмотреть
window.__NUXT__=(function(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T){return {data:{meta:{result:{meta:{h1:n,main_text:a,main_title:o,main_description:p,main_keywords:q,main_image:a},app:{logo_image:r,logo_image_width:s,logo_image_height:t,logo_text:a,image_width_full:u,image_width_min:v,recaptcha_key:w,yandex_search:x,ya_context:e,banner_1:a,banner_2:a,banner_3:a,banner_4:a,banner_5:a,banner_6:a,banner_7:a,banner_8:a,banner_9:a,banner_10:a,email:y,footer_before:a,footer:a,dark_theme:e,strategy:{image_alt_words:j,item_slide_header:z,item_images:A,item_images_tag:h,item_words_h3:d,item_words_tag:h,item_words_1:k,routing:B,item_slide_skipping:C,item_download_timeout:D,item_slider_width:E,item_show_pdf:d,item_show_contents:d,item_show_filename:d},colors:{header_text:l,header_bg:i,accent:i}},social:F}},item:{result:{id:16417,name:m,category:G,count:44,jpg:"jpg\u002F16417",hits:102,download:b,h1:"Tuning SQL query performance презентация",meta:{h1:m,title:m,description:"Tuning SQL query performance презентация на тему, доклад, Информатика",keywords:"Tuning SQL query performance, презентация, доклад, проект, скачать, на тему, PowerPoint, урок, класс, школа, Информатика",download_link:a},text:"Вы можете изучить и скачать доклад-презентацию на тему Презентация на тему Tuning SQL query performance из раздела Информатика. Презентация на заданную тему содержит 44 слайдов. Для просмотра воспользуйтесь проигрывателем, если материал оказался полезным для Вас - поделитесь им с друзьями с помощью социальных кнопок и добавьте наш сайт презентаций в закладки!",seo_text:"\u003Cp\u003ETest questions\u003Cbr\u003E\u003Cp\u003E1. What functions does the query optimizer perform?\u003Cbr\u003E2. What is the purpose of the indexes?\u003Cbr\u003E3. Compare the Estimated execution plan with Actual execution plan .\u003Cbr\u003E\u003Cp\u003Eru:\u003Cbr\u003E\u003Cp\u003E1. Какие функции выполняет оптимизатор запросов?",file:"Tuning-SQL-query-performance.pptx",texts:["\u003Cp\u003ETuning SQL query performance\u003Cbr\u003E",H,"\u003Cp\u003EContents\u003Cbr\u003E\u003Cp\u003E1. Query Processing\u003Cbr\u003E2. Database Indexes\u003Cbr\u003E3. Query Analysis Tools\u003Cbr\u003E4. Query tuning practice\u003Cbr\u003E\u003Cbr\u003E","\u003Cp\u003E1. Query Processing\u003Cbr\u003E","\u003Cp\u003E1.1. End User Interaction with DBMS\u003Cbr\u003E\u003Cp\u003EEnd users interact with the DBMS through the use of queries to generate information,\u003Cbr\u003Eusing the following sequence:\u003Cbr\u003E1. The end-user application generates a query.\u003Cbr\u003E2. The query is sent to the DBMS.\u003Cbr\u003E3. The DBMS executes the query.\u003Cbr\u003E4. The DBMS sends the resulting data set to the end-user application.\u003Cbr\u003E\u003Cp\u003EThe goal of database performance is to execute queries as fast as possible\u003Cbr\u003E","\u003Cp\u003EQuery Processing includes translations on high level Queries into low level expressions that can be used at physical level of file system, query optimization and actual execution of query to get the actual result.\u003Cbr\u003E\u003Cp\u003E1.2. Query Processing\u003Cbr\u003E","\u003Cp\u003EImportance: The goal of query optimization is to reduce the system resources required to fulfill a query, and ultimately provide the user with the correct result set faster.\u003Cbr\u003E1. It provides the user with faster results, which makes the application seem faster to the user.\u003Cbr\u003E2. It allows the system to service more queries in the same amount of time, because each request takes less time than unoptimized queries.\u003Cbr\u003E3. Query optimization ultimately reduces the amount of wear on the hardware (e.g. disk drives), and allows the server to run more efficiently (e.g. lower power consumption, less memory usage).\u003Cbr\u003E\u003Cp\u003E1.3. Query Optimization\u003Cbr\u003E","\u003Cp\u003EA single query can be executed through different algorithms or re-written in different forms and structures. Hence, the question of query optimization comes into the picture – Which of these forms or pathways is the most optimal? The query optimizer attempts to determine the most efficient way to execute a given query by considering the possible query plans.\u003Cbr\u003EThe process of searching and evaluating various options (that is, different candidate execution plans) for fulfilling the query occurs at the optimization phase using the Query Optimizer.\u003Cbr\u003EIt selects the best plan for the next phase. The actual execution plan is a single tree with physical operators.\u003Cbr\u003E\u003Cp\u003E1.4. Query Optimizer\u003Cbr\u003E","\u003Cp\u003EQuery Optimizer is often a cost-based optimizer. It assigns a number called cost to each possible plan. A higher cost means a more complex plan, and a more complex plan means a slower query. \u003Cbr\u003EQuery Optimizer calculates the cost of an operation by determining the algorithm used by a physical operator and by estimating the number of rows that have to be processed. The estimation of the number of rows is also called cardinality estimation. The cost expresses usage of physical resources such as the amount of disk I\u002FO, CPU time, and memory needed for execution. \u003Cbr\u003EFor calculating the cost, the Query Optimizer needs some information for the estimation of the number of rows processed by each physical operator. The Query Optimizer gets this information from optimizer statistics. DBMS maintains statistics about the total number of rows and distribution of the number of rows over key values of an index for each index.\u003Cbr\u003EAfter the Query Optimizer gets the cost for all operators in a plan, it can calculate the cost of the whole plan.\u003Cbr\u003E\u003Cp\u003E1.5. Cost of Execution Plan\u003Cbr\u003E","\u003Cp\u003ESince database structures are complex, in most cases, and especially for not-very-simple queries, the needed data for a query can be collected from a database by accessing it in different ways, through different data-structures, and in different orders. \u003Cbr\u003EEach different way typically requires different processing time. Processing times of the same query may have large variance, from a fraction of a second to hours, depending on the way selected. \u003Cbr\u003EThe purpose of query optimization, which is an automated process, is to find the way to process a given query in minimum time. The large possible variance in time justifies performing query optimization, though finding the exact optimal way to execute a query, among all possibilities, is typically very complex, time consuming by itself, may be too costly, and often practically impossible. \u003Cbr\u003EBecause the number of possible plans grows in a factorial way with query complexity, it is impossible to generate and check all possible plans for complex queries. The Query Optimizer balances between plan quality and time needed for the optimization. Therefore, the Query Optimizer cannot guarantee that the best possible plan is always selected.\u003Cbr\u003EThus query optimization typically tries to approximate the optimum by comparing several common-sense alternatives to provide in a reasonable time a \"good enough\" plan which typically does not deviate much from the best possible result.\u003Cbr\u003E\u003Cp\u003E1.6. Query Optimization Issues\u003Cbr\u003E","\u003Cp\u003E2. Database Indexes\u003Cbr\u003E","\u003Cp\u003EA database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure.\u003Cbr\u003ETables in the database can have a large number of rows that are stored in random order, and it can take a lot of time to search them according to a specified criterion by sequentially viewing the table row by row. \u003Cbr\u003EThe index is formed from the values ​​of one or more columns of the table and pointers to the corresponding rows of the table and, thus, allows you to search for rows that meet the search criteria. \u003Cbr\u003EAcceleration of work using indexes is achieved primarily due to the fact that the index has a structure optimized for search - for example, a balanced tree.\u003Cbr\u003E\u003Cp\u003E2.1. Database Index Concept\u003Cbr\u003E","\u003Cp\u003EClustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. \u003Cbr\u003EThere can be only one clustered index per table, because the data rows themselves can be stored in only one order. \u003Cbr\u003EThe only time the data rows in a table are stored in sorted order is when the table contains a clustered index. \u003Cbr\u003EWhen a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.\u003Cbr\u003E\u003Cp\u003E2.2. Types of Indexes\u003Cbr\u003E\u003Cp\u003EClustered indexes\u003Cbr\u003E","\u003Cp\u003ENonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.\u003Cbr\u003EThe pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.\u003Cbr\u003EWhen you create a table with a UNIQUE constraint, Database Engine automatically creates a nonclustered index.\u003Cbr\u003EWhen you try to enforce a PRIMARY KEY constraint on an existing table and a clustered index already exists on that table, SQL Server enforces the primary key using an nonclustered index.\u003Cbr\u003E\u003Cp\u003E2.2. Types of Indexes\u003Cbr\u003E\u003Cp\u003ENonclustered indexes\u003Cbr\u003E","\u003Cp\u003EWhen you create a table with a Primary Key, SQL Server automatically creates a corresponding clustered index based on columns included in the primary key.\u003Cbr\u003EIn case a table does not have a primary key, which is very rare, you can use the CREATE CLUSTERED INDEX statement to define a clustered index for the table.\u003Cbr\u003E\u003Cbr\u003EExample. For the PriceList (Name, Price) table :\u003Cbr\u003ECREATE CLUSTERED INDEX IX_PriceList_Name\u003Cbr\u003EON PriceList (Name); \u003Cbr\u003E\u003Cp\u003E2.3. Create Indexes\u003Cbr\u003E\u003Cp\u003EClustered indexes\u003Cbr\u003E\u003Cp\u003ECREATE [UNIQUE] INDEX index_name \r\nON table_name (column1, column2, ...)\u003Cbr\u003E\u003Cp\u003EIn Visual Studio 2017:\u003Cbr\u003E","\u003Cp\u003EExample. For the Products table :\u003Cbr\u003ECREATE INDEX IX_Products_Name \r\nON Products (Name);\u003Cbr\u003E\u003Cp\u003E2.3. Create Indexes\u003Cbr\u003E\u003Cp\u003ENonclustered indexes\u003Cbr\u003E\u003Cp\u003ECREATE [ UNIQUE ] [NONCLUSTERED ] INDEX index_name \r\nON \u003Cobject\u003E ( column_name [ ASC | DESC ] [ ,...n ] )\u003Cbr\u003E\u003Cp\u003EIn SSMS 2017:\u003Cbr\u003E","\u003Cp\u003EExample. For the Products table :\u003Cbr\u003EDROP INDEX Products.IX_Products_Name;\u003Cbr\u003E\u003Cp\u003E2.4. Drop Index\u003Cbr\u003E\u003Cp\u003EDROP INDEX table_name.index_name;\u003Cbr\u003E\u003Cp\u003ENote. Indexes that are created as the result of creating PRIMARY KEY or UNIQUE constraints cannot be dropped by using DROP INDEX. They are dropped using \r\nthe ALTER TABLE DROP CONSTRAINT statement. \u003Cbr\u003E","\u003Cp\u003E2.5. Looking for indexes\u003Cbr\u003E\u003Cp\u003Esp_helpindex is a system stored procedure which lists the information of all the indexes on a table or view. sp_helpindex returns the name of the index, description of the index and the name of the column on which the index was created.\u003Cbr\u003E\u003Cbr\u003EExample.\u003Cbr\u003E\u003Cbr\u003E\u003Cbr\u003E\u003Cp\u003EEXEC sp_helpindex '[[[SCHEMA-NAME.TABLE-NAME]]]' \u003Cbr\u003E","\u003Cp\u003E2.5. Looking for indexes\u003Cbr\u003E\u003Cp\u003Esp_helpindex is a system stored procedure which lists the information of all the indexes on a table or view. sp_helpindex returns the name of the index, description of the index and the name of the column on which the index was created.\u003Cbr\u003E\u003Cp\u003EEXEC sp_helpindex '[[[SCHEMA-NAME.TABLE-NAME]]]' \u003Cbr\u003E\u003Cp\u003EExample.\u003Cbr\u003E","\u003Cp\u003E3. Query Analysis Tools\u003Cbr\u003E","\u003Cp\u003ESTATISTICS IO will tell you the cost of the query in terms of the actual number of physical reads from disk, logical reads from memory on query and read-ahead reads as\tnumber of pages placed into the cache for the query by SQL Servers ‘Read-ahead’ mechanism.\u003Cbr\u003E\u003Cp\u003E3.1. STATISTICS IO\u003Cbr\u003E\u003Cp\u003ESET STATISTICS IO { ON | OFF } \u003Cbr\u003E\u003Cp\u003ETable 'Sales'. Scan count 1, logical reads 87, physical reads 1, read-ahead reads 85, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\u003Cbr\u003ETable 'Products'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\u003Cbr\u003E\u003Cp\u003EMessage:\u003Cbr\u003E\u003Cp\u003EDBCC DROPCLEANBUFFERS; -- Clear cache data\u003Cbr\u003ESET STATISTICS IO ON\u003Cbr\u003ESELECT Sale_date, Name, Quantity\u003Cbr\u003EFROM Sales JOIN Products ON Sales.ProductId = Products.ProductId\u003Cbr\u003ESET STATISTICS IO OFF \u003Cbr\u003E\u003Cp\u003EExample.\u003Cbr\u003E","\u003Cp\u003EDisplays the number of milliseconds required to parse, compile, and execute each statement.\u003Cbr\u003E\u003Cp\u003E3.2. STATISTICS TIME\u003Cbr\u003E\u003Cp\u003ESET STATISTICS TIME { ON | OFF } \u003Cbr\u003E\u003Cp\u003ESQL Server Execution Times:\u003Cbr\u003E CPU time = 62 ms, elapsed time = 490 ms.\u003Cbr\u003E\u003Cp\u003EMessage:\u003Cbr\u003E\u003Cp\u003EDBCC DROPCLEANBUFFERS; -- Clear cache data\u003Cbr\u003ESET STATISTICS TIME ON\u003Cbr\u003ESELECT Sale_date, Name, Quantity\u003Cbr\u003EFROM Sales JOIN Products ON Sales.ProductId = Products.ProductId\u003Cbr\u003ESET STATISTICS TIME OFF \u003Cbr\u003E\u003Cp\u003EExample.\u003Cbr\u003E","\u003Cp\u003EExecution plans can tell you how a query will be executed, or how a query was executed.\u003Cbr\u003EEstimated execution plan is the plan that represents the output from the optimizer. \r\nThe operators, or steps, within the plan will be labelled as logical, because they’re representative of the optimizer’s view of the plan.\u003Cbr\u003EActual execution plan is represents the output from the actual query execution. \r\nIt shows what actually happened when the query executed.\u003Cbr\u003EThe main cause of a difference between the plans is differences between the statistics and the actual data. This generally occurs over time as data is added and deleted. This causes the key values that define the index to change, or their distribution (how many of what type) to change. This means that, over time, the statistics become a less-and-less accurate reflection of the actual data.\u003Cbr\u003E\u003Cp\u003E3.3. Types of Execution Plans \u003Cbr\u003E","\u003Cp\u003EIn the Query Editor window, click the Display Estimated Execution Plan icon on the tool bar.\u003Cbr\u003E\u003Cp\u003E3.4. Estimated Execution Plans \u003Cbr\u003E","\u003Cp\u003E1. In the Query Editor window, click the Include Actual Execution Plan icon on the tool bar.\u003Cbr\u003E2. Click the Execute icon \u003Cbr\u003E\u003Cp\u003E3.5. Estimated Execution Plans \u003Cbr\u003E\u003Cp\u003E111\u003Cbr\u003E\u003Cp\u003E211\u003Cbr\u003E","\u003Cp\u003EUsually, you read a graphical execution plan from right to left and top to bottom.\u003Cbr\u003EThe arrows represent the data transmitted between the operators in the form of icons.\u003Cbr\u003EThe thickness of the arrow reflects the amount of data being passed, thicker meaning more rows.\u003Cbr\u003E\u003Cp\u003E3.6. Reading the Execution Plan \u003Cbr\u003E\u003Cp\u003EIf you hover over these arrows, it will show the number of rows that it represents.\u003Cbr\u003EBelow each icon is displayed a number as a percentage. It represents the relative cost to the query for that operator (the estimated execution time).\u003Cbr\u003E","\u003Cp\u003EExecution plans can tell you how a query will be executed, or how a query was executed.\u003Cbr\u003EEstimated execution plan is the plan that represents the output from the optimizer. \r\nThe operators, or steps, within the plan will be labelled as logical, because they’re representative of the optimizer’s view of the plan.\u003Cbr\u003EActual execution plan is represents the output from the actual query execution. \r\nIt shows what actually happened when the query executed.\u003Cbr\u003EThe main cause of a difference between the plans is differences between the statistics and the actual data. This generally occurs over time as data is added and deleted. This causes the key values that define the index to change, or their distribution (how many of what type) to change. This means that, over time, the statistics become a less-and-less accurate reflection of the actual data.\u003Cbr\u003E\u003Cp\u003E3.7. Types of Execution Plans \u003Cbr\u003E","\u003Cp\u003E3.8. Operator Descriptions\u003Cbr\u003E\u003Cp\u003Ehttps:\u002F\u002Fdocs.microsoft.com\u002Fen-us\u002Fsql\u002Frelational-databases\u002Fshowplan-logical-and-physical-operators-reference?view=sql-server-ver15\u003Cbr\u003E","\u003Cp\u003E4. Query tuning practice\u003Cbr\u003E","\u003Cp\u003E Identify relevant stakeholders. (All involved parties + DBA)\u003Cbr\u003E Focus on business outcomes. Be sure the query has a definite and unique purpose.\u003Cbr\u003E Prepare a discussion for good requirements. Define the function and scope of the report, specifying the intended audience. This will focus the query on tables with the right level of detail.\u003Cbr\u003E Develop good requirements by asking great questions. Those questions typically follow the 5 W’s – Who? What? Where? When? Why?\u003Cbr\u003E Write very specific requirements and confirm them with stakeholders. The performance of the production database is too critical to have unclear or ambiguous requirements.\u003Cbr\u003E\u003Cp\u003E4.1. Define business requirements before starting\u003Cbr\u003E",I,I,"\u003Cp\u003ESELECT DISTINCT is a handy way to remove duplicates from a query. \u003Cbr\u003ESELECT DISTINCT works by GROUPing all fields in the query to create distinct results. \u003Cbr\u003E\u003Cp\u003E4.3. Avoid DISTINCT in SQL Queries\u003Cbr\u003E\u003Cp\u003EInstead of:\u003Cbr\u003E \u003Cbr\u003E\u003Cbr\u003E\u003Cp\u003Euse:\u003Cbr\u003E","\u003Cp\u003EIn some databases, this type of queries are inefficient as it first creates temp data with all possible options (most probably CROSS JOIN) and then it applies WHERE conditions.\u003Cbr\u003E\u003Cp\u003E4.4. Create Joins with INNER JOIN Rather than WHERE\u003Cbr\u003E\u003Cp\u003EInstead of:\u003Cbr\u003E \u003Cbr\u003E\u003Cbr\u003E\u003Cp\u003Euse:\u003Cbr\u003E\u003Cp\u003EIn SQL Server, they are equivalent\u003Cbr\u003E","\u003Cp\u003EPractice to create clustered and non-clustered index since indexes helps in to access data fastly. \u003Cbr\u003EBut be careful, more indexes on a table will slow the INSERT, UPDATE, DELETE operations.\u003Cbr\u003EHence try to keep small no of indexes on a table.\u003Cbr\u003E\u003Cp\u003E4.5. Create Clustered and Non-Clustered Indexes\u003Cbr\u003E\u003Cp\u003EExample. Optimize performance of the query\u003Cbr\u003E\u003Cp\u003ESELECT \u003Cbr\u003E SalesId, ProductId, Quantity\u003Cbr\u003EFROM Sales\u003Cbr\u003EWHERE ProductId = 1;\u003Cbr\u003E\u003Cp\u003ESteps:\u003Cbr\u003ECheck indexes on the Sales table\u003Cbr\u003ESimplified query without non-clustered indexes\u003Cbr\u003EAdd non-clustered index on ProductId\u003Cbr\u003EAdd new Quantity field in SELECT\u003Cbr\u003EInclude columns\u003Cbr\u003E","\u003Cp\u003EEXEC sp_helpindex 'Sales'\u003Cbr\u003E\u003Cp\u003ECheck indexes on the Sales table\u003Cbr\u003E\u003Cp\u003EExample\u003Cbr\u003E","\u003Cp\u003E2. Simplified query without non-clustered indexes\u003Cbr\u003E\u003Cp\u003EExample\u003Cbr\u003E\u003Cp\u003ESELECT SalesId, ProductId\u003Cbr\u003EFROM Sales\u003Cbr\u003EWHERE ProductId = 1;\u003Cbr\u003E","\u003Cp\u003E3. Add non-clustered index on ProductId\u003Cbr\u003E\u003Cp\u003EExample\u003Cbr\u003E\u003Cp\u003ECREATE INDEX IX_Sales_ProductID \u003Cbr\u003EON Sales(ProductID);\u003Cbr\u003E\u003Cp\u003EEXEC sp_helpindex 'Sales'\u003Cbr\u003E\u003Cp\u003ESELECT SaleId, ProductId\u003Cbr\u003EFROM Sales\u003Cbr\u003EWHERE ProductId = 1;\u003Cbr\u003E","\u003Cp\u003E4. Add new Quantity field in SELECT\u003Cbr\u003E\u003Cp\u003EExample\u003Cbr\u003E\u003Cp\u003ESELECT SaleId, ProductId, Quantity \u003Cbr\u003EFROM Sales\u003Cbr\u003EWHERE ProductId = 1;\u003Cbr\u003E\u003Cp\u003ERight click – Missing Index Details\u003Cbr\u003E","\u003Cp\u003E5. Include columns\u003Cbr\u003E\u003Cp\u003EExample\u003Cbr\u003E\u003Cp\u003EDROP INDEX IX_Sales_ProductID \u003Cbr\u003EON Sales(ProductID);\u003Cbr\u003E\u003Cp\u003ECREATE INDEX IX_Sales_ProductID_Inc\u003Cbr\u003EON Sales (ProductId)\u003Cbr\u003EINCLUDE (SaleId,Quantity)\u003Cbr\u003E\u003Cp\u003ESELECT SaleId, ProductId, Quantity \u003Cbr\u003EFROM Sales\u003Cbr\u003EWHERE ProductId = 1;\u003Cbr\u003E",J,J,"\u003Cp\u003E6. Add column and condition\u003Cbr\u003E\u003Cp\u003EExample (extension- from tips)\u003Cbr\u003E\u003Cp\u003EDROP INDEX IX_Sales_Sale_date \u003Cbr\u003EON Sales;\u003Cbr\u003E\u003Cp\u003ESELECT SaleId, Sale_date, ProductId, Quantity \u003Cbr\u003EFROM Sales\u003Cbr\u003EWHERE ProductId = 1 AND Sale_date='01.01.2018';\u003Cbr\u003E\u003Cp\u003ECREATE INDEX IX_Sales_Sale_date_ProductId\u003Cbr\u003EON Sales ([Sale_date],[ProductId])\u003Cbr\u003E",H],href:"\u002Finformatika\u002Ftuning-sql-query-performance",href_category:"\u002Finformatika",date:1624031505,related:[{id:129872,name:"Информация и её свойства. Для заочников",href:"\u002Finformatika\u002Finformatsiya-i-eyo-svoystva-dlya-zaochnikov",jpg:"jpg\u002F129872"},{id:24265,name:"Основные web-технологии",href:"\u002Finformatika\u002Fosnovnye-web-tehnologii",jpg:"jpg\u002F24265"},{id:167927,name:"Условный оператор",href:"\u002Finformatika\u002Fuslovnyy-operator-5",jpg:"jpg\u002F167927"},{id:553395,name:"Презентация по информатике Печать документов",href:"\u002Finformatika\u002Fprezentatsiya-po-informatike-pechat-dokumentov",jpg:"jpg\u002F553395"},{id:205163,name:"Технология Умный дом",href:"\u002Finformatika\u002Ftehnologiya-umnyy-dom",jpg:"jpg\u002F205163"},{id:345681,name:"Сравнительный анализ WEB-дизайна сайтов",href:"\u002Finformatika\u002Fsravnitelnyy-analiz-web-dizayna-saytov",jpg:"jpg\u002F345681"},{id:379869,name:"Основы операционных систем",href:"\u002Finformatika\u002Fosnovy-operatsionnyh-sistem-3",jpg:"jpg\u002F379869"},{id:330038,name:"Строки. Лекция 4",href:"\u002Finformatika\u002Fstroki-lektsiya-4",jpg:"jpg\u002F330038"},{id:342843,name:"Cloud Computing. Новый способ предоставления вычислительных ресурсов",href:"\u002Finformatika\u002Fcloud-computing-novyy-sposob-predostavleniya-vychislitelnyh",jpg:"jpg\u002F342843"},{id:254655,name:"Операционные системы, среды и оболочки. Архитектура операционной системы (Лекция 2)",href:"\u002Finformatika\u002Foperatsionnye-sistemy-sredy-i-obolochki-arhitektura-1",jpg:"jpg\u002F254655"},{id:210773,name:"Компьютерные презентации. Этапы создания и планирование. (Лекция 6)",href:"\u002Finformatika\u002Fkompyuternye-prezentatsii-etapy-sozdaniya-i-planirovanie",jpg:"jpg\u002F210773"},{id:586205,name:"Шифрование с открытым ключом. Алгоритм RSA",href:"\u002Finformatika\u002Fshifrovanie-s-otkrytym-klyuchom-algoritm-rsa-1",jpg:"jpg\u002F586205"},{id:523237,name:"Урок в 6 классе по теме: Создание новогоднего поздравления - открытки в текстовом процессоре Word.",href:"\u002Finformatika\u002Furok-v-6-klasse-po-teme-6",jpg:"jpg\u002F523237"},{id:140426,name:"Тип даних множина",href:"\u002Finformatika\u002Ftip-danih-mnozhina",jpg:"jpg\u002F140426"},{id:184575,name:"Связывание таблиц базы данных",href:"\u002Finformatika\u002Fsvyazyvanie-tablits-bazy-dannyh-1",jpg:"jpg\u002F184575"},{id:233260,name:"Текстурный анализ изображений",href:"\u002Finformatika\u002Fteksturnyy-analiz-izobrazheniy",jpg:"jpg\u002F233260"},{id:136602,name:"Решение логических задач с помощью нескольких таблиц",href:"\u002Finformatika\u002Freshenie-logicheskih-zadach-s-pomoshchyu-neskolkih-1",jpg:"jpg\u002F136602"},{id:167427,name:"Реферативная база данных",href:"\u002Finformatika\u002Freferativnaya-baza-dannyh",jpg:"jpg\u002F167427"},{id:582361,name:"Программирование разветвляющихся алгоритмов. Начала программирования. Информатика. 8 класс",href:"\u002Finformatika\u002Fprogrammirovanie-razvetvlyayushchihsya-algoritmov-nachala-programmirovaniya-informatika",jpg:"jpg\u002F582361"},{id:302143,name:"Поколения ЭВМ",href:"\u002Finformatika\u002Fpokoleniya-evm-5",jpg:"jpg\u002F302143"},{id:201516,name:"New CCC user guide 2017.5. Overseas Service Planning Team",href:"\u002Finformatika\u002Fnew-ccc-user-guide-20175-overseas",jpg:"jpg\u002F201516"},{id:396849,name:"Подготовка к выполнению курсовой работы",href:"\u002Finformatika\u002Fpodgotovka-k-vypolneniyu-kursovoy-raboty",jpg:"jpg\u002F396849"},{id:333315,name:"Фреймы в HTML",href:"\u002Finformatika\u002Ffreymy-v-html",jpg:"jpg\u002F333315"},{id:406840,name:"Архитектура интеллектуальных информационных систем",href:"\u002Finformatika\u002Farhitektura-intellektualnyh-informatsionnyh-sistem",jpg:"jpg\u002F406840"},{id:306787,name:"Информация и информационные процессы",href:"\u002Finformatika\u002Finformatsiya-i-informatsionnye-protsessy-14",jpg:"jpg\u002F306787"},{id:588567,name:"Алгоритмы и структуры данных. Лекция 6. Двоичные (бинарные) деревья поиска",href:"\u002Finformatika\u002Falgoritmy-i-struktury-dannyh-lektsiya-6-65",jpg:"jpg\u002F588567"},{id:241195,name:"Клавиатура. Основные позиции пальцев на клавиатуре",href:"\u002Finformatika\u002Fklaviatura-osnovnye-pozitsii-paltsev-na-klaviature",jpg:"jpg\u002F241195"},{id:20611,name:"Губка Боб Квадратные Штаны",href:"\u002Finformatika\u002Fgubka-bob-kvadratnye-shtany",jpg:"jpg\u002F20611"}],navigation:{next:"\u002Fastronomiya\u002Fviktorina-etot-zagadochnyy-kosmos",next_name:"Викторина Этот загадочный космос",prev:"\u002Fastronomiya\u002Fsostav-stroenie-i-proishozhdenie-solnechnoy-sistemy",prev_name:"Состав, строение и происхождение Солнечной системы"}}},categories:{result:[{id:K,ordering:b,name:"Без категории",pseudoname:"uncategorized"},{id:144,ordering:b,name:"Бизнес",pseudoname:"biznes"},{id:146,ordering:b,name:"Образование",pseudoname:"obrazovanie"},{id:149,ordering:b,name:"Финансы",pseudoname:"finansy"},{id:150,ordering:b,name:"Государство",pseudoname:"gosudarstvo"},{id:152,ordering:b,name:"Спорт",pseudoname:"sport"},{id:154,ordering:b,name:"Армия",pseudoname:"armiya"},{id:156,ordering:b,name:"Культурология",pseudoname:"kulturologiya"},{id:157,ordering:b,name:"Еда и кулинария",pseudoname:"eda-i-kulinariya"},{id:158,ordering:b,name:"Лингвистика",pseudoname:"lingvistika"},{id:163,ordering:b,name:"Черчение",pseudoname:"cherchenie"},{id:164,ordering:b,name:"Физкультура",pseudoname:"fizkultura"},{id:165,ordering:b,name:"ИЗО",pseudoname:"izo"},{id:166,ordering:b,name:"Психология",pseudoname:"psihologiya"},{id:116,ordering:K,name:"Английский язык",pseudoname:"angliyskiy-yazyk"},{id:108,ordering:2,name:"Астрономия",pseudoname:"astronomiya"},{id:104,ordering:3,name:"Алгебра",pseudoname:"algebra"},{id:106,ordering:4,name:"Биология",pseudoname:"biologiya"},{id:142,ordering:5,name:"География",pseudoname:"geografiya"},{id:141,ordering:6,name:"Геометрия",pseudoname:"geometria"},{id:123,ordering:7,name:"Детские презентации",pseudoname:"detskie-prezentatsii"},{id:107,ordering:8,name:G,pseudoname:"informatika"},{id:109,ordering:9,name:"История",pseudoname:"istoriya"},{id:139,ordering:j,name:"Литература",pseudoname:"literatura"},{id:119,ordering:k,name:"Маркетинг",pseudoname:"marketing"},{id:114,ordering:12,name:"Математика",pseudoname:"matematika"},{id:138,ordering:13,name:"Медицина",pseudoname:"medetsina"},{id:118,ordering:14,name:"Менеджмент",pseudoname:"menedzhment"},{id:137,ordering:15,name:"Музыка",pseudoname:"muzyka"},{id:136,ordering:16,name:"МХК",pseudoname:"mhk"},{id:130,ordering:17,name:"Немецкий язык",pseudoname:"nemetskiy-yazyk"},{id:105,ordering:18,name:"ОБЖ",pseudoname:"obzh"},{id:133,ordering:19,name:"Обществознание ",pseudoname:"obshchestvoznanie"},{id:132,ordering:20,name:"Окружающий мир ",pseudoname:"okruzhayushchiy-mir"},{id:131,ordering:21,name:"Педагогика ",pseudoname:"pedagogika"},{id:129,ordering:22,name:"Русский язык",pseudoname:"russkiy-yazyk"},{id:128,ordering:24,name:"Технология",pseudoname:"tehnologiya"},{id:127,ordering:25,name:"Физика",pseudoname:"fizika"},{id:126,ordering:26,name:"Философия",pseudoname:"filosofiya"},{id:125,ordering:27,name:"Химия",pseudoname:"himiya"},{id:124,ordering:29,name:"Экология",pseudoname:"ekologiya"},{id:121,ordering:30,name:"Экономика",pseudoname:"ekonomika"},{id:120,ordering:31,name:"Юриспруденция",pseudoname:"yurisprudentsiya"}]}},state:{},_errors:{},serverRendered:d,config:{public:{SITE_NAME:L,SITE_HOST:"mypreza.com",SITE_LANG:M,API_BASE_URL:"https:\u002F\u002Fmypreza.com\u002Fapi\u002F",pwaManifest:{name:L,short_name:a,description:a,lang:M,start_url:"\u002F?standalone=true",display:"standalone",background_color:l,theme_color:"#000000",icons:[{src:"\u002F_nuxt\u002Ficons\u002F64x64.f2a6e93d.png",type:c,sizes:N,purpose:f},{src:"\u002F_nuxt\u002Ficons\u002F64x64.maskable.f2a6e93d.png",type:c,sizes:N,purpose:g},{src:"\u002F_nuxt\u002Ficons\u002F120x120.f2a6e93d.png",type:c,sizes:O,purpose:f},{src:"\u002F_nuxt\u002Ficons\u002F120x120.maskable.f2a6e93d.png",type:c,sizes:O,purpose:g},{src:"\u002F_nuxt\u002Ficons\u002F144x144.f2a6e93d.png",type:c,sizes:P,purpose:f},{src:"\u002F_nuxt\u002Ficons\u002F144x144.maskable.f2a6e93d.png",type:c,sizes:P,purpose:g},{src:"\u002F_nuxt\u002Ficons\u002F152x152.f2a6e93d.png",type:c,sizes:Q,purpose:f},{src:"\u002F_nuxt\u002Ficons\u002F152x152.maskable.f2a6e93d.png",type:c,sizes:Q,purpose:g},{src:"\u002F_nuxt\u002Ficons\u002F192x192.f2a6e93d.png",type:c,sizes:R,purpose:f},{src:"\u002F_nuxt\u002Ficons\u002F192x192.maskable.f2a6e93d.png",type:c,sizes:R,purpose:g},{src:"\u002F_nuxt\u002Ficons\u002F384x384.f2a6e93d.png",type:c,sizes:S,purpose:f},{src:"\u002F_nuxt\u002Ficons\u002F384x384.maskable.f2a6e93d.png",type:c,sizes:S,purpose:g},{src:"\u002F_nuxt\u002Ficons\u002F512x512.f2a6e93d.png",type:c,sizes:T,purpose:f},{src:"\u002F_nuxt\u002Ficons\u002F512x512.maskable.f2a6e93d.png",type:c,sizes:T,purpose:g}]},yandexMetrika:{id:"85004146",metrikaUrl:"https:\u002F\u002Fmc.yandex.ru\u002Fmetrika\u002Ftag.js",accurateTrackBounce:d,childIframe:e,clickmap:e,defer:e,useRuntimeConfig:d,trackHash:e,trackLinks:d,type:b,webvisor:e,triggerEvent:e,consoleLog:d,partytown:e,isDev:e}},app:{baseURL:"\u002F",buildAssetsDir:"\u002F_nuxt\u002F",cdnURL:a}},pinia:{app:{meta:{h1:n,main_text:a,main_title:o,main_description:p,main_keywords:q,main_image:a},app:{logo_image:r,logo_image_width:s,logo_image_height:t,logo_text:a,image_width_full:u,image_width_min:v,recaptcha_key:w,yandex_search:x,ya_context:e,banner_1:a,banner_2:a,banner_3:a,banner_4:a,banner_5:a,banner_6:a,banner_7:a,banner_8:a,banner_9:a,banner_10:a,email:y,footer_before:a,footer:a,dark_theme:e,strategy:{image_alt_words:j,item_slide_header:z,item_images:A,item_images_tag:h,item_words_h3:d,item_words_tag:h,item_words_1:k,routing:B,item_slide_skipping:C,item_download_timeout:D,item_slider_width:E,item_show_pdf:d,item_show_contents:d,item_show_filename:d},colors:{header_text:l,header_bg:i,accent:i}},social:F,token:a}}}}("",0,"image\u002Fpng",true,false,"any","maskable","h2","#d53032",10,11,"#ffffff","Tuning SQL query performance","Учебные презентации","Презентации в PowerPoint на разные темы","Презентации, проекты, доклады в PowerPoint на разные темы для учебы","Презентация, на тему, урок, класс, PowerPoint, скачати презентацію, фон, шаблон, доклад, проект.","images\u002Fdata\u002Flogo.png",185,40,720,480,"6LeioWIpAAAAAGqMN4KqMVIkekcMW7fBKHjXCd-a","2503267","[e-mail]","div","images_before","category","both",88,800,"\u003Cscript src=\"\u002F\u002Fyastatic.net\u002Fes5-shims\u002F0.0.2\u002Fes5-shims.min.js\"\u003E\u003C\u002Fscript\u003E\r\n\u003Cscript src=\"\u002F\u002Fyastatic.net\u002Fshare2\u002Fshare.js\"\u003E\u003C\u002Fscript\u003E\r\n\u003Cdiv class=\"ya-share2\" data-services=\"collections,gplus,vkontakte,facebook,odnoklassniki,gplus,twitter,moimir\" data-counter=\"\"\u003E\u003C\u002Fdiv\u003E","Информатика","\u003Cp\u003ETest questions\u003Cbr\u003E\u003Cp\u003E1. What functions does the query optimizer perform?\u003Cbr\u003E2. What is the purpose of the indexes?\u003Cbr\u003E3. Compare the Estimated execution plan with Actual execution plan .\u003Cbr\u003E\u003Cp\u003Eru:\u003Cbr\u003E\u003Cp\u003E1. Какие функции выполняет оптимизатор запросов? \u003Cbr\u003E2. Каково назначение индексов?\u003Cbr\u003E3.Сравните предполагаемый\u003Cbr\u003Eплан выполнения с действитель-\u003Cbr\u003Eным планом выполнения.\u003Cbr\u003E\u003Cp\u003Een:\u003Cbr\u003E","\u003Cp\u003EDBMS should scan column names and replace * with actual table columns.\u003Cbr\u003E\u003Cp\u003E4.2. Avoid SELECT * in Your Queries\u003Cbr\u003E\u003Cp\u003EInstead of:\u003Cbr\u003E \u003Cbr\u003E\u003Cbr\u003E\u003Cp\u003Euse:\u003Cbr\u003E","\u003Cp\u003E6. Add column and condition\u003Cbr\u003E\u003Cp\u003EExample (extension)\u003Cbr\u003E\u003Cp\u003ECREATE INDEX IX_Sales_Sale_date\u003Cbr\u003EON Sales (Sale_date)\u003Cbr\u003E\u003Cp\u003ESELECT SaleId, Sale_date, ProductId, Quantity \u003Cbr\u003EFROM Sales\u003Cbr\u003EWHERE ProductId = 1 AND Sale_date='01.01.2018';\u003Cbr\u003E",1,"Mypreza","ru","64x64","120x120","144x144","152x152","192x192","384x384","512x512"))

Example. For the Products table :
DROP INDEX Products.IX_Products_Name;
2.4. Drop Index
DROP INDEX table_name.index_name;
Note. Indexes that are created as the result of creating PRIMARY KEY or UNIQUE constraints cannot be dropped by using DROP INDEX. They are dropped using
the ALTER TABLE DROP CONSTRAINT statement.

2.5. Looking for indexes
sp_helpindex is a system stored procedure which lists the information of all the indexes on a table or view. sp_helpindex returns the name of the index, description of the index and the name of the column on which the index was created.
Example.
EXEC sp_helpindex '[[[SCHEMA-NAME.TABLE-NAME]]]'

2.5. Looking for indexes
sp_helpindex is a system stored procedure which lists the information of all the indexes on a table or view. sp_helpindex returns the name of the index, description of the index and the name of the column on which the index was created.
EXEC sp_helpindex '[[[SCHEMA-NAME.TABLE-NAME]]]'
Example.

3. Query Analysis Tools

STATISTICS IO will tell you the cost of the query in terms of the actual number of physical reads from disk, logical reads from memory on query and read-ahead reads as number of pages placed into the cache for the query by SQL Servers ‘Read-ahead’ mechanism.
3.1. STATISTICS IO
SET STATISTICS IO { ON | OFF }
Table 'Sales'. Scan count 1, logical reads 87, physical reads 1, read-ahead reads 85, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Products'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Message:
DBCC DROPCLEANBUFFERS; -- Clear cache data
SET STATISTICS IO ON
SELECT Sale_date, Name, Quantity
FROM Sales JOIN Products ON Sales.ProductId = Products.ProductId
SET STATISTICS IO OFF
Example.

Displays the number of milliseconds required to parse, compile, and execute each statement.
3.2. STATISTICS TIME
SET STATISTICS TIME { ON | OFF }
SQL Server Execution Times:
CPU time = 62 ms, elapsed time = 490 ms.
Message:
DBCC DROPCLEANBUFFERS; -- Clear cache data
SET STATISTICS TIME ON
SELECT Sale_date, Name, Quantity
FROM Sales JOIN Products ON Sales.ProductId = Products.ProductId
SET STATISTICS TIME OFF
Example.

Execution plans can tell you how a query will be executed, or how a query was executed.
Estimated execution plan is the plan that represents the output from the optimizer.
The operators, or steps, within the plan will be labelled as logical, because they’re representative of the optimizer’s view of the plan.
Actual execution plan is represents the output from the actual query execution.
It shows what actually happened when the query executed.
The main cause of a difference between the plans is differences between the statistics and the actual data. This generally occurs over time as data is added and deleted. This causes the key values that define the index to change, or their distribution (how many of what type) to change. This means that, over time, the statistics become a less-and-less accurate reflection of the actual data.
3.3. Types of Execution Plans

In the Query Editor window, click the Display Estimated Execution Plan icon on the tool bar.
3.4. Estimated Execution Plans

1. In the Query Editor window, click the Include Actual Execution Plan icon on the tool bar.
2. Click the Execute icon
3.5. Estimated Execution Plans
111
211

Usually, you read a graphical execution plan from right to left and top to bottom.
The arrows represent the data transmitted between the operators in the form of icons.
The thickness of the arrow reflects the amount of data being passed, thicker meaning more rows.
3.6. Reading the Execution Plan
If you hover over these arrows, it will show the number of rows that it represents.
Below each icon is displayed a number as a percentage. It represents the relative cost to the query for that operator (the estimated execution time).

Execution plans can tell you how a query will be executed, or how a query was executed.
Estimated execution plan is the plan that represents the output from the optimizer.
The operators, or steps, within the plan will be labelled as logical, because they’re representative of the optimizer’s view of the plan.
Actual execution plan is represents the output from the actual query execution.
It shows what actually happened when the query executed.
The main cause of a difference between the plans is differences between the statistics and the actual data. This generally occurs over time as data is added and deleted. This causes the key values that define the index to change, or their distribution (how many of what type) to change. This means that, over time, the statistics become a less-and-less accurate reflection of the actual data.
3.7. Types of Execution Plans

3.8. Operator Descriptions
https://docs.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference?view=sql-server-ver15

4. Query tuning practice

Identify relevant stakeholders. (All involved parties + DBA)
Focus on business outcomes. Be sure the query has a definite and unique purpose.
Prepare a discussion for good requirements. Define the function and scope of the report, specifying the intended audience. This will focus the query on tables with the right level of detail.
Develop good requirements by asking great questions. Those questions typically follow the 5 W’s – Who? What? Where? When? Why?
Write very specific requirements and confirm them with stakeholders. The performance of the production database is too critical to have unclear or ambiguous requirements.
4.1. Define business requirements before starting

DBMS should scan column names and replace * with actual table columns.
4.2. Avoid SELECT * in Your Queries
Instead of:
use:

DBMS should scan column names and replace * with actual table columns.
4.2. Avoid SELECT * in Your Queries
Instead of:
use:

SELECT DISTINCT is a handy way to remove duplicates from a query.
SELECT DISTINCT works by GROUPing all fields in the query to create distinct results.
4.3. Avoid DISTINCT in SQL Queries
Instead of:
use:

In some databases, this type of queries are inefficient as it first creates temp data with all possible options (most probably CROSS JOIN) and then it applies WHERE conditions.
4.4. Create Joins with INNER JOIN Rather than WHERE
Instead of:
use:
In SQL Server, they are equivalent

Practice to create clustered and non-clustered index since indexes helps in to access data fastly.
But be careful, more indexes on a table will slow the INSERT, UPDATE, DELETE operations.
Hence try to keep small no of indexes on a table.
4.5. Create Clustered and Non-Clustered Indexes
Example. Optimize performance of the query
SELECT
SalesId, ProductId, Quantity
FROM Sales
WHERE ProductId = 1;
Steps:
Check indexes on the Sales table
Simplified query without non-clustered indexes
Add non-clustered index on ProductId
Add new Quantity field in SELECT
Include columns

EXEC sp_helpindex 'Sales'
Check indexes on the Sales table
Example

2. Simplified query without non-clustered indexes
Example
SELECT SalesId, ProductId
FROM Sales
WHERE ProductId = 1;

3. Add non-clustered index on ProductId
Example
CREATE INDEX IX_Sales_ProductID
ON Sales(ProductID);
EXEC sp_helpindex 'Sales'
SELECT SaleId, ProductId
FROM Sales
WHERE ProductId = 1;

4. Add new Quantity field in SELECT
Example
SELECT SaleId, ProductId, Quantity
FROM Sales
WHERE ProductId = 1;
Right click – Missing Index Details

5. Include columns
Example
DROP INDEX IX_Sales_ProductID
ON Sales(ProductID);
CREATE INDEX IX_Sales_ProductID_Inc
ON Sales (ProductId)
INCLUDE (SaleId,Quantity)
SELECT SaleId, ProductId, Quantity
FROM Sales
WHERE ProductId = 1;

6. Add column and condition
Example (extension)
CREATE INDEX IX_Sales_Sale_date
ON Sales (Sale_date)
SELECT SaleId, Sale_date, ProductId, Quantity
FROM Sales
WHERE ProductId = 1 AND Sale_date='01.01.2018';

6. Add column and condition
Example (extension)
CREATE INDEX IX_Sales_Sale_date
ON Sales (Sale_date)
SELECT SaleId, Sale_date, ProductId, Quantity
FROM Sales
WHERE ProductId = 1 AND Sale_date='01.01.2018';

6. Add column and condition
Example (extension- from tips)
DROP INDEX IX_Sales_Sale_date
ON Sales;
SELECT SaleId, Sale_date, ProductId, Quantity
FROM Sales
WHERE ProductId = 1 AND Sale_date='01.01.2018';
CREATE INDEX IX_Sales_Sale_date_ProductId
ON Sales ([Sale_date],[ProductId])