Listar Las Queries Mas "Pesadas" de Nuestro Servidor - (las queries "que mas tardan" en devolver resultados)

Estimados amigos, una vez más con ustedes.

Cuántas veces se han preguntado cuáles son las queries que se presentan como "las más complicadas de resolver" para nuestro motor de base de datos?

 Pues aquí les dejo un script que hará este trabajo para uds...

 Es muy útil  cuando notan problemas de performance y deciden comenzar a "atacarlos".

 Es una muy buena práctica dedicarle un tiempo a estudiar y tratar de mejorar al menos las queries más costosas (añadiendo índices, mejorando las estadísticas, cambiando lo lógica de las queries etc)

 Este script les devuelve las 10 queries más costosas con datos tomados desde la última modificación del plan de ejecución.

 Desde ya, incluye todas las queries que atacan por consola o por medio de un store procedure.

 La última columna les da un link que les permite ver el plan de ejecución gráfico.

 Cualquier duda estoy a su disposición.

 Saludos desde Argentina queridos colegas.

 IMPORTANTE --> El nivel de compatibilidad de la BD debe ser 90 o superior.   

Detalle: Lista las 10 queries más costosas de nuestra base de datos 

Autor  : Gustavo Herrera para Sql Server Para Todos                             

SELECT

TOP 10

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,

qs.execution_count as 'cantidad de ejecuciones',

qs.total_logical_reads as 'total_lecturas_lógicas',

qs.total_logical_writes as 'total_escrituras_lógicas',

qs.total_worker_time 'total_CPU_consumida_ms',

qs.total_elapsed_time/1000000/60 'total_mts_consumidos_x_la_ejecución',

qs.max_elapsed_time/1000000/60 'máxima_tardanza_en_la_ejecución_mts',

qp.query_plan 'link_plan_ejecucion'

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY qs.last_elapsed_time DESC

SP_WHO2 - Estudiando Lentitud en Sql Server

Amigos,

Hay tres análisis básicos que pueden ayudarnos a la hora de estudiar un degradamiento general de nuestra Base de Datos.

A) Estudiar Posibles Bloqueos

B) Analizar Valores Altos de I/O y CPU

C) Relevar Múltiples Procesos con un  mismo SPID, (el spid es el nº que el sql server le da a cada proceso corriendo en el servidor)

  
Para estudiar estos tres puntos vitales de nuestro sistema mi arma favorita es el sp_who2

Este sp muestra todas las sesiones que están actualmente establecidas en el servidor.

La sentencia es sumamente sencilla:

EXEC sp_who2

El resultado ha obtener será el siguiente:



 Los primeros 50 SPID están reservados para procesos del sistema que generalmente no merecen nuestra atención, ya que no suelen ser el origen de la degrdación de la performance del server.

 Ahora los puntos que SI debemos tener en cuenta

 1) Bloqueos

     Existe una columna llamada BlkBy la cual nos informa el SPID que está bloqueando al proceso de la fila que estemos observando.

     Antes de "matar al proceso"  con el comando KILL + SPID,  podemos saber en que consiste el proceso  que está bloqueado. Cómo? Pues simplemente utilizando la sentencia DBCC Inputbuffer (SPID).

      Puede ocurrir que varios procesos estén bloqueados por el mismo SPID, en ese caso conviene estudiar en profundidad el proceso bloqueante


  2) Altos Valores de INPUT/OUPUT y/o CPU
   
    Para esto disponemos de dos columnas "CPUTime" y "DiskIO". Los procesos que demanden mucha lectura/escritura en disco y/ o CPU merecen ser analizados en detalle. Pueden hacerse mejoras de distinto tipo para evitar esta utilización excesiva de los recursos del sistema. (Recomiendo puntualmente analizar en detalle los planes de ejecución para determinar los pasos a seguir)


 3) Procesos Paralelos

    Cuando observamos varias filas destinadas a un mismo SPID tenemos que saber que es una acción que toma el SQL SERVER al estimar que le llevará un largo tiempo resolver el proceso. Cuando esto ocurre el sql server "tira" multiples hilos para el mismo proceso pudiendo tener esto consecuencias muy malas en términos de performance del server.
Una vez más, estudiemos el proceso "responsable" y tratemos de mejorarlo. De ser imposible podemos disminuir el máximo número de procesadores que el paralelismo puede usar. Esto se hace a nivel de servidor (MAXDOP).


 Amigos, eso es todo, espero que el artículo sea de utilidad.

 Saludos.
  
 Otro Post Recomendado por el Autor

 "Mantenimiento de Estadísticas Para una Performance Optima de nuestra BD"

 https://www.blogger.com/blogger.g?blogID=4841087034568585749#editor/target=post;postID=5281491474180357583;onPublishedMenu=allposts;onClosedMenu=allposts;postNum=0;src=postname

Script Para Consultar el Historial de Ejecución de un Job

Amigos,

    Cuantas veces han tratado de ver el historial de ejecución de un job por consola haciendo botón derecho sobre el job y "view history" y se han encontrado con el molesto cartelito de "Time Out Expired", quedando vuestras manos vacías?

     Estoy seguro que muchas veces... Ppor ejemplo en ocasión del fallo de un Job ante la necesidad de conocer  el mensaje del error del job....

     Pues bien, les traigo un script super práctico que les permitirá saber, no solo el motivo en caso de un error o simplemente el detalle de lo ejecutado en caso de un job exitoso, sino también la duración en la ejecución del job y el resultado final de mismo.

     Entonces.. tengan a mano este script también cuando sospechen que un job "esta tardando en ejecutarse más que hace xx tiempo" . Con este script prontamente podrán evacuar esa duda.

     Con uds el script, solo seteen el run_date para limitar la fecha para la cual quieren la data.

     Cualquier duda como siempre quedo a disposición, saludos!!

 ----------------------------------------------------------------------

-- Detalle: Estudia La Historia de Ejecución de un Job A Partir de una Fecha

-- Tip: Utiliza la tablas msdb SysJobHistory/SysJobs    

-----------------------------------------------------------------------

 use msdb

 select

 step_id,

 message,

 run_date,

 case

   len(run_duration) when  6 then left(convert(varchar(6), run_duration),2)+':'+substring(convert(varchar(6), run_duration),3,2)+':'+right(convert(varchar(6),run_duration),2)

                     when  5 then '0'+left(convert(varchar(5), run_duration),1)+':'+substring(convert(varchar(5), run_duration),2,2)+':'+right(convert(varchar(6),run_duration),2)   

                     when  4 then '00'+':'+left(convert(varchar(4),run_duration),2)+':'+right(convert(varchar(4),run_duration),2)

                     when  3 then '00:0'+left(convert(varchar(3),run_duration),1)+':'+right(convert(varchar(4),run_duration),2)     

        end       

         as 'run_duration',

 case run_status when 0 then 'failed'

                  when  1 then 'succeded'

                  when 2 then 'retry'

                  when 3 then 'canceled'

                 end as 'run_status'

 from

 SysJobHistory as a

 --

 left join SysJobs  as b

 on a.job_id = b.job_id

 --

where

b.name = 'DBA - Indexes Optimize - Arcalltv - Miercoles 6 AM' and

run_date > 20130902

order by run_date

Script Para Determinar Nivel de Fragmentación de Indices

Que tal amigos, una vez más con uds. esta vez para acercarles un script útil, para tener a mano a la hora de echar un vistazo a los niveles de fragmentación de nuestros idx.
Les recomiendo antes que nada leer en mi blog el white paper relacionado con el tema.

Hoy nada de teoría, simplemente dejarles el script aclarandoles lo siguiente:

- El script puede ser utilizado para medir la fragmetnación a nivel de Servidor/Base de Datos/Tablas (por default se los dejo "comentado (--)" para que lo puedan utilizar a nivel de Base de Datos

- En el where pueden filtrar el nivel de fragmentación que desean listar.

Quedo a disposición de uds. Saludos!

-----------------------------------------------------------------------------

-- Detalle: Se listan lo objetos que se encuentran fragmentados

-- acorde a lo seteado en el wheare    

-- Detalle 2 (podemos hacer a nivel de Servidor/ Base de Datos / Tabla)                          

-- Autor: GH - Octubre 2013                                                                      

-- --------------------------------------------------------------------------

SELECT

db.name as 'Database',

o.name as 'Table',

case when I.name is null then 'Heap' else I.name end as 'Indice',

left(avg_fragmentation_in_percent,4) as '% frag'

--FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) AS a   

FROM sys.dm_db_index_physical_stats (DB_ID('NOMBRE BASE DE DATOS'), NULL, NULL, NULL, NULL) AS a  

--FROM sys.dm_db_index_physical_stats (DB_ID('NOMBRE BASE DE DATOS'), OBJECT_ID('NOMBRE DE TABLA'), NULL, NULL, NULL) AS a  

-- left para obtener nombre db --

LEFT JOIN Sys.Databases as DB    

on A.Database_Id = DB.Database_Id

-- left para obtener nombre del idx --

LEFT JOIN sys.indexes AS I

ON

a.object_id = I.object_id AND

a.index_id = I.index_id

-- left para obtener nombre tabla --

LEFT JOIN sys.objects as o

on I.[object_id] = o.[object_id]

----------------

WHERE avg_fragmentation_in_percent > 20 -- % de fragmentación en este caso > 20

and index_level = 0 --> analizando la rama principal de los índices

and page_count > 1000 --> mas de 1000 hojas

order by

o.name 

Compute Scalar - Qué es y Cómo Evitarlo

Amigos,

    Hoy les quiero hablar acerca de un operador llamado "Computer Scalar", presente en nuestros planes de ejecución frecuentemente.

   Como su nombre lo sugiere el operador indica la presencia de un cómputo escalar que devuelve un valor. También podemos hallar este  operador  dentro de nuestro plan de ejecución cuando realizamos conversiones explícitas o implícitas dentro de nuestas queries.
  
   Normalmente ignoramos al "Compute Scalar" ya que no representa un costo grande dentro del contexto de nuestro plan de ejecución  mas debemos tratar de tomar las medidas necesarias para evitarlo cuando realizamos operaciones de conversión o cálculos dentro de cursores grandes o de  loops de dimensiones considerables  Por qué? Porque pueden generar grandes cargas en nuestro server a nivel cpu, derivando esto en problemas de performance.   
   
   Les dejo pues un ejemplo de una operación que genera un "Computer Scalar" y abajo el cómo evitar el operador obteniendo grandes ganacias de performance con el mismo resultado final.

  1)  Operación que genera Computer Scalar:
 
  DECLARE @Count Int

 SET @Count = 0

 WHILE @Count < 100000000

 BEGIN

  IF EXISTS(SELECT Idcdr FROM Tabla WHERE Idcdr = @I)

     Begin

      --Accion

     End

  SET @Count = @Count + 1;

 END

  

  2)  Evitando el  Computer Scalar con el uso del @@ROWCOUNT en lugar del Exists

 DECLARE @Count Int

 SET @Count = 0

 WHILE @Count < 1000000

 BEGIN

   SELECT Idcdr FROM Tabla WHERE Id = @I

   IF @@ROWCOUNT > 0

   BEGIN

     -- Accion

   END

   SET @Count = @Count + 1;

 END

  Amigos "de yapa" y para que entiendan lo importante que es definir una variable corréctamente les dejo un ejemplo más que genera un Compute Scalar por "conversión implícita" y la solución como paso 2 del ejemplo:

 1)  Buscando el Máximo Idcdr (campo integer) dentro de una tabla produciendo un  Compute Scalar por mala definición de la variable @idini defnida como numeric (18,0) (cuando el campo idcdr de la tabla cdr_subscriptions es integer)

 -- Declaro variables --

Declare @idini numeric

-- Determino el Idcr maximo de la Tabla -

Select @idini =  coalesce (max(idcdr),0)

from cdr_subscriptions with (nolock)

 
** La mala definición de la variable genera una conversión implícita de numeric a integer lo que a su vez produce un "Compute Scalar"

  2)  Buscando el Máximo Idcdr (campo integer) dentro de una tabla eliminando el  Compute Scalar definiendo corréctamente la variable @idini  -esta vez como integer-

 -- Declaro variables --

Declare @idini integer

-- Determino el Idcr maximo de la Tabla -

Select @idini =  coalesce (max(idcdr),0)

from cdr_subscriptions with (nolock)

 

 Amigos, espero que lo expuesto haya sido de vuestra utilidad, les dejo mi saludo y como siempre estoy a vuestra disposición.

 Gustavo Herrera.

Espacio Ocupado Por Cada Una de Las Tablas En La Base de Datos

Amigos,

     Hoy quiero compartir un script muy útil que he preparado para uds.

     Cuántas veces notamos que nuestras bases de datos crecen a un rítmo importante sin saber exáctamente cuál o cuáles son las tablas "culpables" de ese crecimiento?

     Cuánta veces hemos pensado al ver nuestras tablas por consola "mmmm me parece que los índices ya representan mucho del espacio ocupado por la totalidad de la tabla..Debería analizar esto..."

      Pues bien, les traigo la solución...;  Un simple script que, haciendo uso de un sp del sistema llamado sp_spaceused,  nos permite listar para cada tabla de usuario de la base de datos en la que lo ejecutemos lo siguiente: (en orden descendente por la cantidad de registros de cada tabla):

    A - Epacio total ocupado por la tabla (MB)                                            

    B - Espacio en la tabla utilizado por datos (MB)                                       

    C - % del espacio utilizado por datos                                                  

    D - Espacio en la tabla utilizado por índices (MB)                                     

    E - % del espacio utilizado por indices                                                -- 

    D - Cantidad total de registros de las tabla

     Solo les queda probarlo... si tienen dudas... como siempre estoy a vuestra disposición...

BEGIN

-- Declaro Variable

Declare @object_name as varchar(50)

-- Declaro Tabla Temporal

CREATE TABLE #results

  (name varchar(50),

   filas integer,

   reserved varchar(50),

   data varchar(50),

   index_size varchar(50),

   Unused varchar(50))

/* Cursor_Tablas -Guarda en la tabla #result

 el resultado de la ejecución del sp_spaceused

sobre c/u de las tablas de usuario de la BD */

DECLARE Cursor_Tablas

Cursor For

Select distinct(s.name + '.' + o.name)

      from sys.schemas s

INNER JOIN sys.objects o  

ON o.schema_id = s.schema_id

Where

      type = 'U'  --Tablas de usuario

OPEN Cursor_Tablas

Fetch Next From  Cursor_Tablas

Into @object_name

WHILE @@FETCH_STATUS = 0

 BEGIN

   Insert Into #results

   EXEC sp_spaceused @object_name

   Fetch Next From cursor_tablas

   Into @object_name

 END;

Close Cursor_Tablas;

Deallocate Cursor_Tablas;

-- Se quita el "KB" de la tabla #result --

UPDATE

#results

SET

reserved = LEFT(reserved,LEN(reserved)-3),

data = LEFT(data,LEN(data)-3),

index_size = LEFT(index_size,LEN(index_size)-3),

unused = LEFT(unused,LEN(unused)-3)

-- Se listan las tablas ordenadas descendentemente por la cantidad de registros --

SELECT

distinct(t.Name) AS 'Table',

reserved/1024 as '[Disco (MB)]',

data/1024 as '[Datos (MB)]',

case when (data/1024) = 0 then 0.00 else (((data/1024)*100)/(reserved/1024)) end as '[% Datos (MB)]',

index_size/1024 as '[Idx (MB)]',

case when (data/1024) = 0 then 0.00 else 100-(((data/1024)*100)/(reserved/1024)) end as '[% Index (MB)]',

filas AS  'Records'

FROM #results as t

Inner Join sys.objects o

ON o.name =  t.name

Where

reserved is not null and

o.Type <> 'S' AND 

O.Type <> 'IT'     

order by

filas desc

--Eliminar la tabla temporal

Drop Table #results

Analizar Utilización de Indices - Depurar Indices no Utilizados

Amigos, 

    En más e una oportunidad hemos caído en la cuenta de que nuestros índices representan un valor elevado del espacio en mb que está utilizando una tabla. Este es un signo de que "algo está mal" y que, por consiguiente, es necesario revalidar  los índices creados historicamente sobre la tabla para poder establecer su utilización verdadera y de ese modo llevar adelante una eventual depuración.

  Recordemos como concepto general que los índices bien utlizados aceleran los tiempos de respuesta de una consulta  reduciendo los tiempos de  bloqueo sobre las tablas en las cuales se establecen la peticiónes. Por contrapartida una tabla excesivamente indexada genera una notable pérdida de performance en los procesos de Insert / Update / Delete ya que el motor de nuestra base de datos, dependiendo de la magnitud de estas operaciones, se verá forzado a rehacer los idx para mantenerlos actualizados, generando  una sobrecarga innecesaria en nuestro servidor y tiempos de actualización de data mucho más perezosos.

  Una vez más la gente de Microsoft pensó en nosotros, los sacrificados DBA, y puso a nuestra diposición una vista dinámica que, joineada con otras vistas del sistema, nos proporciona la data necesaria como para realizar esta tarea de análisis.

  Estoy hablando de la vista sys.dm_db_index_usage_stats..
   
  Esta vista acumula, desde el último reinicio del motor de nuestra base de datos, un contador para cada uno de los índices, y para los siguientes eventos, (relacionados diréctamente con la utilización de los indices como producto de la ejecucion de las queries por nosotros definidas ) a saber:

       user_seeks --  cantidad de veces que se utilizó el índice en búsquedas directas
     user_scans -- cantidad de veces que  scanearon en base al idx
       user_lookups -- cantidad de lookups 

   Estos tres eventos nos hablan de la utilización efectiva que han tenidos nuestros índices. 

   Pero para que nuestro estudio sea realmente efectivo, Microsoft nos propociona un cuarto campo vital para determinar el "costo/beneficio" de tener un idx activo en nuestra base de datos hablo del contador:

     user_updates -- este contador nos habla de la cantidad de veces que el sql server tuvo que hacer una tarea de mantenimiento sobre el índice (que puede ser la recontrucción total o parcial del mismo), como consecuencia de operaciones de Insert - Update - Delete.


    Ahora bien, tenemos estos datos valiosísimos... Cómo establecer si un índice justifica su existencia?

     A - Si el idx tiene valores elevados en cualquier de los 3 primeros campos detallados no debería ser deprecado

     B -  Si el idx no presenta valores significativos de utilización y a la vez  requiere de frecuentes tareas de mantenimiento por parte del sistema (contador user_updates con cifras elevadas), debería de eliminado.
  
   C - Si el idx presenta valores intermedios de utilización y mantenimiento, pues nada mejor que apelar al conocimiento que uno tiene sobre el sistema y la base de datos para de ese modo determinar la acción a seguir. Es decir, si vale la pena o no mentener el idx.


 Ahora les dejo la query que armé en base a la vista, linkeando la misma con otras vistas del sistema, para que puedan ya mismo empezar a revalidar sus indices.

  
------------------------------------------------------------------------------------------
-- Detalle:  Estudiar la utilización de los idx de una base de datos           
-- Vista base: sys.dm_db_index_usage_stats                                        - Autor: Gustavo Herrera para "SQL Server Para Todos"                   --------------------------------------------------------------------------------------------

Use NombreBaseDatos
SELECT 
DISTINCT OBJECT_NAME(sis.OBJECT_ID) TableName,
si.name AS IndexName,
sc.Name AS ColumnName,
sis.user_seeks,  
sis.user_scans,  
sis.user_lookups, 
sis.user_updates 
FROM sys.dm_db_index_usage_stats sis
INNER JOIN sys.indexes si  on sis.object_id = si.object_id and sis.index_id = si.Index_id
INNER JOIN sys.index_columns sic on sis.object_id = sic.object_id and sic.Index_id = si.Index_id
INNER JOIN sys.columns sc on sis.object_id = sc.object_id and sic.Column_id = sc.Column_id
INNER JOIN  sys.objects o on si.object_id = o.object_id
WHERE sis.database_id = DB_ID('NombreBaseDatos') and o.type = 'U' ;
go

       Quedo a disposición de uds, hasta la próxima !