SolidQ Journal Octubre 2010 en castellano liberado!

Ha sido liberada la versión en castellano del SolidQ Journal en su edición de Octubre 2010.

“Este mes en The SolidQ Journal​, vea lo que sucede cuando la compañía de investigación y análisis del mercado inmobiliario Hanley Wood confió en Solid Quality Mentors para la creación de un sistema analítico que ayude a los clientes a reducir costes, mitigar el riesgo e identificar nuevas oportunidades en la industria inmobiliaria. Nuestra edición de Octubre también incluye al MVP en SharePoint, Daniel Seara, tratando sobre las búsquedas FAST en SharePoint 2010; a Greg Low con vistas, procedimientos almacenados, y abstracción; Herbert Albert y Gianluca Hotz con la continuación de su artículo sobre restauración con PowerShell; y a Enrique Catalá hablando sobre la detección de patrones de consulta ineficientes en un entorno real. A continuación, tenga un uno a uno con el miembro de SolidQ, Antonio Soto, en su misión de erradicar los datos erróneos.”

 

Como sabes, la revista es completamente gratuita y está disponible a traves de la web siguiente http://www.solidq.com/sqj/es/Pages/Home.aspx

En este caso, para ir diréctamente a la versión Octubre 2010 en castellano puedes acceder a traves de: http://www.solidq.com/sqj/es/Pages/Home.aspx?dates=October,%202010&enddatetime=2010-10-31T00:00:00&startdatetime=2010-10-29T00:00:00

Ya sabes que la tienes disponible tambien en Ingles y Portugues!

¿Por qué debemos evitar funciones escalares T-SQL?

Hoy me he decidido a escribir sobre por qué las funciones escalares T-SQL no son buena opción. Esto es otra vez mas, algo que veo como un patrón recurrente en todos los clientes que visito y siempre acabo recomendando que sean eliminados, aunque lamentáblemente muchas veces solo se hace en los escenarios críticos…es decir, la/las consultas o patrones T-SQL maléficos que les detecto que están destrozando a su pobrecito SQL Server

Antes de nada me gustaria recordar las razones por las que evitar siempre utilizar una funcion escalar en SQL Server:

1. No son visibles en los planes de ejecución
2. Producen malísimas estimaciones estadísticas que derivan en el uso inadecuado de operadores NESTED LOOPS
3. El código de la función se interpreta en cada llamada (cada fila)
4. Por último y lo más importante de todo: NO ES POSIBLE EL PARALELISMO

Independientemente de cual sea la razón por las que uno piense en utilizarlas, hay que quitarse la idea de la cabeza. Y no, no me vale eso de “pero esque así la consulta T-SQL queda más limpia y bonita” o “esque así reutilizo código”. 

Para rebatir cualquier afirmación pienso que un ejemplo vale más que 1000 palabras:

use tempdb
go

if (object_id('dbo.TablaGrande') is not null)
    drop table dbo.TablaGrande
go

-- Creamos una tablita con 100k filas para el ejemplo
--
with
    t0 as (select n = 1 union all select n = 1),
    t1 as (select n = 1 from t0 as a, t0 as b),
    t2 as (select n = 1 from t1 as a, t1 as b),
    t3 as (select n = 1 from t2 as a, t2 as b),
    t4 as (select n = 1 from t3 as a, t3 as b),
    t5 as (select n = 1 from t4 as a, t4 as b),
    result as (select row_number() over (order by n) as n from t5)    
select n col1 ,n+1 col2
into dbo.TablaGrande
from result where n <= 1000000

-- Le creo 2 índices, por hacer algo, realmente tampoco es 
-- imprescindible
create clustered index ci_tablagrande on TablaGrande(col1 asc)
create nonclustered index nci2_tablaGrande on TablaGrande(col2 asc)

-- Creamos una función tonta para ilustrar que incluso la cosa
-- mas increiblemente tonta produce resultados desastrosos
--
if (object_id('dbo.ChorraFuncion') is not null)
    drop function dbo.ChorraFuncion
go
create function dbo.ChorraFuncion( @a int,@b int)
returns varchar(100)
begin
return cast((@a+@b) as varchar(100))
end

-- ponemos en marcha la salida de tiempos
--
set statistics time on

-- Para no hacer trampas, borramos cualquier página útil 
-- para que sean ejecuciones vírgenes
dbcc dropcleanbuffers
go

-- Usando la función, aqui vemos los tiempos en mi máquina:
-- 
-- SQL Server Execution Times:
--  CPU time = 5444 ms,  elapsed time = 6229 ms.
--
select max(dbo.ChorraFuncion(t1.col1,t2.col2))
from TablaGrande t1
    inner join TablaGrande t2 on t1.col1 = t2.col2
go

dbcc dropcleanbuffers

 
-- Expandiendo la función (sin llamarla, pero haciendo lo mismo que hace), vemos como los tiempos son radicalmente mejores
-- SQL Server Execution Times:
--  CPU time = 3711 ms,  elapsed time = 1118 ms.
--
select max(cast((t1.col1 + t2.col2) as varchar(1000)))
from TablaGrande t1
    inner join TablaGrande t2 on t1.col1 = t2.col2
go

La razón de esto ya la he comentado anteriormente, pero quizás se aprecie mejor viendo por un lado el plan de ejecución:

En serie cuando lanzamos la consulta con la llamada a ChorraFunción:

Y en paralelo cuando no la usamos:

Por cierto…¿Ves por algún lado en el plan de ejecución en serie referencia alguna a la función? (si, es eso que pone “Compute Scalar” y que afirma erroneamente que tiene coste 0%.

Además, si dejamos puesto el SQL Server profiler, en la ejecución de la query que usa ChorraFuncion podemos ver si capturamos el evento SP:SmptCompleted, las ejecuciones de la función que hay por cada una de las filas procesadas (en este caso han sido 1 millon de ejecuiones)

Solución al error “allow remote connections through DCOM”

Cuando tratamos de conectar remotamente a SSIS eventualmente podemos recibir el siguiente mensaje de error, cuando no hemos realizado una correcta configuración de seguridad:

En este caso lo que se nos está diciendo es que aunque hemos llegado al servicio, se nos ha denegado el acceso. Además de esto nos está dando una pista que es debido a que no hemos configurado correctamente el acceso remoto.

La solución es bien sencilla y pasa por añadir en la configuración DCOM del servicio MsDtsServer100 o MsDtsServer (según si estamos en SQL Server 2008 o 2005) al usuario que queremos que tenga acceso remoto y listo.

Para ello abrimos “Component services”, buscamos dentro de DCOM las propiedades del servicio MsDtsServer100 y añadimos en su pestaña de seguridad al usuario en cuestión:

Buscamos en DCOM la configuración de MsDtsServer o MsDtsServer100 (según si es Sql Server 2005 o 2008)

En las propiedades, se añaden los permisos que necesitemos para el usuario que queramos que conecte remoto:

Para más información, recomiendo leer el siguiente artículo: http://msdn.microsoft.com/en-us/library/aa337083.aspx

Liberada la nueva edición SolidQ Journal Octubre

Ha sido liberada la nueva edición SolidQJ de octubre

“This month in The SolidQ Journal, see what happens when real estate research powerhouse Hanley Wood turns to Solid Quality Mentors for an advanced analytical system that helps clients cut costs, mitigate risk, and identify new opportunities in the housing industry. Our October issue also features SharePoint MVP Daniel Seara on FAST search for SharePoint 2010; Greg Low on views, stored procedures, and abstraction; Herbert Albert and Gianluca Hotz’s next iteration of their PowerShell restore solution; and Enrique Catalá on detecting inefficient query patterns through query aggregation. Then go one-on-one with SolidQ’s Antonio Soto on his mission to stamp out bad data.​”

Si deseas leer más recuerda que es gratuita y la tienes aqui: http://www.solidq.com/sqj/Pages/Home.aspx

Detección de patrones de consulta ineficientes

Es bien sabido que como norma general, realizar una optimización de rendimiento a un sistema en producción no es tarea fácil. Cualquiera que ha trabajado en un proyecto de optimización ya se ha dado cuenta que cuando comienza, rara vez dispone de una zona acotada donde se encuentra el problema. En esencia, todo proyecto de optimización sea del ámbito que sea, conlleva una fase de análisis en la que detectar dónde está el problema.

Lo raro en este tipo de proyectos es por tanto encontrar situaciones en la que alguien nos comenta: “cuando pulsamos en este botón del formulario X, dados estos valores de entrada…todo va lento”. Esto no solo seria algo trivial, sino que como luego veremos, se trata de un caso particular que puede que no requiera por nuestra parte ningun estudio, con el fin de optimizar el sistema global.

Pero cuando el sistema que queremos optimizar incluye un motor de base de datos, todavía se complica más puesto que un escenario real, conlleva decenas de aplicaciones y cientos o miles de usuarios trabajando contra SQL Server, por lo que no resulta fácil conocer qué casos concretos están produciendo una merma de rendimiento, ya que esta puede venir tanto de la aplicación como del motor de base de datos.

A grandes rasgos, la primera y más importante tarea que debe llevarse a cabo a la hora de optimizar un sistema por tanto es ….

Si deseas seguir leyendo, te recomiendo que leas el journal gratuito (si, gratuito) Solid Quality Journal.

Para continuar leyendo, pincha en este enlace: http://www.solidq.com/sqj/Pages/Home.aspx?mentor=Enrique%20Catal%C3%A1&dates=October,%202010&enddatetime=2010-10-26T00:00:00&startdatetime=2010-10-24T00:00:00

Cuando salga la edición en castellano editada actualizaré este post:

Ejecución de paquetes SSIS por código

En todo proyecto, puede surgir la necesidad de lanzar paquetes SSIS al vuelo mediante .NET. Este post está centrado en mostrar una forma de hacerlo utilizando las librerias de administración de SSIS y en dar una idea de al menos, como podeis empezar.

Ejecutar manualmente

Recordemos que para lanzar un paquete SSIS podeis utilizar el SQL Server Management Studio de la siguiente forma:

1. Conectar al servidor de SSIS (por ejemplo yo-pc\sql2008r2):

NOTA: No es posible utilizar las herramientas de SQL Server Express para conectar a Integration Services, hay que utilizar la versión Developer o Enterprise

2. Ir a Stored Packages->MSDB->DTS (la carpeta DTS ha sido creada manualmente por mi para el ejemplo)

3. Con botón derecho en el paquete SSIS a ejecutar, si lanzar “Run Package”:

Este es el formulario de ejecución donde poder dar valor a variables. Para terminar ejecutando, click en “execute”.

Por código Visual Basic.NET

El proposito de la entrada de este post es ejecutar código mediante código .NET. Para ello existen dos opciones para realizar ejecuciones de paquetes SSIS mediante código, la primera consiste en utilizar las clases de la librería Microsoft.SqlServer.ManagedDTS.dll y la otra utilizar la aplicación enominada dtexec que viene con las herramientas de inteligencia de negocio de SQL Server.

En ambos casos, hay que instalar como mínimo las herramientas cliente de inteligencia de negocio como vemos en la imagen (no es necesario instalar ningun sevicio de SQL Server en los clientes por tanto, ni las herramientas de administración):

NOTA: La imagen corresponde a instalación de SQL Server express con herramientas avanzadas

Para obtener tanto la herramienta, como las dll, en este caso si que nos vale por tanto utilizar la versión express con herramientas cliente avanzadas: http://www.microsoft.com/downloads/details.aspx?familyid=B5D1B8C3-FDA5-4508-B0D0-1311D670E336&displaylang=es

 

Ejecución mediante código Visual Basic

Una vez tenemos las librerías instaladas en el equipo desde donde queramos lanzar los paquetes SSIS, podemos utilizar código como el siguiente para efectuar ejecuciones de los mismos:

A continuación vemos como sería el código en Visual Basic.NET para ejecutar un Paquete SSIS.

NOTA: Si la aplicación no es .NET (por ejemplo, si es VB6) hay que programar un wrapper de acceso a la libreria ManagedDTS mencionada anteriormente (el código de más abajo es VB.NET). Más adelante, se da opción de utilizar dtexec, si no se quiere implementar dicho wrapper

Los paquetes deben estar desplegados en el servicio de Integration Services y además según se puede ver en el ejemplo de código, haberse desplegado sobre la carpeta DTS (no es requisito, pero para el ejemplo se ha realizado de esta forma, para ir alineados con las imágenes anteriores también).

Imports DTS = Microsoft.SqlServer.Dts.Runtime

Module Module1

    Sub Main()

        Dim instance As DTS.Application

        Dim packagePath As String

        Dim serverName As String

        Dim serverUserName As String

        Dim serverPassword As String

        Dim events As DTS.IDTSEvents

        Dim returnValue As DTS.Package

        Dim executionResult As DTS.DTSExecResult

        instance = New DTS.Application()

        packagePath = "\DTS\__TuDTSVaAqui__"

        serverName = "__TuServidorVaAqui__"

        serverUserName = "solidq" 'Nombre de usuario

        serverPassword = "solidq" 'Password de usuario

        events = Nothing

        returnValue = instance.LoadFromSqlServer(packagePath, serverName, serverUserName, serverPassword, Nothing)
 ‘Para asignar propiedades a variables

  ‘pkg.Variables("VarName").Value = "Value"

        executionResult = returnValue.Execute()

        If executionResult = DTS.DTSExecResult.Success Then

            Console.WriteLine("Paquete ejecutado correctamente")

        Else

            If executionResult = DTS.DTSExecResult.Failure Then

                Console.WriteLine("Se produjo un error al ejecutar el paquete")

            End If

        End If

        Console.ReadKey()

    End Sub

End Module

NOTA: El usuario que ejecute debe tener permisos en la Base de datos MSDB, puesto que es necesario listar las carpetas acceder a la carpeta DTS y cargar el paquete.

 
Ejecución mediante línea de commandos dtexec

Este método es el más sencillo y consiste en lanzar el paquete utilizando la aplicación dtexec, destinada especialment para ello.

Se parte de la base nuevamente en que se ha instalado la herramienta en el cliente y por tanto se encuentra instalada y accessible (por defecto se encuentra en C:\Program Files\Microsoft SQL Server\100\DTS\Binn\)

Se trataria por tanto de realizar una llamada desde visual basic a la aplicación dtexec con los parámetros necesarios (ver imagen adjunta como ejemplo sencillo):

Por ejemplo, para lanzar el paquete llamado “paquetePrueba” que se encuentra en el servidor “yo-pc\sql2008r2”, utilizando un usuario de sql “usuariosql” y password “passwordusuario”, asignando valor a la variable llamada “miVariable”, podríamos crear una llamada como esta:

Dtexec /ser yo-pc\sql2008r2 /U usuariosql /P passwordusuario /sq paquetePrueba /set \package.variable[miVariable].Value;AquiPonesElValorQueQuieresAsignar

NOTA: Para información sobre los parámetros de entrada podemos utiliza dtexec /? O diréctamente dirigirnos a la web de consulta de dtexec aqui: http://technet.microsoft.com/en-us/library/ms162810(SQL.100).aspx

Que lo disfruteis.

Detectar usuarios huérfanos en BBDD

En caso de que querais buscar usuarios huérfanos en BBDD (que no tengan login asociado), como recordais podeis utilizar el procedimiento almacenado sp_change_users_login.

Pues bien, he aqui un buen tip para detectar todos los usuarios huérfanos de todas las BBDD que tengas con una simple consulta:

sp_msforeachdb 'use ?;

declare @mitabla as table (dbname sysname default db_name(),uname sysname, usid varbinary(max))

insert into @mitabla(uname,usid)

EXEC sp_change_users_login ''Report''

select * from @mitabla'

Ahora bien, si estais en un caso como el mio en que tengo que revisar más de 150 BBDD…quizás te resulte esto mas util

DECLARE cursorBBDD CURSOR read_only fast_forward forward_only FOR

   select name from sys.databases where database_id>4



declare @nombreBBDD sysname

create table #mitabla (dbname sysname default db_name(),uname sysname, usid varbinary(max))



OPEN cursorBBDD

FETCH NEXT FROM cursorBBDD

INTO @nombreBBDD

WHILE @@FETCH_STATUS = 0

BEGIN

   declare @sentenciaSQL nvarchar(max)

   set @sentenciaSQL = 'use ' + @nombreBBDD + ';

                     insert into #mitabla(uname,usid)

                     EXEC sp_change_users_login ''Report'''

   --print @sentenciaSQL

   exec(@sentenciaSQL)



   FETCH NEXT FROM cursorBBDD

   into @nombreBBDD

END

CLOSE cursorBBDD

DEALLOCATE cursorBBDD



select * from #mitabla

drop table #mitabla

Suerte y que no detecteis muchos!