Ms sql запросы: Подключение к экземпляру SQL Server и выполнение запросов с помощью SQL Server Management Studio (SSMS) — SQL Server Management Studio (SSMS)

Как просмотреть историю запросов в SQL Server Management Studio

Историю запросов можно просмотреть с помощью системных представлений:

1. sys.dm_exec_query_stats
2. sys.dm_exec_sql_text
3. sys.dm_exec_query_plan

Например, с помощью следующего запроса:

select  top(100)
        creation_time,
        last_execution_time,
        execution_count,
        total_worker_time/1000 as CPU,
        convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
        qs.total_elapsed_time/1000 as TotDuration,
        convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],
        total_logical_reads as [Reads],
        total_logical_writes as [Writes],
        total_logical_reads+total_logical_writes as [AggIO],
        convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0)) as [AvgIO],
        [sql_handle],
        plan_handle,
        statement_start_offset,
        statement_end_offset,
        plan_generation_num,
        total_physical_reads,
        convert(money, total_physical_reads/(execution_count + 0.0)) as [AvgIOPhysicalReads],
        convert(money, total_logical_reads/(execution_count + 0.0)) as [AvgIOLogicalReads],
        convert(money, total_logical_writes/(execution_count + 0.0)) as [AvgIOLogicalWrites],
        query_hash,
        query_plan_hash,
        total_rows,
        convert(money, total_rows/(execution_count + 0.0)) as [AvgRows],
        total_dop,
        convert(money, total_dop/(execution_count + 0.0)) as [AvgDop],
        total_grant_kb,
        convert(money, total_grant_kb/(execution_count + 0.0)) as [AvgGrantKb],
        total_used_grant_kb,
        convert(money, total_used_grant_kb/(execution_count + 0.0)) as [AvgUsedGrantKb],
        total_ideal_grant_kb,
        convert(money, total_ideal_grant_kb/(execution_count + 0.0)) as [AvgIdealGrantKb],
        total_reserved_threads,
        convert(money, total_reserved_threads/(execution_count + 0.0)) as [AvgReservedThreads],
        total_used_threads,
        convert(money, total_used_threads/(execution_count + 0.0)) as [AvgUsedThreads],
        case 
            when sql_handle IS NULL then ' '
            else(substring(st.text,(qs.statement_start_offset+2)/2,(
                case
                    when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2      
                    else qs.statement_end_offset    
                end - qs.statement_start_offset)/2  ))
        end as query_text,
        db_name(st.dbid) as database_name,
        object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as [object_name],
        sp.[query_plan]
from sys.dm_exec_query_stats as qs with(readuncommitted)
cross apply sys.dm_exec_sql_text(qs.[sql_handle]) as st
cross apply sys.dm_exec_query_plan(qs.[plan_handle]) as sp
WHERE st.[text] LIKE '%query%'
 
 

Текущие запущенные запросы можно просмотреть с помощью следующего скрипта:

select ES.[session_id]
      ,ER.[blocking_session_id]
      ,ER.[request_id]
      ,ER.[start_time]
      ,DateDiff(second, ER.[start_time], GetDate()) as [date_diffSec]
      , COALESCE(
                    CAST(NULLIF(ER.[total_elapsed_time] / 1000, 0) as BIGINT)
                   ,CASE WHEN (ES.[status] <> 'running' and isnull(ER.[status], '')  <> 'running') 
                            THEN  DATEDIFF(ss,0,getdate() - nullif(ES.[last_request_end_time], '1900-01-01T00:00:00.000'))
                    END
                ) as [total_time, sec]
      , CAST(NULLIF((CAST(ER.[total_elapsed_time] as BIGINT) - CAST(ER.[wait_time] AS BIGINT)) / 1000, 0 ) as bigint) as [work_time, sec]
      , CASE WHEN (ER.[status] <> 'running' AND ISNULL(ER.[status],'') <> 'running') 
                THEN  DATEDIFF(ss,0,getdate() - nullif(ES.[last_request_end_time], '1900-01-01T00:00:00.000'))
        END as [sleep_time, sec] --Время сна в сек
      , NULLIF( CAST((ER.[logical_reads] + ER.[writes]) * 8 / 1024 as numeric(38,2)), 0) as [IO, MB]
      , CASE  ER.transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'ReadUncommited'
        WHEN 2 THEN 'ReadCommited'
        WHEN 3 THEN 'Repetable'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot'
        END as [transaction_isolation_level_desc]
      ,ER.[status]
      ,ES.[status] as [status_session]
      ,ER.[command]
      ,ER.[percent_complete]
      ,DB_Name(coalesce(ER.[database_id], ES.[database_id])) as [DBName]
      , SUBSTRING(
                    (select top(1) [text] from sys.dm_exec_sql_text(ER.[sql_handle]))
                  , ER.[statement_start_offset]/2+1
                  , (
                        CASE WHEN ((ER.[statement_start_offset]<0) OR (ER.[statement_end_offset]<0))
                                THEN DATALENGTH ((select top(1) [text] from sys.dm_exec_sql_text(ER.[sql_handle])))
                             ELSE ER.[statement_end_offset]
                        END
                        - ER.[statement_start_offset]
                    )/2 +1
                 ) as [CURRENT_REQUEST]
      ,(select top(1) [text] from sys.dm_exec_sql_text(ER.[sql_handle])) as [TSQL]
      ,(select top(1) [objectid] from sys.dm_exec_sql_text(ER.[sql_handle])) as [objectid]
      ,(select top(1) [query_plan] from sys.dm_exec_query_plan(ER.[plan_handle])) as [QueryPlan]
      ,NULL as [event_info]--(select top(1) [event_info] from sys.dm_exec_input_buffer(ES.[session_id], ER.[request_id])) as [event_info]
      ,ER.[wait_type]
      ,ES.[login_time]
      ,ES.[host_name]
      ,ES.[program_name]
      ,cast(ER.[wait_time]/1000 as decimal(18,3)) as [wait_timeSec]
      ,ER.[wait_time]
      ,ER.[last_wait_type]
      ,ER.[wait_resource]
      ,ER.[open_transaction_count]
      ,ER.[open_resultset_count]
      ,ER.[transaction_id]
      ,ER.[context_info]
      ,ER.[estimated_completion_time]
      ,ER.[cpu_time]
      ,ER.[total_elapsed_time]
      ,ER.[scheduler_id]
      ,ER.[task_address]
      ,ER.[reads]
      ,ER.[writes]
      ,ER.[logical_reads]
      ,ER.[text_size]
      ,ER.[language]
      ,ER.[date_format]
      ,ER.[date_first]
      ,ER.[quoted_identifier]
      ,ER.[arithabort]
      ,ER.[ansi_null_dflt_on]
      ,ER.[ansi_defaults]
      ,ER.[ansi_warnings]
      ,ER.[ansi_padding]
      ,ER.[ansi_nulls]
      ,ER.[concat_null_yields_null]
      ,ER.[transaction_isolation_level]
      ,ER.[lock_timeout]
      ,ER.[deadlock_priority]
      ,ER.[row_count]
      ,ER.[prev_error]
      ,ER.[nest_level]
      ,ER.[granted_query_memory]
      ,ER.[executing_managed_code]
      ,ER.[group_id]
      ,ER.[query_hash]
      ,ER.[query_plan_hash]
      ,EC.[most_recent_session_id]
      ,EC.[connect_time]
      ,EC.[net_transport]
      ,EC.[protocol_type]
      ,EC.[protocol_version]
      ,EC.[endpoint_id]
      ,EC.[encrypt_option]
      ,EC.[auth_scheme]
      ,EC.[node_affinity]
      ,EC.[num_reads]
      ,EC.[num_writes]
      ,EC.[last_read]
      ,EC.[last_write]
      ,EC.[net_packet_size]
      ,EC.[client_net_address]
      ,EC.[client_tcp_port]
      ,EC.[local_net_address]
      ,EC.[local_tcp_port]
      ,EC.[parent_connection_id]
      ,EC.[most_recent_sql_handle]
      ,ES.[host_process_id]
      ,ES.[client_version]
      ,ES.[client_interface_name]
      ,ES.[security_id]
      ,ES.[login_name]
      ,ES.[nt_domain]
      ,ES.[nt_user_name]
      ,ES.[memory_usage]
      ,ES.[total_scheduled_time]
      ,ES.[last_request_start_time]
      ,ES.[last_request_end_time]
      ,ES.[is_user_process]
      ,ES.[original_security_id]
      ,ES.[original_login_name]
      ,ES.[last_successful_logon]
      ,ES.[last_unsuccessful_logon]
      ,ES.[unsuccessful_logons]
      ,ES.[authenticating_database_id]
      ,ER.[sql_handle]
      ,ER.[statement_start_offset]
      ,ER.[statement_end_offset]
      ,ER.[plan_handle]
      ,NULL as [dop]--ER.[dop]
      ,coalesce(ER.[database_id], ES.[database_id]) as [database_id]
      ,ER.[user_id]
      ,ER.[connection_id]
from sys.dm_exec_requests ER with(readuncommitted)
right join sys.dm_exec_sessions ES with(readuncommitted)
on ES.session_id = ER.session_id 
left join sys.dm_exec_connections EC  with(readuncommitted)
on EC.session_id = ES.session_id
where ER.[status] in ('suspended', 'running', 'runnable')
or exists (select top(1) 1 from sys.dm_exec_requests as ER0 where ER0.[blocking_session_id]=ES.[session_id])
 
  
 
 
 
 

Этот запрос отображает все активные запросы и все те запросы, которые явно блокируют активные запросы.

Все эти и другие полезные скрипты реализованы в виде представлений в базе данных SRV, которая распространяется свободно. Например, первый сценарий был получен из представления [inf].[vBigQuery] , а второй-из представления [inf].[vRequests] .

Существуют также различные сторонние решения для истории запросов. Я использую диспетчер запросов от Dbeaver : и История выполнения запросов из инструментов SQL , которая встроена в SSMS :

Пример создания запроса (Query) в базе данных MS SQL Server. База данных размещена в локальном файле *.mdf

Пример создания запроса (Query) в базе данных MS SQL Server. База данных размещена в локальном файле *.mdf

Содержание

Поиск на других ресурсах:

Условие задачи

Задана база данных, которая размещается в файле Education.mdf. База данных содержит две связанные между собою таблицы Student и Session.

Таблицы связаны между собою за полем ID_Book.

Используя средства Microsoft Visual Studio создать запрос с именем Query1, который будет иметь следующую структуру:

Выполнение (пошаговая инструкция)

1. Загрузить Microsoft Visual Studio

2. Подключить базу данных Education.mdf к перечню баз данных утилиты Server Explorer

Чтобы не тратить время на разработку и связывание таблиц базы данных Education.mdf, архив ранее подготовленной базы данных можно загрузить

Подключение базы данных реализуется одним из нескольких стандартных способов:

• выбором команды «Connect to Database…» с меню Tools;
• выбором кнопки (команды) «Connect to Database…» из утилиты Server Explorer.

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

Рис. 1. Способы добавления/подключения базы данных

Подробное описание того, как осуществляется подключение базы данных типа Microsoft SQL Server в Microsoft Visual Studio, приведено в теме:

После подключения, окно утилиты Server Explorer будет выглядеть как показано на рисунке 2.

Рис. 2. Утилита Server Explorer с подключенной базой данных Education.mdf

3. Добавление нового запроса. Команда «New Query»

К базе данных можно создавать запросы. В нашем случае нужно создать запрос в соответствии с условием задачи.

Запрос создается с помощью команды «New Query», которая вызовется из контекстного меню (рисунок 3). Чтобы вызвать команду, достаточно сделать клик правой кнопкой мышки в области поля, которое выделено для отображения элементов базы данных Education.mdb. Следует отметить, что запросы не сохраняются системой. Для отображения сохраненных (сложных) запросов используется представление (Views).

На рисунке 3 отображено контекстное меню, которое вызывается при нажатии на вкладке Views (представление). В этом меню нужно выбрать команду «New Query». Эта команда есть в перечне контекстных меню других составляющих базы данных (таблиц, диаграмм и т.п.).

Рис. 3. Команда New Query

В результате откроется окно «Add Table», в котором нужно выбрать таблицы, данные из которых будут использоваться в запросе (рисунок 4).

Рис. 4. Выбор таблиц, которые будут использоваться в запросе

Для нашего случая нужно выбрать обе таблицы.

В результате окно Microsoft Visual Studio будет выглядеть, как показано на рисунке 5.

Рис. 5. Окно MS Visual Studio после создания запроса

В таблицах нужно выделить поля, которые будут использоваться в запросе. Порядок выбора полей должен соответствовать отображению их в запросе в соответствии с условием задачи. Это означает, что сначала выбираются поля таблицы Student (NumBook, Name), а потом выбираются поля таблицы Session (Mathematics, Informatics, Philosophy).

Для нашего случая выбор полей изображен на рисунке 6.

Рис. 6. Выбор полей для запроса

Как видно из рисунка 6, в нижней части окна отображается запрос на языке SQL, сформированный системой

SELECT  Student.Num_Book, Student.Name, Session.Mathematics,
        Session.Informatics, Session.Philosophy
FROM    Session INNER JOIN
          Student ON Session.ID_Book = Student.ID_Book

4. Добавление вычисляемого поля Average

Чтобы создать вычисляемое поле Average, нужно в окне, где отображается SQL-запрос изменить текст этого запроса. Например:

SELECT  Student.Num_Book, Student.Name, Session.Mathematics, Session.Informatics, Session.Philosophy,
        (Session.Mathematics + Session.Informatics + Session.Philosophy) / 3.0 AS Average
FROM  Session INNER JOIN
        Student ON Session.ID_Book = Student.ID_Book

Добавляется вычислительное поле Average, которое есть средним арифметическим (рисунок 7).

Рис. 7. Добавление вычисляемого поля Average

5. Запуск запроса на выполнение. Команда «Execute SQL»

Чтобы запустить на выполнение запрос, используется кнопка «Execute SQL» (рисунок 8).

В нижней части окна на рисунке 8 изображен результат выполнения запроса.

Рис. 8. Кнопка «Execute SQL» запуска запроса на выполнение и результат выполнения запроса

Другой способ запуска запроса на выполнение – команда «Execute SQL» из меню «Query Designer».

Связанные темы

Методы получения плана запроса в СУБД MS SQL Server — AUsevich

Планом запроса называется последовательность логических и физических операторов, которые должны быть выполнены СУБД для того чтобы получить результат SQL-запроса. Получение плана запроса может потребоваться в различных ситуациях, а в первую очередь при задачах оптимизации запросов.

Я хочу описать три способа получить план запроса используя инструменты СУБД MS SQL Server, каждый из способов может оказаться предпочтительнее в той или иной ситуации.

Получение плана запроса с помощью Profiler’а (или Extended Events)

Получение плана запроса с помощью Profiler’а (или нового механизма — Extended Events) является, наверное, наиболее популярным способом и причина заключается в том, что с помощью Profiler’а можно перехватить информацию о любом запросе, исполняемом в данный момент экземпляром SQL Server’а. Типичным, с целью получения плана запроса, является приведенный ниже способ работы с Profiler’ом (в связке с 1С:Предприятие):

1. Настроить Profiler (или Extended Events) и поставить его на паузу
2. Если трассировка содержит данные, очистить окно трассировки
3. С помощью отладчика в 1С:Предприятие остановиться перед выполнением необходимого запроса
4. Снять Profiler с паузы
5. Выполнить запрос
6. Остановить Profiler
7. Проанализировать полученную информацию

Profiler является инструментом для создания трассировок и управления ими. С помощью данного инструмента можно получать различную информацию о работе СУБД. В данном случае мы будем использовать его для получения информации о выполняемом запросе, в частности, для того чтобы получить его план выполнения.

Для того чтобы получить план запроса в Profiler следует добавить следующие события:

Также можно добавить необходимые отборы: по имени/идентификатору базы данных, номеру соединения, длительности выполнения.

Более подробная информация о работе с профайлером приведена в статье «Работа с Profiler SQL Server. Примеры настройки трассировок».

Получение кэшированного плана запроса с помощью динамической функции

Перед выполнением запроса СУБД проверяет наличие актуального кэшированного плана запроса. Если такой план запроса существует, тогда СУБД использует его, а не компилирует план запроса заново. Это позволяет сократить время выполнения запроса и именно поэтому, после выполнения очистки процедурного кэша, запросы выполняются дольше (происходит компиляция плана запроса). Таким образом, если мы знаем текст искомого запроса, мы можем получить его план из кэша (если он есть в кэше). Для этого необходимо обратиться к следующим динамическим функциям:

Для поиска плана запроса необходимо выполнить в SQL Server Management Studio нижеприведенный запрос (также доступен во вложении к статье), при этом изменив условия отбора:

SELECT TOP 20 qs.last_execution_time AS Last_execution_time, SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END — qs.statement_start_offset)/2) + 1) AS Query_text, qp.query_plan AS Query_plan, qs.execution_count AS Execution_count FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp WHERE qs.last_execution_time > ‘2016-08-01 11:30:00.000’ /* 1. Date & Time filter */ and qt.text like ‘%FROM dbo._AccumRg17539 T1%’ /* 2. SQL query text filter */ and qt.text not like ‘%Query Finder%’ /* 3. Special condition */

В запросе добавлены условия по:

1. Времени последнего выполнения
2. Тексту искомого запроса (таких фильтров можно добавить несколько, уточняя результат поиска)
3. Специальное условие для того чтобы сам запрос поиска не попадал в результат поиска (менять не надо)

Результатом запроса будет таблица с колонками: Last_execution_time (последнее время выполнения), Query_text (текст SQL-запроса), Query_plan (План SQL-запроса) и Execution_count (количество выполнений).

Щелкнув в Management Studio по ссылке в колонке Query_plan будет открыто графическое представление плана запроса.

Получение плана запроса в Management Studio

Еще один способ получить план запроса — выполнить SQL-запрос непосредственно в Management Studio. Специально для этих целей в командном меню «Запрос» (Query) присутствует два пункта:

1. Показать предполагаемый план выполнения (Display Estimated Execution Plan)
2. Включить действительный план выполнения (Include Actual Execution Plan)

Для получения предполагаемого плана необходимо ввести текст запроса и нажать на пункт «Показать предполагаемый план выполнения», выполнять сам запрос не требуется. Получение актуального плана непосредственно связано с выполнением запроса, поэтому пункт «Включить действительный план выполнения» включает/отключает возможность вывода информации об актуальном плане запроса. Таким образом, для получения действительного плана выполнения необходимо включить вышеуказанную опцию и выполнить запрос.

Программный комплекс «Оптимизатор запросов в MS SQL 2005»   

Для системы 1С:УПП 8.1 и других подобных «тяжелых» конфигураций очень актуальна задача оптимизации неэффективных запросов. В результате на практике оптимизация запросов на MS SQL 1C 8.1 и 1С 8.2 — это тяжелая, кропотливая работа, требующая высокой квалификации. Компания СофтПоинт предлагает новый продукт «Оптимизатор запросов в MS SQL 2005», который позволяет максимально упростить процесс оптимизации запросов для MSSQL 2005 1С 8.1 и 1С 8.2.

Конкурентные преимущества «Оптимизатора запросов в MS SQL 2005»:

1. Возможно оценить вклад каждого запроса в общую нагрузку SQL сервера. Группировка возможна, как на уровне номера строки модуля 1С, так и на уровне выполнения различных планов запросов.

2. Возможно оценить эффект оптимизации в динамике. Оптимизированные запросы изменят статистику распределения нагрузки. Эти изменения во времени можно будет наблюдать в удобном графическом представлении.

3. Возможно оценить эффект от того или иного индекса и понять в каких конкретно запросах он сработает.

4. Можно найти «узкие» места в запросах с детализацией до «узла» плана выполнения и предикатов. Группировку можно сделать также в разрезе объектов 1С и возможность в графическом интерфейсе просматривать план выполнения с названиями объектов 1С.

5. «Оптимизатор запросов в MS SQL 2005» не только понимает, какой индекс необходимо добавить, но и дает рекомендации по оптимальному синтаксису и оптимальной структуре запросов.

6. Сбор данных информации не нагружает рабочую систему (доп. нагрузка не более 5 %).

7. Анализ селективности получаемой информации пользователями (анализ замера «рекордсетов»).

Детальное описание каждого из преимуществ «Оптимизатора запросов в MS SQL 2005»:

Эффективная оценка вклада каждого запроса в общую нагрузку SQL сервера.

«Оптимизатор запросов в MS SQL 2005» легко интегрируется с системой мониторинга производительности «PerfEpxert»,  что в комплексе позволяет отображать и анализировать важную информацию для разработчиков 1С по оптимальным запросы SQL, и знать, где именно в 1С они вызываются, а также какой вклад дают в общую нагрузку. Увидев статистику нагрузки на сервер по CPU, reads и т.п. в разрезе Номеров строк и модулей 1С, разработчик четко будет понимать, что именно в 1С необходимо оптимизировать и какой это даст эффект.

В ряде случаев одна и та же строка 1С может формировать достаточно сильно отличающие запросы. Это происходит потому, как конструктор запросов формирует запрос динамически. Дополнительные условия фильтрации могут сильно изменить план его выполнения. Система сбора информации позволяет группировать нагрузку также в разрезе планов выполнения.

Пример: У одного из клиентов бухгалтерский запрос работал с не оптимальными планами выполнения, отличающимися друг от друга. Проведенный анализ показал, что основной вклад дает определенная строка модуля 1С, формирующая бухгалтерский запрос к счету товарных остатков. Просмотр плана выполнения запроса на первый взгляд не показал явных ошибок. Анализ выполненный с помощью «Оптимизатора запросов в MS SQL 2005» показал, что большую часть нагрузки дает узел плана выполнения с поиском по индексу. При этом количество строк возвращаемых узлом было большим и недостаточно селективным. В данном случае гораздо лучше себя вел тот же самый запрос с сканированием индекса, а не поиском по нему. В результате задача достаточно быстро решилась добавлением предварительной проверки условий даты отбора. И при определенных значения интервала не являющимся достаточно селективным условие — это вообще исключалось из SQL конструкции (переносилась проверка в клиентскую часть). Хотя, конечно, в данном случае имелась определенная специфика работы планировщика запросов MS SQL. 

Оценка оптимизации в динамике.

Анализируя эффект от оптимизации необходимо видеть его в динамике. Важно проводить анализ с по нагрузке за определенный интервал времени. 

Эффективная оптимизации в расстановке индексов.

На практике недостаточно просто понимать что дополнительный индекс улучшит скорость запроса и структуру плана. Важно понимать в каких именно запросах сработает индекс. В некоторых случаях полезно понимать с детализацией до узла плана выполнения. Очень важно заранее знать о том насколько станет лучше, потому как перестройка индексов на больших БД задача достаточно трудоемкая, да и само обслуживание индексов приводит к дополнительной нагрузке.

Эффективность группировки нагрузки на систему в разрезе узлов планов запросов.

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

Эффективность оптимизации индексов и запросов (на уровне синтаксиса).

Существующие средства автоматического анализа запросов предполагают рекомендации только в разрезе индексов. К сожалению, с помощью оптимизации индексов или хинтов в ряде случаев добиться существенного эффекта невозможно. «Адвайзер» дает рекомендации не только по созданию индексов, но и рекомендации по улучшению структуры запроса — его синтаксиса.

Эффективный анализ фильтрации при выборке данных.

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

Практически нулевая нагрузка, создаваемая средствами мониторинга, на систему в целом.

Анализ планов выполнения происходит непрерывно на отдельном сервере мониторинга. Механизмы сбора максимально оптимизированы за счет чего дополнительная нагрузка на сервер системой сбора данных составляет не более 5-и процентов.

Обход дерева данных в таблице MS SQL Server (рекурсивный запрос)

Допустим имеется таблица подразделений вуза (tblDepartment) в формате:
intID — strDepartmentName — intParentID
Где,
intID — идентификатор подразделения,
strDepartmentName  — наименование подразделения,
intParentID — идентификатор родительского подразделения.

Корневой элемент будет ссылаться в качестве «родителя» на самого себя.
Пример данных:

WITH Recursive (intID, intParentID, strDepartmentName)

AS

(

    SELECT intID, intParentID, strDepartmentName

    FROM tblDepartment e

    WHERE e.intID = 9 

    UNION ALL

    SELECT e.intID, e.intParentID, e.strDepartmentName

    FROM tblDepartment e

        JOIN Recursive r ON e.intParentID = r.intID

)

SELECT intID, intParentID, strDepartmentName

В строке «WHERE e.intID = 9» указываем идентификатор исходного подразделения.

WITH Recursive (intID, intParentID, strDepartmentName)

AS

(

    SELECT intID, intParentID, strDepartmentName

    FROM tblDepartment e

    WHERE e.intID = 35 

    UNION ALL

    SELECT e.intID, e.intParentID, e.strDepartmentName

    FROM tblDepartment e

        JOIN Recursive r ON e.intID = r.intParentID

        WHERE e.intID <> e.intParentID

)

SELECT top 15 intID, intParentID, strDepartmentName

В строке «WHERE e.intID = 35» указываем идентификатор исходного подразделения.

Если Вам понравилась статья, пожалуйста, поставьте лайк, сделайте репост или оставьте комментарий. Если у Вас есть какие-либо замечания, также пишите комментарии.

Запросы SQL в PowerShell — Get-PowerShell

Посмотрим каким образом можно выполнять различные запросы SQL, такие как Select, Insert, Update, используя PowerShell. Это может быть полезно для интеграции PowerShell с базами данных SQL. Речь пойдет про Access, MS SQL, а так же любые другие СУБД.

Разбирать будем на примере моего скрипта из прошлого поста Чем открыть JSON из приложения проверка чеков? Например, Access! Скрипт можно скачать и ознакомиться с достаточно подробными комментариями.

Создаем подключение

Для начала нам необходимо создать объект подключения к нашей СУБД. Конечно это напрямую зависит от способа подключения к БД — SQLClient, OleDB, ODBC, OracleClient.

Давайте рассмотрим сначала самый универсальный способ — OleDB. Создадим подключение к файлу Access, путь к которому хранится в переменной $AccessFullFilePath.

#Создаем подключение к файлу базы данных $conn = New-Object System.Data.OleDb.OleDbConnection $conn.ConnectionString = «Provider=Microsoft.ACE.OLEDB.16.0.7;Data Source=$AccessFullFilePath;Persist Security Info=False»

Как видно из приведенного примера достаточно создать переменную и присвоить ей новый .Net объект OleDb.OleDbConnection. После чего указать параметр ConnectionString. Более подробно о строке подключения тут.

В общем-то OleDB можно использовать и для подключения к MS SQL серверу, однако в этом случае придется указать имя пользователя и пароль в открытом виде. Поэтому я решил воспользоваться объектом SqlClient.SqlConnection.

Давайте рассмотрим еще один способ — SQLClient. У меня уже есть переменная $credential типа System.Management.Automation.PSCredential. Из этой переменной я получил пароль и имя пользователя. Обратите внимание пароль установлен в режим readonly. После чего я получил объект SqlCredential, который и использовал совместно со строгой подключения

#приглашение на ввод логина и пароля $Credential = Get-Credential #Получаем пароль из объекта System.Management.Automation.PSCredential и делаем его доступны для чтения $password = $Credential.Password $password.makereadonly() #создаем объект SQLCredential $sqlcreds = New-Object System.Data.SqlClient.SqlCredential($Credential.username,$password) #создаем подключение к базе данных и указывает разрешения $conn = New-Object System.Data.SqlClient.SqlConnection $conn.credential = $sqlcreds #указываем строку подключения $conn.ConnectionString = «server=$sqlserver;database=$database;»

Обратите внимание строка подключения в одном параметре, объект Credential в другом.

Подключение к SQL или Access

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

#Открываем базу данных $conn.open() # #тут ваш длинный код по работе с базой данных # #закрываем подключение к БД $conn.close()

Выполнение SQL запроса на получение данных

Если вам нужно получить, какие-либо данные вам необходимо

• Создать команду
• Вставить SQL запрос в команду
• Выполнить запрос, сохранив результат
• Можно построчно читать данные

#создаем команду, которую потом будем несколько раз изменять $cmd=$conn.CreateCommand() #указываем SQL запрос в команде $cmd.CommandText=»select recID,[user],totalsum,datet from receipt» #выполняем и сохраняем результат $result = $cmd.ExecuteReader() #выводим по очереди заголовки столбцов write-host $result.getname(0) $result.getname(1) $result.getname(2) $result.getname(3) #запускаем цикл чтения всех данных while ($result.Read()) { #для получения данных используем функцию getvalue(номер_столбца), указывая номер столбца write-host $result.Getvalue(0) $result.GetValue(1) $result.GetValue(2) $result.GetValue(3) }

Обратите внимание, каждый раз вызывая Read() мы переходим к новой строчке в нашей таблице. Для чтения заголовков столбцов используем GetName() и указываем номер столбца. Для чтения значений используем GetValue() так же с указанием столбца. Вместо GetValue() можно использовать GetDateTime(), GetDouble(), GetGuid(), GetInt32(). Подробнее об использовании DataReader можно почитать на сайте Microsoft.

Выполняем SQL запрос для изменения данных

Если мы желаем использовать запросы, которые не возвращают нам таблицы: DDL, DCL, некоторые команды DML и т.п. В этом случае мы используем не ExecuteReader(), а ExecuteNonQuery() и конечно же нам уже не нужно так сильно анализировать результат.

#Формируем запрос на вставку чека $cmd.CommandText=»INSERT INTO Receipt (cashTotalSum,dateT) values ($($Receipt.cashTotalSum/100),’$($Receipt.dateTime -replace ‘(….)-(..)-(..)T(……..)’,’$3.$2.$1 $4′)’)» #выполняем запрос $cmd.ExecuteNonQuery()

Т.е. тут особых действий применять не нужно. Вставили SQL запрос и выполнили команду. Если все успешно, то будет 1, если команда вышла с ошибками, то — 0.

Успехов вам в ваших скриптах.

Заметки Дмитрия Пилюгина о Microsoft SQL Server

Введние

SQL запрос описывает результат, который необходимо получить, но не способ его получения. Набор конкретных шагов, которые сервер должен предпринять, чтобы вернуть результат называется планом выполнения запроса, его построением занимается оптимизатор. От выбора плана зависит скорость выполнения, поэтому, он является одним из самых важных элементов при анализе проблем производительности запроса.

План выполнения состоит из операторов и их свойств, связанных между собой в древовидную структуру, каждый из которых отвечает за определенную логическую и физическую операцию. Все вместе они обеспечивают получения того результата, который описан текстом запроса. Внутри сервера операторы представляют собой объекты классов в памяти SQL Server. Пользователи сервера, т.е. мы с вами, видим их описание, сформированное в виде документа XML с определенной схемой, и отображаемое средой SQL Server Management Studio (SSMS) в графическом виде.

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

Версия сервера

Иногда встречаются ситуации, когда на форумах просят уточнить версию сервера, даже если выложен план запроса в правильном sqlplan (XML) формате. Вместо этого, можно сэкономить время и открыть план как XML, в самом первом элементе, который описывает план, вы увидите версию сервера в свойстве Build.

Таким образом нельзя увидеть полную информацию, например о редакции сервера, но информации о версии в большинстве случаев хватает, чтобы понять с чем мы имеем дело.

Число строк в таблицах

Второй частый вопрос, это «сколько строк у вас в таблице». Эту информацию можно также узнать из плана запроса (начиная с 2008 версии сервера). Для этого необходимо выбрать оператор доступа к данным (Scan или Seek) интересующей таблицы и посмотреть свойство TableCardinality для уточнения числа строк. Также может быть интересно свойство Estimated Row Size для уточнения размера строки и приблизительной оценки размеров таблицы или индекса (при условии, что таблица не сжата).

Уточню, что это не действительное число строк, которые есть в таблице, а данные из объектов статистки. Однако, это те самые данные на основании которых оптимизатор принимает решения при построении плана.

Контекст

План запроса сохраняет значимые настройки SET, для которых был построен, для того, чтобы их посмотреть, нужно выбрать в плане корневой элемент и открыть свойство Set Options. Например, так можно узнать, был ли план построен при включенной опции ARITHABORT (различие в этой настройке часто приводит к двум разным планам и ситуации с плохим прослушиванием параметров, описанной в статье Медленно в приложении, быстро в SSMS).

Количество CPU

Можно узнать число процессоров, доступных оптимизатору, для этого, в том же корневом элементе нужно открыть свойство OptimizerHardwareDependentProperties -> EstimatedAvailableDegreeOfParallelism и умножить его на 2. Исключение, если доступен всего один процессор, умножать на 2 не нужно.
Например, в моем случае:

2*2 = 4, доступно 4 CPU, действительно у меня на машине 4 ядерный процессор и все 4 ядра доступны серверу. Эта информация может дать понимание о том, на какой машине получен план.

Версия Cardinality Estimator

Начиная с SQL Server 2014 доступны несколько версий механизма оценки числа строк (Cardinality Estimator) оптимизатором, от этого механизма зависит стоимость операторов и большинство решений оптимизатора при выборе плана. Версию Cardinality Estimator можно посмотреть в свойстве корневого оператора CardinalityEstimationModelVersion.

• 70 – SQL Server
• 120 – SQL Server 2014
• 130 – SQL Server 2016
• 140 – SQL Server vNext

Время выполнения запроса и ожидания

Начиная с SQL Server 2016 SP1, в действительном плане запроса доступна информация о времени выполнения и времени процессора, для этого в корневом элементе нужно раскрыть свойство QueryTimeStats и посмотреть значения CpuTime и ElapsedTime. Теперь не будет необходимости отдельно включать сбор времени выполнения или спрашивать «сколько выполнялся запрос» — все уже есть в плане.

Второе значимое улучшение это top 10 самых длинных ожиданий во время выполнения запроса. Для этого, в том же корневом элементе необходимо открыть свойство WaitStats. Это добавление, позволяет получить более полную картину того, почему запрос выполнялся медленно и существенно упрощает диагностику.

Типы параметров

Свойство Parameter List, в котором перечислялись используемые в запросе параметры, присутствовало в плане давно, однако, начиная с SQL Server 2016 SP1, в определение параметров было добавлено свойство Parameter Data Type, в котором хранится тип данных параметра. Это может быть полезно для понимания проблем с преобразованиями типов.

Число прочитанных строк и Оценочное число строк к прочтению

В плане запроса есть два очень важных свойства Actual Number Of Rows и Estimated Number of Rows, действительное и оценочное число строк. Эти свойства, содержат информацию только о том, сколько строк вернул оператор чтения данных, но не сколько строк он действительно прочитал. Свойства Number of Rows Read и Estimated Number of Rows to be Read отвечают как раз на этот вопрос и позволяют понять, сколько строк в действительности прочитал или собирается прочитать сервер. Свойство ActualRowsRead (Number of Rows Read в SSMS) доступно начиная с SQL Server 2012 SP3, 2014 SP2, 2016 SP1. Свойство EstimatedRowsRead (Estimated Number of Rows to be Read в SSMS) доступно начиная с SQL Server 2016 SP1.

Статистика IO и времени выполнения по операторам

Еще несколько очень полезных свойств, которые появились в SQL Server 2016 , 2014 SP2 и доступны в действительном плане запроса, это метрики IO (если у оператора есть IO) – Actual IO Statistics, CPU и времени выполнения – Actual Time Statistics, а также памяти (начиная с 2016 SP1, если оператор требует память).
Свойства включают в себя следующие новые метрики, которые могут быть разделены по потокам, в случае параллельного плана:

• ActualElapsedms
• ActualCPUms
• ActualScans
• ActualLogicalReads
• ActualPhysicalReads
• ActualReadAheads
• ActualLobLogicalReads
• ActualLobPhysicalReads
• ActualLobReadAheads
• InputMemoryGrant
• OutputMemoryGrant
• UsedMemoryGrant

Как видно из этого списка, теперь можно получить исчерпывающую информацию о времени выполнения того или иного оператора, затратах IO и памяти. В последних версиях SSMS эти метрики отражаются в окне свойств, если вы используете старую версию SSMS – вы можете посмотреть их, открыв план как XML. На мой взгляд, теперь открыты все пути к тому, чтобы показывать проценты в плане запроса не по оценочной стоимости, а по реальным затратам ресурсов (я создал предложение на Connect, если вам нравится эта идея, голосуйте).

Сведения о включенных флагах трассировки

Флаги трассировки в SQL Server являются своеобразными «переключателями» поведения сервера с поведения по умолчанию, на какое-либо другое, отличающееся. Начиная с SQL Server 2014 SP2 и 2016 SP1, информация о включенных флагах трассировки доступна в свойстве TraceFlags корневого элемента, она может включать до 100 одновременно включенных флагов в момент построения плана.

Информация о сливе данных в tempdb

Некоторые операторы плана, например, такие как Sort или Hash Match, требуют память во время выполнения запроса, однако, количество памяти рассчитывается в момент компиляции. В силу разных причин (например, неверной оценки числа предполагаемых строк), количество памяти может быть посчитано неверно. Если памяти было выделено меньше, чем нужно для выполнения, сервер вынужден сливать данные в tempdb (spill to tempdb), что замедляет выполнение запроса. Предупреждение о такой ситуации появилось в 2012 сервере, но начиная с SQL Server 2012 SP3, 2014 SP2, 2016 диагностическая информация была расширена и теперь включает в себя объем слитых и прочитанных данных, так что можно оценить степень бедствия и принять адекватные меры.

Заключение

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

запросов — SQL Server | Документы Microsoft

• 19.03.2021
• 2 минуты на чтение

В этой статье

Применимо к: SQL Server (все поддерживаемые версии) База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics Platform System (PDW)

Data Manipulation Language (DML) — это словарь, используемый для извлечения и работы с данными в SQL Server и базе данных SQL.Большинство из них также работают в системе Azure Synapse Analytics и Analytics Platform System (PDW) (подробные сведения см. В каждом отдельном заявлении). Используйте эти операторы для добавления, изменения, запроса или удаления данных из базы данных SQL Server.

В этом разделе

В следующей таблице перечислены операторы DML, которые использует SQL Server.

В следующей таблице перечислены предложения, которые используются в нескольких операторах или предложениях DML.

Обзор: построитель запросов для Microsoft SQL Server

 ВЫБРАТЬ ТОП 20
p.ModifiedDate AS дата,
p.Name, sd.OrderQty
ОТ Sales.SalesOrderDetail sd
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Производство. Продукт p
НА sd.ProductID = p.ProductID
ЗАКАЗАТЬ ПО sd.OrderQty DESC

 nmap -p 1433 --script ms-sql-query --script-args mssql.username = sa, mssql.password = sa, ms-sql-query.query = "SELECT * FROM master..syslogins" 
 

 | мс-sql-запрос:
| [192.168.100.25 \ MSSQLSERVER]
| Запрос: SELECT @@ version version
| версия
| =======
| Microsoft SQL Server 2005 - 9.00.3068.00 (Intel X86)
| 26 февраля 2008 18:15:01
| Авторское право (c) 1988-2005 Microsoft Corporation
| _ Express Edition в Windows NT 5.2 (сборка 3790: пакет обновления 2)
 

  с s как (
выбрать верх (100)
creation_time,
last_execution_time,
Execution_count,
total_worker_time / 1000 как CPU,
convert (money, (total_worker_time)) / (execution_count * 1000) как [AvgCPUTime],
qs.total_elapsed_time / 1000 как TotDuration,
convert (money, (qs.total_elapsed_time)) / (execution_count * 1000) как [AvgDur],
total_logical_reads как [Прочитано],
total_logical_writes как [Записывает],
total_logical_reads + total_logical_writes как [AggIO],
конвертировать (деньги, (total_logical_reads + total_logical_writes) / (execution_count + 0.0)) как [AvgIO],
[sql_handle],
plan_handle,
statement_start_offset,
statement_end_offset,
plan_generation_num,
total_physical_reads,
convert (деньги, total_physical_reads / (execution_count + 0.0)) как [AvgIOPhysicalReads],
convert (money, total_logical_reads / (execution_count + 0.0)) как [AvgIOLogicalReads],
convert (money, total_logical_writes / (execution_count + 0.0)) как [AvgIOLogicalWrites],
query_hash,
query_plan_hash,
total_rows,
convert (money, total_rows / (execution_count + 0.0)) как [AvgRows],
total_dop,
convert (money, total_dop / (execution_count + 0.0)) как [AvgDop],
total_grant_kb,
convert (money, total_grant_kb / (execution_count + 0.0)) как [AvgGrantKb],
total_used_grant_kb,
convert (money, total_used_grant_kb / (execution_count + 0.0)) как [AvgUsedGrantKb],
total_ideal_grant_kb,
convert (money, total_ideal_grant_kb / (execution_count + 0.0)) как [AvgIdealGrantKb],
total_reserved_threads,
convert (money, total_reserved_threads / (execution_count + 0.0)) как [AvgReservedThreads],
total_used_threads,
convert (money, total_used_threads / (execution_count + 0.0)) как [AvgUsedThreads]
из sys.dm_exec_query_stats как qs с (readuncommitted)
заказ по преобразованию (деньги, (qs.total_elapsed_time)) / (execution_count * 1000) desc
)
Выбрать
s.creation_time,
с.last_execution_time,
s.execution_count,
s.CPU,
s. [AvgCPUTime],
s.TotDuration,
s. [AvgDur],
s. [AvgIOLogicalReads],
s. [AvgIOLogicalWrites],
s. [AggIO],
s. [AvgIO],
s. [AvgIOPhysicalReads],
s.plan_generation_num,
s. [AvgRows],
s. [AvgDop],
s. [AvgGrantKb],
s. [AvgUsedGrantKb],
s. [AvgIdealGrantKb],
s. [AvgReservedThreads],
s. [AvgUsedThreads],
--st.text как query_text,
кейс
когда sql_handle ЕСТЬ NULL, тогда ''
else (substring (st.text, (s.statement_start_offset + 2) / 2, (
кейс
когда s.statement_end_offset = -1, тогда len (convert (nvarchar (MAX), st.текст)) * 2
иначе s.statement_end_offset
конец - s.statement_start_offset) / 2))
заканчиваться как query_text,
db_name (st.dbid) как имя_базы_данных,
имя_схемы_объекта (st.objectid, st.dbid) + '.' + имя_объекта (st.objectid, st.dbid) как [имя_объекта],
sp. [query_plan],
s. [sql_handle],
s.plan_handle,
s.query_hash,
s.query_plan_hash
из с
перекрестно применить sys.dm_exec_sql_text (s. [sql_handle]) как st
перекрестно применить sys.dm_exec_query_plan (s. [plan_handle]) как sp