Personalmente, nunca me ha gustado la opción de filtrado de objetos en SSMS. Me parece poco útil, por lo que al final no la uso nunca. Por eso me ha parecido muy interesante el Add-In que se puede descargar en aquí (para SQL Server 2008, aunque también está para la versión 2005). El problema es que los complementos no están soportados de forma oficial en SSMS, por lo que hay veces que no funcionan todo lo bien que deberían. Con este que os comento he tenido algún que otro problemilla menor, tal vez debidos a la máquina con la que trabajo.
Sin embargo, hay otra opción más sencilla y más rápida que la de usar herramientas externas (aunque sea un complemento). Os explico:
Desde el menú Tools/Options, ir al nodo Environment/Keyboard y en cualquier combinación de teclas que os quede libre (yo por ejemplo lo tengo en CTRL+8), añadir este texto (así tal cual, sin saltos de línea):
SELECT name, type_desc FROM (SELECT OBJECT_NAME(object_id) + '.' + name AS name, 'COLUMN' AS type_desc, OBJECT_NAME(object_id) AS [table] FROM sys.columns UNION SELECT name, type_desc, NULL FROM sys.objects WHERE [type] <> 'S' ) T WHERE [name] LIKE
Reiniciar SSMS.
Al volver a entrar y abrir una nueva ventana de consulta, cualquier texto que escribáis, lo seleccionéis y pulséis la combinación de teclas que habíais elegido antes, os listará todos los objetos (nombre, tipo objeto) que tienen como nombre ese texto.
Por ejemplo, si escribís en una nueva ventana
'%miTabla%'
Al aparecerá el nombre y el tipo de cada uno de los objetos de la base de datos actual que contienen el nombre miTabla
Esto es ampliable. Es decir, podéis añadir cambiar el patrón de búsqueda, añadir más condiciones de búsqueda, u ordenar por el tipo de objeto:
-- busca sólo en las columnas de las tablas
'%miColumna%' AND type_desc LIKE 'COLUMN'
-- ordenado por tipo de objeto
'%miObjeto%' ORDER BY type_desc
Espero que os sea de utilidad.
Si alguno de vosotros estáis habituados a utilizar Visual Studio, igual este truco no os resulte nuevo. Para mí, en cambio, sí que lo ha sido cuando lo he descubierto.
Hasta ahora, cuando desplazamos el ratón por el texto con el botón izquierdo pulsado se marca toda la línea. Es el comportamiento normal y que todos conocemos. Sin embargo, si queremos sólo marcar ciertas columnas no teníamos más remedio que modificar la instrucción que devolvía esos datos y copiar todo el texto. Desgraciadamente hay veces que esto no es posible (por ejemplo, en un procedimiento almacenado que no podemos modificar)…
El truco es sencillo: prueba a mantener pulsado ALT mientras realizas la misma acción. Vaya, ahora no se marca toda la línea, sino el rectángulo que vas dibujando según te desplazas con el ratón. J
Lo mejor es que también funciona en la ventana donde se escribe el código. Esto resulta muy útil cuando, por ejemplo, tenemos un texto comentado: en vez de marcarlo, descomentarlo, ejecutarlo y volverlo a comentar, simplemente siguiendo el método comentado (excluyendo del recuadro los dos guiones que marcan el texto como comentario), es suficiente para que podamos ejecutarlo.
Muy práctico!!
El otro día me encontré con un artículo que vino a confirmar una sensación que tengo desde hace tiempo sobre el cometido de un DBA. Normalmente cuando se nombra el término "DBA", se piensa en una persona que se encarga de analizar el rendimiento del servidor de base de datos con herramientas como el monitor de rendimiento, o consultando objetos de sistema (vistas, DMV, procedimientos almacenados, etc.). Tareas como replicación entre base de datos, log shipping, backups, creación de logins… son operaciones que sabemos que tiene que hacer esta persona.
En un entorno pequeño, esta persona se puede encargar además de "guiar" a los desarrolladores (o al jefe de proyecto) para que hagan las cosas del modo en que un experto en la materia (en este caso SQL Server) puede llegar a hacer. Sin embargo, en la mayoría de las empresas, el número de servidores hace que el trabajo del día a día impida realizar este otro tipo de operaciones, tanto o más importantes que las otras (incluso he conocidos algunos DBA que consideran estas tareas como degradantes). Esto supone en muchos casos que el DBA se termina encontrando con el problema directamente en el servidor de producción, ya que nadie ha validado que el proceso cumpla con unos requisitos de eficiencia mínimos: en unos casos porque el desarrollador no tiene los conocimientos suficientes (sí, sabe crear procedimientos almacenados, pero poco más) o el jefe de proyecto considera que lo principal de su trabajo consiste en que el modelo de clases que ha pensado quede bien reflejado, dando menor importancia a todo lo relacionado con la base de datos. En definitiva, por diversas circunstancias, el problema al final está ahí, y normalmente aparece en el peor momento.
Por eso hace tiempo empecé a pensar en que realmente deberían existir varios DBA o, mejor dicho, varios perfiles de DBA: uno de ellos se debería dedicar más a tareas relacionadas con los servidores de producción, encargándose de que ese entorno ofrezca el servicio que se presupone; pero otro de ellos debería encargarse de que las operaciones que se realizan en los entornos menos "conflictivos" cumplan con unos criterios de calidad mínimos, aconsejando en todo lo relacionado con el diseño de la base de datos (tablas, índices, funciones, procedimientos, etc.). De este modo el rendimiento vendría avalado por un experto en la materia, que tendría el mismo punto de vista que la persona que se encarga del entorno de producción, consiguiendo de ese modo, además, que los posibles problemas que puedan surgir se resuelvan mucho antes.
El artículo al que hacía referencia define muy bien el objetivo en el que deben centrarse estas dos figuras: el DBA (tal y como lo entendemos hasta ahora) debe centrarse en SQL Server como instancia, mientras que esta nueva figura debe centrarse en las bases de datos que alberga cada instancia de SQL Server.
¿Tú qué opinas? ¿Consideras que esta figura debería estar en todas las empresas? ¿Crees tal vez que el nivel de conocimientos de un desarrollador debe ser mayor en todo lo referente a las bases de datos? ¿Piensas que es el jefe de proyecto el que debe garantizar el rendimiento óptimo en base de datos?
Hace poco me encontré con una situación que me sorprendió por un lado, pero por otro la verdad es que no. Al final de esta entrada seguramente comprendáis porqué digo algo aparentemente contradictorio.
Pongamos este ejemplo: queremos extraer los datos de las líneas de pedido que más dinero han proporcionado al negocio. Extrapolando este ejemplo a la base de datos AdventureWorks, podríamos escribirlo así:
SELECT od.*
FROM (
SELECT SalesOrderID, MAX(LineTotal) AS max_Total
FROM [Sales].[SalesOrderDetail]
GROUP BY SalesOrderID
) mx INNER JOIN [Sales].[SalesOrderDetail] od ON mx.SalesOrderID=od.SalesOrderID AND mx.max_Total=od.LineTotal
Quisiera hacer un inciso en este punto para comentar que en muchas ocasiones hemos visto en clientes que para resolver esta consulta primeramente creaban una tabla temporal (variable o no, eso da igual), insertaban los datos en dicha tabla y posteriormente hacían el join con la tabla final para obtener los datos. Como podréis imaginar, esa opción es muy ineficiente, implicando la necesidad de unos recursos de más que pueden ser perfectamente evitables reescribiendo la instrucción al estilo que el ejemplo muestra.
Una vez hecha la puntualización, si queremos que la consulta se resuelva de forma eficiente, nos hace falta un índice sobre las columnas SalesOrderId y LineTotal, algo que hace la siguiente instrucción:
CREATE NONCLUSTERED INDEX nci_Total ON [Sales].[SalesOrderDetail](SalesOrderID, LineTotal)
Si mostramos el plan de ejecución, veremos que usa el índice que acabamos de crear:
Hasta aquí nada extraño ni fuera de lo común: SQL Server ha creído oportuno usar el índice nci_Total para agrupar los resultados en base a una columna y extraer el valor máximo de la otra columna. Luego tan sólo tiene que hacer un hash match para mostrar los datos solicitados (en este caso, todas las columnas de la tabla).
Sin embargo, ahora viene uno de los jefes que nos pide una serie de modificaciones en la consulta en un momento dado, ya que ahora nos pide sacar los datos de las últimas líneas de pedido de cada uno de los pedidos (campo ModifiedDate). Y como nos lo pide para ayer, nosotros, para cumplir con las exigencias, dejamos la instrucción así:
SELECT SalesOrderID, MAX(LineTotal) AS max_Total, MAX(ModifiedDate) AS max_Date
) mx INNER JOIN [Sales].[SalesOrderDetail] od ON mx.SalesOrderID=od.SalesOrderID AND mx.max_Date=od.ModifiedDate
Si vemos el plan de ejecución de esta consulta, vemos que es muy pesada porque no hay un índice útil para agrupar los datos de la forma en la que se solicitan, por lo que se tiene que recorrer dos veces la tabla (Clustered Index Scan) para resolverla. Pero como era una petición puntual, no creemos conveniente (con toda la razón del mundo) crear un índice.
Una vez pasado el momento de crisis, volvemos a modificar la consulta, pero con las prisas del día a día la dejamos así:
El problema está en que se nos olvidó quitar el campo max_Date de la consulta interna, con lo que el índice nci_Total no va a ser suficiente para resolver eficientemente la instrucción… ¿Seguro? Analicemos el plan de ejecución:
No, no he vuelto a copiar el plan de ejecución anterior. Si os fijáis, la instrucción es diferente (aparece MAX(ModifiedDate)). Sin embargo, como SQL Server sabe que ese campo no va a ser accedido en ninguna otra parte de la consulta, directamente lo obvia: ha conseguido que un olvido por culpa del día a día no tenga implicaciones en el rendimiento del servidor.
Con lo cual volvemos al párrafo inicial: cuando me encontré con la situación parecida a la que muestro en el ejemplo, por un lado me sorprendió al ver que usaba el índice para resolver la consulta y que no tenía en cuenta el resto de las columnas sobre las que se aplicaban funciones de agregado, pero por otro lado no me sorprendió porque realmente es lo mismo que hace cuando diariamente combinamos dos tablas: al procesar la instrucción, una de las fases es ver qué es lo que necesita y qué no para resolver la consulta, no cogiendo nada más que lo imprescindiblemente necesario.
Además, de este modo podemos comprobar lo importante que es pedir sólo lo que se va a usar. En muchas ocasiones hemos escuchado decir también que solicitan todos los campos de la tabla "por si acaso más adelante se necesitan". Como si eso no implicara nada, ¿verdad?
Mi amigo José Miguel Torres, ha publicado hace un par de semanas un libro de buenas prácticas sobre SQL Server Compact Edition; si estás metido en tema de desarrollo con dispositivos móviles, y almacenas la información que gestionas en algún sitio -- base de datos... este es un ebook que deberías tener; algo así como los Inside SQL Server para los viciosos de SQL Server :)
Aquí tienes más información sobre el ebook:
http://geeks.ms/blogs/jmtorres/archive/2008/09/16/sql-server-compact-edition-buenas-pr-225-cticas.aspx
Si lo quieres comprar, lo puedes hacer desde aquí:
http://www.solidq.com/ib/Press.aspx
Desde SQL Server 2005 disponemos de un conjunto interesante de DMV que nos permiten conocer que índices desearía disponer el optimizador (sys.dm_db_missing_index*). En SQL Server 2008 vamos un paso más allá y cuando visualizamos el plan de ejecución de una consulta nos aparecerá directamente si SQL Server desea que creemos algún índice para satisfacerle ;)
Para ejemplo aquí tenéis una captura con una consulta sin índice de cobertura y la recomendación que se nos muestra en SSMS:
Como podéis ver, además, con el botón derecho nos aparece la opción "Missing Index Details" que nos abrirá en una nueva ventana el CREATE INDEX correspondiente J Se agradecen estos detalles que nos hacen la vida un poco más sencilla a todos J
Tanto en SQL Server 2000 como en 2005 uno de los inconvenientes que tenemos cuando utilizamos full-text search es que éste sistema no se encuentra integrado con el optimizador de consultas. Dicho de otra forma, una consulta que incluya parte relacional pura y parte full-text debe descomponerse en dos consultas independientes y luego operar con los resultados. Esto provoca ineficiencias que en algunos casos se solucionaban de forma artificial (por ejemplo incluyendo columnas relacionado en el texto a indexar).
En SQL Server 2008 el motor se encuentra integrado y los índices full-text pasan a ser índices especializados (como los de los tipos de datos geográficos) pero que siguen estando dentro del motor relaciona. Además se ha optimizado el motor para máquinas con más procesadores y mayores cantidades de memoria intentando así obtener mejores rendimientos en las omnipresentes plataformas de 64 bits.
Para poder hacer algunas pruebas representativas decidí realizar una carga de textos a partir de unos cuantos libros gratuitos del proyecto Gutenberg (http://www.gutenberg.org). La carga incluye 33278 documentos con un total de 3.67GB de texto danto una media de 115KB por documento. Para que nos hagamos todos una idea de la longitud en texto plano que son 115KB hice una prueba de copiar sobre un documento Word uno de los registros. Con interlineado simple y letra de 11 puntos correspondió a unas 50 páginas con lo que interpolando al total estamos indexando aproximadamente más de un millón seiscientas mil páginas. A dichas tablas añadí un par de columnas fecha y país para hacer pruebas sobre consultas cruzadas con alta-baja selectividad respecto al filtro relacional. La fecha la he distribuido uniformemente de forma que cada entrada corresponde a 1 hora con lo que la diferencia entre el primer documento y el último es de casi 4 años. El país lo he distribuido entre 15 valores de forma no uniforme. La distribución que he seguido ha sido en base 2. Esto es, al primero de los países le he asignado 2 documentos, al segundo 4, al tercero 8, 16, 32, 64,… J Ambas columnas tienen un índice no cluster sobre esa única columna. Esto lo podemos conseguir fácilmente con un update en función de un identity o utilizando row_number. En mi caso he hecho un sencillo update como este basándome en una columna IDENTITY(1,1) que ya tenía definida:
update documentos
set fecha = dateadd(hour, id, '20050101')
, pais = floor(log10(id+1)/log10(2))
Obteniendo este resultado:
ID Fecha País
1 2005-01-01 01:00:00.000 1
2 2005-01-01 02:00:00.000 1
3 2005-01-01 03:00:00.000 2
4 2005-01-01 04:00:00.000 2
5 2005-01-01 05:00:00.000 2
6 2005-01-01 06:00:00.000 2
7 2005-01-01 07:00:00.000 3
8 2005-01-01 08:00:00.000 3
9 2005-01-01 09:00:00.000 3
10 2005-01-01 10:00:00.000 3
11 2005-01-01 11:00:00.000 3
12 2005-01-01 12:00:00.000 3
13 2005-01-01 13:00:00.000 3
14 2005-01-01 14:00:00.000 3
15 2005-01-01 15:00:00.000 4
16 2005-01-01 16:00:00.000 4
…. …. …
33278 2008-10-18 14:00:00.000 15
La primera diferencia obvia que encontramos al pasar a 2008 es el aumento de tamaño de nuestros ficheros de datos al incluirse los índices dentro de la propia base de datos. En ambos casos el tamaño ocupado por los datos es muy similar (~3.7 GB) pero en el caso de SQL Server 2008 el tamaño de los índices es de 7.3 GB. Si, no es una equivocación J Los índices full-text no son como los índices tradicionales con lo que es habitual que acaben teniendo un tamaño superior al de los datos indexados. Obviamente esto depende de muchos factores como la variabilidad de las palabras, el número de palabras de "ruido" que se descarten, etc. En SQL Server 2000 los índices se almacenan en ficheros independientes por defecto dentro de la carpeta FTDATA de la instancia en cuestión. En nuestro caso el tamaño de dichos índices es de 4.9 GB. Ciertamente la diferencia de casi un 50% en el tamaño de los índices es significativa y debe tenerse en cuenta de cara a afrontar los upgrades de versiones anteriores. Por otra parte el proceso de carga completa del índice (population) fue bastante lento en ambos casos debido principalmente a las limitaciones de la máquina en la que lo realicé (32 bits, 3Gb RAM).
En realidad la CPU no llegó a sobrepasar el 50% durante todo el proceso por lo que el subsistema de IO y la cantidad de memoria de la máquina se mostraron claramente insuficientes para esta labor. No puedo por tanto sino recomendar que implementaciones de fulltext search se realicen sobre máquinas de 64 bits con buena capacidad de entrada/salida así como memoria disponible. Casualmente, esto suele ser lo que todos deseamos para nuestros servidores de bases de datos por lo que no se requiere un tipo de servidor especializado diferente a los que utilicemos para bases de datos sin full-text search J Respecto al número de procesadores, diferentes pruebas muestran que el valor "dulce" está sobre 8 procesadores siendo complicado que a partir de esta cifra se obtenga un beneficio para la carga inicial. Obviamente, para resolver una gran cantidad de búsquedas por segundo tener más procesadores puede ayudarnos.
Por daros alguna referencia os indico que el tiempo de carga del índice en SQL Server 2000 fue de 1 hora y 40 minutos mientras que en SQL Server 2008 fue de 1 hora y 51 minutos. Aunque tenemos una diferencia de 11 minutos considero que no es significativa al tratarse de un portátil con características muy alejadas a las de un servidor. También destacar que la operación es muy intensiva en IO. Esto hizo que el portátil estuviera prácticamente "knoqueado" durante el proceso. Deberemos tener en cuenta esto para hacer estas operaciones masivas dentro de una ventana de mantenimiento o al menos en horas valle de poca actividad del sistema.
A continuación vamos a hacer algunas pruebas sobre algunas consultas puras full-text y algunas mixtas relacional + full-text para poder comparar el rendimiento del nuevo optimizador integrado. Para todas las pruebas haremos una limpieza de la caché previamente y lanzaremos 2 veces la consulta para comparar resultados en frio y en caliente.
SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,'daemon')
Esta consulta nos devuelve 45 resultados en SQL Server 2008 y 44 en SQL Server 2000. El motivo de esto es que tanto los separadores de palabras así como el conjunto de sinónimos, etc. ha sido mejorado. Buscando el documento que existe de diferencia encontré que el problema es que en 2008 "daemon" se considera sinónimo de "demon" mientras que en SQL 2000 no. Muy probablemente ocurra algo parecido con "deamon".
SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,'angel*')
En este caso tenemos 820 resultados en ambos casos. Parece que nuestros textos son más angelicales que demoniacos J Veamos cuantos contienen ambos términos relativamente cerca.
SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,'angel NEAR daemon')
En esta consulta obtenemos un resultado bastante sorprendente al encontrar SQL Server 2008 muchas más coincidencias de cercanía. No aparece nada en la documentación que indique que se ha aumentando la sensibilidad o el "alcance" del concepto de cercanía pero así parece ser. En SQL Server 2000 obtenemos únicamente 3 documentos mientras que en SQL Server 2008 obtenemos 26 documentos. Obviamente esto se verá reflejado en cierta forma en la entrada/salida necesaria para el conteo. Aunque podríamos extendernos utilizando muchos de las herramientas de las que tenemos disponibles (sinónimos, formas verbales, pesos, expresiones complejas, etc.) creo que este conjunto de consultas son suficientemente representativas. A continuación vamos a ver el rendimiento en consultas que combinan filtros sobre columnas de la tabla junto a filtros full-text. Para ello vamos a comprobarlo desde diferentes perspectivas de cardinalidad.
Como filtro no selectivo para full-text utilizaremos "CONTAINS(texto,'page')" que devuelve 1242 filas mientras que para el relacional utilizaremos "fecha > '20060101'" que devuelve 24518 resultados. En el caso de filtro selectivo utilizaremos "CONTAINS(texto,'torque')" que devuelve 12 filas y para el relacional "pais=2" que devuelve 4 filas. Para cada escenario las consultas a utilizar serán las siguientes:
SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,'torque') AND pais=2
SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,'torque') AND fecha > '20060101'
SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,'page') AND pais=2
SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,'page') AND fecha > '20060101'
En la siguiente tabla comparativa incluyo los valores medios de 4 ejecuciones en mi máquina de cada una de las consultas.
SQL SERVER 2000
SQL SERVER 2008
CONSULTA
CACHE
IO
CPU
Total
% Mejora
FRIA
132
0
955
90
15
443
53,6%
CALIENTE
99
835
1
99,8%
2235
1448
1640
32
622
57,1%
1707
832
10
14
98,3%
6
1366
52
16
797
41,6%
1015
4
5
99,5%
30
879
86
31
563
35,9%
50
54
-8,1%
36
121
495
-309,1%
118
57
51,6%
2
869
3192
829
4,6%
134
64
52,2%
2846
455
972
-113,6%
2580
150
-177,7%
Como podemos ver, en aquellas consultas puras full-text search el rendimiento es considerablemente mejor en todos los casos. Las mayores mejoras se encuentran cuando la consulta se ejecuta contra la caché en caliente en cuyo casi el coste de algunas de ellas es irrisorio mientras que en SQL Server 2000 necesita de un mínimo de 800ms en cada una de ellas. Cuando utilizamos escenarios mixtos donde se combinan tanto filtros relacionales como full-text la situación se vuelve mucho menos predecible. Cuando utilizamos un escenario donde ambos predicados son muy selectivos o lo es el predicado relacional el rendimiento de SQL Server 2008 está a la par o mejora bastante el de SQL Server 2000. El principal problema aparece cuando este predicado relacional no es selectivo (fecha > '20060101'). En ese caso, si el predicado fulltext es poco selectivo 2008 resulta ser el doble de lento que 2000. Si el predicado fulltext es selectivo, tenemos el escenario más radical de todos: con la caché fría 2008 es 3 veces más lento que 2000 y con la caché caliente es el doble de rápido.
En conclusión diremos que aunque estos resultados no son interpolables a un entorno de producción, hay ciertos indicios que la nueva funcionalidad de fulltext search muestra un mejor rendimiento en general cuando disponemos de los datos en caché o cuando la consulta es puramente sobre el índice full-text. Sin embargo, en escenarios mixtos y, especialmente en aquellas consultas únicas que puedan llegar con caché fría, el rendimiento puede ser significativamente más lento o algo mejor. Por tanto para conocer de forma fiable la ventaja de rendimiento que podemos obtener en nuestro entorno la única forma precisa es probando la carga actual de nuestro sistema SQL Server 2000 sobre una instalación de SQL Server 2008 y medir los resultados.
De vez en cuando, como formador y consultor, tengo que justificar a nuestros clientes los beneficios que supone usar Integridad Referencial Declarativa (Claves Ajenas). Generalmente tengo comentarios del siguiente tipo:
hay muchos más casos aparte de estos cuatro; de mi experiencia, veo que la raiz del problema está relacionado con el ciclo de vida de sus aplicaciones (ALM), y mejorar sus buenas prácticas. Casi siempre, se puede llegar a una solución mejor diseñada y más segura; de hecho, durante las clases y reuniones, el cliente se queda convencido, pero días después, vuelven a la realidad de su trabajo y por diversas razones, es complicado cambiar los hábitos, y poner en marcha "lo que ya le has convencido" anteriormente.
Aquí pongo un post de Conor Cunningham (http://blogs.msdn.com/conor_cunningham_msft/archive/2008/08/07/foreign-keys-are-our-friends.aspx) donde muestra beneficios ocultos de usar DRI: Utilizando DRI el Optimizador de SQL Server, puede decidir no acceder a la tabla base, porque SQL Server ya sabe -- por la Integridad Referencial -- que si una fila existe en la tabla hija, tiene que existir en la tabla padre.
Además, me gustaría hacer algunas recomendaciones sobre todo esto, que quizás ves útil.
Usando un script muy similar al de Conor:
use AdventureWorks2008;go
drop table f1drop table t1go
create table t1(id int, v char(1) default 'a' , constraint t1_pk primary key (id))go
create table f1(f_id int identity, t_id int not null, v char(1) default 'a' , constraint f1_pk primary key (f_id) , constraint fk1 FOREIGN KEY (t_i