Pasando Datos Numéricos Largos Desde Sql Server Hacia Excel (evitar el truncamiento)

Amigos,

Me ha llegado una consulta desde la hermana República de Panamá. Don Omar Alfanno me cuenta de un inconveniente que tiene "al bajar datos desde el sql server al excel, cuando intento copiar y pegar la columna con mis números telefónicos, pues veo que los últimos tres dígitos se truncan y el Excel los cambia por tres números ceros. No encuentro la solución puede ud. ayudarme?"

Respuesta:

Ante todo don Omar quiero contarle que es un error con el que todos quienes trabajamos alguna vez con el SQL Server nos hemos topado y es de muy fácil resolución.

Simplemente debe (y en este orden, de lo contrario no funciona)

1) Abrir el excel y darle formato texto (text) a las columnas en las cuales ud. quiere pegar sus datos numéricos extensos.

2) Luego si copiar y pegar los datos desde el sql server en el excel.

Es muy importante que no invierta el orden, le repito. No pegue para luego cambiar el formato porque sus datos serán truncados.

Eso es todo, mi saludo desde Argentina.

Bloqueos en Sql Server (Qué Son / Cómo Detectarlos y Monitorearlos / Cómo Minimizar su Ocurrencia)

Amigos,

     Como DBA’s de una compañía todos alguna vez hemos escuchado la famosa frase “el sistema está lento” o bien, de boca de un desarrollador, “por favor me puedes dar una mano, el sistema tira time out y tengo muchos usuarios furiosos, no entiendo que pasa”…

     Desde ya que uno supone que, cuando el reclamo ha llegado a nuestra área, se han descartado o bien se están descartando,  todo un abanico de cuestiones que exceden a nuestra función  tales como, problema de conexiones saturadas o lentas, problemas de hardware, etc. etc.

     Hablando de Base de Datos, no siempre la lentitud  está directamente relacionada con la existencia de constantes bloqueos, pero si es nuestra responsabilidad tratar disminuir al mínimo posible los mismos.

     Para ello, sin querer aburrirlos con teoría, les propongo:

-          Un poco de teoría (lo mínimo para entender realmente este importante tema)

-            ontSistematizar rar la forma de detectarlos (queries y algo más..)

-          Aprender tips para minimizar su ocurrencia

Lo prometido, un poco de teoría…

    Empecemos por definir qué es un Bloqueo?...

    Pues bien, en términos sencillos, un bloqueo es aquella demora que se produce en el acceso a un recurso de una base de datos (tablas, índices, etc),  cuando dos procesos “compiten” por tener la exclusividad sobre el mismo. En este caso se produce el famoso “bloqueo” que hace que uno de los procesos deba esperar a que el otro termine de utilizar el recurso, para poder hacer uso del mismo.

    Pero por qué un proceso querría tomar la exclusividad sobre un recurso? Pues bien… para entender ello debemos repasar los dos principales tipos de Locks del SQL Server … (hay otros derivados de ellos pero que no van a ser abordados en el presente art.)

    Muy brevemente ..

·         Shared Lock (S): es el loqueo que establece cualquier SELECT  sobre una tabla (lectura).

·         Exclusive Lock (X): es el nivel de lockeo necesario para cualquier instrucción de modificación de data (INSERT/UPDATE/DELETE).

    De la existencia de estos dos tipos de lockeos es fácil inferir que, si un usuario “A” está realizando un SELECT sobre una tabla,  y otro usuario “B”  quiere hacer una modificación sobre los datos de la misma (INSERT/UPDATE/DELETE),  “B”, tendrá que esperar a que “A” termine dicha operación de lectura, quedando momentáneamente BLOQUEADA la tabla.

    Del mismo modo… , si un usuario está modificando los datos de una tabla, los demás usuarios que deseen hacer desde una simple lectura hasta una modificación de  datos de esa tabla deberán esperar, puesto que, como hemos dicho.. todo INSERT/UPDATE/DELETE requiere de un lockeo exclusivo de la tabla…

    Pero por qué Microsoft  ha dispuesto la necesidad de “lockeos exclusivos” ? Tranquilos amigos, no se trata de mentes malvadas, se trata de que SQL SERVER es una base de datos relacional que toma este tipo de comportamiento para asegurarnos la integridad de la data. (es decir, que uno lea datos reales, no datos que ya no existen o han sido modificados al momento de la lectura)

   Se imaginan Uds en caso contrario, qué ocurriría si un usuario “A” está consultando el stock de un artículo cuyo stock  está siendo modificado  por otro usuario “B” al haber hecho una venta del mismo?. Pues el usuario “A” contaría con un estado falso del stock… ya que fue modificado por el usuario “B”. Eso es lo que se llama una “lectura sucia” y es lo que el sql server por default nos evita.

   Uds me dirán “Entonces es normal que existan bloqueos?”. Pues bien, todo sistema de base de datos tiene bloqueos, el problema es cuando los mismos son prolongados,  ya que esto produce  encolamientos y una degradación general todas las operaciones que afectan al sistema

    Y en este punto amigos es donde nosotros como DBA debemos intervenir…. Ya veremos cómo, no sin antes hablar de algo directamente relacionado con el tema que nos convoca… el “Isolation Level” (nivel de aislamiento en castellano).

   No es otra cosa más que un “seteo” que determina el grado de concurrencia simultánea con el que los datos pueden ser accedidos dentro de una sesión de Base de Datos.

   Por defecto, el SQL Server viene seteado con un “isolation leve”l llamado “READ COMMITED” . Este nivel de aislamiento no permite lecturas sucias.  Es decir, si “A” quiere leer o modificar un recurso que “B” está modificando, como explicamos anteriormente,  no puede hacerlo.

    Es posible sin embargo “setear”, por sesión o sólo para una determinada sentencia,  el Isolation Level para que permita lecturas sucias. Es decir,  lograr que por defecto ningún usuario al realizar una lectura establezca un lockeo, esto si vamos a la prática permitirá al proceso “A”  leer si “B” está modificando datos y, a la vez a “B” modificar datos si “A” está leyendo. Este tipo de seteo se llama “READ UNCOMITTED” y es el más laxo de los isolation levels, aptos para entornos donde la integridad de la data no sea un tema a tener en consideración.

-   Seteando una query de lectura para permitir lecturas sucias:

       select idarticulo from articulos with (nolock)

-           Seteando el isolation level de una sesión para permitir lecturas sucias:

      SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    Existe un tercer tipo de isolation level  que elimina un efecto llamado “REPETABLE READ”. Vamos a un ejemplo para una mejor comprensión. Supongamos que una transacción bancaria necesita leer el saldo inicial del cliente, para, a partir del mismo,  realizar cálculos y la adición de un nuevo registro con los intereses bancarios que modifica el saldo del cliente leído. Luego, como parte de la misma transacción, necesita volver a leer el saldo original dado que necesita insertar otro registro con x gasto bancario relacionado con el saldo original del cliente, (saldo que ya fue modificado como producto de la inserción previa de los intereses). Pues bien, la única forma de que esta transacción pueda contar con el saldo original es seteando la misma dentro un isolation level llamado “REPETABLE READ”, de lo contrario no podrá recuperar el saldo original. El saldo hasta finalizada la transacción siempre será el original si bien en la práctica ya  fue modificado por la adición de intereses.

-         -       Seteando el isolation level de una sesión:

    SET TRANSACTION ISOLATION LEVEL REPETEABLE READ

   Por último tenemos el más restrictivo de los Isolation Level, es llamado “SERIALIZABLE”. En este nivel de aislamiento el lockeo es sostenido durante toda la transacción, no pudiendo modificarse ni leerse dato alguno hasta que la misma no finalice.

-         -   Seteando el isolation level de una sesión:

   SET TRANSACTION ISOLATION LEVEL SERILIZABLE

 

    Pero como bonus, el SQLSrv nos entrega un Isolation Level que es muy interesante llamado “READ_COMITTED_SNAPSHOT”.  Este nivel de Isolation permite leer sin establecer lockeos, pero no nos entrega una lectura sucia (ahí lo interesante de este nivel). Bajo este isolation el sql server toma el último estado consistente (comitido) de un dato al comienzo de la lectura del mismo. Cómo lo hace? Leyendo desde la TempDb esta última versión consistente del dato….  Es decir que la integridad del dato está garantizada a nivel de sentencia sin establecer lockeos. Interesante no?. A tenerlo en cuenta ya que es muy útil…

-         - Seteando el isolation level de una sesión:

  

 SET TRANSACTION ISOLATION LEVEL READ_COMMITTED_SNAPSHOT

 Cómo Monitorear y Detectar Bloqueos ?

1) Utilizando la tabla del sistema sysprocesses

Ejemplo:

             Aquí la columna “blocked” nos dice qué proceso está causando el bloqueo. Para nuestro ejemplo el proceso 53 está siendo bloqueado por una requisitoria hecha por el proceso 52.

              Ahora pues podemos averiguar qué procesos están corriendo utilizando el comando DBCC INPUTBUFFFER, veamos:

   Si  quisiéramos entender qué tipo de lockeo está sosteniendo el proceso 52 …utilizamos SP_LOCK..

   Como podemos ver en la fila sombreada, el proceso 52 tiene un  intento de lockeo exclusivo permitido (IX), por lo tanto el proceso 53, que quiere hacer una lectura tiene que aguardar….

 2)      utilizando las vistas dinámicas del sistema

     A partir del Sql Server 2005 contamos con estas vistas que nos dan un nivel mayor de información que las viejas tablas del sistema.

     En este caso vamos a utilizar dos vistas a saber:

   sys.dm_exec_sessions: es una vista que, a nivel de server nos otorga información acerca de todas las conexiones de usuarios abiertas así como también las tareas internas del sistema (spid menor a 50).

   sys.dm_exec_requests: es una vista que nos devuelve información de cada solitud (request) que está pasando por el servidor.

   sys.dm_exec_sql_text: que actúa como una función que nos devuelve el texto del batch que se está ejecutando (identificado como sql_handle).

  Ahora que sabemos que función tiene cada vista, les cuento que las mismas pueden usarse combinadas con joins para encontrar en detalle:

·         El proceso bloqueado .

·         El proceso que está bloqueando.

Nada mejor que un buen ejemplo para poder entender su funcionamiento:

Vamos crear una tabla llamada “bloqueo” donde intentaremos insertar un dato y obteniendo pues el lockeo exclusivo de la tabla, el cual vamos a retardar con un waitfor delay de 2 minutos

-- creo la tabla y le insterto un registro, luego un wait..--

begin transaction

insert into bloqueo (id)

values (1)

waitfor delay  '00:02:00'

  Ahora abrimos otra session (otra pestaña de nuestro sql server) y tratemos de leer la tabla que está siendo bloqueada

-- intento leer la tabla lockeada en modo exlclusivo --

select * from bloqueo

Y llegó el momento de abrir una tercer pestaña y ejecutar nuestra query, la cual les recomiendo, guarden en su “botiquín de primeros auxilios”

 La query es la siguiente:

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

--  Detalle: Muestra bloqueos en nuestra base de datos --

--  1) La sesion que está bloqueando                   --

--  2) La sesión bloqueada                             --

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

SELECT

er.session_id ,

host_name,

program_name,

original_login_name,

er.status,

er.reads ,

er.writes,

er.cpu_time,

wait_type,

wait_time,

wait_resource,

blocking_session_id,

st.text,

er.transaction_isolation_level

FROM sys.dm_exec_sessions es

--

LEFT JOIN sys.dm_exec_requests er

ON er.session_id = es.session_id

--

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st

--

WHERE

er.blocking_session_id > 0

Union

SELECT

er.session_id ,

host_name,

program_name,

original_login_name,

er.status,

er.reads ,

er.writes,

er.cpu_time,

wait_type,

wait_time,

er.wait_resource,

er.blocking_session_id,

st.text,

er.transaction_isolation_level

FROM sys.dm_exec_sessions es

---

LEFT JOIN sys.dm_exec_requests er

ON er.session_id = es.session_id

---

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st

--

WHERE es.session_id IN ( SELECT blocking_session_id

FROM sys.dm_exec_requests

WHERE blocking_session_id > 0 )

Veremos pues como resultado de nuestra query en primer lugar al proceso que encuentra el bloqueo, el proceso nº 61, el cual intenta leer la tabla de nuestro ejemplo previamente lockeada en modo exclusivo por el proceso 67.

En “text” tenemos las sentencias que forman parte de los bloqueos…

     3)  ESTABLECIENDO UN “BASELINE” MEDIANTE EL PERFORMANCE MONITOR

   El “performance monitor” en si no es una herramienta que nos permita detectar la query que está bloqueando y la query que está esperando por la liberación de recursos. Mas su principal utilidad está en obtener un “baseline”, es decir un número promedio, de lo que serían los bloqueos promedio de nuestro sistema de base de datos.

   Para ello debemos utilizar los contadores “LOCK request/dec” – “Avg Wait Time (ms)” – “Lock Waits/sec” and “Number of Deadlocks”

Ejemplo:

 Cómo Minimizar La Ocurrencia de Bloqueos ?

 Amigos, he aquí unos consejos breves y concretos para tratar de minimizar la cantidad y duración de los bloqueos en nuestra base de datos:

  La premisa es que nuestras queries “lean y liberen lo más rápido posible”  Esto lo lograremos:

1)      Haciendo que nuestros códigos devuelvan la mínima cantidad de datos posibles

1.1) No a los Select * from ¡!!! Seleccionemos sólo los campos estrictamente necesarios.

1.2) No a las queries poco selectivas que devuelven muchos más datos de los necesarios,     filtremos bien en los where!!!

1.3) Revisemos los códigos que identificamos como conflictivos y rehagamos aquellos que sean necesario rehacer.

2)       Dotando a las tablas de los índices necesarios

2.1)Estudiemos los planes de ejecución a fondo y optimicemos las queries dotando las tablas              los índices necesarios para el funcionamiento óptimo de las queries.

3)      Implementando rutinas de mantenimiento de índices y estadísticas.

3.1) En entornos OLTP (de mucha modificación de datos), los índices tienden a degradarse rápidamente. Se fragmentan y pierden efectividad. Es necesario implementar rutinas de optimización y mantenimiento de los mismos (en este blog encontraran un script para ello)

3.2) 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.

4)      Utilizando el Isolation Level apropiado para nuestro negocio.

4.1) Hemos estudiado los distintos tipos de Isolation Level existentes. La idea es utilizar el que se adecue a las exigencias de nuestro sistema. Si por ejemplo estamos en un sistema que permite utilizar lecturas sucias sería una buena idea abolir los lock utilizando el nivel Read Uncommitted a nivel de sesión o con el hint with (no lock) luego del from.

4.2) Tal vez no podemos realizar lecturas sucias pero si utilizar el Isolation Level “READ_COMMITED_SNAPSHOT” y asi evitar lockeo y los posteriores bloqueos.

5)      Particionando tablas cuando sea conveniente hacerlo.

5.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)

6)      Ubicando correctamente los datafiles de nuestras bases de datos.

6.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.

6.2)  Traten siempre de que sea posible de lograr este escenario. No olviden 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.

6.3) Para asegurarse de que el SqlSrv está seteado para utilizar todos los procesadores disponibles controlen el valor de “Server Properties”àAdvancedàParallelismàMaxDegree of Parallelism, el mismo debe ser de cero (0)-

7)      Manteniendo en línea sólo 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.

8)      Parametrizando consultas

Si podemos utilizar una consulta y llegar a ella con parámetros, vamos a poder reutilizar los planes de ejecución cacheados en memoria. Lo opuesto sería el utilizar la misma consulta pero con valores hardcodeados cada vez que la utilicemos.

9)      Manteniendo los discos desfragmentados

Es muy importante que tengamos corriendo en períodos de baja o nula concurrencia alguna herramienta eficiente de desfragmentado de nuestros hard disk.

10) Monitoreando permanentemente el nivel de actividad de nuestro servidor

Es vital la uilización del performance monitor y el event viewer y el establecimiento de baselines. Si vemos por ejemplo que constantemente nuestro sistema pagina, puede ser una buena idea revisar la memoria ram asignada a nuestro sql server.

COMMON TABLE EXPRESSION (CTE)

   Amigos,

     Desde el año 2005 Microsoft Sql Server ha puesto en nuestras manos una herramienta muy valiosa cuando de simplificar la lectura y escritura de código hablamos.
      Se trata de los famosos CTE - "Common Table Expression"

     Qué son los CTE ?

     En la práctica los CTE son recordset de datos que cuentan con la ventaja de poder ser referenciados dentro de una sentencia select - update - insert - delete tantas veces como sea necesario
      Básicamente actúan como vistas  y nos permiten prescindir del uso de tablas temporales, cursores, vistas y complejas lógicas (tales como subqueries "poco leíbles")
      Los CTE permiten definir uno o varias subqueries con un Alias (el nombre del CTE), y luego utilizar esas subqueries referenciando diréctamente al Alias.

      
    Para qué se usan?

1.    Como ya se ha mencionado, para agilizar la lectura y escritura de queries complejas que requieren de subqueries, tablas temporales etc.
2.    En aquellas ocasiones en la que es necesario referenciar el resultado de una tabla en múltiples ocasiones.
3.    Para poder agrupar por una columna derivada de subqueries.
4.    Para poder realizar consultas recursivas

    Cuál es su syntaxis?

    -          La sintaxis comienza con un WITH seguida del NombredelCTE

-         A continuación pueden detallarse los nombres de las columnas del CTE (opcional)

-         Luego un AS seguido de la SubConsulta entre paréntesis, esto último todo obligatorio 

WITH NombredelCTE (columna1, columna2)

AS

(Subconsulta)

Ejemplo 1.  Reemplazando una querie tradicional por otra utilizando CTE

Supongamos que tenemos:

-  Una tabla llamada Users_Subscriptions con el Teléfono(Ani)y la Fecha de Subscripción de un usuario a X servicio (Date_Begin)

-  Una segunda tabla llamada Cdr_Subscriptions con el detalle de las cobranzas hechas a cada ANI de la tabla User_Subscriptions - un registro en esta tabla es una cobranza realizada-

Nuestro jefe nos encarga la tarea de "Hacer un informe que conste de ANI, FechaSubscripción, Cobranzas, para el mes de Febrero de 2015"

Es aquí que podemos resolver nuestra solicitud del modo tradicional (utilizando subqueries) o del modo que les propongo hoy, mucho más sencillo, entendible y escalable en grado de complejidad (utilizando CTE)

A) Resolviendo la requisitoria de la forma tradicional

select

a.ani,

b.date_begin,

a.cobros

from

 --

(select ani, count(*) as cobros from

 cdr_subscriptions with (nolock)

 where smsdatetime between '2015-02-01 00:00:00' and '2015-02-28 23:59:59'

group by ani) as a

 --

INNER JOIN

 users_subscriptions as b with (nolock)

 on a.ani = b.ani

 --

 where

 date_begin between  '2015-02-01 00:00:00' and '2015-02-28 23:59:59'

B) Resolviendo la requisitoria usando CTE  (entendible y escalable, sumando tantas  CTEs como hagan falta y referenciando las mismas en la query final)

-- Querie con CTE --

-- Defino al Cte --

WITH A   (ANI, COBROS)

AS

(select ani, count(*) from

 cdr_subscriptions with (nolock)

 where smsdatetime between '2015-02-01 00:00:00' and '2015-02-28 23:59:59' 

 group by ani)

 -- Hago query referenciando al CTE

 select A.ani, A.cobros, b.date_begin

 from users_subscriptions AS B

 INNER join A on

 a.ANI = b.ANI

 where

 date_begin between  '2015-02-01 00:00:00' and '2015-02-28 23:59:59' 

No he querido hacer de este ejemplo un ejemplo complejo, puesto que quiero que se familiaricen y entiendan el funcionamiento de las CTE. 

La idéa es que "pierdan el miedo"a utilizar las CTE.

Hace ya 10 años que están disponibles y creanmé aún hay gente que se complica la vida escribiendo y/o interpretando complejísimas queries cuya escencia podría ser alivianada sustantivamente si se utilizacen las CTE.

El objetivo es que, aunque sea, tengan presente la posibilidad de utilizarlas en reemplazo de tablas temporales, vistas, subqueries etc, cuando tengan que encarar trabajos en los que la complejidad de las queries hagan que bien valga la pena el intento de uso de las CTE.

Ejemplo 2.  Utilizando Una CTE Recursiva

Una de las grandes utilidades de las CTE está relacionada con la necesidad de utilizar queries recursivas.

Puede ser necesario utilizar una query recursiva cuando queremos mostrar los empleados en un organigrama o una lista de materiales en donde un producto primario tiene uno o varios componentes que, a su vez, tienen subcomponentes o son componentes de otros elementos primarios.

-  Un CTE es recursivo cuando se ejecuta una x cantidad de veces devolviendo subconjunto de datos, hasta obtener el conjunto de datos completo.

- Una CTE Recursiva se compone de una o varias CTE combinadas mediante operadores UNION ALL, UNION, EXCEPT

- La CTE Recursiva finaliza de modo implícito cuando no se devuelven más filas desde su llamado.

Una vez más voy a utilizar un ejemplo muy sencillo para que se familiaricen con el uso de las CTE, en este caso las recursivas. La idéa es no asustarlos sino invitarlos a que las usen

-----------QUERY RECURSIVA ----------

WITH MuestraMensaje(Mensaje, Largo)

AS

(

SELECT

Mensaje = CONVERT(varchar(300), 'Amo '),

Largo =  LEN('Amo ')

UNION ALL

SELECT

CONVERT (VARCHAR(300), Mensaje + 'Sql Server! '),

LEN(mensaje)

FROM MuestraMensaje

WHERE Largo < 300

)

-- Llamo la cte recursiva

SELECT mensaje, largo FROM MuestraMensaje

Amigos, espero que haya sido de utilidad este post y como siempre espero sus contactos para resolver dudas. Los saludo cordialmente