viernes, 17 de marzo de 2017

SQL Server Profiler - Scripteando Un Traceo en el Servidor - (Server Side Trace)

Amigos, cuando estudiamos problemas de performance en un server, sin lugar a dudas uno de nuestros aliados es el PROFILER.
   Esta herramienta nos permite recolectar distintos valores e incluso sentencias que están corriendo en nuestro server.
     Ahora bien, qué ocurre si queremos tracear una base de datos por espacio prolongado en horas?  Seguramente trataremos en primera instancia el hacerlo utilizando la vía más fácil, es decir, la Consola Sql Profiler desde fuera del server.
     El gran problema del uso de la consola del Profiler en estos casos es que,  corrida desde la consola a nivel de usuario contra el servidor, es pasible de sufrir interrupciones ante la pérdida de conexión con el server.  Estas interrupciones nos van a impedir la colección de datos necesarios para atacar el problema que estamos buscando.
  Cuál sería la solución al problema si debemos tracear un servidor por unas cuantas horas?

  La solución, y a la vez el objetivo de este post, es  generar un script que nos permita hacer el mismo traceo que con el Profiler, pero del lado del server. En inglés se lo conoce como “Server Side Trace”y consta de los siguientes comandos, veamos:

sp_trace_create Crea la definición del nuevo traceo que se inicializará como “stopeado

Su sintaxis es la siguiente:
sp_trace_create @TraceID OUTPUT@Options@fileName@maxFileSize, @Stoptime, @FileCount  

Vemos los parámetros a definir:
@TraceID = es el nº de id que el sql le da por default al traceo. Sirve para identificar al mismo posteriormente. Se recomienda utilizar una variable integer y dejarla nula (ver en el ejemplo)
@Options =  Aquí podemos setear un 2 como valor en cuyo caso cuando se alcance el máximo tamaño por archivo se generará un nuevo sin borrar el anterior con el mismo nombre y un integer adosado al mismo…
                  … O un 4, que especifica que , ante una falla que no le permita al trace escribir sobre el archivo sql server se apagará. Opción útil para traces en traceos de seguridad.
@FileName =  Donde debe definirse la ruta y el nombre del file donde se va a grabar el trace. Ejemplo 'C:\filetrace’. Aquí debemos indicar que el file será acompañador con una extensión .trc que el sql le adosará en forma automática y que el mismo NO DEBE UTILIZAR EL CARÁCTER “UDERSCORE” es decir nunca utilizar un nombre como este “my_trace”.
@maxFileSize = donde se especifica el máximo número en mb que un  archivo de traceo puede alcanzar. El default es 5 mb.
 @stoptime= su valor puede ser nulo y es donde se especifica el horario en el cual el trace debe detenerse (este valor tiene precedencia sobre el @maxfilesize cuando no se especifica el valor 2 en options.
@filecount=   puede ser nulo o no. Su valor si se setea debe ser un integer mayor a 1 . Con este parámetro se determinan la máxima cantidad de files que el sql server va a mantener para este traceo antes de borrar el más viejo si la opción @Options = 2
Las posibles respuestas que debemos validar:

Return code            Description
0              No error.
1              Unknown error.
10            Invalid options. Returned when options specified are incompatible.
12            File not created.
13            Out of memory. Returned when there is not enough memory to perform the specified action.
14            Invalid stop time. Returned when the stop time specified has already happened.
15            Invalid parameters. Returned when the user supplied incompatible parameters.

sp_trace_setevent Permite agregar eventos a tracear al trace previamente creado y stopeado

Su sintaxis es la siguiente:
sp_trace_setevent @TraceID, @EventId, @ColumId, @on

Vemos los parámetros a definir:
@TraceID = es el nº de del trace al cual vamos a agregarle eventos
@EventID = es el id del evento a agregar y puede ser uno a más de los sgtes:

Event number         Event name             Description
10            RPC:Completed       Occurs when a remote procedure call (RPC) has completed.
12            SQL:BatchCompleted              Occurs when a Transact-SQL batch has completed.
15            Audit Logout          Occurs when a user logs out of SQL Server.
16            Attention Occurs when attention events, such as client-interrupt requests or broken client connections, happen.
El listado total de eventos se encuentra en https://msdn.microsoft.com/en-us/library/ms186265.aspx

@ColumnId = es el id de columna a ser agregado para el evento. Los valores:

Column number     Column name         Description
1              TextData Text value dependent on the event class that is captured in the trace.
2              BinaryData              Binary value dependent on the event class captured in the trace.
3              DatabaseID             ID of the database specified by the USE database statement, or the default database if no USE database statement is issued for a given connection.

El listado total de eventos se encuentra en https://msdn.microsoft.com/en-us/library/ms186265.aspx

@On = es un valor de tipo “bit”y puede tener un 0 o un 1
1 = “on” el evento es “prendido”
0 = “off” el evento es “apagado”

sp_trace_filter : Determina los filtros a setear para el trace

Su sintaxis es la siguiente:
sp_trace_filter @TraceID, @ColumnId@LogicalOperator@Comparison_Operator, @Value  

Vemos los parámetros a definir:
@TraceID = es el nº de del trace al cual vamos a agregarle eventos
@ColumnId = es el id de columna sobre la que establecemos el filtro
@LogicalOperator = es un integer = 0 si la operación es AND, =1 si es OR
@Comparison Operator = es un integer que puede representar alguna de las siguientes:

Valor   Operador de comparación
0              (Es igual a)
1              (No es igual a)
2              (Mayor que)
3              (Menor que)
4              (Mayor o igual que)
5              (Less Than Or Equal)
6              LIKE
7              NOT LIKE
@Value = valor que se va filtrar (puede incluir % si lo precede un operador lógico)

sp_trace_status Modifica el estado actual del trace

Su sintaxis es la siguiente:
sp_trace_status @TraceID, @Status

Vemos los parámetros a definir:
@TraceID = es el nº de del trace
@ColumnId = es un integer que determina la acción a tomar acorde a la sgte tabla:

Status  Description
0              Detiene el seguimiento especificado.
1              Inicia el seguimiento especificado.
2              Cierra el seguimiento especificado y elimina su definición del servidor.




Y bien amigos les propongo un script de ejemplo en el cual vamos a tracear el evento "Scan”con el objetivo de entender en que instancias nuestro motor ejecuta esta operación tan poco performante y tomar las acciones correctivas posteriormente.

/***************************************************/
-- Traceo Por Script Desde el Servidor            --
-- Autor: Gustavo Herrera - Sql Server Para Todos --
-- Detalle: Se tracea el evento "Scan: Started"   --
/***************************************************/

-- Declaración de Variables
DECLARE
@Trc INT ,
@TraceID INT,
@MaxFileSize bigint,
@FileName NVARCHAR(128),
@On bit,
@MaxDatetime datetime,
@FileCount int

-- Set variables
SET @MaxFileSize =  500 -- (500 mb)
SET @FileName = 'C:\TrcBuscaScan'  -- Ubicación de los Files
SET @On = 1
SET @FileCount = 2  -- que vaya pisando c/2 files
SET @MaxDatetime = '2017-03-17 23:59:59' -- hora de fin del traceo

-- Definimos el Trace y guardamos el resultado en @Trc
EXEC @Trc = sp_trace_create @TraceID output, 2, @fileName, @maxFileSize, @MaxDatetime, @FileCount

-- Si La Definición Da Error que salgo por error
IF (@Trc != 0) GOTO error

-- Seteo el evento a tracear y las columnas que deseo
EXEC sp_trace_setevent @TraceID, 51,  1, @on  --TextData
EXEC sp_trace_setevent @TraceID, 51, 11, @on  --LoginName
EXEC sp_trace_setevent @TraceID, 51, 12, @on  --Spid
EXEC sp_trace_setevent @TraceID, 51, 13, @on  --Duration
EXEC sp_trace_setevent @TraceID, 51, 14, @on  --StarTime
EXEC sp_trace_setevent @TraceID, 51, 15, @on  --EndTime
EXEC sp_trace_setevent @TraceID, 51, 16, @on  --Reads
EXEC sp_trace_setevent @TraceID, 51, 17, @on  --Writes
EXEC sp_trace_setevent @TraceID, 51, 18, @on  --CPU

-- Seteo Filtros

-- Filtro: solo traceamos sobre la base de datos con iddatabase = 6
EXEC sp_trace_setfilter @TraceID, 3, 1, 0, 6

-- filtro2: excluímos la app SQL Profiler
EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'

-- Comezamos el traceo
EXEC sp_trace_setstatus @TraceID, 1
-- Muestra en pantalla el trace id 
SELECT TraceID=@TraceID 
GOTO finish 

-- Atrpamos el error
error: 
SELECT ErrorCode=@Trc 

-- exit
finish: 
GO


Eso es todo amigos, espero que les haya sido útil. Saludos desde Argentina.

martes, 15 de noviembre de 2016

Property Owner is not Available for Database '[ ]'. This property may not exist for this object, or may not be retrivable due to insufficient acces rights (Microsoft.SqlServer.Smo) // SOLUCION AL PROBLEMA

Buen dia amigos,

   Hoy quiero contestar una pregunta de don Armando Gonzalez del Rio quien nos escribe desde Monterrey en el querido país Azteca.

    Don Armando refiere que trabaja para una cia donde fueron despedidos empleados del Depto de Sistemas, alguno de los cuales cumplían funciones de DBA.
 
    Concreteamente el problema que nos refiere es "cuando intento clickear sobre una base de datos y obtener sus propiedad me encuentro imposibilitado de hacerlo y me aprece este cartel:"


Solución:

 Don Armando ud no puede acceder a las propiedades de las bases de datos puesto que el owner de las mismas a quedado ligado a los empleados de la empresa que han sido desafectados de la misma.

  Tiene pues que correr un comando asignando la base de datos a un login, preferentemente de domino, que esté asignado a un empleado del payroll actual de la Cia.

  Cómo se hace? Pues de la siguiente forma, utilizando el sp sp_changedbowner.

  use [base de datos]

exec sp_changedbowner 'gherrera';


En el ejemplo precedente se asigna un nuevo owner al login "gherrera".

Es todo amigos, nos vemos proximamente.

Saludos desde Argentina.



   




jueves, 27 de octubre de 2016

Problemas al Renombrar Una Base de Datos

Amigos, 

   Hoy es un dia de respuesta a preguntas. Tabaré Montero de Las Cañas Uy me refiere un problema a la hora de renombrar las bases de datos... 
   Tbaré me comenta que lo hace a través de la consola, "parándose sobre la base de datos", "clickeando" con botón derecho y utilizando la opción "rename". El resultado... obtiene siempre un mensaje de error.
   Pablo, para evitar errores a la hora de renombrar una base de datos no hay otra forma que hacerlo en modo monousuario y con sentencias t-sql , no con la consola, veamos:

-----------------------------------------------
 -- Autor GH para
 -- Detalle: Renombrar Una Base de Datos
 -----------------------------------------------

-- 1 Seteo la Base de Datos a Single User

Alter Database Repuestos
Set Single_User with Rollback Immediate

2-- Renombro la Base de Datos

ALTER DATABASE Respuestos
Modify Name = RespuestosFord ; 

-- 3 Seteo la Base de Datos a Multi User
Alter Database RepuestosFord

Set Multi_User

 
Amigo, un saludo grande a la gente de Fray Bentos, Las Cañas. A su disposición



Scriptear Todos los Indices de Una Base de Datos (con un solo script)

Amigos,

   Cuantas veces nos encontramos ante una migración con la necesidad de contar con la ayuda de un script que nos permita scriptear todos los Indices de todas las tablas de una base de datos?
   Estoy seguro que muchas veces se han efrentado a esta situación y, por cierto, no parece la situación ideal "pararse" sobre cada tabla y con el botón derecho scriptear cada uno de los idx...
   Les dejo entonces un script que hace esto por ustedes, solo lo ejeutan y el resultado en formato texto será el script con el cual podrán regenerar uno a uno todos los índices de todas las tablas de una base de datos.
   Aprovecho para saludar al amigo Agapito Buenaventura quien me ha escrito preguntando por este tema desde la querida Bolivia.

  ---------------------------------------------------------------------------
-- Autor Gustavo Herrera                                                
-- Detalle: Este código scriptea todos los indices correspondientes a una BD 
-----------------------------------------------------------------------------

DECLARE cIX CURSOR FOR
   SELECT
   OBJECT_NAME(SI.Object_ID),
   SI.Object_ID,
   SI.Name,
   SI.Index_ID,
   F.NAME
   FROM Sys.Indexes SI
   LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
   ON
    SI.Name = TC.CONSTRAINT_NAME AND
    OBJECT_NAME(SI.Object_ID) = TC.TABLE_NAME
   LEFT JOIN sys.filegroups f -- para saber el filegroup
   ON SI.data_space_id = f.data_space_id
   WHERE --TC.CONSTRAINT_NAME IS NULL --AND
         OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1
          ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID

   DECLARE
   @IxTable SYSNAME,
   @IxTableID INT,
   @IxName SYSNAME,
   @IxID INT,
   @PKSQL varchar (50),
   @FILEGROUP VARCHAR (50)

   -- Loopea a través de todos los idx
   OPEN cIX
   FETCH NEXT FROM cIX INTO
   @IxTable, @IxTableID, @IxName, @IxID, @FILEGROUP
  
   WHILE (@@FETCH_STATUS = 0)
   BEGIN
       DECLARE @IXSQL NVARCHAR(4000) SET @PKSQL = ''
       SET @IXSQL = 'CREATE '

       -- Chequea si el indice es unique
       IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
        SET @IXSQL = @IXSQL + 'UNIQUE '
       -- Chequea si el idx es clustered
       IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
        SET @IXSQL = @IXSQL + 'CLUSTERED '
        SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON ' + @IxTable + '('

         -- Obtiene todas las columnas que contienen índices
         DECLARE cIxColumn CURSOR FOR
              SELECT SC.Name
              FROM Sys.Index_Columns IC
                   JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
              WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID
              ORDER BY IC.Index_Column_ID

            DECLARE
            @IxColumn SYSNAME,
            @IxFirstColumn BIT
            SET @IxFirstColumn = 1

            -- Loopea a través de todas las columnas que forman un idx y les agrega un CREATE
            OPEN cIxColumn
            FETCH NEXT FROM cIxColumn INTO @IxColumn
            WHILE (@@FETCH_STATUS = 0)
            BEGIN
                IF (@IxFirstColumn = 1)
                    SET @IxFirstColumn = 0
                ELSE
                    SET @IXSQL = @IXSQL + ', '
             SET @IXSQL = @IXSQL + @IxColumn

                  FETCH NEXT FROM cIxColumn INTO @IxColumn
            END
            CLOSE cIxColumn
            DEALLOCATE cIxColumn

            SET @IXSQL = @IXSQL + ') '+'ON '+@FILEGROUP
            
            
           
          -- Imprime CREATE statement para los índices
   PRINT @IXSQL

   FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID, @FILEGROUP
END

CLOSE cIX
DEALLOCATE cIX