You don't have access to this class

Keep learning! Join and start boosting your career

Aprovecha el precio especial y haz tu profesión a prueba de IA

Antes: $249

Currency
$209
Suscríbete

Termina en:

1 Días
9 Hrs
26 Min
49 Seg

Planes de mantenimiento: ordenar los índices en nuestra base de datos

25/31
Resources

How to sort the indexes of fragmented tables in SQL?

Efficient maintenance of a database is essential to ensure its optimal performance. Among the various administration tasks, sorting the indexes of fragmented tables is crucial. This process should be executed regularly depending on the type of business and the fragmentation you have. Join me on this walkthrough to discover how to create a maintenance plan in SQL Server Management Studio and improve your database performance.

What is a maintenance plan in SQL?

A maintenance plan in SQL Server is a feature that allows you to perform in an automated way different essential tasks such as:

  • Periodic database backups.
  • Dataintegrity review.
  • Execution of scheduled queries.
  • Agent jobs' services.
  • Backuphistory cleaning.
  • Notifications to operators through e-mails or messages.
  • Reorganization and reconstruction of indexes.
  • Statistics update.

All these functions help to keep the database organized and make sure it operates efficiently.

How to create and configure a maintenance plan?

  1. Create a new plan: Within SQL Management Studio, go to the "Management" section. Right click on "Maintenance Plans" and select "New Maintenance Plan".

  2. Add tasks to the plan: When the maintenance plan opens, you will see a list on the left side of all possible tasks. To sort the indexes, add the task "Rebuild Indices".

  3. Select the databases: Once you have added the rebuild indexes task, select the databases you want to reorganize. In the example, the "Whiteboard Importers" database is used.

  4. Define the fragmentation threshold: Generally, it is recommended to perform defragmentation when the indexes of a table have more than 30% fragmentation. However, this depends on how business critical it is. In the example, 20% is set.

  5. Scheduling: This step is crucial. You can schedule the task to run on a daily, weekly, or monthly basis. The decision is based on server usage and availability. Example: every Friday at 8 PM when the server is least busy.

  6. Add notifications: It is possible to configure notifications to receive alerts about the execution of processes. You can choose to receive notifications only in case of errors, or also every time a process ends.

How to manage and execute a maintenance plan?

Once created, the maintenance plan needs to have the SQL Server Agent configured and running in order to work. If you need to run it manually, you can do so by right clicking on the plan and selecting "Run".

It is essential to keep in mind that these plans, although simple to configure with the SQL graphical tool, must be scheduled at times that do not interfere with production. Choosing night or weekend schedules may be the most appropriate, depending on the type of business.

Final Considerations

Proper database maintenance is essential for the proper functioning and performance of your applications. Index reorganization is just one aspect, but it plays an important role in maintaining data access agility. Now that you know how to create and configure a maintenance plan, you can ensure that your database operates smoothly, uninterrupted and optimally.

Continue exploring and learning more about SQL maintenance plans to optimize your databases and maintain control over your infrastructure!

Contributions 9

Questions 3

Sort by:

Want to see more contributions, questions and answers from the community?

Si igual sigue sacando error al tratar de ejecutar el comando sp_configure ‘Agent XPs’, 1, tienen que ejecutar así:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Agent XPs', 1
GO
RECONFIGURE
GO

Para los que le sale un error al crear el plan de mantenimiento diciendo que la configuración Agent Xps está deshabilitada por razones de seguridad, solo deben ejecutan este query:

sp_configure 'Agent XPs', 1
GO
RECONFIGURE

Este artículo es un buen complemento para la clase, espero les ayude a comprender mas sobre el tema.

https://www.sqlshack.com/es/mantenimiento-de-indices-sql-server/

Problema al crear el plan de mantenimiento:


Solución:

A mi me paso que GMAIL me mando un mensaje de que la aplicación no es segura, basta con desactivar en GMAIL esa opción para que permita que SQL Server pueda enviar correos.

Yo no utilizo la parte gráfica, es una buena opción, pero me parece que para bases pequeñas. Yo utilizo la solución de para todos las tareas de mantenimiento. Es un poco más complicado de configurar, pero tiene más beneficios.

Pero a la final, lo que se necesita es ejecutar periódicamente los mantenimientos.

````js EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'Agent XPs', 1; RECONFIGURE; ```EXEC sp\_configure 'show advanced options', 1; RECONFIGURE; EXEC sp\_configure 'Agent XPs', 1; RECONFIGURE; ````
Ojo, en la clase anterior nunca se creo el operador.

Consulta para conocer fragmentación de indices tablas en base de datos seleccionada

SELECT  OBJECT_NAME(IDX.OBJECT_ID) AS Table_Name, 
IDX.name AS Index_Name, 
IDXPS.index_type_desc AS Index_Type, 
IDXPS.avg_fragmentation_in_percent  Fragmentation_Percentage
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) IDXPS 
INNER JOIN sys.indexes IDX  ON IDX.object_id = IDXPS.object_id 
AND IDX.index_id = IDXPS.index_id 
ORDER BY Fragmentation_Percentage DESC