Обновление статистики: Обновить статистику — SQL Server

Обновить статистику — SQL Server

Twitter LinkedIn Facebook Адрес электронной почты

• Статья
• 09/26/2022
• Чтение занимает 2 мин

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

Обновить статистику оптимизации запросов для таблицы или индексированного представления в SQL Server можно с помощью SQL Server Management Studio или Transact-SQL. По умолчанию оптимизатор запросов обновляет статистику по мере необходимости для усовершенствования плана запроса. В некоторых случаях можно повысить производительность запроса, выполняя обновление статистики с помощью инструкции UPDATE STATISTICS или хранимой процедуры sp_updatestats чаще, чем это происходит по умолчанию.

Обновление статистики гарантирует, что запросы будут компилироваться с актуальной статистикой. Однако обновление статистики вызывает перекомпиляцию запросов. Рекомендуется не обновлять статистику слишком часто, поскольку необходимо найти баланс между выигрышем в производительности за счет усовершенствованных планов запросов и потерей времени на перекомпиляцию запросов. Критерии выбора компромиссного решения зависят от приложения. UPDATE STATISTICS может использовать базу данных tempdb для сортировки образцов строк для построения статистики.

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

• Перед началом работы

  Безопасность

• Для обновления объекта статистики используются:

  Среда SQL Server Management Studio

  Transact-SQL

Перед началом

Безопасность

Разрешения

При использовании инструкции UPDATE STATISTICS или внесении изменений в среде SQL Server Management Studioнеобходимо разрешение ALTER на таблицу или представление. При использовании процедуры sp_updatestatsнеобходимо быть членом предопределенной роли сервера sysadmin или владельцем базы данных (dbo).

Использование среды SQL Server Management Studio

Обновление объекта статистики

1. В обозревателе объектовщелкните значок «плюс», чтобы развернуть базу данных, в которой нужно обновить статистику.

2. Чтобы развернуть папку Таблицы , щелкните значок «плюс».

3. Щелкните значок «плюс», чтобы развернуть таблицу, в которой нужно обновить статистику.

4. Щелкните значок «плюс», чтобы развернуть папку Статистика .

5. Щелкните правой кнопкой мыши объект статистики, который нужно обновить, и выберите пункт Свойства.

6. В диалоговом окне Свойства статистики —имя_статистики установите флажок Обновить статистику для этих столбцов и нажмите кнопку ОК.

Использование Transact-SQL

Обновление указанного объекта статистики

1. В обозревателе объектовподключитесь к экземпляру компонента Компонент Database Engine.

2. На стандартной панели выберите пункт Создать запрос.

3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.

   USE AdventureWorks2012;  
   GO  
   -- The following example updates the statistics for the AK_SalesOrderDetail_rowguid index of the SalesOrderDetail table.  
   UPDATE STATISTICS Sales.SalesOrderDetail AK_SalesOrderDetail_rowguid;   
   GO  
   

Обновление всей статистики в таблице

1. В обозревателе объектовподключитесь к экземпляру компонента Компонент Database Engine.

2. На стандартной панели выберите пункт Создать запрос.

3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.

   USE AdventureWorks2012;   
   GO  
   -- The following example updates the statistics for all indexes on the SalesOrderDetail table.
   UPDATE STATISTICS Sales.SalesOrderDetail;   
   GO  
   

Дополнительные сведения см. в статье Инструкция UPDATE STATISTICS (Transact-SQL).

Обновление всей статистики в базе данных

1. В обозревателе объектовподключитесь к экземпляру компонента Компонент Database Engine.

2. На стандартной панели выберите пункт Создать запрос.

3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.

   USE AdventureWorks2012;   
   GO  
   -- The following example updates the statistics for all tables in the database.  
   EXEC sp_updatestats;  
   

Автоматическое управление индексами и статистикой

Используйте такие решения, как Адаптивная дефрагментация индексов, чтобы автоматически управлять дефрагментацией индексов и обновлениями статистики для одной базы данных или нескольких. Эта процедура автоматически выбирает, следует ли перестроить или реорганизовать индекс, сверяясь с уровнем фрагментации и другими параметрами, и обновляет статистику на основе линейных пороговых значений.

Дальнейшие действия

Дополнительные сведения см. в статье Инструкция UPDATE STATISTICS (Transact-SQL).

Задача «Обновление статистики» (план обслуживания) — SQL Server

Twitter LinkedIn Facebook Адрес электронной почты

• Статья
• 09/26/2022
• Чтение занимает 2 мин

Применимо к: SQL Server (все поддерживаемые версии)

Используйте диалоговое окно «Задача обновления статистики» для обновления сведений о данных в таблицах и индексах Корпорации Майкрософт SQL Server.

Эта задача осуществляет повторную выборку статистики распределения каждой пользовательской таблицы или индекса. Статистика распределения используется SQL Server для оптимизации навигации по таблицам во время обработки инструкций языка Transact-SQL. Чтобы автоматически создавать статистику распределения, SQL Server периодически выборки данных в соответствующей таблице для каждого индекса. Размер выборки зависит от количества строк в таблице и частоты изменения данных. Данный параметр используется для дополнительной выборки с использованием заданного процента данных в таблице. SQL Server использует эти сведения для создания более качественных планов запросов.

Эта задача выполняет инструкцию UPDATE STATISTICS.

Параметры

Соединение
Выберите соединение с сервером, которое будет использоваться для выполнения этой задачи.

Создать
Создать новое соединение с сервером для его использования при выполнении этой задачи. Диалоговое окно Создание соединения описано ниже.

Базы данных
Укажите базы данных, для которых должна выполняться эта задача.

• Все базы данных

  Создайте план обслуживания, который выполняет задачи обслуживания для всех баз данных SQL Server, кроме tempdb.

• Все системные базы данных

  Будет сформирован план обслуживания, запускающий задачи обслуживания для каждой системной базы данных SQL Server , за исключением базы данных tempdb. Для баз данных, созданных пользователями, задачи обслуживания выполняться не будут.

• Все пользовательские базы данных

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

• Определенные базы данных

  Создается план обслуживания, по которому задачи обслуживания должны выполняться только для указанных баз данных. Если выбран этот параметр, необходимо выбрать в списке хотя бы одну базу данных.

Примечание

Планы обслуживания выполняются только для баз данных, уровень совместимости которых 80 или выше. Базы данных с уровнем совместимости 70 или ниже не отображаются.

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

Выбор
Укажите таблицы или индексы, которые должны обрабатываться этой задачей. Недоступно, если в диалоговом окне «Объект» выбран тип Таблицы и представления .

Вся собранная статистика
Обновить статистику столбцов и статистику индексов.

Только статистика по столбцам
Обновить только статистику столбцов.

Только статистика индексов
Обновить только статистику индексов.

Тип просмотра
Тип просмотра, который используется для сбора обновленной статистики.

Полный просмотр
Считывает все строки в таблице или представлении для сбора статистики.

Пример
Укажите процент таблицы или индексированного представления либо количество строк для выборки, если статистика собирается для больших таблиц или представлений.

Просмотр T-SQL
Просмотрите инструкции Transact-SQL, выполняемые на сервере для этой задачи, на основе выбранных параметров.

Примечание

Если количество затронутых объектов велико, построение этого отображения может занять значительное время.

Диалоговое окно «Создание соединения»

Имя соединения
Введите имя нового соединения.

Выберите или введите имя сервера
Выберите сервер для подключения при выполнении этой задачи.

Обновить
Обновите список доступных серверов.

Введите данные для входа на сервер
Укажите способ проверки подлинности на сервере.

Использовать встроенную безопасность Windows
Подключиться к экземпляру компонента SQL Server Компонент Database Engine c проверкой подлинности Microsoft Windows.

Использовать указанные имя пользователя и пароль
Подключиться к экземпляру компонента SQL Server Компонент Database Engine с использованием проверки подлинности SQL Server. Этот параметр недоступен.

User name
Укажите имя входа, используемое при проверке подлинности SQL Server . Этот параметр недоступен.

Пароль
Укажите используемый при проверке подлинности пароль. Этот параметр недоступен.

См. также:

UPDATE STATISTICS (Transact-SQL)
sp_updatestats
Адаптивная дефрагментация индексов

Автоматическое обновление статистики / Хабр

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

Собственно для чего она нужна?

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

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

Такая оценка количества элементов позволяет оптимизатору запросов создавать более эффективные планы выполнения. В то же время, если статистика будет содержать устаревшие данные, могут быть выбраны менее эффективные операции, которые приведут к созданию медленных планов выполнения. Например, когда для небольшой выборки на устаревшей статистике выбирается более затратный оператор Index Scan, вместо оператора Index Seek.

Как Вы видите, чтобы быть максимально полезной для оптимизатора запросов, статистика должна быть точной и свежей. Время от времени SQL Server периодически сам обновляет статистику — данное поведение регулируется опциями AUTO_CREATE_STATISTICS и AUTO_UPDATE_STATISTICS.

Кроме того, при пересоздании индексов, статистика по ним обновляется автоматически с включенным флагом FULLSCAN, гарантирующим наиболее точное распределение данных. При реорганизации индексов же — статистика не обновляется.

Когда данные в таблицах изменяются очень часто, целесообразно выполнять избирательное обновление статистики вручную, с помощью операции UPDATE STATISTICS.

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

SELECT s.*
FROM sys.stats s
JOIN sys.objects o ON s.[object_id] = o. [object_id]
WHERE o.is_ms_shipped = 0

Применяя возможности динамического SQL, напишем скрипт по автоматическому обновлению устаревшей статистики:

DECLARE @DateNow DATETIME
SELECT @DateNow = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = (
    SELECT '
	UPDATE STATISTICS [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] [' + s.name + ']
		WITH FULLSCAN' + CASE WHEN s.no_recompute = 1 THEN ', NORECOMPUTE' ELSE '' END + ';'
	FROM sys.stats s WITH(NOLOCK)
	JOIN sys.objects o WITH(NOLOCK) ON s.[object_id] = o.[object_id]
	WHERE o.[type] IN ('U', 'V')
		AND o.is_ms_shipped = 0
		AND ISNULL(STATS_DATE(s.[object_id], s.stats_id), GETDATE()) <= @DateNow
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
PRINT @SQL
EXEC sys.sp_executesql @SQL

При выполнении будут генерироваться следующие стейтменты:

UPDATE STATISTICS [Production].[Shift] [PK_Shift_ShiftID] WITH FULLSCAN;
UPDATE STATISTICS [Production]. [Shift] [AK_Shift_Name] WITH FULLSCAN, NORECOMPUTE;

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

DECLARE @DateNow DATETIME
SELECT @DateNow = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = (
    SELECT '
	UPDATE STATISTICS [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] [' + s.name + ']
		WITH FULLSCAN' + CASE WHEN s.no_recompute = 1 THEN ', NORECOMPUTE' ELSE '' END + ';'
	FROM (
		SELECT 
			  [object_id]
			, name
			, stats_id
			, no_recompute
			, last_update = STATS_DATE([object_id], stats_id)
		FROM sys.stats WITH(NOLOCK)
		WHERE auto_created = 0
			AND is_temporary = 0 -- 2012+
	) s
	JOIN sys. objects o WITH(NOLOCK) ON s.[object_id] = o.[object_id]
	JOIN (
		SELECT
			  p.[object_id]
			, p.index_id
			, total_pages = SUM(a.total_pages)
		FROM sys.partitions p WITH(NOLOCK)
		JOIN sys.allocation_units a WITH(NOLOCK) ON p.[partition_id] = a.container_id
		GROUP BY 
			  p.[object_id]
			, p.index_id
	) p ON o.[object_id] = p.[object_id] AND p.index_id = s.stats_id
	WHERE o.[type] IN ('U', 'V')
		AND o.is_ms_shipped = 0
		AND (
			  last_update IS NULL AND p.total_pages > 0 -- never updated and contains rows
			OR
			  last_update <= DATEADD(dd, 
				CASE WHEN p.total_pages > 4096 -- > 4 MB
					THEN -2 -- updated 3 days ago
					ELSE 0 
				END, @DateNow)
		)
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
PRINT @SQL
EXEC sys.sp_executesql @SQL

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

Если хотите поделиться этой статьей с англоязычной аудиторией, то прошу использовать ссылку на перевод:
SQL Server Typical Maintenance Plans: Automatic Statistics Update

Обновление статистики — SQL Server

Обратная связь Редактировать

Твиттер LinkedIn Фейсбук Эл. адрес

• Статья
• 24.05.2022
• 2 минуты на чтение

Применимо к: SQL Server (все поддерживаемые версии) База данных SQL Azure Управляемый экземпляр Azure SQL Аналитика синапсов Azure Система аналитической платформы (PDW)

Статистику оптимизации запросов для таблицы или индексированного представления в SQL Server можно обновить с помощью SQL Server Management Studio или Transact-SQL. По умолчанию оптимизатор запросов уже обновляет статистику по мере необходимости для улучшения плана запроса; в некоторых случаях вы можете повысить производительность запросов, используя UPDATE STATISTICS или хранимую процедуру sp_updatestats , чтобы обновлять статистику чаще, чем обновления по умолчанию.

Обновление статистики гарантирует, что запросы будут компилироваться с актуальной статистикой. Однако обновление статистики приводит к повторной компиляции запросов. Мы рекомендуем не обновлять статистику слишком часто, поскольку существует компромисс между улучшением планов запросов и временем, которое требуется для перекомпиляции запросов. Конкретные компромиссы зависят от вашего приложения. UPDATE STATISTICS может использовать базу данных tempdb для сортировки выборки строк для построения статистики.

В этой теме

• Прежде чем начать:

  Безопасность

• Для обновления объекта статистики, используя:

  Студия управления SQL Server

  Transact-SQL

Перед началом работы

Безопасность

Разрешения

При использовании ОБНОВЛЕНИЯ СТАТИСТИКИ или внесении изменений через SQL Server Management Studio требуется разрешение ALTER для таблицы или представления. Если использовать sp_updatestats , требуется членство в фиксированной роли сервера sysadmin или владение базой данных ( dbo ).

Использование SQL Server Management Studio

Чтобы обновить объект статистики

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

2. Щелкните значок плюса, чтобы развернуть папку Tables .

3. Щелкните знак плюс, чтобы развернуть таблицу, в которой вы хотите обновить статистику.

4. Щелкните значок плюса, чтобы развернуть папку Статистика .

5. Щелкните правой кнопкой мыши объект статистики, который вы хотите обновить, и выберите Свойства .

6. В диалоговом окне Свойства статистики — имя_статистики установите флажок Обновить статистику для этих столбцов и нажмите ОК .

Использование Transact-SQL

Чтобы обновить конкретный объект статистики

1. В Object Explorer подключитесь к экземпляру Database Engine.

2. На стандартной панели щелкните Новый запрос .

3. Скопируйте и вставьте следующий пример в окно запроса и нажмите Выполнить .

    ИСПОЛЬЗОВАТЬ AdventureWorks2012;
   ИДТИ
   -- В следующем примере обновляется статистика для индекса AK_SalesOrderDetail_rowguid таблицы SalesOrderDetail.
   ОБНОВЛЕНИЕ СТАТИСТИКИ Sales.SalesOrderDetail AK_SalesOrderDetail_rowguid;
   ИДТИ
    

Чтобы обновить всю статистику в таблице

1. В Object Explorer подключитесь к экземпляру Database Engine.

2. На стандартной панели щелкните Новый запрос .

3. Скопируйте и вставьте следующий пример в окно запроса и нажмите Выполнить .

    ИСПОЛЬЗОВАТЬ AdventureWorks2012;
   ИДТИ
   -- В следующем примере обновляется статистика для всех индексов таблицы SalesOrderDetail.
   ОБНОВЛЕНИЕ СТАТИСТИКИ Sales.SalesOrderDetail;
   ИДТИ
    

Дополнительные сведения см. в разделе СТАТИСТИКА ОБНОВЛЕНИЯ (Transact-SQL).

Чтобы обновить всю статистику в базе данных

1. В Object Explorer подключитесь к экземпляру Database Engine.

2. На стандартной панели щелкните Новый запрос .

3. Скопируйте и вставьте следующий пример в окно запроса и нажмите Выполнить .

    ИСПОЛЬЗОВАТЬ AdventureWorks2012;
   ИДТИ
   -- В следующем примере обновляется статистика для всех таблиц в базе данных.
   EXEC sp_updatestats;
    

Автоматическое управление индексами и статистикой

Используйте такие решения, как Adaptive Index Defrag, для автоматического управления дефрагментацией индексов и обновлениями статистики для одной или нескольких баз данных. Эта процедура автоматически выбирает, следует ли перестраивать или реорганизовывать индекс в соответствии с его уровнем фрагментации, среди других параметров, и обновляет статистику с линейным порогом.

Следующие шаги

Дополнительные сведения см. в разделе СТАТИСТИКА ОБНОВЛЕНИЯ (Transact-SQL).

Обратная связь

Отправить и просмотреть отзыв для

Этот продукт Эта страница

Просмотреть все отзывы о странице

Как обновить статистику SQL Server для больших таблиц

ГлавнаяРазработка баз данных, ТаблицыКак обновить статистику SQL Server для больших таблиц

В предыдущей статье я кратко рассказал о статистике базы данных, ее важности и о том, почему статистику следует обновлять. Кроме того, я продемонстрировал пошаговый процесс создания плана обслуживания SQL Server для обновления статистики. В этой статье будут объяснены следующие вопросы: 1. Как обновить статистику с помощью команды T-SQL. 2. Как определить часто обновляемые таблицы с помощью T-SQL, а также как обновить статистику таблиц с часто вставляемыми/обновляемыми/удаляемыми данными.

Обновление статистики с помощью T-SQL

Статистику можно обновлять с помощью сценария T-SQL. Если вы хотите обновить статистику с помощью студии управления T-SQL или SQL Server, вам необходимо разрешение ALTER database для базы данных. См. пример кода T-SQL для обновления статистики конкретной таблицы:

 UPDATE STATISTICS <имя_схемы>.<имя_таблицы>. 

Рассмотрим на примере обновления статистики таблицы OrderLines База данных WideWorldImporters . Следующий скрипт сделает это.

 UPDATE STATISTICS [Sales].[OrderLines] 

Если вы хотите обновить статистику определенного индекса, вы можете использовать следующий сценарий:

 UPDATE STATISTICS .   

В случае вы хотите обновить статистику индекса IX_Sales_OrderLines_Perf_20160301_02 таблицы OrderLines , вы можете выполнить следующий скрипт:

 ОБНОВЛЕНИЕ СТАТИСТИКИ [Продажи].[Строки заказов] [IX_Sales_OrderLines_Perf_20160301_02] 

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

 USEwideworldimporters.
идти
EXEC Sp_updatestats 

Обновление статистики для таблиц с часто вставляемыми/обновляемыми/удаляемыми данными

В больших базах данных планирование задания статистики усложняется, особенно когда у вас есть всего несколько часов на обслуживание индекса, обновление статистики и выполнение других задач по обслуживанию . Под большой базой данных я подразумеваю базу данных, содержащую тысячи таблиц, каждая из которых содержит тысячи строк. Например, у нас есть база данных с именем X. В ней сотни таблиц, и каждая таблица содержит миллионы строк. И только несколько таблиц часто обновляются. Другие таблицы редко изменяются, и с ними выполняется очень мало транзакций. Как я упоминал ранее, чтобы поддерживать производительность базы данных на должном уровне, статистика таблиц должна быть актуальной. Поэтому мы создаем план обслуживания SQL для обновления статистики всех таблиц в базе данных X. Когда SQL-сервер обновляет статистику таблицы, он использует значительный объем ресурсов, что может привести к проблемам с производительностью. Таким образом, обновление статистики сотен больших таблиц занимает много времени, и пока статистика обновляется, производительность базы данных значительно снижается. В таких обстоятельствах всегда рекомендуется обновлять статистику только для часто обновляемых таблиц. Вы можете отслеживать изменения объема данных или количества строк с течением времени, используя следующие динамические представления управления: 1. sys.partitions предоставляет информацию об общем количестве строк в таблице. 2. sys.dm_db_partition_stats предоставляет информацию о количестве строк и страниц для каждого раздела. 3. sys.dm_db_index_physical_stats предоставляет информацию о количестве строк и страниц, а также информацию о фрагментации индекса и многое другое. Подробности об объеме данных важны, но они не делают картину активности базы данных полной. Например, промежуточную таблицу с почти одинаковым количеством записей можно удалять из таблицы или вставлять в таблицу каждый день. Из-за этого снимок количества строк предполагает, что таблица является статической. Возможно, добавленные и удаленные записи имеют очень разные значения, которые сильно изменяют распределение данных. В этом случае автоматическое обновление статистики в SQL Server делает статистику бессмысленной. Поэтому очень полезно отслеживать количество модификаций таблицы. Это можно сделать следующими способами: 1. rowmodctr столбец в sys.sysindexes 2. модифицированный_счет столбец в sys.system_internals_partition_columns 3. модификация_счетчик столбец в sys.dm_db_stats_properties Таким образом, как я объяснял ранее, если у вас есть ограниченное время для обслуживания базы данных, всегда рекомендуется обновлять статистика только для таблиц с более высокой частотой изменения данных (вставка/обновление/удаление). Чтобы сделать это эффективно, я создал скрипт, который обновляет статистику для «активных» таблиц. Сценарий выполняет следующие задачи: • Объявляет необходимые параметры • Создает временную таблицу с именем #tempstatistics для хранения имени таблицы, имени схемы и имени базы данных • Создает другую таблицу с именем #tempdatabase для хранения имени базы данных. Сначала выполните следующий скрипт, чтобы создать две таблицы:

 DECLARE @databasename VARCHAR(500)
ЗАЯВИТЬ @i INT=0
DECLARE @DBCOunt INT
DECLARE @SQLCOmmand NVARCHAR(max)
DECLARE @StatsUpdateCOMmand NVARCHAR(max)
СОЗДАТЬ ТАБЛИЦУ #tempstatistics
(
имя_базы_данных VARCHAR(max),
имя_таблицы    VARCHAR(max),
имя_схемы   VARCHAR(max)
)
СОЗДАТЬ ТАБЛИЦУ #tempdatabases
(
имя_базы_данных VARCHAR(max)
)
ВСТАВИТЬ В #tempdatabases
(имя базы данных)
ВЫБЕРИТЕ ИМЯ
ИЗ   sys. databases
ГДЕ  database_id > 4
ЗАКАЗАТЬ ПО ИМЕНИ 

Затем напишите цикл while для создания динамического SQL-запроса, который выполняет итерацию по всем базам данных и вставляет список таблиц, у которых счетчик изменений больше 200, в таблицу #tempstatistics . Для получения информации об изменении данных я использую sys.dm_db_stats_properties . Изучите следующий пример кода:

 SET @DBCOunt=(SELECT Count(*)
ОТ   #tempdatabases)
ПОКА ( @i < @DBCOunt )
НАЧИНАТЬ
DECLARE @DBName VARCHAR(max)
SET @DBName=(SELECT TOP 1 имя базы данных
ОТ   #tempdatabases)
SET @SQLCOmmand= '     use [' + @DBName + ']; Выбрать
отличные '''+ @DBName+ ''', a.TableName,a.SchemaName из (SELECT obj.name as TableName, b.name as SchemaName, obj.object_id, stat.name, stat.stats_id, last_updated, modification_counter       FROM [ ' + @DBName+ '].sys.objects AS obj     inner join ['+ @DBName + '].sys.schemas b on obj.schema_id=b.schema_id   INNER JOIN [' + @DBName+ '].sys. stats AS stat ON stat.object_id = obj.object_id    CROSS APPLY [' + @DBName+'].sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp WHERE modification_counter > 200 и obj.name не как ''sys%''and b .имя не нравится
''систем%'')а'
ВСТАВИТЬ В #tempstatistics
(имя базы данных,
имя таблицы,
имя схемы)
EXEC Sp_executesql
@SQLCOMmand 

Теперь создайте второй цикл внутри первого цикла. Он сгенерирует динамический SQL-запрос, который обновит статистику при полном сканировании. См. пример кода ниже:

 DECLARE @j INT=0.
DECLARE @StatCount INT
SET @StatCount =(SELECT Count(*)
ОТ   #tempstatistics)
ПОКА @J < @StatCount
НАЧИНАТЬ
DECLARE @DatabaseName_Stats VARCHAR (макс.)
DECLARE @Table_Stats VARCHAR(макс.)
DECLARE @Schema_Stats VARCHAR (макс.)
DECLARE @StatUpdateCommand NVARCHAR(max)
SET @DatabaseName_Stats=(SELECT TOP 1 имя базы данных
ОТ   #tempstatistics)
SET @Table_Stats=(SELECT TOP 1 название таблицы
ОТ   #tempstatistics)
SET @Schema_Stats=(SELECT TOP 1 имя схемы
ОТ   #tempstatistics)
SET @StatUpdateCommand='Обновить статистику [' + @DatabaseName_Stats
+ ']. [' + @Schema_Stats + '].[' + @Table_Stats
+ '] с полным сканированием
EXEC Sp_executesql
@StatUpdateCommand
SET @[email защищен] + 1
УДАЛИТЬ ИЗ #tempstatistics
ГДЕ  название_базы_данных = @DatabaseName_Stats
И название_таблицы = @Table_Stats
И имя_схемы = @Schema_Stats
КОНЕЦ
SET @[email защищен] + 1
УДАЛИТЬ ИЗ #tempdatabases
ГДЕ  имя_базы_данных = @DBName
КОНЕЦ 

После завершения выполнения сценария все временные таблицы будут удалены.

 ВЫБЕРИТЕ *
ОТ   #tempstatistics
УДАЛИТЬ ТАБЛИЦУ #tempdatabases
DROP TABLE #tempstatistics 

Весь сценарий будет выглядеть следующим образом:

 --set count on
СОЗДАТЬ ПРОЦЕДУРУ Statistics_maintenance
В КАЧЕСТВЕ
НАЧИНАТЬ
DECLARE @databasename VARCHAR(500)
ЗАЯВИТЬ @i INT=0
DECLARE @DBCOunt INT
DECLARE @SQLCOmmand NVARCHAR(max)
DECLARE @StatsUpdateCOMmand NVARCHAR(max)
СОЗДАТЬ ТАБЛИЦУ #tempstatistics
(
имя_базы_данных VARCHAR(max),
имя_таблицы    VARCHAR(max),
имя_схемы   VARCHAR(max)
)
СОЗДАТЬ ТАБЛИЦУ #tempdatabases
(
имя_базы_данных VARCHAR(max)
)
ВСТАВИТЬ В #tempdatabases
(имя базы данных)
ВЫБЕРИТЕ ИМЯ
ИЗ   sys. databases
ГДЕ  database_id > 4
ЗАКАЗАТЬ ПО ИМЕНИ
SET @DBCOunt=(SELECT Count(*)
ОТ   #tempdatabases)
ПОКА ( @i < @DBCOunt )
НАЧИНАТЬ
DECLARE @DBName VARCHAR(max)
SET @DBName=(SELECT TOP 1 имя базы данных
ОТ   #tempdatabases)
SET @SQLCOmmand= '     use [' + @DBName + ']; Выбрать
отличные '''+ @DBName+ ''', a.TableName,a.SchemaName из (SELECT obj.name as TableName, b.name as SchemaName, obj.object_id, stat.name, stat.stats_id, last_updated, modification_counter       FROM [ ' + @DBName+ '].sys.objects AS obj     inner join ['+ @DBName + '].sys.schemas b on obj.schema_id=b.schema_id   INNER JOIN [' + @DBName+ '].sys.stats AS stat ON stat.object_id = obj.object_id    CROSS APPLY [' + @DBName+'].sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp WHERE modification_counter > 200 и obj.name не как ''sys%''and b .имя не нравится
''систем%'')а'
ВСТАВИТЬ В #tempstatistics
(имя базы данных,
имя таблицы,
имя схемы)
EXEC Sp_executesql
@SQLCOmand
ЗАЯВИТЬ @j INT=0
DECLARE @StatCount INT
SET @StatCount =(SELECT Count(*)
ОТ   #tempstatistics)
ПОКА @J < @StatCount
НАЧИНАТЬ
DECLARE @DatabaseName_Stats VARCHAR (макс. )
DECLARE @Table_Stats VARCHAR(макс.)
DECLARE @Schema_Stats VARCHAR (макс.)
DECLARE @StatUpdateCommand NVARCHAR(max)
SET @DatabaseName_Stats=(SELECT TOP 1 имя базы данных
ОТ   #tempstatistics)
SET @Table_Stats=(SELECT TOP 1 название таблицы
ОТ   #tempstatistics)
SET @Schema_Stats=(SELECT TOP 1 имя схемы
ОТ   #tempstatistics)
SET @StatUpdateCommand='Обновить статистику [' + @DatabaseName_Stats
+ '].[' + @Schema_Stats + '].[' + @Table_Stats
+ '] с полным сканированием
EXEC Sp_executesql
@StatUpdateCommand
SET @[email защищен] + 1
УДАЛИТЬ ИЗ #tempstatistics
ГДЕ  название_базы_данных = @DatabaseName_Stats
И название_таблицы = @Table_Stats
И имя_схемы = @Schema_Stats
КОНЕЦ
SET @[email защищен] + 1
УДАЛИТЬ ИЗ #tempdatabases
ГДЕ  имя_базы_данных = @DBName
КОНЕЦ
ВЫБРАТЬ *
ОТ   #tempstatistics
УДАЛИТЬ ТАБЛИЦУ #tempdatabases
УДАЛИТЬ ТАБЛИЦУ #tempstatistics
КОНЕЦ 

Вы также можете автоматизировать этот сценарий, создав задание агента SQL Server, которое будет выполнять его в запланированное время. Пошаговая инструкция по автоматизации этой работы приведена ниже.

Создание задания SQL

Во-первых, давайте создадим задание SQL для автоматизации процесса. Для этого откройте SSMS, подключитесь к нужному серверу и разверните агент SQL Server, щелкните правой кнопкой мыши Jobs и выберите New Job . В диалоговом окне New Job введите нужное имя в поле Имя поля . Теперь щелкните пункт меню Steps на левой панели диалогового окна New Job , затем щелкните New в окне Steps . В открывшемся диалоговом окне New Job Step введите нужное имя в поле Step name . Затем выберите Transact-SQL script (T-SQL) в раскрывающемся списке Введите . Затем выберите DBATools в раскрывающемся списке Database и напишите следующий запрос в текстовом поле команды:

 EXEC Statistics_maintenance 

Чтобы настроить расписание задания, щелкните пункт меню Schedules в диалоговом окне New Job . Откроется диалоговое окно New Job Schedule . В поле Имя укажите желаемое имя расписания. В нашем примере мы хотим, чтобы это задание выполнялось каждую ночь в час ночи, поэтому в раскрывающемся списке Occurs в разделе Frequency выберите Daily . В Происходит один раз в  в разделе Ежедневная частота введите 01:00:00. Нажмите OK , чтобы закрыть окно New Job Schedule , а затем снова нажмите OK в диалоговом окне New Job , чтобы закрыть его. Теперь давайте проверим эту работу. В агенте SQL Server щелкните правой кнопкой мыши Update_Statistics_Daily . В случае успешного выполнения задания вы увидите следующее окно.

Резюме

В этой статье были рассмотрены следующие вопросы: 1. Как обновить статистику таблиц с помощью T-SQL Script. 2. Как получить информацию об изменении объема данных и частоте изменения данных. 3. Как создать скрипт, обновляющий статистику по активным столам. 4. Как создать задание агента SQL Server для выполнения сценария в запланированное время.

Теги: sql server, t-sql, статистика обновлений Последнее изменение: 20 сентября 2021 г.

Обновить статистику базы данных—Справка | ArcGIS Desktop

• Использовать команду «Анализ»
• Использовать инструмент «Анализ наборов данных»
• Запустить скрипт Python

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

Статистика может быть обновлена ​​для следующих типов таблиц и индексов:

• Таблица, которую вы выбрали для анализа, и связанные с ней индексы
• Таблицы изменений версий таблиц и классов объектов и индексы в таблицах изменений
• Исторические архивные таблицы наборов данных, которые включены для архивирования, и индексы архивных таблиц
• Системные таблицы базы геоданных
• индексы

Примечание:

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

В ArcGIS есть несколько методов, которые можно использовать для обновления статистики. Они описаны в следующих разделах:

Используйте команду «Анализ»

Вы можете использовать команду «Анализ» для обновления статистики по определенным наборам данных, выбранным непосредственно в дереве каталога. Вы должны быть владельцем набора данных, чтобы обновлять статистику.

1. Запустите ArcMap или ArcCatalog.
2. В разделе Подключения к базе данных в дереве каталога подключитесь к базе данных или многопользовательской базе геоданных, содержащей набор данных, для которого вы хотите обновить статистику.

   Обязательно подключитесь как владелец набора данных.

3. Щелкните правой кнопкой мыши набор данных в дереве каталога.

   Это может быть набор пространственных данных, класс пространственных объектов, таблица, каталог растров, набор растровых данных или набор данных мозаики.

4. Укажите Управление и щелкните Анализировать.

Статистика обновляется для таблиц и индексов в этом наборе данных, как описано в начале этого раздела.

Используйте инструмент Анализировать наборы данных

Вы можете использовать инструмент геообработки Анализировать наборы данных (находящийся в группе инструментов Администрирование базы геоданных набора инструментов Управление данными) для обновления статистики. Используйте этот инструмент вместо команды «Анализ», если вы хотите обновить статистику по нескольким наборам данных одновременно, если вы хотите выбрать определенные таблицы для анализа (например, если вы хотите обновить статистику только для дельта-таблиц) или если вы хотите обновить статистику в системных таблицах базы геоданных.

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

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

   Подключиться как владелец таблиц.

2. Откройте инструмент геообработки Анализировать наборы данных.

   Используйте окно поиска, чтобы найти инструмент и открыть его, или перейдите к инструменту в наборе инструментов Администрирование базы геоданных набора инструментов Управление данными.

3. Используйте файл подключения к базе данных, созданный на шаге 1, в качестве входной рабочей области.
4. Отметьте, какие таблицы вы хотите анализировать:
   • Если вы хотите обновить статистику для выбранных базовых таблиц, оставьте отмеченным.
   • Отметьте, чтобы обновить статистику в дельта-таблицах версионных наборов данных.
   • Установите этот флажок, чтобы обновить статистику по историческим архивным таблицам наборов данных, для которых разрешено архивирование.
   • Установите флажок Включить системные таблицы, чтобы обновить статистику по системным таблицам базы геоданных.
5. Нажмите OK, чтобы запустить инструмент.

Статистика обновлена ​​для выбранных вами таблиц и их индексов.

Запустить скрипт Python

Вы можете вызвать функцию AnalyzeDatasets_management в скрипте Python, чтобы обновить статистику по базовым таблицам, дельта-таблицам, архивным таблицам и/или системным таблицам базы геоданных. Как и в случае с инструментом Analyze Datasets, вы должны запустить AnalyzeDatasets_management от имени владельца анализируемых таблиц.

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

Например, чтобы запустить этот сценарий в Linux с помощью файла подключения к базе данных entgdb.sde, расположенного в /usr/connections, введите следующее:

./ AnalyzeDatasets.py /usr/connections/entgdb.sde

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

 # Имя: AnalyzeDatasets.py
# Описание: анализирует все наборы данных в многопользовательской базе геоданных.
# для данного пользователя.
# Импорт системных модулей
импортировать аркпи, ОС
# установить рабочее пространство
# пользователь в этой рабочей области должен быть владельцем анализируемых данных. рабочая область = arcpy.GetParameterAsText (0)
# устанавливаем рабочую среду
arcpy.env.workspace = рабочая область
# ПРИМЕЧАНИЕ. Анализ наборов данных может принимать список наборов данных Python.
# Получить список всех наборов данных, к которым у пользователя есть доступ.
# Во-первых, получите все отдельные таблицы, классы пространственных объектов и растры.
dataList = arcpy.ListTables() + arcpy.ListFeatureClasses() + arcpy.ListRasters()
# Затем для наборов данных объектов получите все наборы данных и классы объектов
# из списка и добавить их в основной список.
для набора данных в arcpy.ListDatasets("", "Feature"):
 arcpy.env.workspace = os.path.join(рабочая область,набор данных)
 dataList += arcpy.ListFeatureClasses() + arcpy.ListDatasets()
# сбросить рабочую область
arcpy.env.workspace = рабочая область
# Получить имя пользователя для рабочей области
userName = arcpy.Describe(workspace).connectionProperties.user.lower()
# удалить все наборы данных, которые не принадлежат подключенному пользователю.