Buscar este blog

miércoles, 11 de diciembre de 2013

DELETE CON JOIN (inner - left - right)

Amigos,

    He recibido varias consultas vía mail preguntando sobre esta posibilidad, la de hacer el borrado de una tabla filtrando los registros a borrar a partir del valor de un campo de otra tabla que "joinearemos" con la tabla base.

   La respuesta es si! Claro que si, simplemente les dejo un ejemplo que es de por si explicativo y como siempre me pongo a disposición de Uds. para cualquier duda o consulta.


 -- Delete With Inner T-Sql --


-- Se escribe el delete from la tabla en la que se quiera borrar
Delete
From
HumanResources.Employee
-- Se construye el Join que permitirá utilizar en el where el campo que usaremos como condición para borrar
From HumanResources.Employee
inner join HumanResources.EmployeeAddress
on HumanResources.Employee.EmployeeID = HumanResources.EmployeeAddress.EmployeeID
-- En el where se filtran los registros a borrar haciendo referencia a un campo de la tabla joineada
where HumanResources.EmployeeAddress.EmployeeID = 11

  
   Saludos, Gustavo Herrera

martes, 10 de diciembre de 2013

FUNCIONES SQL SERVER - Utilidades y Diferencias con Stores Procedures y Vistas --

Amigos,

    Hoy les traigo un tema que puede parecer triyado, pero sobre el que no está de más hacer un repaso, estoy hablando de las Funciones.

   Definición:

   Ante todo las funciones son un conjunto de sentencias que funcionan como una unidad lógica.

   Pero si las funciones son un conjunto de sentencias que devuelve un conjunto de resultados, qué diferencia tienen con los Store Procedures y las Vistas?

   Hablemos de algunas diferencias fundamentales que pueden justificar su uso:


   - A diferencia de un procedimiento almacenado,  las funciones pueden ser referenciadas en el From/Where/Having y Select de una consulta  y utlizadas  en el join de  un consulta


  -  Deben retornar un valor mientras que los SP  pueden ejecutar instrucciones sin devolver un parámetro de salida.

  - Pueden tener sólo parámetros de entrada mientras los store procedures de entrada y salida
    
  -  Las Funciones  pueden ser "llamadas" desde un Sp, mientras que los procedimientos no pueden ser llamados desde  una Función.

 -  Las Funciones sólo permiten instrucciones de tipo select mientras que los SP permiten  también instrucciones DML (Insert - Update - Delete)  



  - A diferencia de las vistas, pueden incluir todo tipo de sentencias lógicas como IF,  mientras que las vistas solo pueden devolver el resultado de un select.



 - En el caso de las funciones escalares pueden emplearse como campo en cualquier consulta

 -   Las Funciones no retienen su plan de ejecución cacheado, por lo tanto no es reutilizable, deben ser compiladas cada vez que son llamadas, por el contrario los SP son precompilados.  

-   Las Funciones no permiten la utilización de Try Catch por bloques mientras que los Stores Procedures si.

-   Las Funciones no son trackeables a través del transaction manager mientras los sp si.

  
   Tipos de Funciones 

   Yo les propongo pues que entendamos la utilidad de las funciones mediante ejemplos prácticos para cada tipo de función. Empecemos...


   1) Funciones Escalares (Scalar-valued Functions)

       1.1)  Retornan un único valor
       1.2)  Pueden emplearse en cualquier consulta como un campo más de la misma
       1.3)  Admite parámetro de entrada y devuelve un único valor como salida.
       1.4)  Al ser "llamadas" se las debe llamar con su nombre completo dbo.NombreFunción

        La siguiente función llamada "F_Vacaciones"  calcula y devuelve las vacaciones que corresponden a cada empleado a partir de su fecha de ingreso a la empresa (recibida como parámetro) .
-- Creamos una tabla llamada “empleados” con el nombre y su fecha de ingreso --
CREATE TABLE [dbo].[empleados](
   [nombre] [varchar](30) NOT NULL,
[fecha_ingreso] [datetime] NULL)
   ON [PRIMARY]

   -- Insertamos un empleado junto con su fecha de ingreso -
      INSERT INTO empleados
      (nombre, fecha_ingreso)
      values
      ('pablo', '2015-02-03 08:59:59')

--Creamos una función escalar "F_Vacaciones" donde se calcula y devuelve
  las vacaciones que corresponden a cada empleado a partir de su fecha de       ingreso a la empresa(recibida como parámetro) .
     
Create Function F_Vacaciones

 -- Parametros de entrada con valor por default --
 (@fechaingreso datetime='2010-01-01 00:00:00')

 -- tipo de datos retornado --
  Returns varchar(10)

  as
  -- cuerpo de la funcion --
  Begin
    -- declaro variable que va a devolver --
    declare @vacaciones varchar(10)
    
    -- declaro variables generales --
    declare @antiguedad int 
  
    -- calculo las antiguedad de los empledos en base a la fecha de ingreso --
    select @antiguedad = DATEDIFF(mm, @fechaingreso, getdate())
   
    -- Determino las vacaciones que corresponden en base a la antiguedad --
    select @vacaciones = 
       case when @antiguedad <= 12 then '7 dias'
       when @antiguedad > 12  then  '2 semanas'
       when @antiguedad > 60  then  '3 semanas'
       when @antiguedad > 120  then '4 semanas'
     end;
    
     -- retorno en @vacaciones el valor que corresponde al empleado --
     return @vacaciones
end; 


-- Listamos la tabla empledo utilizando como campo la función creada --
select
nombre,
fecha_ingreso,
dbo.F_Vacaciones(fecha_ingreso) as 'vacaciones'

from empleados;

  
  2) Funciones de Tabla (Table-valued Functions)

       1.1)  Retornan una tabla
       1.2)  Pueden emplearse en lugar del from de una consulta
       1.3)  Pueden "joinearse" en  cualquier consulta.
       1.4)  No necesitan ser llamadas por su nombre completo.

      La siguiente función llamada F_Baratas  devuelve una tabla con todas aquellas novelas cuyo precio sea menor al pasado como parámetro

.
-- Create table Libros --
create table libros
(id integer,
 novela varchar (50),
 autor varchar (35),
 editorial varchar (40),
 novela_tipo int,
 precio decimal (9,3))


 -- Insertamos dos registros --
 insert into libros
 (id,novela,autor,editorial, novela_tipo, precio)
 values
 (1,'Cuentos de la Selva', 'Horacio Quiroga', 'ATENEO',4, 9.43)


 insert into libros
 (id,novela,autor,editorial, novela_tipo, precio)
 values

 (2,'Relatos de un Náufrago', 'García Marquez', 'ATENEO', 1, 25.43)



Create Function f_baratas
-- Parámetro/s de Entrada --
 (@Valor decimal (6,2))
 
-- Nombre de la Tabla de Retorno --
  returns @Libros table

-- Formato de la Tabla de Retorno
 (Id Integer,
  Novela varchar(40),
  Autor varchar(40)
  Precio decimal (6,2))
  as
 
-- Cuerpo de la funcion --
 Begin

   -- Inserta Datos en la Tabla --
   insert @Libros
   select id, novela, autor, precio from libros
   where precio < @Valor
  
   -- retorna la tabla --
   return
 end;
 
 -- Llamado de la Función --
 select * from f_baratas (10);

 -- Podemos hacer un Join --
 Select *, l.novela_tipo from f_baratas(10) as b
 left join libros as l
 on b.id = l.id

 -- O Podemos usarlo como vista --
 Select id, autor
 from f_baratas(10)


    3) Funciones de Agregado (Aggregate Functions)

       3.1) Son las típicas funciones que combinan varios valores y retornan un único valor
       3.2) Ejemplos Típicos "count" "sum" "min" "avg" etc.


    4) Funciones del Sistema (System Functions)

       4.1) Son las distintas funciones preestablecidas por el sql server.
       4.2) Hay de distintos tipos...
             4.2.1) De configuración, Select @@version;
             4.2.2) De fecha y hora ... Datead ()
             4.2.3) De metadatos (devuelven info sobre las bd y objetos)
             4.2.4)etc. (recomiendo abrir el front end del sql server y ver cada una de Ellas)


   Amigos, esto fue todo, espero haber sido claro y ante consultas saben que cuenta conmigo

    Saludos desde Argentina, 

     Gustavo Herrera para "SQL Server para Todos"

                        


  

jueves, 14 de noviembre de 2013

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

viernes, 18 de octubre de 2013

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

viernes, 4 de octubre de 2013

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

jueves, 3 de octubre de 2013

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