oracle ola
This is the third article in Ola Hallengren’s SQL Server Maintenance Solution series. The article will cover the IndexOptimize stored procedure in more detail, along with the index optimization jobs created by Ola Hallengren’s scripts.
这是Ola HallengrenSQL Server维护解决方案系列的第三篇文章。 本文将更详细地介绍IndexOptimize存储过程，以及由Ola Hallengren的脚本创建的索引优化作业。
To get the detailed guide on installation procedure and solutions for database backup and integrity check, feel free to first review the first two articles of the series:
要获取有关数据库备份和完整性检查的安装过程和解决方案的详细指南，请先阅读本系列的前两篇文章：
- Ola Hallengren’s SQL Server Maintenance Solution – Installation and SQL Server Backup solutionOla HallengrenSQL Server维护解决方案–安装和SQL Server备份解决方案
- Ola Hallengren’s SQL Server Maintenance Solution – Database integrity checkOla HallengrenSQL Server维护解决方案–数据库完整性检查
The SQL Server Index and Statistics Maintenance part of Ola Hallengren’s solution consists of IndexOptmize stored procedure and IndexOptimize – USER_DATABASES agent job, that are created during the installation process. The procedure CommandExecute also has to be installed in order to use the index optimize solution. All these procedures and jobs are installed by default, but if you prefer the custom installation, make sure that everything is installed properly on the server.
Ola Hallengren解决方案的“ SQL Server索引和统计信息维护”部分包括在安装过程中创建的IndexOptmize存储过程和IndexOptimize – USER_DATABASES代理作业。 为了使用索引优化解决方案，还必须安装CommandExecute过程。 默认情况下，所有这些过程和作业均已安装，但如果您喜欢自定义安装，请确保所有内容均已正确安装在服务器上。
In SSMS, connect to the server, and expand the following nodes in Object Explorer:
在SSMS中，连接到服务器，然后在对象资源管理器中展开以下节点：
Databases\System Databases\master\Programmability\Stored Procedures
数据库\系统数据库\ master \可编程性\存储过程
Make sure that the procedures dbo.CommandExecute and dbo.IndexOptimize are present on the list.
确保过程dbo.CommandExecute和dbo.IndexOptimize存在于列表中。
The IndexOptimize – USER_DATABASES agent job is available at: SQL Server Agent\Jobs
可在以下位置获得IndexOptimize – USER_DATABASES代理作业：SQL Server代理\作业
IndexOptimize存储过程 (IndexOptimize stored procedure)
To fully understand the agent job created by the installation script, we need to go through the parameters that IndexOptimize stored procedure uses. The are total of 27 parameters that can be used to configure the procedure. To get full details on the procedure parameters, run a following query:
为了完全理解由安装脚本创建的代理作业，我们需要遍历IndexOptimize存储过程使用的参数。 共有27个可用于配置过程的参数。 要获取有关过程参数的完整详细信息，请运行以下查询：
SELECT 'Parameter_name' = name,
'Type' = TYPE_NAME(user_type_id),
'Length' = max_length,
'Prec' = CASE
WHEN TYPE_NAME(system_type_id) = 'uniqueidentifier'
THEN precision
ELSE OdbcPrec(system_type_id, max_length, precision)
END,
'Scale' = OdbcScale(system_type_id, scale),
'Param_order' = parameter_id,
'Collation' = CONVERT(SYSNAME,
CASE
WHEN system_type_id IN(35, 99, 167, 175, 231, 239)
THEN SERVERPROPERTY('collation')
END)
FROM sys.parameters
WHERE object_id = OBJECT_ID('dbo.IndexOptimize');
Executing the query gets the following results:
执行查询将得到以下结果：
It is possible to change the default values for each parameter inside the procedure, but this is not advised, as it will affect any agent job that already uses the procedure (like IndexOptimize – USER_DATABASES job for example).
可以更改过程中每个参数的默认值，但是不建议这样做，因为这会影响已经使用该过程的任何代理作业（例如IndexOptimize – USER_DATABASES作业）。
The common practice is to pass all necessary parameters to the stored procedure through a T-SQL, or CmdExec script. These scripts can then be included in a SQL Server Agent job, and assigned to run on a custom schedule. Creating and configuring these jobs would result in a custom, fully automated index maintenance solution.
通常的做法是通过T-SQL或CmdExec脚本将所有必需的参数传递给存储过程。 然后，这些脚本可以包含在SQL Server代理作业中，并可以按自定义计划运行。 创建和配置这些作业将产生一个定制的，全自动索引维护解决方案。
Before creating the script, it is necessary to understand the most important procedure parameters, along with the corresponding values.
在创建脚本之前，有必要了解最重要的过程参数以及相应的值。
- Databases
The parameter specifies one or more databases that will be affected by the script. To include multiple databases, just separate the database names by comma (,). To exclude a database, use the hyphen character (-) in front of the database name. For the wildcard selection, use the percentage character (%). The list of all accepted parameters is presented in the table.Parameter value Description SYSTEM_DATABASES Performs the operations on all system databases USER_DATABASES Performs the operations on all user databases ALL_DATABASES Performs the operations on all databases AVAILABILITY_GROUP_DATABASES Performs the operations on all databases belonging to the availability group DATABASE01, DATABASE2 Performs the operations on databases named “DATABASE01” and “DATABASE02” %DAT% Performs the operations on all databases that have the “DAT” string in their name - 资料库
该参数指定一个或多个将受脚本影响的数据库。 要包含多个数据库，只需用逗号（，）分隔数据库名称。 要排除数据库，请在数据库名称前面使用连字符（-）。 对于通配符选择，请使用百分比字符（％）。 下表列出了所有可接受的参数。参数值 描述 SYSTEM_DATABASES 在所有系统数据库上执行操作 USER_DATABASES 在所有用户数据库上执行操作 ALL_DATABASES 在所有数据库上执行操作 AVAILABILITY_GROUP_DATABASES 对属于可用性组的所有数据库执行操作 DATABASE01，DATABASE2 在名为“ DATABASE01”和“ DATABASE02”的数据库上执行操作 ％DAT％ 在名称中带有“ DAT”字符串的所有数据库上执行操作 - Indexes 指标
The parameter specifies the index selection. Comma, hyphen and percentage characters are used in the same way as with Databases parameter. If no other value is specified, the selection will include all indexes.
该参数指定索引选择。 逗号，连字符和百分比字符的使用方式与数据库参数相同。 如果未指定其他值，则选择将包括所有索引。
Parameter value Description ALL_INDEXES Selects all indexes Database1.Schema1.Tab1.Index1 Selects the Index1 on the object Schema1.Tab1 in Database1 Database1.Schema1.% Selects all indexes in Schema1 in Database1 参数值 描述 ALL_INDEXES 选择所有索引 数据库1.Schema1.Tab1.Index1 在数据库 1中的对象Schema1.Tab1上选择索引1 Database1.Schema1。％ 选择Database1中Schema1中的所有索引 - AvailabilityGroups 可用性组
Same rules apply for the availability groups selection, which is controlled by AvailabilityGroups parameter.
相同的规则适用于可用性组选择，这由AvailabilityGroups参数控制。
最低0.47元/天 解锁文章
516
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
