Monitorización de SQL II


En esta entrada se describen problemas y recomendaciones relacionadas con la monitorización de SQL.

Estado de la Servicio

El siguiente enlace podemos ver el estado de los servicios de SQL Azure para determinar si hay alguna incidencia en la plataforma.

Falta de Tunning

Falta de un diseño adecuado (indices, consultas y planes de ejecución ineficientes…)

  • SQL Database Advisor
  • Analizando Statistics IO/Time de las consultas
  • Analizando Execution Planes de las consultas
  • Consulta DMV que nos suguiere indices que nos faltan (ojo, hay usar el sentido comun para determinar cuales aplicamos).
    SELECT CONVERT (varchar, getdate(), 126) AS runtime,
    mig.index_group_handle,
    mid.index_handle,
    CONVERT (decimal (28,1),
    migs.avg_total_user_cost *
    migs.avg_user_impact *
    (migs.user_seeks + migs.user_scans))
    AS improvement_measure,
    'CREATE INDEX missing_index_' +
    CONVERT (varchar, mig.index_group_handle) +
    '_' +
    CONVERT (varchar, mid.index_handle) +
    ' ON ' +
    mid.statement +
    ' (' + ISNULL (mid.equality_columns,'') +
    CASE WHEN mid.equality_columns IS NOT NULL
    AND mid.inequality_columns IS NOT NULL
    THEN ','
    ELSE ''
    END + ISNULL (mid.inequality_columns, '') +
    ')' +
    ISNULL (' INCLUDE (' + mid.included_columns + ')',
    '') AS create_index_statement,
    migs.*,
    mid.database_id, mid.[object_id]
    FROM sys.dm_db_missing_index_groups mig
    INNER JOIN sys.dm_db_missing_index_group_stats migs
    ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details mid
    ON mig.index_handle = mid.index_handle
    WHERE CONVERT (decimal (28,1),
    migs.avg_total_user_cost *
    migs.avg_user_impact *
    (migs.user_seeks + migs.user_scans)) > 10
    ORDER BY migs.avg_total_user_cost *
    migs.avg_user_impact *
    (migs.user_seeks + migs.user_scans) DESC
    

Consumo de Recursos

Problemas de rendimiento en la ejecución de las consultas.

  • En el panel de SQL Database Performance Insight
  • Consulta DMV donde vemos durante la ultima hora el estado cada 15 segundos del recursos consumidos por la base de datos.

    select * from Sys.dm_db_Resource_stats 
    order by end_time desc
    
  • Consulta DMV a dm_db_wait_stats donde informa los tipos de espera relevantes más frecuentes que se producen en la ejecución de consultas. El siguiente enlace muestra los diferentes tipos de espera. En el siguiente documento SQL Server Performance Tuning Using Wait Statistics nos indica como actuar ante tiempos de espera elevados.
    WITH [Waits] AS
        (SELECT
            [wait_type],
            [wait_time_ms] / 1000.0 AS [WaitS],
            ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
            [signal_wait_time_ms] / 1000.0 AS [SignalS],
            [waiting_tasks_count] AS [WaitCount],
           100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
            ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
        FROM sys.dm_os_wait_stats
        WHERE [wait_type] NOT IN (
            N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
            N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
            N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
            N'CHKPT', N'CLR_AUTO_EVENT',
            N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
     
            -- Maybe uncomment these four if you have mirroring issues
            N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
            N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
     
            N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
            N'EXECSYNC', N'FSAGENT',
            N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
     
            -- Maybe uncomment these six if you have AG issues
            N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
            N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
            N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
     
            N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
            N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT',
            N'ONDEMAND_TASK_QUEUE',
            N'PREEMPTIVE_XE_GETTARGETSTATE',
            N'PWAIT_ALL_COMPONENTS_INITIALIZED',
            N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
            N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',
            N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
            N'QDS_SHUTDOWN_QUEUE', N'REDO_THREAD_PENDING_WORK',
            N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
            N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
            N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
            N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
            N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
            N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
            N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
            N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
            N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
            N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
            N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
            N'WAIT_XTP_RECOVERY',
            N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
            N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
            N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
        AND [waiting_tasks_count] > 0
        )
    SELECT
        MAX ([W1].[wait_type]) AS [WaitType],
        CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
        CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
        CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
        MAX ([W1].[WaitCount]) AS [WaitCount],
        CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
        CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
        CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
        CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S],
        CAST ('https://www.sqlskills.com/help/waits/' + MAX ([W1].[wait_type]) as XML) AS [Help/Info URL]
    FROM [Waits] AS [W1]
    INNER JOIN [Waits] AS [W2]
        ON [W2].[RowNum] <= [W1].[RowNum]
    GROUP BY [W1].[RowNum]
    HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 95; -- percentage threshold
    
  • Consultas DMV que nos indican las consultas que más tiempo consumen o se ejecutan con más frecuencia sobre la base de datos (dm_exec_query_stats):
    -- Top CPU Time Querys 
    SELECT TOP 10
    GETDATE() AS "Collection Date",
    qs.execution_count AS "Execution Count",
    SUBSTRING(qt.text,qs.statement_start_offset/2 +1, 
                    (CASE WHEN qs.statement_end_offset = -1 
                        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
                        ELSE qs.statement_end_offset END -
                            qs.statement_start_offset
                    )/2
                ) AS "Query Text", 
        DB_NAME(qt.dbid) AS "DB Name",
        qs.total_worker_time AS "Total CPU Time",
        qs.total_worker_time/qs.execution_count AS "Avg CPU Time (ms)",     
        qs.total_physical_reads AS "Total Physical Reads",
        qs.total_physical_reads/qs.execution_count AS "Avg Physical Reads",
        qs.total_logical_reads AS "Total Logical Reads",
        qs.total_logical_reads/qs.execution_count AS "Avg Logical Reads",
        qs.total_logical_writes AS "Total Logical Writes",
        qs.total_logical_writes/qs.execution_count AS "Avg Logical Writes",
        qs.total_elapsed_time AS "Total Duration",
        qs.total_elapsed_time/qs.execution_count AS "Avg Duration (ms)",
        qp.query_plan AS "Plan"
    FROM sys.dm_exec_query_stats AS qs 
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt 
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
    ORDER BY qs.total_worker_time DESC
    --- Change Order to get Top Number executed querys. 
    --- ORDER BY qs.EXECUTION_COUNT DESC
    
  • Consulta DMV que nos indica cuantas lecturas y como se realizan (seek, scan y read) de los indices de las tablas; así mismo también las escrituras. Permite detectar indices que se uso no es correcto, ineficiente o simplemente no se usan. Permite ver que indices producen overhead de cara eliminarlos ;-).
    SELECT  OBJECT_NAME(ddius.[object_id], ddius.database_id) AS [object_name] ,		
            ddius.index_id ,
    	i.[name] AS [index_name],
            ddius.user_seeks ,
            ddius.user_scans ,
            ddius.user_lookups ,
            ddius.user_seeks + ddius.user_scans + ddius.user_lookups 
                                                         AS user_reads ,
            ddius.user_updates AS user_writes ,
            ddius.last_user_scan ,
            ddius.last_user_update
    FROM    sys.dm_db_index_usage_stats ddius
     INNER JOIN sys.indexes i ON ddius.[object_id] = i.[object_id]
                                         AND i.[index_id] = ddius.[index_id]
    WHERE   ddius.database_id > 4 -- filter out system tables
            AND OBJECTPROPERTY(ddius.OBJECT_ID, 'IsUserTable') = 1
            AND ddius.index_id > 0  -- filter out heaps 		
    ORDER BY ddius.user_scans DESC
    
  • Consulta DMV que nos indica que se esta ejecutando en la base de datos en ese mismo momento.
    SELECT qs.start_time,
    qs.status,
    qs.command,
    qs.blocking_session_id, -- if is null is not bloquing, if is blocked add additional fields
    qs.open_transaction_count, --Number of transations opened
    qs.cpu_time,
    qs.total_elapsed_time,
    qs.reads,
    qs.writes,
    qs.logical_reads,
    st.TEXT, 
    DB_NAME(st.dbid) AS database_name, 
    st.objectid AS OBJECT_ID, 
    ses.host_name, 
    ses.program_name, 
    db.name
    FROM sys.dm_exec_requests qs
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
    JOIN sys.dm_exec_sessions ses ON ses.session_id = qs.session_id
    JOIN sys.databases db ON ses.database_id = db.database_id
    WHERE sql_handle IS NOT NULL
    

Bloqueos

Hay datos que son actualizados por varias sesiones concurrentemente, lo que provoca esperas 😦
Consulta DMV para detectarlos:

select
conn.session_id as blockerSession,
conn2.session_id as BlockedSession,
req.wait_time as Waiting_Time_ms,
cast((req.wait_time/1000.) as decimal(18,2)) as Waiting_Time_secs,
cast((req.wait_time/1000./60.) as decimal(18,2)) as Waiting_Time_mins,
t.text as BlockerQuery,
t2.text as BlockedQuery
from sys.dm_exec_requests as req
inner join sys.dm_exec_connections as conn
	on req.blocking_session_id=conn.session_id
inner join sys.dm_exec_connections as conn2
	on req.session_id=conn2.session_id
cross apply sys.dm_exec_sql_text(conn.most_recent_sql_handle) as t
cross apply sys.dm_exec_sql_text(conn2.most_recent_sql_handle) as t2

Se puede matar las sessiones (Ojo aborta todo lo que esta en ejecución);

-- Execute on Database
DECLARE @kill varchar(8000) = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
FROM sys.dm_exec_sessions
WHERE session_id <> @@spid

EXEC(@kill);

Timeouts

Consultas que tardan en exceso o bloqueos que duran en exceso (deadlocks).

Falta de Mantenimiento

Con el uso la base de datos se debe realizar acciones de mantenimiento, para evitar perdida de rendimiento.

  • Mantenimiento de indices. Al realizar inserciones y borrados de datos los indices se van degradando (fragmentando). La siguiente consulta permite obtener el grado de fragmentación de los indices de la base de datos. Si este es elevado (por ejemplo, superior al 20%) deberíamos reconstruir el indice (ALTER INDEX {0} ON {1} REBUILD WITH (STATISTICS_NORECOMPUTE = ON, ONLINE=ON). Más información en el enlace Mantenimiento de indices.
    --- For Instance, indexes fragmented over 20 percent
    SELECT name, avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats (
           DB_ID(N'DatabaseName')
         , OBJECT_ID('[NombreDeTabla]')
         , NULL
         , NULL
         , NULL) AS a
    JOIN sys.indexes AS b 
    ON a.object_id = b.object_id AND a.index_id = b.index_id
    where avg_fragmentation_in_percent >20
    order by avg_fragmentation_in_percent DESC
    

    También es importante que las estadísticas del indice estén actualizadas, de cara a elaborar planes de ejecución óptimos.

Conexiones

Establecer conexiones requiere de múltiples idas y vueltas en la red; proceso más lento en la nube. Además SQL Azure aborta (throttle) las conexiones si hay un numero elevado o estas consumen elevados recursos. Debemos proteger la aplicación ante el aborte de las conexiones:

Debemos evitar en lo posible el throttling ya que requiere de restablecer la conexión y volver a ejecutar la consulta. La información de conexiones la tenemos disponible:

  • En el SQL Database Advisor podemos añadir las métricas conexiones activas, falladas y deadlocks.
  • Consulta que muestra el sumario de las conexiones cada 5 minutos.
    -- Execute on Master DB
    select *
    from sys.database_connection_stats 
    where database_name = '[NombreDeBaseDatos]'
    order by start_time desc
    

    Información más detallada por grupo de conexión para encontrar las causas 😉

    -- Execute on Master DB
    select *
    from sys.event_log 
    where database_name = '[NombreDeBaseDatos]'
    order by start_time desc
    

Aplicar Funciones Avanzadas

  • Optimización en Memoria (In-Memory OLTP): Obtiene mejoras de rendimiento al trabajar en memoria: tablas (Memory-optimized tables) y la compilación nativa de stores procedures (Natively compiled modules).
  • Active Geo-Replication: Se replica la base de datos activa y se crean hasta 4 base de datos secundarias en modo lectura.
Anuncios
Esta entrada fue publicada en Sql Azure, Sql Server. Guarda el enlace permanente.

4 respuestas a Monitorización de SQL II

  1. Otro caso interesante:

    http://geeks.ms/windowsplatform/2016/07/20/por-que-unos-cuantos-millones-de-tuplas-pueden-reventar-tu-ultra-flamante-optimizada-parametrizada-y-vitaminada-consulta-en-linq-to-entities-o-tsql/

    Unos apuntes muy interesantes. Comentas sobre TempDb, y que su uso excesivo de TempDb y bloqueos de I/O en los accesos a disco pueden generar esos problemas graves de rendimiento. Me pregunto si hubiera alguna consulta SQL útil para determinar el uso excesivo de TempDb y bloqueos de I/O. http://www.c-sharpcorner.com/article/50-important-queries-in-sql-server/ No entiendo bien la parte que comentabas “mejor hacer la comparación fuera y ampliar el where de la query.”

    Personalmente en temas de optimización de consultas me quedaba en lo clásico (quitar select *, forma de anadir condiciones al WHERE y poco más) https://jsilupu.wordpress.com/ https://blogs.msdn.microsoft.com/apinedo/2007/01/24/mejorar-el-rendimiento-de-queries-en-sql-server/

    Viendo el artículo, ahora sí que hay tener en cuenta las grandes consultas parametrizadas (con filtros complejos y múltiples criterios) y revisar los planes de ejecución. Dudo cómo saber si un plan de ejecución necesita TempDb para guardar “resultados parciales”, aparece de forma visual? En todo caso, hay también un trabajo nocturno para lanzar ciertos comandos como “UPDATE STATISTICS”, y seguramente otros tantos muchos para ir dejando optimizada la base de datos.

    No sé cuáles son esos posts clásicos de buenas prácticas, más en todo caso, aplicado al acceso a datos en .NET y Oracle encontré otro tema de rendimiento con FetchSize Lo comentaban aquí: https://kiquenet.wordpress.com/2016/06/18/performance-dataaccess-oracle-and-odp-net/ Saludos.

  2. En esta página comentan para SQL On Premise

    https://ola.hallengren.com/

    The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, and SQL Server 2016

    Sobre todos estos dos puntos:
    SQL Server Integrity Check
    SQL Server Index and Statistics Maintenance

  3. Pregunton Cojonero dijo:

    Alguna referencia excelente para crear y optimizar consultas en SQL, como comentan aquí http://www.awerty.net/telemantenimiento/realizar-consultas-en-sql/ pero mucho más en detalle y profundidad?

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s