Buscar este blog

martes, 23 de mayo de 2017

Sql Server Best Practices

Amigos,

    Son muchos los mails que recibo y contesto en modo privado con preguntas relacionadas con "buenas prácticas" para nuestro entorno de Base de Datos.

    Ya hemos hablado sobre el tema en post anteriores mas, en lo personal, creo que debemos seguir "machacando" una y otra vez sobre estas buenas prácticas para que nuestra vida como DBA's no sufra evitables y molestas desventuras...
  
   Hagamos entonces un breve repaso de buenas prácticas, como siempre quedo a vuestra disposición, saludos cordiales desde Argentina.

    
1.    Diseñando las bases con el máximo grado de normalización posible.

                       *  Elimine los grupos repetidos de las tablas individuales.(redundancia)
                       *  Cree una tabla independiente para cada conjunto de datos relacionados 
                       *  Identifique cada conjunto de datos relacionados con una clave principal.
                       *  Relacione estas tablas con una clave externa.
                       *  Elimine los campos que no dependan de la clave.

2.     Utilizando el Isolation Level más laxo acorde nuestras regla de negocio

·         Si nuestras reglas de negocio permiten realizar lecturas sin loqueo, conocidas como “lecturas sucias”,  podemos entonces setear nuestro Isolation Level en “Read Uncomitted” o bien utilizar el hint “no lock “
·         Hay también otras alternativas intermedias entre el IL por default y el Read Uncomitted, por ejemplo el que permite la lectura por versionado llamado “Read_Uncomitted_Snapshot”

3.     Programando queries performantes

 3.1) Seleccionando la menor cantidad de datos posibles. (ser muy selectivo en los campos a filtrar, en los where, en los having…)
 3.2) Manteniendo las transacciones chicas. (un Select no tiene por qué formar parte de transacción )
3.3) Utilizando datos del mismo tipo para comparaciones y joins de tablas evitando así conversiones implícitas-
3.4) Reemplazando cursores por el uso de While+Tablas temporales o Tablas variables  http://www.sqlbook.com/advanced/sql-cursors-how-to-avoid-them/
3.5) Utilizando los campos indexados para los campos select, las cláusulas where, los joins, lor order by, group by…
3.6) No permitiendo interacciones de usuarios dentro de las transacciones (cualquier transacción que permanezca abierta esperando un input de un user es bloqueante)
3.7)  Utilizando Métodos “Set – Based” por sobre “Procedural Methods”        https://www.codeproject.com/Articles/34142/Understanding-Set-based-and-Procedural-approaches
3.8) Estudiar Planes de Ejecución y otimizando las queries y atacando las queries + lentas
3.9) Eliminando los Order By  cuando no son necesarios
3.10)  Disminuyendor al Máximo el Uso de Funciones del Sistema (por ejemlplo la función max(), genera un scant table más allá del que el campo esté o no idx.
3.11)  Utilizando los Hints Necesarios
3.12)  Evitando bloqueos de recursos por borrados masivos de datos. A veces en mucho menos perjudicial para el sistema el generar una tabla nueva con los datos online que el borrar los datos que queremos deprecar de una tabla mediante el uso de un delete
3.13)  Evitando “Chatty Aplications”. Se conoce como Chatty App aquellas que commitean data en reiteradas ocasiones en lugar de hacerlo una vez. (por ejemplo formularios de suscripción web que validan cada dato que el user va llenando)

4.    Dotando a las tablas de los índices necesarios

4.1) Una buena indexación es aquella que es funcional a los procedimientos y que no excede las necesidades reales. 

4.2) Seleccionando los campos más “chicos” en términos de “tipo de datos” (un idx  debería ser hecho sobre un campo integer o datetime mientras su utilidad sería muy poca en campos del tipo varchar)

4.3)  Eliminando aquellos índices que NO son necesarios (recordemos que todo índice tiene asociado un costo de mantenimiento  y almacenamiento además de atentar contra la performance en caso de inserciones sobre tablas sobreindexadas)

4.4)  Generando e Implementando Rutinas de Mantenimiento de Indices (es muy importante que los índices se mantengan desfragmentados, especialmente en entornos oltp)

4.5) Utilizando Indices Cubiertos, para consultas en las que puedan eliminarse key lookups, incluyendo campos del select en forma lógica con la cláusula “include”


5.    Generando Rutinas de Mantinimiento de Estadísticas

5.1) Las estadísticas son importantísimas para que nuestros planes de ejecución elijan la mejor alternativa para ejecutar una query. Tengan en cuenta que si bien Microsoft nos da la chance de setear su actualización automáticamente dicha actualización no es efectiva. Utilicen una rutina para tal fin. La pueden encontrar también en este blog


6.    Implementando el Pariticionado de Tablas

6.1)  El particionar tablas puede agilizar mucho nuestras consultas en tablas que necesitan mantener grandes cantidades de datos históricos on line y que por lo tanto tienen varios millones de registros.  Si en este tipo de tablas, existen consultas donde se puede llegar a necesitar datos históricos y actuales de un cliente (por ejemplo la consulta histórica de la cuenta corriente de una empresa), pues bien, sería una buena idea particionar por año y generar, distribuir datafiles en distintos discos y generar lecturas paralelas. OJO NO ABUSAR de las PARTCIONES, ya que su mantenimiento es complejo (más de todo este tema en este blog)

7.    Distribuyendo los datafiles de nuestra base de datos correctamente

7.1)  El gran punto fuerte del SQL Server es la posibilidad de realizar lectoescrituras paralelas Para ello un escenario ideal es el tener separado en unidades físicas diferentes:
                 - El OS
                  - El motor de Base de Datos
                  - Los archivos de Datos
                  - Los archivo de índices
                  - Los archivos de log.

7.2)  Una vez hecho esto el analizar la carga de los discos (i/o) valiéndose del perfomance monitor y, llegado el caso, dettachar y reubicar los files que sean necesarios:

8.     Seteando la instancia del motor de base de datos del modo más eficaz posible.

Seteos como Memoria Asignada / Máx grado de paralelismo / Uso de Procesadores etc son sumamente importantes para la performance y estabilidad de nuestro sistema. Es importante lograr la mejor configuración para los mismos y revalidarla acorde al control permanente que debemos hacer sobre las bases y su estabilidad y performance.

9.     Manteniendo on line solo la data necesaria
El realizar el pase a files “off line” o bien a backups, es una tarea que si la reglas de negocio la permiten debería ser planificada y ejecutada con periodicidad.

10.  Evitando la fragmentación física de los discos
Es muy importante que tengamos corriendo en períodos de baja o nula concurrencia alguna herramienta eficiente de desfragmentado de nuestros hard disk.


11.   Monitoreando permanentemente nuestro servidor y base de datos

Es vital la uilización del performance monitor y el event viewer y el establecimiento de baselines..Las vistas del sistema son también aliados excluyentes para el monitoreo y el traceo de posibles deficiencias El revisar logs y utilizar trazas "server side"es algo fundamental.

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.