Listado de la etiqueta: performance

Crear indices faltantes

/1 Comentario/en , , , /por

Como buscar y crear índices faltantes en nuestra base de datos

Navegando por la web me tope con esta conjunto de scripts recomendado por Microsoft que aseguran ser útil para cualquier DBA

https://gallery.technet.microsoft.com/Some-random-collection-of-b607bf1b

Como siempre son para MS SQL Server.

Entre ellos encontré uno muy útil que analiza las estadísticas de una base en producción y trate de generarte el TSQL para crear los índices recomendados faltantes

Veamos el código

/**********************************************
--Missing Index Script
**********************************************/

SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

Resultado de correr el script

Luego de unos segundos veremos algo de este estilo:

buscando indices faltantes con tsqlPodremos ver el tiempo promedio estimado de mejora al realizar el índice, como el TSQL para crearlo.

(*) Un error que tiene el script es el nombre del índice que genera, que no debe superar los 128 caracteres, y en el mismo no lo contempla.

Por lo demás promete cumplir lo que ofrece….

Actualmente lo estoy probando en mis bases de producción como alternativa a la herramienta de análisis de rendimiento de MS SQL Server

Nivel de Fragmentacion de una base de dato

/0 Comentarios/en , /por

Nivel de Fragmentación

Uno de los principales tópicos que se debe verificar es el crecimiento de los archivos que componen una base de datos, como así también el nivel de fragmentación de los índices que la componen.

Asegurar el mejor rendimiento de una base de datos en MSSQL Server puede ser una tarea labiosa.

Verifica que bases de datos crecieron de manera automática

Cuando alguno de los archivos que componen la base (mdf y ldf) se queden sin espacios, y tiene permitido autocrecer (autogrowth) detienen la consulta en curso,  y autoincrementa su tamaño, y continua con la operación inicial. Esto si ocurre muy seguido puede provocar que se reduzca el rendimiento de la base.
Una forma de detectarlo es consultando el log del SQL Server:

SELECT
DatabaseName as Base,
[FileName] as Archivo,
CASE EventClass
			WHEN 92 THEN 'Datos'
			WHEN 93 THEN 'Log'
END  as Tipo,
CONVERT(VARCHAR(20), EndTime - StartTime, 114) as TiempoDemora,
StartTime as FechaInicio,
EndTime as FechaFin
 

FROM (
SELECT pt = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces
WHERE is_default = 1
) p
CROSS APPLY sys.fn_trace_gettable(pt, DEFAULT)
WHERE EventClass IN (92, 93)
ORDER BY StartTime DESC

AutogrowthDe acuerdo al resultado será la base en la que deberemos trabajar:

  • Si aparece la TEMPDB la opción mas recomendable es: Crear un archivos de Datos (.MDF) y un archivo de LOG( ldf) por cada procesador que tiene el servidor. Y el tamaño inicial total de la base debe ser el actual + 10% . Y luego irlo ajustando a medida que se vaya necesitando.

Verifica el nivel de fragmentación de los indices

El segundo punto importante es verificar que nivel de fragmentación tienen cada uno de los indices que componen la base de datos. Este punto puede provocar que nuestra base de datos tenga un tamaño mayor que el real. Y provoque que las consultas se demoren un poco mas de lo que deberian:

Para corroborarlo podemos usar el siguiente script:

IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp
GO
CREATE TABLE #temp (
db SYSNAME DEFAULT DB_NAME(),
flname SYSNAME,
size_after_growth DECIMAL(18,2),
size DECIMAL(18,2),
space_used DECIMAL(18,2),
growth INT,
is_percent_growth BIT,
PRIMARY KEY CLUSTERED (db, flname)
)
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = STUFF((
SELECT '
USE [' + name + ']
INSERT INTO #temp (flname, size_after_growth, space_used, size, growth, is_percent_growth)
SELECT
name
, CASE WHEN is_percent_growth = 1 THEN size * (1 + (growth / 100.)) ELSE size + growth END * 8. / 1024
, space_used = FILEPROPERTY(name, ''SpaceUsed'') * 8. / 1024
, size = size * 8. / 1024
, CASE WHEN is_percent_growth = 1 THEN growth ELSE growth * 8. / 1024 END
, is_percent_growth
FROM sys.database_files'
FROM sys.databases
WHERE [state] = 0
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
EXEC sys.sp_executesql @SQL
SELECT
db
, flname
, size_after_growth
, size
, space_used
, CAST(growth AS VARCHAR(10)) + CASE WHEN is_percent_growth = 1 THEN ' %' ELSE ' MB' END
FROM #temp
WHERE (is_percent_growth = 0 AND growth < 50)
OR (is_percent_growth = 1 AND growth < 5)
OR (size - space_used < 20)

 

Para correguirlo podemos ejecutar el siguiente script por cada una de nuestras bases de datos:

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = (
SELECT '
ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +
CASE WHEN s.avg_fragmentation_in_percent > 30
THEN 'REBUILD'
ELSE 'REORGANIZE'
END + ';
'
FROM (
SELECT
s.[object_id]
, s.index_id
, avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent)
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s
WHERE s.page_count > 128 -- > 1 MB
AND s.index_id > 0 -- <> HEAP
AND s.avg_fragmentation_in_percent > 5
GROUP BY s.[object_id], s.index_id
) s
JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
JOIN sys.objects o ON o.[object_id] = s.[object_id]
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
PRINT @SQL
EXEC sys.sp_executesql @SQL

 

 

Mejorar rendimiento de SQL Server – Estadísticas de Espera

/0 Comentarios/en , /por

En este oficio se escucha de manera repetitiva frases como: «Mi servidor anda lento«, «Se tarda demasiado«, «como hago para que funcione más rápido«.

Al intentar encontrar la causa del problema muchas veces no sabes dónde empezar. En esta serie de artículos veremos algunos pasos a seguir para tratar de determinar el motivo que lo origina.
A tener en cuenta:

Generalmente el resultado es ocasionado, no solo por un motivo, sino por la suma de varios. Por lo se debe analizar y tomar resultados de la mayor cantidad de variables posibles (red, disco, memoria, consultas, etc) y recién ahí, en conjunto tomar medidas. Caso contrario, si se analizan por separado, seguramente cada uno determinará una solución diferente.

 

Estádisticas de Espera (waits statistics)
Nos darán una visión general de cuáles son los mayores tiempos de espera a nivel de recursos y de sistema operativo.
En esta oportunidad nos valemos de un script de http://habrahabr.ru/post/216309/ para listar los mismos:

SELECT TOP 10 
wait_type , max_wait_time_ms wait_time_ms , signal_wait_time_ms , wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms , 
100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS percent_total_waits , 100.0 * signal_wait_time_ms / 
SUM(signal_wait_time_ms) OVER ( ) AS percent_total_signal_waits , 100.0 * ( wait_time_ms - signal_wait_time_ms ) / 
SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits FROM sys.dm_os_wait_stats WHERE wait_time_ms > 0 
-- remove zero wait_time 
AND wait_type NOT  IN 
 -- filter out additional irrelevant waits
(	'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH', 'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 
	'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH', 'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',
	'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP', 'LAZYWRITER_SLEEP', 
	'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS', 'DBMIRROR_EVENTS_QUEUE',
	'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 
	'XE_DISPATCHER_JOIN', 'RESOURCE_QUEUE' ) 
ORDER  BY wait_time_ms DESC

 


 

El resultado dependerá de su servidor:
A continuación una descripción de los waits mas comunes:

  • CXPACKET o A menudo indica nada más que ciertas consultas se ejecutan con el paralelismo; CXPACKET no son una señal inmediata de los problemas, a pesar de que puede ser el síntoma de otro problema, asociado con uno de los otros tipos de espera alto valor en la instancia.
  • SOS_SCHEDULER_YIELD o Las tareas que se ejecutan en el sistema pueden haber superado su cantidad permitida, y están teniendo que esperar en la cola de ejecutables. Esto puede indicar que el servidor está bajo demasiada presión.
  • ThreadPool o Una tarea tuvo que esperar a tener un trabajador libre para poder ejecutarla. Esta podría ser una señal de inanición de subproceso de trabajo, lo que requiere un aumento en el número de CPU en el servidor, para manejar una carga de trabajo altamente concurrente, o puede ser una señal de bloqueo, lo que resulta en un gran número de tareas paralelas que consumen los subprocesos de trabajo por largos períodos.
  • LCK_ * o Estos esperan tipos significan que el bloqueo se produce en el sistema y que las sesiones han tenido que esperar para adquirir un bloqueo de un tipo específico, que fue detenido por otra sesión de base de datos. Este problema puede investigarse más a fondo utilizando, por ejemplo, la información de los sys.dm_db_index_operational_stats.
  • PAGEIOLATCH_ *, IO_COMPLETION, WriteLog o Estas esperas son comúnmente asociados con el disco I / O y ocasionan los cuellos de botella, aunque la causa raíz del problema puede ser, y es comúnmente, una consulta de bajo rendimiento que está consumiendo cantidades excesivas de memoria en el servidor.
  • PAGEIOLATCH_ * se asocian específicamente con los retrasos en la capacidad de leer o escribir datos desde los archivos de base de datos.
  • WriteLog están relacionados con problemas con la escritura en archivos de registro. Estas esperas deben evaluarse conjuntamente con las estadísticas de archivos virtuales, así como los contadores de rendimiento de disco físico, para determinar si el problema es específico de una sola base de datos, archivo o disco, o es ejemplo de ancho.
  • PAGELATCH_ * o Non-I / O espera pestillos de páginas de datos en el grupo de búferes. Muchas veces PAGELATCH_ * esperas están asociados con problemas de contención de la asignación. Uno de los temas más conocidos de las asignaciones asociadas a PAGELATCH_ * espera que ocurra en tempdb cuando se crea y se destruye en el tempdb un gran número de objetos y el sistema experimenta la contención en el Mapa Global Shared Asignación (SGAM), mapa de asignación global (GAM ), y Página espacio libre (PFS) páginas en la base de datos tempdb.
  • LATCH_ * o Estas esperas se asocian con objetos de peso ligero a corto plazo de sincronización que se utilizan para proteger el acceso a las memorias caché internas, pero no el caché del búfer. Estas esperas pueden indicar una serie de problemas, dependiendo del tipo pestillo. La determinación de la clase de enganche específica que tiene el tiempo de espera más acumulado asociado a él se puede encontrar mediante la consulta de los sys.dm_os_latch_stats del DMV.
  • ASYNC_NETWORK_IO o Esta espera es a menudo erróneamente atribuido a un cuello de botella de la red. De hecho, la causa más común de esta espera es que una aplicación cliente está realizando el procesamiento de los datos fila por fila (tipo cursor). Y si bien están siendo transmitidos desde SQL Server como un conjunto de resultados el cliente los procesa fila por fila. La corrección de este tipo de espera generalmente requiere cambiar el código del lado del cliente para que se lea el conjunto de resultados lo más rápido posible, y luego se realiza el procesamiento por el lado del cliente.

 Una vez hecho algún cambio deberé reiniciar los contadodes, y empezar a recopilar las estadísticas de nuevo, para verificar si hubo alguna mejora:

DBCC SQLPERF('sys.dm_os_wait_stats',clear)

Esta primer entrada es el primer paso para determinar obtener una visión generar. En próximas entradas veremos algunas tareas específicas para continuar con la mejora del rendimiento del motor SQL Server.