{"id":410,"date":"2017-03-22T09:00:54","date_gmt":"2017-03-22T09:00:54","guid":{"rendered":"https:\/\/sqldoubleg.live-website.com\/?p=410"},"modified":"2017-11-17T20:48:37","modified_gmt":"2017-11-17T20:48:37","slug":"not-the-typical-story-about-key-lookups","status":"publish","type":"post","link":"https:\/\/www.sqldoubleg.com\/es\/2017\/03\/22\/not-the-typical-story-about-key-lookups\/","title":{"rendered":"Esta No es la T\u00edpica Historia sobre Lookups"},"content":{"rendered":"

Los planes de ejecuci\u00f3n son muy \u00fatiles a las hora de mejorar la eficacia de nuestras consultas, pero muchas veces tratamos de evitar algunos operadores o culparlos de los problemas de rendimiento cuando no siempre son los autenticos culpables, dejadme ense\u00f1aros uno de esos casos en que la teor\u00eda no se ajusta a la realidad Entiendo que si quieres mantenerte al d\u00eda con la tecnolog\u00eda (o al menos intentarlo), debes pasar gran parte de tu tiempo leyendo sobre ella, sin importar si usas libros en l\u00ednea, compras libros de la tienda o lo que m\u00e1s disfruto, leer publicaciones de blogs y art\u00edculos t\u00e9cnicos.<\/p>\n

As\u00ed que estoy leyendo un art\u00edculo de Klaus Aschenbrenner (b<\/a>|t<\/a>) llamado The Performance Penalty of Bookmark Lookups in SQL Server<\/a> que explica la degradaci\u00f3n del rendimiento que puede sufrir debido al operador bookmark (or Key) lookups.<\/p>\n

Todo bien, es un reputado experto y sabe de lo que est\u00e1 hablando y no pretendo decir que est\u00e1 equivocado, porque no lo est\u00e1, pero d\u00e9jadme mostraros el 0,000001% que hace que SQL Server sea un sistema tan fascinante.<\/p>\n

 
\nAntecedentes<\/strong><\/p>\n

No voy a entrar en muchos detalles sobre los Key Lookups ya que puedes leer el art\u00edculo de Klaus, que obviamente recomiendo, pero como siempre hay un ‘depende’. Y voy a mostrarte algo que encontr\u00e9 la semana pasada y mantuve los planes de ejecuci\u00f3n para explicar.<\/p>\n

Como dije, 99.999999% (suposici\u00f3n es educada) de los tiempos, el optimizador de consultas elegir\u00e1 un \u00edndice de cobertura, ya que solo tiene que leer de un sitio para finalizar la consulta, evitando costosos operadores de bucle anidados y las b\u00fasquedas temibles. A mi me parece bastante razonable.<\/p>\n

Pero en algunas condiciones y donde hay otro \u00edndice que se pueda usar, el optimizador puede elegir (sabiamente) utilizar ese \u00edndice y luego realizar las b\u00fasquedas. D\u00e9jame mostrarte las consultas y sus planes de ejecuci\u00f3n.<\/p>\n

\r\nSELECT DISTINCT\r\n        Object1.Column1 ,\r\n        Object1.Column2 ,\r\n        Object1.Column3 AS Column4 ,\r\n        Object1.Column5 AS Column6 ,\r\n        Object1.Column7 AS Column8\r\n\tFROM Schema1.Object2 AS Object1\r\n\t\tINNER JOIN Schema1.Object3 AS Object3 \r\n\t\t\tON Object1.Column1 = Object3.Column1\r\n        INNER JOIN Schema1.Object4 AS Object5 \r\n\t\t\tON Object3.Column10 = Object5.Column11\r\n\tWHERE Object1.Column12 = 'Value'\r\n        AND Object1.Column3 BETWEEN 'Date' AND 'Date'\r\n        AND ( Object5.Column13 LIKE '%String%' );\r\n\r\nSELECT DISTINCT\r\n        Object1.Column1 ,\r\n        Object1.Column2 ,\r\n        Object1.Column3 AS Column4 ,\r\n        Object1.Column5 AS Column6 ,\r\n        Object1.Column7 AS Column8\r\n\tFROM Schema1.Object2 AS Object1 WITH ( INDEX = Index5 )\r\n\t\tINNER JOIN Schema1.Object3 AS Object3 \r\n\t\t\tON Object1.Column1 = Object3.Column1\r\n        INNER JOIN Schema1.Object4 AS Object5 \r\n\t\t\tON Object3.Column10 = Object5.Column11\r\n\tWHERE Object1.Column12 = 'Value'\r\n        AND Object1.Column3 BETWEEN 'Date' AND 'Date'\r\n        AND ( Object5.Column13 LIKE '%String%' );\r\n<\/pre>\n
 
\nLa primera consulta no utilizaba el \u00edndice de cobertura que hab\u00eda creado, as\u00ed que decid\u00ed forzarlo en la segunda consulta, los planes de ejecuci\u00f3n son los siguientes<\/p>\n
\"\"<\/a><\/p>\n
Puedes ver que hay Key Lookups, y realmente me intrig\u00f3 esto, \u00bfse me ha pasado a\u00f1adir una columna para cubrir la consulta? As\u00ed que decid\u00ed usar una sugerencia de consulta para forzar el \u00edndice de cobertura. Y este fu\u00e9 el plan de ejecuci\u00f3n<\/p>\n
\"\"<\/a><\/p>\n
Se puede observar c\u00f3mo Object2.Index3 Seek + Object2.Index4 Lookup<\/em><\/strong> se ha convertido en Object2.Index5 Seek<\/em><\/strong>. Y en teor\u00eda, eso deber\u00eda ser m\u00e1s barato para SQL Server, entonces \u00bfen qu\u00e9 est\u00e1 pensando? \u00bfSe ha vuelto loco o algo as\u00ed?<\/p>\n
Realmente no.<\/p>\n
 
\nSigamos investigando<\/strong><\/p>\n
Los planes de ejecuci\u00f3n son buenos para el ajuste del rendimiento, pero estas no son las \u00fanicas herramientas que debemos analizar. Otra gran herramienta es STATISTICS IO y STATISTICS TIME, siendo esta \u00faltima un poco m\u00e1s ~ish en algunos aspectos, pero en general ambos son los que uso todo el rato para optimizar el SQL.<\/p>\n
El resultado para estas dos consultas es<\/p>\n
\r\n\/*\r\n(2619 row(s) affected)\r\nTable 'Object2'. Scan count 0, logical reads 150702, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'Object3'. Scan count 0, logical reads 76737, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'Object4'. Scan count 9, logical reads 64938, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\n\r\n(1 row(s) affected)\r\n\r\n SQL Server Execution Times:\r\n   CPU time = 19716 ms,  elapsed time = 2632 ms.\r\n\r\n(2619 row(s) affected)\r\nTable 'Object3'. Scan count 0, logical reads 76735, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'Object4'. Scan count 9, logical reads 64938, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'Object2'. Scan count 9, logical reads 293220, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\n\r\n(1 row(s) affected)\r\n\r\n SQL Server Execution Times:\r\n   CPU time = 24297 ms,  elapsed time = 3071 ms.\r\n*\/\r\n<\/pre>\n
 
\nSi usamos StatisticsParser<\/a> obtenemos una imagen mucho m\u00e1s bonita<\/p>\n
\"\"<\/a><\/p>\n
 
\nEn la primera consulta (la elecci\u00f3n del optimizador), la tabla m\u00e1s grande tiene casi la mitad de las lecturas, incluidos los dos \u00edndices, en el que buscamos por la clave y el que hacemos el lookup para obtener el resto de columnas.<\/p>\n
Obligar a SQL a usar el \u00edndice de cobertura en este caso no ayuda, ya que necesita casi el doble de esfuerzo para satisfacer la misma consulta.<\/p>\n
 
\nLa trampa<\/strong><\/p>\n
Este es un caso muy especial donde el \u00edndice de cobertura tiene tres claves y luego un par de columnas incluidas, una de las cuales es la columna NVARCHAR (MAX), por lo que el \u00edndice de cobertura es bastante grande y solo buscamos en la columna de la izquierda, que tambi\u00e9n es posible utilizando otro \u00edndice mucho m\u00e1s peque\u00f1o en esa \u00fanica columna.<\/p>\n
En ambos casos, el operador puede enviar el (los) predicado(s) de la consulta a la b\u00fasqueda y, gracias a eso, el n\u00famero de filas que sale del operador no es tan grande. Pero el n\u00famero de filas que coinciden con la clave m\u00e1s a la izquierda y, por lo tanto, deben leerse es bastante grande.<\/p>\n
Recuerde que aunque vea \u00abBuscar\u00bb lo que es en realidad un escaneo parcial y dependiendo de qu\u00e9 tan grande es la porci\u00f3n para escanear y el n\u00famero de filas que caben en una p\u00e1gina, el optimizador usar\u00eda el \u00edndice m\u00e1s estrecho, ya que podr\u00eda contrarrestar la penalizaci\u00f3n de tener que hacer Lookups para las filas retornadas.<\/p>\n
As\u00ed que tom\u00e9 la consulta y la divid\u00ed de forma que coincida con el operador l\u00f3gico, teniendo una consulta com\u00fan a los dos planes de ejecuci\u00f3n diferentes y luego dos variaciones, con una \u00fanica b\u00fasqueda para el \u00edndice de cobertura y otra con dos la b\u00fasqueda m\u00e1s la b\u00fasqueda.<\/p>\n
Y mirar el resultado de STATISTICS IO es bastante revelador:<\/p>\n
\"\"<\/a><\/p>\n
 
\nAmbas consultas causan menos lecturas en total, lo que significa que los dos operadores (b\u00fasqueda + lookup) son, en este caso, menos costosos en IO que una b\u00fasqueda (escaneo parcial) en el \u00edndice de cobertura m\u00e1s amplio.<\/p>\n
Podemos ver ambos para comparar uno al lado del otro.<\/p>\n
\"\"<\/a><\/p>\n
 <\/p>\n
El lado izquierdo intenta replicar las dos b\u00fasquedas mediante el uso de dos tablas temporales de etapas y el de la derecha usar\u00e1 el \u00edndice de cobertura.<\/p>\n
Entonces, debido al tama\u00f1o del \u00edndice de cobertura y la cardinalidad de la columna de la izquierda, el optimizador cree que usar el \u00edndice m\u00e1s estrecho y luego buscar las columnas restantes para las filas ser\u00eda m\u00e1s barato que mirar el \u00edndice de cobertura, que resulta en m\u00e1s IO’s, cerca del doble de lecturas.<\/p>\n
 
\nConclusi\u00f3n<\/strong><\/p>\n
Esta es una demostraci\u00f3n de lo dif\u00edcil que puede ser ajustar y optimizar las consultas para el rendimiento y los muchos factores que pueden estar involucrados.<\/p>\n
Los hechos explicados no contradicen la regla general, pero si hay m\u00e1s factores involucrados, esta puede ser ignorada. Tambi\u00e9n viene de nuevo para demostrar que casi nunca una soluci\u00f3n funciona en absolutamente todos los  casos cuando se habla de SQL Server, aunque se trata de una caso bastante extra\u00f1o.<\/p>\n
Obviamente, debemos seguir las gu\u00edas de buenas pr\u00e1cticas porque cubrir\u00e1n la gran mayor\u00eda de los casos, pero aunque las sigamos, a\u00fan podemos encontrar cosas que nos sorprendan.<\/p>\n
He tratado de replicar con diferentes conjuntos de valores y el \u00edndice de cobertura fue la opci\u00f3n preferida m\u00e1s com\u00fan, lo que hace que valga la pena incluso si hay algunas excepciones.<\/p>\n
Gracias por leer y cualquier pregunta, usad los comentarios!<\/p>\n
 
\n<\/p>","protected":false},"excerpt":{"rendered":"
Los planes de ejecuci\u00f3n son muy \u00fatiles a las hora de mejorar la eficacia de nuestras consultas, pero muchas veces tratamos de evitar algunos operadores o culparlos de los problemas de rendimiento…<\/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":[61,55,32,28],"_links":{"self":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/410"}],"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=410"}],"version-history":[{"count":0,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/410\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/media?parent=410"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/categories?post=410"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/tags?post=410"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}