Buscar este blog

lunes, 27 de octubre de 2014

Notas del SQL Saturday Barcelona (#338): Conocimiento compartido

Volvemos de Barcelona con la sensación de haber sido partícipes de una experiencia enriquecedora: el primer SQL Saturday celebrado en España ha sido todo un éxito.

La encomiable labor organizativa del grupo PASS España -encabezados por Rubén Pertusa-, la generosidad a la hora de compartir su conocimiento de los expertos venidos de diversos países para realizar sus excelentes ponencias y el entusiasmo de todos los profesionales registrados, allí reunidos durante más de 10 horas de sesiones, con un gran afán por compartir y aprender han hecho del SQL Saturday Barcelona un evento del que salimos con un bagaje positivo y muchas ganas de repetir.

Como bien expuso Fernando G. Guerrero, CEO de SolidQ -uno de los sponsors del evento-, "la curiosidad es lo que nos mueve". El apetito por aprender, las ganas de compartir experiencias y conocer a quienes también las viven son el motor de los SQL Saturday. Nada más acertado que el lema de "Share what you know, learn what you don't" (Comparte lo que sabes, aprende lo que no).
Nuestro sábado dio comienzo poco después de las ocho y media de la mañana. Tras el pertinente registro, llegaba la presentación del evento.

La primera sensación fue de sorpresa, pues el número de profesionales inscrito, dispuestos a sacrificar su tiempo libre para no perderse la cita -a centenares de kilómetros de su hogar, en muchos casos-, era elevado.

Tras la bienvenida en uno de los salones de actos del IQS (lugar donde se desarrollaría la jornada), comenzaban las sesiones. Distribuidas entre tres salas, las rutas a elegir eran claras: Business Intelligence, Big Data o DBA (y developers, añadiríamos).

Escogimos, por supuesto, la que más se ajustaba a nuestro perfil, la tercera de ellas. Comenzamos la mañana aprendiendo muchas cosas nuevas sobre SQL Server 2014, de la mano de Enrique Catalá.
No entraremos en detalles técnicos, puesto que no es el propósito de esta entrada en la bitácora, pero las Transacciones de Durabilidad Diferida enseguida captaron nuestra atención. Ya había merecido la pena el esfuerzo realizado por estar presente.

La segunda sesión, también en manos de Enrique, nos dejó con ganas de más. El tiempo destinado a explicar los planes de ejecución -que nos explican qué hace el motor de bases de datos para devolvernos lo que le pedimos- fue a todas luces escaso. Es un tema que da para muchas horas, y que conviene conocer a la perfección. Por cierto, Enrique sólo lo mencionó de pasada porque se quedó sin tiempo para más: si has de verte las caras con planes de ejecución, no dudes en usar SQL Sentry Plan Explorer.

Llegó el tiempo para el primer descanso. Un café y la primera oportunidad para conversar con compañeros de fatigas. Allí conocimos a Eduardo Burillo, alicantino él, con el que fue un placer compartir inquietudes.

Dos sesiones más antes de comer: En la primera, Sergey Olontsev nos habló de una utopía en "Cómo sacar provecho de In-Memory OLTP (Hekaton) en nuestras aplicaciones". Imposible. No se puede. Parece que el afán por competir con otras soluciones para Big Data ha provocado el parto prematuro de Hekaton. SQL Server 2014 salió mucho antes de que esta tecnología estuviese madura. Si es que algún día lo está. Habrá que estar pendientes de su evolución en próximas ediciones. Por ahora solamente alguna aplicación de nuevo cuño y con finalidad muy específica podría sacarle partido sin sufrir alguna se sus casi infinitas restricciones. Eso, y el rayo de luz final que supuso conocer la existencia de una alternativa en memoria a las tablas temporales y variables tipo tabla.

Llegaba el turno después para Davide Mauro, hablándonos de un tema en el que somos más bien profanos: "Cómo elegir el hardware adecuado para SQL Server". Apuntamos algunas cosas interesantes, como las herramientas TPC (Transaction Processing Performance Council) para banco de pruebas. Las usaremos.

Tras la hora de la comida, aún restaban tres sesiones más. Descubrimos el potencial de Power BI, de la mano Rodney Landrum como antesala a otra de las sesiones más interesantes del día, de la mano del divertido alemán Uwe Ricken. Su charla sobre lo que ocurre en las entrañas de SQL Server -esto es, a nivel de páginas de datos- cada vez que modificamos datos mediante instrucciones INSERT, UPDATE y DELETE fue realmente reveladora. No hubo tiempo para completarla, pero nos sumergió de lleno en lo que ocurre cuando ejecutamos dichas sentencias.

Finalizamos el día con la sesión más relajada del sábado. Alberto López Grande nos propuso una reflexión, desarrollada en un agradable debate colectivo, sobre el día a día del DBA, centrada en el desafío que supone mantener los servidores en las versiones más recientes para entornos productivos.

Las interesantes intervenciones de los allí presentes reflejó a la perfección el propósito y espíritu de los SQL Saturday: participar, compartir, debatir. En resumen, aprender. SQL Saturdays que, esperamos, se instauren y se conviertan en costumbre, no sólo en Barcelona, sino en cualquier ciudad donde los profesionales que trabajamos con SQL Server queramos seguir compartiendo conocimiento.

viernes, 4 de julio de 2014

Humor y SQL Server: inyección SQL y cómo evitarla (no llames a tu hijo DROP TABLE)

El portal xkcd publica esta viñeta sobre inyección de código en SQL Server:

Inyección en SQL Server
Los peligros de llamar a tu hijo DROP TABLE
Tratado con gran sentido del humor, esta cómica situación nos pone en alerta frente a la inyección de código SQL Server, que puede suponer una vulnerabilidad seria para nuestras bases de datos.

Una aplicación que no compruebe los datos que en ella se introducen a través de aplicaciones externas está expuesta al desastre. En el cómic se introduce una instrucción DROP TABLE en medio del supuesto nombre de un niño inscrito en un colegio. Al introducir el falso nombre en el sistema informático del centro, el resultado es catastrófico: la tabla de estudiantes es eliminada por completo.

La Wikipedia presenta un ejemplo similar para demostrar en qué consiste la inyección SQL Server, así como diversas soluciones a adoptar, dependiendo del lenguaje de programación de la aplicación:
"Por ejemplo, asumiendo que el siguiente código reside en una aplicación web y que existe un parámetro "nombreUsuario" que contiene el nombre de usuario a consultar, una inyección SQL se podría provocar de la siguiente forma:
El código SQL original y vulnerable es:
consulta := "SELECT * FROM usuarios WHERE nombre = '" + nombreUsuario + "';"
Si el operador escribe un nombre, por ejemplo "Alicia", nada anormal sucederá, la aplicación generaría una sentencia SQL similar a la siguiente, que es perfectamente correcta, en donde se seleccionarían todos los registros con el nombre "Alicia" en la base de datos:
SELECT * FROM usuarios WHERE nombre = 'Alicia';
Pero si un operador malintencionado escribe como nombre de usuario a consultar:
"Alicia'; DROP TABLE usuarios; SELECT * FROM datos WHERE nombre LIKE '%"
(sin las comillas externas), se generaría la siguiente consulta SQL, (el color verde es lo que pretende el programador, el azul es el dato, y el rojo, el código SQL inyectado):
SELECT * FROM usuarios WHERE nombre = 'Alicia'; DROP TABLE usuarios; SELECT * FROM datos WHERE nombre LIKE '%DROP TABLE usuarios;SELECT * FROM datos WHERE nombre LIKE '%';
En la base de datos se ejecutaría la consulta en el orden dado, se seleccionarían todos los registros con el nombre 'Alicia', se borraría la tabla 'usuarios' y finalmente se seleccionaría toda la tabla "datos", que no debería estar disponible para los usuarios web comunes."

jueves, 3 de julio de 2014

¡Nos vemos en el SQLSaturday Barcelona!

¡Por primera vez llegan a España los famosos eventos SQLSaturday! Será en Barcelona, el próximo sábado 25 de octubre de 2014.

Ya me he registrado en el evento. Será una buena oportunidad para conocer a otros profesionales de SQL Server y compartir experiencias y conocimiento, todo ello mientras aprendemos de los mejores expertos, incluidos algunos MVP y MCM de Microsoft, en las diferentes sesiones programadas.
SQLSaturday es un evento gratuito de un día de duración para profesionales y futuros profesionales relacionados con SQL Server, Big Data y Business Intelligence; organizado por la Asociación de Profesionales de SQL Server (PASS). Se han celebrado más de 300 eventos SQLSaturday alrededor de todo el mundo (el de Barcelona será el 338) y ésta va a ser la primera edición en España. El plazo de registro lleva abierto algunos días, en los que ya se han inscrito más de 70 asistentes.
Tal y como afirma la web del evento, dentro de la multitud de temas que se van a tratar en las sesiones, podremos encontrar:

  • Motor relacional (optimización, bloqueos, índices...)
  • Alta disponibilidad
  • SQL Server 2014
  • Big Data (HDinsight, Pig, Hive...)
  • Power BI
  • Integration Services, Analysis Services, Reporting 
  • Powershell 
  • ...
Habrá sesiones en inglés y en castellano. Los ponentes han ido enviando sus propuestas de sesiones, que aparecen listadas en la web del evento. En próximas fechas se publicará el calendario final del evento, para que cada asistente se programe aquéllas a las que tiene más interés en asistir.

Además, el evento favorece el networking entre profesionales de SQL Server, facilitando un lugar común en el que conocer al resto de asistentes mediante sus perfiles de LinkedId o sus cuentas de Twitter.

viernes, 23 de mayo de 2014

Haz bien tus consultas: Llama a las cosas por su nombre (y esquema)

La MSDN nos explica cual es la forma correcta de referenciar a los objetos en tus consultas, y es la siguiente:

Esquema.Nombre

¿Por qué? Hay diversos motivos y, aunque parezca algo trivial, pueden evitarnos acceder a objetos incorrectos, errores en consultas para usuarios de la base de datos distintos al que creó el objeto e incluso problemas de rendimiento.

Resolución de nombres

Cuando ejecutamos una consulta, el servidor SQL realiza un proceso llamado resolución de nombres (name resolution, en inglés) para identificar los objetos referidos en ella.

Si identificamos cada objeto por su nombre completo (fully qualified name), SQL sabrá determinar inmediatamente a qué objeto nos referimos y si existe o no. Por el contrario, si solamente especificamos su nombre, el motor de bases de datos tendrá que determinar a qué objeto nos referimos. Para ello usará el default schema del usuario conectado.

Esquema por defecto o Default schema

Cada usuario de una base de datos puede tener un esquema por defecto (desde SQL Server 2008 R2, también los grupos de Windows. Esto es importante, como veremos a continuación). Si para un usuario no está definido el esquema por defecto, se asume que éste es "dbo".

Para comprobar el esquema por defecto que tienen los usuarios de nuestra base de datos, podemos ejecutar la siguiente consulta:

Cuando SQL tiene que resolver un nombre de objeto sin esquema, primero busca en el esquema por defecto del usuario actual. Si no lo encuentra ahí, entonces lo buscará en el esquema dbo. Si tampoco estuviera ahí, la consulta fallaría.

Es conveniente que el esquema por defecto de todos los usuarios y grupos sea dbo o no esté definido (NULL), salvo que haya razones muy concretas para que no sea así.

Cuando el esquema por defecto de un usuario es otro, se dan los problemas descritos a continuación.

Problemas relacionados con el esquema por defecto

  • Si un usuario tiene un esquema por defecto que no es dbo, cuando cree nuevos objetos en la base de datos, estos se llamarán esquema_usuario.nombre_objeto. Si cualquier otro usuario con un esquema por defecto distinto hace una consulta sobre dicho objeto sin especificar su esquema, la consulta fallará.
  • Si se crea un objeto con el esquema dbo, pero un usuario con el esquema por defecto distinto de dbo hace una consulta se hace sin especificar el esquema, SQL buscará el objeto primero en su esquema por defecto y después en dbo (name resolution). Esto puede producir problemas de rendimiento. Si bien estos problemas no serían dramáticos, se han medido que entre el 10% y el 15% del proceso de ejecución de una consulta puede ser debido a la resolución de nombres.
Los esquemas existen desde SQL Server 2005 y ofrecen multitud de posibilidades de organización y control de seguridad sobre nuestros objetos. Sin embargo, aunque no los uses y todos tus objetos estén en el esquema dbo, que es el por defecto, es posible que el esquema por defecto de alguno de tus usuarios no sea dbo.

Para comprobar qué usuarios no tienen por defecto el esquema "dbo", puedes ejecutar el siguiente script:

La MSDN explica muy bien todo este proceso en su sección "How to Refer to Objects" (como referirse a objetos) del artículo -en inglés- "SQL Server Best Practices - Implementation of Database Object" (Prácticas recomendadas en SQL Server - Implementación de Objetos de Base de Datos):
Es siempre una buena práctica referirse a los objetos de la base de datos por el nombre de esquema y el nombre de objeto separados por un punto.
...
El proceso [de resolución de nombres] puede mejorarse usando el nombre completo o configurando correctamente el esquema por defecto del usuario, eliminando un proceso innecesario de comprobación de propiedad. Esto puede mejorar considerablemente el rendimiento en sistemas utilizados intensivamente.

Problema adicional con grupos de usuarios de Windows en versiones anteriores a SQL Server 2012

Si asignas permisos a tus bases de datos a grupos de usuarios de Windows en versiones de SQL Server anteriores a 2012, cuando los usuarios de dichos grupos accedan a la base de datos, SQL Server les creará un usuario cuyo esquema por defecto será su login de Windows.

Esto hace que el problema descrito de resolución de nombres se agrave especialmente en este tipo de entornos con versiones de SQL Server 2005 o 2008, en los que el administrador de bases de datos gestione los permisos de esta manera.

Por ejemplo: Si el usuario MIDOMINIO\Usuario1 pertenece al grupo MIDOMINIO\Grupo1 y el Usuario1 se conecte por primera vez a una base de datos para la que tenga permisos asignados a través del Grupo1, SQL Server le asignará como esquema por defecto el valor "MIDOMINIO\Usuario1".

A partir de este momento, cada objeto que cree el Usuario1 sin definir el esquema correctamente se llamará de la forma:

MIDOMINIO\Usuario1.NombreObjeto.

Y todas las consultas que haga el usuario sin especificar el esquema en los nombres de objeto penalizarán el rendimiento de SQL Server. Por ejemplo:

Además, si otros usuarios realizan esta misma consulta, fallará, ya que SQL Server no será capaz de encontrar el objeto si no se especifica el nombre de esquema.

Conclusiones

  • Si eres DBA: Asegúrate que los usuarios de tus bases de datos tengan asignado como esquema por defecto dbo o NULL.
  • Si eres programador: Escribe todas tus consultas haciendo referencia a los objetos por su nombre completo, de la forma nombre_de_esquema.nombre_de_objeto

lunes, 14 de abril de 2014

Hacer trazas en SQL Server Express

Si necesitas diagnosticar problemas de rendimiento, encontrar consultas conflictivas o simplemente quieras trazar las consultas que hace una aplicación contra la base de datos, seguramente estés acostumbrado a usar el SQL Server Profiler, la herramienta que principalmente hemos utilizado para realizar este tipo de diagnósticos sobre nuestras bases de datos y servidores. También es posible que te hayas decidido a dar el paso a Extended Events, pero esa es otra cuestión.

Si el Profiler es tu herramienta, muy seguramente la eches de menos en el caso de que el diagnóstico tengas que hacerlo sobre un SQL Server Express. La edición gratuita del motor de bases de datos relacionales de Microsoft no incluye esta aplicación. Es un contratiempo, pero hay alternativas. A continuación, os las presentamos:

ExpressProfiler


ExpressProfiler es un ejecutable, gratuito, de código abierto, anteriormente conocido como SqlExpress Profiler. Presenta una interfaz muy sencilla en un ejecutable que solamente tendremos que descargar y ejecutar, sin instalaciones. Simplemente tendremos que darle al botón de play y monitorizar la traza para ver qué consultas se están ejecutando.

El ExpressProfiler v2.0, en acción
El ExpressProfiler v2.0, en acción

Trace Flags o Marcas de Seguimiento


Existen en SQL Server una serie de características que pueden ser configuradas de manera temporal y dinámica, activando y desactivando lo que se conoce como trace flags o marcas de seguimiento, en castellano. Tal y como explica la MSDN, las marcas de seguimiento se suelen utilizar para diagnosticar problemas de rendimiento o para depurar procedimientos almacenados o sistemas complejos.

Pues bien, dos de ellas nos sirven para nuestro propósito, tal y como se explica en este artículo de CodeProject, escrito por BillyBoatGruff en febrero de 2012.

Activando la marca 4032, todas las consultas ejecutadas sobre SQL Server se trazan. El mecanismo habitual para activar una traza es mediante la instrucción DBCC TRACEON:

Sin embargo, si intentamos activar esta traza, obtendremos el siguiente mensaje:
"Se omitirá la marca de seguimiento 4032. Se trata de una marca de seguimiento no válida o que solo se puede especificar durante el inicio del servidor."
Tal y como explica el mensaje, esta traza ha de especificarse durante el inicio del servidor. Para ello ejecutaremos la siguiente instrucción desde una línea de comandos que hayamos ejecutado como administrador:

Para poder ejecutar la instrucción, el servidor deberá estar detenido. Si no lo está, podemos pararlo desde la misma línea de comandos con la instrucción:


Una vez hecho esto, volcaremos las consultas al log de errores de SQL Server para que podamos consultarla, activando la traza 3605:

Con estas dos trazas activas, podremos revisar en el log de nuestro SQL Server todas las consultas realizadas sobre el servidor. El log permite realizar filtrados y exportar su listado a un fichero de texto, desde donde podremos copiar las consultas.

Hay que tener en cuenta que para poder utilizar este mecanismo, es necesario ser administrador tanto del servidor de bases de datos como del equipo en el que esté instalado.

Para deshabilitar las trazas, una vez hayamos acabado con las tareas de diagnóstico, ejecuta:

viernes, 14 de marzo de 2014

Heaps en SQL Server: tablas sin índice clustered y sus consecuencias

Definición

En SQL Server las tablas pueden tener o no tener un índice clustered. El índice clustered es por el que SQL ordena físicamente los datos (las filas de las tablas) en disco.

El índice clustered le sirve a SQL para buscar, ordenar y agrupar registros de manera eficiente. Así, no tener un índice clustered en una tabla puede llevarnos a problemas de rendimiento. Cuando una tabla no tiene índice clustered, se llama heap.

El problema

Los heaps mantienen los registros en las páginas de datos en el mismo orden en el que se han insertado. Esto hace que los heaps resulten más rápidos a la hora de realizar un INSERT, ya que no han de insertarse en una posición en concreto, sino directamente a continuación del último registro existente para la tabla en cuestión.

Sin embargo, cualquier otra operación que requiera un orden en los datos será más lenta. Esto se aplica a SELECT, DELETE y UPDATE, salvo que se quieran efectuar estas operaciones sobre la tabla completa.

Lo que la MSDN nos dice de los heaps es que:
  • A veces, hay buenos motivos para dejar una tabla como heap en lugar de crear un índice clustered, pero para usar los heaps de forma eficaz se requieren conocimientos avanzados. La mayoría de las tablas deben tener un índice clustered cuidadosamente elegido.
  • Si una tabla es un heap y no tiene ningún índice no clustered, debe examinarse la tabla completa (table scan) cuando se busca una fila.
Es decir, en general los heaps no son aceptables. Solamente bajo ciertos escenarios muy estudiados para los que una ordenación de los datos no es necesaria y un mantenimiento de índices por parte de SQL implicaría un sobrecoste innecesario (por ejemplo, una tabla con una lista de provincias de España, para la que siempre se devuelve la lista completa y nunca se busca información para un registro concreto).

En cualquier otro caso, cualquier consulta sobre una tabla sin índice clustered obligará a SQL Server a recorrer la tabla entera en busca del registro o registros solicitados. Evidentemente, cuanto mayor sea la tabla, mayor será el problema de rendimiento que esta situación puede generar.

Cómo detectarlo

El script a continuación ayuda a detectar heaps en nuestras bases de datos:

SELECT OBJECT_NAME(i.object_id) AS TableName, p.rows
FROM sys.indexes i
INNER JOIN sys.partitions p ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE i.index_id = 0
ORDER BY p.rows DESC

La solución

En general, con cada heap hay que pararse y determinar cuál debería ser su índice clustered. El candidato más idóneo para ser índice clustered suele ser siempre la clave primaria, aunque existen excepciones. Si la tabla sólo tiene clave primaria, ésta debe ser clustered siempre. El resto de los casos podemos estudiarlo, aunque existen algunas directrices:

  • Suelen ser los campos que usamos para hacer búsquedas y joins sobre la tabla.
  • Suelen ser los campos por los que se referencia a la tabla desde otras tablas. 
  •  Hay que intentar que el índice clustered de una tabla tenga el mínimo tamaño posible.

Todo esto señala, como decíamos, a la clave primaria de la tabla. Pero, en ocasiones, creamos claves alternativas a la primaria que se ajustan mejor a esta definición.

Referencias:

miércoles, 12 de marzo de 2014

Tipos de datos por defecto en SQL Server

En ocasiones necesitamos que nuestras consultas devuelvan valores constantes. Por ejemplo, de manera muy sencilla, podemos tener una consulta como la siguiente:

Si necesitamos recuperar los valores devueltos por la consulta en un recordset de ADO.NET, por ejemplo, ¿qué tipo de datos debemos esperar de columnas de este tipo? La respuesta: depende.

Depende del tipo de constante que hayamos introducido. Si se trata de un número que no desborde los 4 bytes de capacidad de un entero (int), como en el caso del 1 de nuestra consulta anterior, éste será el tipo de datos de dicha constante.

Si escribimos la constante entre comillas simples, SQL lo tratará como un varchar del tamaño del literal que hayamos escrito. Por ejemplo:

Creará un campo varchar(1). Éste sería nvarchar(2) si hubiésemos escrito lo siguiente, indicando un literal Unicode mediante la N delante del literal:

Para un número con parte decimal, el tipo de datos será numeric; lo mismo que para un número de tamaño mayor que el máximo entero positivo. Si escribimos un símbolo de moneda, como €, SQL lo interpretará con el tipo de datos money. Y así, sucesivamente.

Para comprobar todos los tipos de datos que queramos, podemos ejecutar el siguiente script:

Y comprobar en sus resultados qué tipo de datos podemos esperar para cada tipo de constante que se nos ocurra:

Llama especialmente la atención el tratamiento que recibe el NULL de la columna 7: su tipo de datos por defecto también es int.

Éste es el comportamiento que cabe esperar de SQL Server. Sin embargo, lo recomendable sería controlar exactamente qué tipo de datos queremos que nos sea devuelto ejecutando los CAST pertinentes sobre cada una de las constantes. En el ejemplo anterior, lo lógico es que queramos tratar el literal '20140312' como una fecha, por lo que la consulta sobre ese valor debería realizarse así:

jueves, 20 de febrero de 2014

Formato de fechas en SQL Server

Resumen: En cada país, en cada idioma, en cada región... usamos un formato diferente para representar las fechas. SQL Server permite comparar columnas que almacenan fechas contra cadenas de textos que convierte internamente al tipo de datos necesario. Sin embargo, según la configuración regional del servidor, la misma cadena puede ser interpretada de diferentes formas, generando errores que incluso pueden pasar inadvertidos. Para solucionarlo existe un formato de fecha estándar e independiente de cualquier configuración que hay que usar siempre: 'yyyymmdd'.