Etiquetas

, , ,

cuello de botella

Los cuello de botella hacen a nuestras aplicaciones lentas y limitan considerablemente el rendimiento de nuestros sistemas, muchas veces cuando la arquitectura fisica que acompaña a nuestra aplicación no ha sido diseñada para ser escalable estos cuellos de botella se producen afectando los tiempos de respuesta de nuestros procesos. En SQL Server hay cuatro razones típicas por las que se producen los cuellos de botella, estas son :

-Un disco duro con baja velocidad lo cual provoca por supuesto lentitud a la hora de leer y escribir en los distintos sectores del disco.

Solución: un disco dura mas rápido o colocar la solución RAID mas adecuada para nuestro diseño de base de datos, generalmente se recomienda un sistema RAID 10 para nuestro archivo log y nuestros archivos de índices mientras que se recomienda un sistema RAID 5 para nuestros archivos de datos.

-Poca memoria lo cual causa una excesiva paginación.

Solución: agregar mas memoria para evitar la cantidad de paginación que tiene que realizar el procesador.

-Una utilización muy alta del procesador que generalmente es causada por tiempos demorados durante la paginación.

Solución: Agregar mas procesadores.

-Un bajo ancho de banda.

Solución: tratar de utilizar una red Ethernet de un gigabyte.

Algunas herramientas que podemos utilizar para detectar la existencia de cuellos de botella son:

Task Manager: nos permite tener una visión general de lo que esta ocurriendo con la utilización de nuestro CPU y con la cantidad de memoria que se esta usando además de poder ver cuantos recursos esta utilizando cada proceso.

System Monitor : desde aquí podemos ver distintos contadores del sistema operativo y del SQL , algunos contadores útiles en nuestro caso serán:

-% processor time counter (>80% es indicación de un cuello de botella a nivel del procesador)

–          % disk time (>50% es indicación de un cuello de botella a nivel de disco)

–          avg. disk queue length (>2 indicación de un cuello de botella a nivel de disco)

–          avg. disk sec/read or avg. disk sec/write (>10-20 ms 2 indicación de un cuello de botella a nivel de disco)

–          vg. disk reads/sec or avg. disk writes/sec (>85%  indicación de un cuello de botella a nivel de disco)

-SQL Server: Buffer manager object, buffer cache hit ratio, page life expectancy, checkpoint pages/sec, lazy writes/sec.

–  Para monitoria tu ancho de banda bytes total/sec, current bandwidth.

-SQL Profiler

Esta herramienta te permitirá capturar toda la actividad que ocurre en tu base de datos incluyendo cada instrucción t-sql ejecutada, con el podemos capturar información referida al CPU, a las lecturas, escrituras y tiempos de respuestas.

-Dynamic Management Views

Hay muchas consultas que podemos ejecutar que nos permitirán identificar cuellos de botella en nuestro sistema.

Que componente esta consumiendo mayor cantidad de memoria en el sql Server.

SELECT TYPE, SUM(MULTI_PAGES_KB) FROM
SYS.DM_OS_MEMORY_CLERKS WHERE
MULTI_PAGES_KB != 0 GROUP BY TYPE

Cuales objetos del sql están consumiendo memoria

SELECT TYPE, PAGES_ALLOCATED_COUNT FROM

SYS.DM_OS_MEMORY_OBJECTS WHERE
PAGE_ALLOCATOR_ADDRESS IN (SELECT TOP 10
PAGE_ALLOCATOR_ADDRESS FROM
SYS.DM_OS_MEMORY_CLERKS ORDER BY
MULTI_PAGES_KB DESC) ORDER BY
PAGES_ALLOCATED_COUNT DESC

Las 25 tablas que tienen mayor espera de lectura y escritura.

SELECT TOP 25 DB_NAME(D.DATABASE_ID) AS

DATABASE_NAME,
QUOTENAME(OBJECT_SCHEMA_NAME(D.OBJECT_ID,
D.DATABASE_ID)) + N’.’ +
QUOTENAME(OBJECT_NAME(D.OBJECT_ID,
D.DATABASE_ID)) AS OBJECT_NAME, D.DATABASE_ID,
D.OBJECT_ID, D.PAGE_IO_LATCH_WAIT_COUNT,
D.PAGE_IO_LATCH_WAIT_IN_MS, D.RANGE_SCANS,
D.INDEX_LOOKUPS FROM (SELECT DATABASE_ID,
OBJECT_ID, ROW_NUMBER() OVER (PARTITION BY
DATABASE_ID ORDER BY
SUM(PAGE_IO_LATCH_WAIT_IN_MS) DESC) AS
ROW_NUMBER, SUM(PAGE_IO_LATCH_WAIT_COUNT) AS
PAGE_IO_LATCH_WAIT_COUNT,
SUM(PAGE_IO_LATCH_WAIT_IN_MS) AS
PAGE_IO_LATCH_WAIT_IN_MS,
SUM(RANGE_SCAN_COUNT) AS RANGE_SCANS,
SUM(SINGLETON_LOOKUP_COUNT) AS
INDEX_LOOKUPS FROM
SYS.DM_DB_INDEX_OPERATIONAL_STATS(NULL, NULL,
NULL, NULL) WHERE PAGE_IO_LATCH_WAIT_COUNT > 0
GROUP BY DATABASE_ID, OBJECT_ID ) AS D LEFT JOIN
(SELECT DISTINCT DATABASE_ID, OBJECT_ID FROM
SYS.DM_DB_MISSING_INDEX_DETAILS) AS MID ON
MID.DATABASE_ID = D.DATABASE_ID AND
MID.OBJECT_ID = D.OBJECT_ID WHERE
D.ROW_NUMBER>20 ORDER BY
PAGE_IO_LATCH_WAIT_COUNT DESC

Lista de columnas que debería de tener índices.

SELECT * FROM SYS.DM_DB_MISSING_INDEX_GROUPS
G JOIN SYS.DM_DB_MISSING_INDEX_GROUP_STATS GS
ON GS.GROUP_HANDLE = G.INDEX_GROUP_HANDLE
JOIN SYS.DM_DB_MISSING_INDEX_DETAILS D ON
G.INDEX_HANDLE = D.INDEX_HANDLE

Las 25 recopilaciones de procedimientos almacenados que están consumiendo más CPU.

SELECT TOP 25 SQL_TEXT.TEXT, SQL_HANDLE,
PLAN_GENERATION_NUM, EXECUTION_COUNT, DBID,
OBJECTID FROM SYS.DM_EXEC_QUERY_STATS A
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE)
AS SQL_TEXT WHERE PLAN_GENERATION_NUM >1
ORDER BY PLAN_GENERATION_NUM DESC

Los 50 mayores consumidores de cpu

SELECT TOP 50 SUM(QS.TOTAL_WORKER_TIME) AS
TOTAL_CPU_TIME, SUM(QS.EXECUTION_COUNT) AS
TOTAL_EXECUTION_COUNT, COUNT(*) AS
NUMBER_OF_STATEMENTS, SQL_TEXT.TEXT,
QS.PLAN_HANDLE FROM SYS.DM_EXEC_QUERY_STATS
QS CROSS APPLY
SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) AS SQL_TEXT
GROUP BY SQL_TEXT.TEXT,QS.PLAN_HANDLE ORDER
BY SUM(QS.TOTAL_WORKER_TIME) DESC