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
Introducción al curso
Cómo aprenderás sobre optimización de bases de datos en SQL Server
¿Por qué es importante optimizar tu base de datos?
Lista de requerimientos
Instalación de herramientas
Instalación de la base de datos
Analizar un plan de ejecución
Conociendo los índices
¿Cómo nos pueden ayudar los índices?
Analizar las consultas para crear índices
Cuál índice utilizar y cómo forzar uno en específico
¿Siempre son buenos los índices?
Optimización de consultas
Qué es Merge y cómo podemos utilizarlo
Distintas opciones de utilizar Merge
Qué es un Trigger y cómo podemos crearlo
Manejo de errores en trigger
Triggers a nivel administrativo
Qué es un Constraint y cómo funciona
Tablas versionadas
Utilizando Full Text Search para búsquedas
Queries más inteligentes
Cómo funcionan las funciones tabla
Tipos de funciones
Cómo funcionan las vistas
Procedimientos almacenados
Tablas temporales vs. tablas variables
Optimización de base de datos
Configuración de un Database Mail
Planes de mantenimiento: ordenar los índices en nuestra base de datos
Planes de mantenimiento: estrategia de respaldos
Planes de mantenimiento: log de base de datos con un tamaño controlado
Configurando TempDB
Monitoreo
Monitoreo con sp_who3
Monitoreo de actividades
Conclusión
Conclusión del curso
You don't have access to this class
Keep learning! Join and start boosting your career
If you already have an account,
Planes de mantenimiento: ordenar los índices en nuestra base de datos
25/31Resources
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?
-
Create a new plan: Within SQL Management Studio, go to the "Management" section. Right click on "Maintenance Plans" and select "New Maintenance Plan".
-
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".
-
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.
-
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.
-
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.
-
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:
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
Want to see more contributions, questions and answers from the community?