\nAntecedentes<\/strong><\/p>\n
En el pasado, alguien decidi\u00f3 que era una gran idea (n\u00f3tese la iron\u00eda) permitir que se almacenaran cualquier tipo de datos dentro de una base de datos, y eso incluye texto y objetos binarios ‘ilimitados’ (im\u00e1genes por ejemplo) de hasta 2GB, por lo TEXT, NTEXT e IMAGE se agregaron a SQL Server como tipos de datos.<\/p>\n
Luego las cosas se pusieron un poco modernas y fueron reemplazadas por VARCHAR (MAX), NVARCHAR (MAX) y VARBINARY (MAX) y los anteriores fueron marcadas como ‘Obsoleto’, lo que significa probablemente no mucho, ya que en el momento de escribir eso, todav\u00eda est\u00e1n ah\u00ed.<\/p>\n
Tengo bases de datos, como cualquier buen DBA, y algunas est\u00e1n llenos de documentos XML almacenados como [N]TEXT. Algunas tablas son realmente grandes, d\u00e9jame mostraros.<\/p>\n Esta tabla contiene algunas columnas con metadatos que ayudan a ubicar el documento xml, por lo que si consultamos la tabla para obtener todos los documentos que coinciden con ciertas condiciones (no hay \u00edndices en esa columna) tendremos que escanear toda la tabla para recuperar esos documentos .<\/p>\n La siguiente consulta no recupera ninguna fila, ya que la fecha de creaci\u00f3n no puede ser mayor que la actual.<\/p>\n Y como era de esperar, tenemos que escanear toda la tabla para satisfacer la consulta.<\/p>\n Todo bien entonces, entonces si tenemos que escanear el \u00edndice agrupado (tabla), podemos suponer que el n\u00famero de p\u00e1ginas le\u00eddas ser\u00e1 bastante cercano a los 80,5M de p\u00e1ginas que hemos visto antes, \u00bfno?<\/p>\n Revisemos la pesta\u00f1a ‘Mensajes’.<\/p>\n \u00bfEjem? \u00bfSolo 125k? \u00bfC\u00f3mo?<\/p>\n Dijimos que los datos en SQL Server se almacenan en 8k p\u00e1ginas, pero estas p\u00e1ginas se administran en una o m\u00e1s unidades de asignaci\u00f3n dependiendo de los tipos de datos.<\/p>\n Si echamos un vistazo a Books online<\/a>, vemos que los diferentes tipos de unidades de asignaci\u00f3n son<\/p>\n Este es el comportamiento predeterminado para los tipos de datos LOB antiguos, que se almacenar\u00e1n por separado, pero los nuevos tipos de datos LOB (MAX) de forma predeterminada tratar\u00e1n de ponerlos en fila si son lo suficientemente peque\u00f1os como para caber.<\/p>\n Si algunos de esos documentos estuvieran almacenados en la filas, dar\u00eda como resultado un aumento importante en el n\u00famero de p\u00e1ginas para escanear, lo que en este caso, afectar\u00eda negativamente al rendimiento.<\/p>\n Hay que tener en cuenta que para el escaneo de tablas solo hemos utilizado las p\u00e1ginas IN_ROW_DATA, lo que lo hace mucho m\u00e1s liviano que si tuvi\u00e9ramos que escanear la suma de todas las p\u00e1ginas.<\/p>\n Como dije, podemos lograr el mismo comportamiento utilizando los nuevos tipos de datos (MAX) utilizando el procedimiento almacenado sp_tableoption<\/a> especificando que la opci\u00f3n ‘tipos de valores grandes fuera de la fila’ es verdadera.<\/p>\n Este SP funcionar\u00e1 al contrario para hacer que los tipos de datos obsoletos tambi\u00e9n se comporten como los nuevos al especificar un valor entre 24 y 7000 para la opci\u00f3n ‘texto en fila’.<\/p>\n Por lo tanto, tenemos el control total de d\u00f3nde se almacenan los datos de LOB, para que coincidan con nuestro patr\u00f3n de uso.<\/p>\n Cuando instalamos SQL Server, este viene con una configuraci\u00f3n predeterminada y lo mismo que solemos cambiar configuracien a niver de servidor y de las base de datos por varias razones, el hecho de poder decidir d\u00f3nde se almacenar\u00e1n nuestros datos abre muchas posibilidades para administrarlo mejor y obtener el mejor rendimiento para nuestras consultas.<\/p>\n En este caso, usar los tipos de datos obsoletos, debido al almacenamiento predeterminado, nos ha beneficiado a la hora de tener que escanear una tabla grande como esta, pero lo mismo se puede lograr ajustando los nuevos tipos de datos, por lo que no es realmente necesario para seguir usando los tipos de datos obsoletos.<\/p>\n Hay algunos enlaces que no quiero terminar sin compartirlos.<\/p>\n <\/p>","protected":false},"excerpt":{"rendered":" De vez en cuando, es bueno recordarle a la gente la complejidad de un producto como SQL Server. Las partes internas son tan amplias y complejas que nos perdemos f\u00e1cilmente y seguimos…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[51,5,12,25,49],"_links":{"self":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/428"}],"collection":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/comments?post=428"}],"version-history":[{"count":1,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/428\/revisions"}],"predecessor-version":[{"id":436,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/428\/revisions\/436"}],"wp:attachment":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/media?parent=428"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/categories?post=428"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/tags?post=428"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nCaso real<\/strong><\/p>\n![\"\"](\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2017\/04\/01_table_size.png\")
<\/a><\/p>\n
\nEntonces una tabla con 13576971 filas y 629,398MB que son documentos XML, no est\u00e1 mal … Sabemos que SQL Server almacena datos en p\u00e1ginas de 8kb, por lo que aproximadamente esta tabla usa 80548829 p\u00e1ginas.<\/p>\n\r\nUSE [my_database]\r\nGO\r\n\r\nSET STATISTICS IO ON \r\nGO\r\n\r\nSELECT * \r\nFROM dbo.data\r\nWHERE created > DATEADD(DAY, 1, GETDATE())\r\nGO\r\n\r\n<\/pre>\n
<\/a><\/p>\n<\/a><\/p>\n
\nExplicaci\u00f3n<\/strong><\/p>\n\n
\nPara ver la distribuci\u00f3n de datos con m\u00e1s detalle, necesitamos dividir el total por tipo de unidad de asignaci\u00f3n.<\/p>\n\r\nSELECT OBJECT_NAME(ix.object_id)\r\n\t\t, ix.name\r\n\t\t, au.*\r\n\t\t, p.rows\r\n\tFROM sys.indexes AS ix\r\n\t\tINNER JOIN sys.partitions AS p\r\n\t\t\tON p.object_id = ix.object_id \r\n\t\t\t\tAND p.index_id = ix.index_id\r\n\t\tINNER JOIN sys.allocation_units AS au\r\n\t\t\tON au.container_id = p.hobt_id\t\r\nWHERE ix.object_id IN (OBJECT_ID('dbo.Data'))\r\n\r\n<\/pre>\n<\/a><\/p>\n
\nPodemos ver que nuestra tabla est\u00e1 distribuida en dos unidades de asignaci\u00f3n diferentes, IN_ROW_DATA y LOB_DATA, lo que significa que todos los datos dentro de las columnas de los tipos de datos anteriores terminar\u00e1n en diferentes tipos de p\u00e1ginas de forma predeterminada, independientemente del tama\u00f1o de los datos.<\/p>\n
\nLa nueva forma de hacer las cosas<\/strong><\/p>\n
\nConclusi\u00f3n<\/strong><\/p>\n\n
\n\u00a1Gracias por leer!<\/p>\n