Supervición y optimizar el rendimiento del servidor:
Implementar estos pasos ayudará a supervisar y optimizar el rendimiento del servidor de SQL Server, asegurando que las operaciones se realicen de manera eficiente y sin interrupciones significativas.
Supervisión
1. Monitoreo del rendimiento actual:
- Utilizar SQL Server Performance Monitor (PerfMon): Configurar contadores relevantes como CPU, memoria, disco y red para identificar posibles cuellos de botella.
- Utilizar SQL Server Profiler: Capturar trazas para analizar las consultas más costosas en términos de tiempo de ejecución, lecturas/escrituras de disco y uso de CPU.
- Dynamic Management Views (DMVs): Utilizar vistas de administración dinámica como sys.dm_exec_query_stats, sys.dm_exec_requests y sys.dm_db_index_usage_stats para obtener información detallada sobre el rendimiento de las consultas y el uso de índices.
2. Evaluación de índices:
- Missing Indexes DMV: Consultar sys.dm_db_missing_index_details para identificar índices que podrían mejorar el rendimiento.
- Índices fragmentados: Utilizar sys.dm_db_index_physical_stats para identificar índices fragmentados que necesiten ser reconstruidos o reorganizados.
- Índices no utilizados: Consultar sys.dm_db_index_usage_stats para identificar índices que no están siendo utilizados y podrían ser eliminados para reducir la carga de mantenimiento.
3. Revisión de estadísticas:
- Verificar la actualidad de las estadísticas con sys.stats y asegurarse de que están actualizadas, ya que estadísticas desactualizadas pueden afectar negativamente la optimización de consultas.
4. Identificación de consultas problemáticas:
- Utilizar el Query Store (si está habilitado) para identificar las consultas que tienen el mayor impacto en el rendimiento y analizar sus planes de ejecución.
Optimización
1. Optimización de índices:
- Crear índices faltantes: Basado en los datos obtenidos de la DMV sys.dm_db_missing_index_details, crear índices que puedan mejorar el rendimiento de las consultas.
- Reorganizar y reconstruir índices: Utilizar scripts de mantenimiento para reorganizar o reconstruir índices fragmentados, basándose en la información de sys.dm_db_index_physical_stats.
- Eliminar índices no utilizados: Basado en sys.dm_db_index_usage_stats, eliminar los índices que no están siendo utilizados para reducir la carga de mantenimiento.
2. Actualización de estadísticas:
Asegurarse de que las estadísticas están actualizadas utilizando comandos como UPDATE STATISTICS o mediante la configuración de SQL Server para actualizar estadísticas automáticamente.
3. Optimización de consultas:
Analizar y optimizar las consultas más costosas identificadas durante la supervisión. Esto puede incluir la reescritura de consultas, el uso de índices adecuados y la revisión de los planes de ejecución para asegurarse de que SQL Server está utilizando los índices de manera eficiente.
4. Configuración de mantenimiento regular:
- Implementar trabajos de mantenimiento regulares utilizando SQL Server Agent para realizar tareas de reorganización/reconstrucción de índices y actualización de estadísticas.
- Configurar planes de mantenimiento que incluyan copias de seguridad regulares y comprobaciones de integridad de la base de datos.
5. Optimización de hardware y configuración del servidor:
- Revisar y ajustar la configuración del servidor, como la cantidad de memoria asignada a SQL Server, la configuración de MAXDOP (máximo grado de paralelismo) y la configuración de I/O del disco.
- Considerar la actualización de hardware si los recursos actuales son insuficientes para manejar la carga de trabajo.
6. Mejora del diseño de la base de datos:
Evaluar el diseño de las tablas y considerar la normalización/desnormalización según sea necesario para mejorar el rendimiento de las consultas.
Implementar particionamiento de tablas grandes para mejorar el rendimiento y la administración.
7. Monitoreo continuo y ajustes:
- Establecer un sistema de monitoreo continuo para supervisar el rendimiento del servidor y ajustar la configuración según sea necesario.
- Utilizar herramientas de monitoreo de terceros si es necesario para obtener una visión más detallada del rendimiento y posibles problemas.
- Implementar estos pasos ayudará a supervisar y optimizar el rendimiento del servidor central de SQL Server, asegurando que las operaciones de los supermercados se realicen de manera eficiente y sin interrupciones significativas.
script de monitoreo:
–1.———————————————————————————————
–USANDO sys.dm_os_performance_counters
–Consulta de Contadores de CPU
SELECT
object_name,
counter_name,
instance_name,
cntr_value AS value
FROM
sys.dm_os_performance_counters
WHERE
counter_name IN (‘% Processor Time’, ‘% User Time’, ‘% Privileged Time’)
AND object_name LIKE ‘%Processor%’
ORDER BY
object_name, counter_name, instance_name;
–Consulta de Contadores de Memoria
SELECT
object_name,
counter_name,
instance_name,
cntr_value AS value
FROM
sys.dm_os_performance_counters
WHERE
counter_name IN (‘Total Server Memory (KB)’, ‘Target Server Memory (KB)’, ‘Available MBytes’, ‘Page Life Expectancy’)
AND object_name LIKE ‘%Memory%’
ORDER BY
object_name, counter_name, instance_name;
–Consulta de Contadores de Disco
SELECT
object_name,
counter_name,
instance_name,
cntr_value AS value
FROM
sys.dm_os_performance_counters
WHERE
counter_name IN (‘Disk Reads/sec’, ‘Disk Writes/sec’, ‘Disk Read Bytes/sec’, ‘Disk Write Bytes/sec’, ‘Avg. Disk sec/Read’, ‘Avg. Disk sec/Write’)
AND object_name LIKE ‘%Disk%’
ORDER BY
object_name, counter_name, instance_name;
–Consultar contadores de red
SELECT
object_name,
counter_name,
instance_name,
cntr_value AS value
FROM
sys.dm_os_performance_counters
WHERE
counter_name IN (‘Bytes Received/sec’, ‘Bytes Sent/sec’, ‘Packets Received/sec’, ‘Packets Sent/sec’)
AND object_name LIKE ‘%Network%’
ORDER BY
object_name, counter_name, instance_name;
–Combinado de revisi n de rendimiento
SELECT
object_name,
counter_name,
instance_name,
cntr_value AS value
FROM
sys.dm_os_performance_counters
WHERE
(counter_name IN (‘% Processor Time’, ‘% User Time’, ‘% Privileged Time’)
AND object_name LIKE ‘%Processor%’)
OR
(counter_name IN (‘Total Server Memory (KB)’, ‘Target Server Memory (KB)’, ‘Available MBytes’, ‘Page Life Expectancy’)
AND object_name LIKE ‘%Memory%’)
OR
(counter_name IN (‘Disk Reads/sec’, ‘Disk Writes/sec’, ‘Disk Read Bytes/sec’, ‘Disk Write Bytes/sec’, ‘Avg. Disk sec/Read’, ‘Avg. Disk sec/Write’)
AND object_name LIKE ‘%Disk%’)
OR
(counter_name IN (‘Bytes Received/sec’, ‘Bytes Sent/sec’, ‘Packets Received/sec’, ‘Packets Sent/sec’)
AND object_name LIKE ‘%Network%’)
ORDER BY
object_name, counter_name, instance_name;
–2.———————————————————————————————
–Uso de consultas Top 10
WITH TableUsage AS (
SELECT
OBJECT_NAME(ius.object_id) AS TableName,
SUM(ius.user_seeks + ius.user_scans + ius.user_lookups + ius.user_updates) AS UsageCount
FROM
sys.dm_db_index_usage_stats AS ius
INNER JOIN
sys.indexes AS idx ON ius.object_id = idx.object_id AND ius.index_id = idx.index_id
INNER JOIN
sys.tables AS tbl ON ius.object_id = tbl.object_id
WHERE
ius.database_id = DB_ID() — Ensure this is run in the context of the database of interest
GROUP BY
ius.object_id
)
SELECT TOP 10
TableName,
UsageCount
FROM
TableUsage
ORDER BY
UsageCount DESC;
–Consultas m s costosas (por tiempo de CPU)
SELECT TOP 10
qs.sql_handle,
qs.execution_count,
qs.total_worker_time AS total_cpu_time,
qs.total_worker_time / qs.execution_count AS avg_cpu_time,
qs.total_elapsed_time,
qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
qs.total_logical_reads,
qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
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
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY
total_cpu_time DESC;
–Consultas actuales en ejecuci n
SELECT
r.session_id,
r.status,
r.command,
r.cpu_time,
r.total_elapsed_time,
r.logical_reads,
r.writes,
r.wait_type,
r.wait_time,
r.last_wait_type,
r.start_time,
SUBSTRING(qt.text, (r.statement_start_offset/2) + 1,
((CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE r.statement_end_offset
END – r.statement_start_offset)/2) + 1) AS query_text
FROM
sys.dm_exec_requests AS r
CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) AS qt
ORDER BY
r.cpu_time DESC;
–3.———————————————————————————————
–Identificar indices faltantes
SELECT
migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS improvement_measure,
mid.statement AS table_name,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns,
migs.last_user_seek,
migs.unique_compiles,
migs.user_seeks,
migs.user_scans
FROM
sys.dm_db_missing_index_details AS mid
INNER JOIN
sys.dm_db_missing_index_groups AS mig
ON mid.index_handle = mig.index_handle
INNER JOIN
sys.dm_db_missing_index_group_stats AS migs
ON migs.group_handle = mig.index_group_handle
ORDER BY
improvement_measure DESC;
–Indices utilizados frecuentemente
SELECT
OBJECT_NAME(s.object_id) AS [Table Name],
i.name AS [Index Name],
s.user_seeks,
s.user_scans,
s.user_lookups,
s.user_updates,
s.last_user_seek,
s.last_user_scan,
s.last_user_lookup,
s.last_user_update
FROM
sys.dm_db_index_usage_stats AS s
INNER JOIN
sys.indexes AS i
ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE
OBJECTPROPERTY(s.object_id,’IsUserTable’) = 1
ORDER BY
s.user_seeks DESC;
–Indices fragmentados
SELECT
dbschemas.[name] AS [Schema],
dbtables.[name] AS [Table],
dbindexes.[name] AS [Index],
indexstats.index_id,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, ‘DETAILED’) AS indexstats
INNER JOIN
sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN
sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN
sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE
indexstats.avg_fragmentation_in_percent > 10 — Adjust threshold as needed
ORDER BY
indexstats.avg_fragmentation_in_percent DESC;
–Indices no utilizados
SELECT
OBJECT_NAME(i.object_id) AS [Table Name],
i.name AS [Index Name],
i.index_id,
i.type_desc AS [Index Type],
s.user_seeks,
s.user_scans,
s.user_lookups,
s.user_updates,
s.last_user_seek,
s.last_user_scan,
s.last_user_lookup,
s.last_user_update
FROM
sys.indexes AS i
LEFT JOIN
sys.dm_db_index_usage_stats AS s
ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE
OBJECTPROPERTY(i.object_id, ‘IsUserTable’) = 1
AND i.type_desc = ‘NONCLUSTERED’
AND s.user_seeks = 0
AND s.user_scans = 0
AND s.user_lookups = 0
AND s.user_updates = 0
ORDER BY
OBJECT_NAME(i.object_id), i.name;
–4.———————————————————————————————
–Analizar particionamiento de tablas-–
–Tama o de las tablas
SELECT
t.name AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) – SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.object_id = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
–WHERE t.name = ‘YourTableName’ — Replace con el nombre de su tabla
GROUP BY
t.name, p.rows
ORDER BY
TotalSpaceKB DESC;
–Consultas lentas
SELECT TOP 10
qs.total_worker_time AS TotalCPU,
qs.execution_count,
qs.total_elapsed_time / qs.execution_count AS AvgElapsedTime,
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 QueryText
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.text LIKE ‘%YourTableName%’ — Replace con el nombre de su tabla
ORDER BY
TotalCPU DESC;
–5.———————————————————————————————
–Bloqueos a la base de datos
SELECT
blocking_session_id AS BlockingSessionId,
session_id AS BlockedSessionId,
wait_time / 1000 AS WaitTimeSeconds,
wait_type AS WaitType,
resource_description AS ResourceDescription,
r.command AS Command,
r.status AS Status,
r.cpu_time AS CPUTime,
r.total_elapsed_time AS TotalElapsedTime,
DB_NAME(r.database_id) AS DatabaseName,
OBJECT_NAME(p.object_id) AS ObjectName,
t.text AS SQLText
FROM
sys.dm_exec_requests r
INNER JOIN
sys.dm_tran_locks l ON r.session_id = l.request_session_id
LEFT JOIN
sys.partitions p ON l.resource_associated_entity_id = p.hobt_id
CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE
r.blocking_session_id <> 0
ORDER BY
WaitTimeSeconds DESC;
— Verificar el estado de las estad sticas
SELECT
OBJECT_NAME(s.[object_id]) AS TableName,
s.name AS StatisticName,
STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated,
sp.row_count AS RowCount,
sp.rows_sampled AS RowsSampled,
sp.steps AS Steps,
sp.unfiltered_rows AS UnfilteredRows,
CASE
WHEN sp.row_count = 0 THEN 0
ELSE CAST((sp.rows_sampled * 100.0) / sp.row_count AS DECIMAL(5, 2))
END AS SampleRate
FROM
sys.stats AS s
CROSS APPLY
sys.dm_db_stats_properties(s.[object_id], s.stats_id) AS sp
ORDER BY
LastUpdated ASC;
