En esta sesión hablaremos de la nueva caracteristica de SQL Server 2012, AlwaysON Availability Group y como usarlo en un ambientes de Alta Disponibilidad y Recuperacion de Desastres.
En esta sesión hablaremos de la nueva caracteristica de SQL Server 2012, AlwaysON Availability Group y como usarlo en un ambientes de Alta Disponibilidad y Recuperacion de Desastres.
Some weeks ago I was asked to help with a performance issue with some MDX Queries.
I was shown what the customer wanted to achieve, they needed to calculate some metrics including sampling errors using the method of “Conglomerados Últimos” (This can be translated to “Conglomerates Last” Method), this method was created by a Spanish Researcher and publish in a Spain Publication to calculate sampling errors a posteriori when the sample is stratified. This method is described in Spanish (Sorry I don’t an English version of this document) here http://dialnet.unirioja.es/descarga/articulo/250808.pdf
Just trying to understand the method and formulas was hard, but the customer had already an elegant solution using an MDX Script and the SCOPE statement. So I focused on the performance issue instead of trying to understand the formulas.
After a couple of hours I found out that the formula was using cell by cell calculation instead of block calculation, then found the operator in fault (the “^” operator) and after a small change (multiply the measure by itself) the query improved from around 30 seconds to 1 second.
You can find some details about the operators that support block computation in “Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services” found in http://www.microsoft.com/en-us/download/details.aspx?id=661, you can also find some guidelines to improve query performance in http://msdn.microsoft.com/en-us/library/bb934106.aspx
What I want to focus in this blog is how you can detect that the issue is Cell by Cell Calculations.
We have two main tools to troubleshoot this kind of problems, SQL Server Profiler and Performance Monitor, the recommended order is to identify that the problem is Formula Engine using the profiler and then use the Performance monitor to confirm that the query is using Cell By Cell Calculation instead of block calculation (or computation).
Here is the recommended methodology, this is not new, but I haven’t found a good reference that goes through this steps.
Step 1. Identify if bottleneck is Formula engine
Use the profiler to capture the execution of your query, it is easiest if you do this in an isolated environment but if not possible you can still filter the events from your query using the ConnectionId or some other column.
You need to add the duration for each QuerySubcube, and then subtract that total from the duration shown in the QueryEnd Event. In this example you can see that most subcubes take 0 ms, some of them 16 ms and some other that is not shown in the image was 110 ms. You can also see that the total for the query was 3,953 ms, that means that, in the Storage Engine, Analysis Services spent 142 ms and the difference 3811 ms were spent in the Formula Engine.
In conclusion, we have a Formula Engine Bottleneck, let’s check is that bottleneck is caused by cell by calculation.
The previous procedure may not scale well when you have hundreds of subcubes events or many potential queries using Cell by Cell in the same trace, in that situation you can use a procedure like this one:
Step 2. Identify if the issue is Cell by Cell Calculation
You can use Performance Monitor to identify if the issue is that the calculations is being done Cell By Cell. To be successful in this task is necessary that you run your query in an isolated environment because we have a single counter for all queries and to be sure that our query is the one causing the problems you need to execute it alone, without other concurrent queries. If your query is using Cell By Cell calculation you will see something like the following image in your query.
In the image you can see that the counter being used is “MSOLAP&InstanceName:MDX \ Total Cells Calculated” and the initial value is 0 and the value after you execute the query is a little above 1 million.
After restarting the instance we can see that a single execution of the query had to calculate more than 1 million rows. If this is the first time you do this, you don’t know if 1 million is too much or too low. I can tell you that is very high for this specific query.
Step 3. Modify your query
Modify the query using the guidelines in the links references until you stop seeing the big increments in cell calculated for your query.
In this particular case the Script was using a couple of expressions like:
Measures.Measure1 ^ 2
We changed the formula to something like:
Measures.Measure1 * Measures.Measure1
After the formula was changed in this particular case we got the following results.
You can see that the total for the query is 156 ms now, considering that the Storage Engine must have used the same time, 132 ms, it means that now the Formula Engine is taking only 24 ms.
In the performance Monitor we can see that the total number of cell calculated was 41. The query was executed after another restart of the instance.
Final Comments
This is just a basic procedure to find out what is causing the problem on your query. Modifying the query to avoid cell by cell calculation will be easy sometimes, like in this real case, and will be hard in other scenarios.
I hope this is helpful to start troubleshooting your own cases.
Hace algunas semanas me pidieron apoyo con un problema de desempeño en algunas consultas MDX.
Me mostraron lo que el cliente quería lograr, ellos necesitaban calcular algunas métricas incluyendo errores de muestra utilizando el método “Conglomerados Últimos” para calcular el error en la muestra a posteriores para muestras estratificadas. Éste método fue creado por un investigador español y fue publicado en España. Pueden encontrar la referencia aquí, http://dialnet.unirioja.es/descarga/articulo/250808.pdf
Tratar de entender el método y las fórmulas que se usan es difícil, afortunadamente el cliente ya tenía una implementación elegante de dichas fórmulas en el Script MDX usando la sentencia SCOPE y en solo unas líneas implementaron la parte más compleja de las fórmulas. Por lo tanto me enfoqué en el tema de performance en lugar de la implementación de las fórmulas.
Después de un par de horas encontré que la fórmula estaba usando calculas cell by cell en logar de cálculos por bloque, la causa era un operador (el operador “^”) y después de un pequeño cambio (multiplicar la métrica por si misma) el query mejoró de aproximadamente 30 segundos a 1 segundo.
Puedes encontrar el detalle acerca de operadores que soportan cálculos basados en bloque en el documento “Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services” encontrado en http://www.microsoft.com/en-us/download/details.aspx?id=661, también puedes encontrar algunas recomendaciones para mejorar performance de queries en http://msdn.microsoft.com/en-us/library/bb934106.aspx
El enfoque de éste blog es en cómo podemos detectar los casos en donde el problema es cálculos Cell by Cell.
Tenemos 2 herramientas que usaremos para éste propósito, una es el SQL Server Profiler y la otra es el Performance Monitor, el orden recomendado es identificar primero que el problema está en el Formula Engine y después confirmar con el Performance Monitor que la causa de que el Formula Engine esté lento son cálculos Cell By Cell.
Aquí está la metodología recomendada, esto no es nuevo, pero no he encontrado una buena referencia que cubra éstos pasos.
Paso 1. Identificar si el cuello de botella está en el Formula Engine.
Utilizar el profiler para capturar la ejecución de tu query, es más fácil si haces esto en un ambiente aislado pero si no es posible, puedes filtrar los eventos en tu query por el ConnectionId o alguna otra columna que identifique todos los eventos de tu query.
Necesitas sumar la duración de cada evento QuerySubcube y restar ese total de la duración mostrada en el evento QueryEnd. En éste ejemplo puedes ver que la mayoría de los subcubos tomó 0 ms, algunos tomaron 16ms y otro que no se ve en la imagen tomó alrededor de 110 ms. El total del query fue 3,953 ms, eso significa que Analysis Services utilizó 142 en el Storage Engine y la diferencia de 3811 se utilizó en el Formula Engine.
En conclusión, en el caso anterior tenemos un cuello de botella en el Formula Engine Bottleneck, revisemos si en éste caso es causado por cálculos cell by cell.
El procedimiento anterior puede no escalar tan bien cuando tienes cientos de subcubos o muchos queries utilizando cálculos Cell by Cell en el mismo trace, en esos casos el siguiente procedimiento puede ser más apropiado:
Paso 2. Identificar si el problema es causado por cálculos Cell by Cell
Puedes utilizar el Performance Monitor para identificar si el problema son cálculos Cell By Cell. Para ser exitoso en ésta tarea es mucho mejor que ejecutes el query en un ambiente aislado porque tenemos un contador con el total de cálculos de celdas para toda la instancia y no hay forma de separarlo por query en el Performance Monitor. La imagen de abajo muestra cómo se ve el ejemplo nuestro.
En la imagen puedes ver que el contador usado es “MSOLAP&InstanceName:MDX \ Total Cells Calculated” y el valor inicial es 0 y el valor después de ejecutar el query es superior a 1 millón.
Los resultados anteriores fueron después de reiniciar la instancia y una sola ejecución del query generó más de un millón de celdas calculadas. Si ésta es la primera vez que haces esto no sabes si 1 millón es muy alto o es bajo. Puedo asegurarte que para éste query es un número bastante alto.
Paso 3. Modificar tu query
Modificar el query utilizando las recomendaciones en los links mencionados anteriormente hasta que dejes de ver el gran incremento en número de celdas calculadas para tu query.
En éste caso particular el Script MDX estaba usando algunas expresiones del tipo:
Measures.Measure1 ^ 2
Cambiamos la formula a algo como:
Measures.Measure1 * Measures.Measure1
Después de que la formula fue cambiada obtuvimos los siguientes resultados.
Puedes ver que el total del query es 156 ms, considerando que el Storage Engine debió usar el mismo tiempo, 132 ms, significa que el Formula Engine tomó aproximadamente 24 ms.
En el Performance Monitor podemos ver que el total de celdas calculadas fue 41. El query fue ejecutado después de que se reinició la instancia.
Comentarios Finales
Esto es solo el procedimiento básico para encontrar si los cálculos cell by cell están causando problemas en tu ambiente. Las modificaciones al query para evitar cálculos cell by cell puede ser fácil algunas veces, como en éste caso real, o muy difícil en otros escenarios.
Espero esto sea útil para que puedan empezar a solucionar sus propios casos.
In one of my regular customers, they are begging the SQL Server 2012 Migration, at the installation process for a SQL Server 2012 Instance on a Windows Server 2008R2 Cluster, they found an error while adding a node to de SQL Instance.
This error didn’t allow them to go forward to add the node on the SQL Instance, there are a few posible causes to check.
In this case we checked the first and second probable causes, but the error still appear.
After that we checked the third probable cause and we observed that the IP was been resolved to the NETBIOS name and not to the FQDN name, so we fix that problem, and we retried the add node process to the SQL Instance, this time was successful.
En uno de mis clientes regulares, están iniciando la migración hacia SQL Server 2012, al momento de la Instalación de una Instancia de SQL Server 2012 sobre un Windows Cluster de Windows Server 2008R2, se presentó el siguiente error durante el proceso de añadir un nodo a la Instancia de SQL.
Este error no permite seguir con el procedimiento de añadir un nodo a la Instancia de SQL en el Cluster, hay un par de motivos por el cual este error puede aparecer:
En este caso se revisó la primera y segunda posible causa, sin embargo el error todavía seguía apareciendo.
Por tanto se revisó la tercera causa y se pudo observar que las respuestas de la resolución de nombre devolvían el nombre NETBIOS y no el nombre FQDN, luego de resolver este problema, se reintento el proceso de añadir nodo a la Instancia de SQL en el cluster de forma satisfactoria.
